Here at Vena, we love Excel and we know you do too. So we partnered with Excel MVP Punra Duggirala, also known as Chandoo, who is passionate about data analytics and visualization. Chandoo has developed a platform for sharing knowledge about Microsoft Excel, Power BI and new ways of presenting data. We also partnered with long-time Excel MVP Liam Bastick, who is the Director of SumProduct and also a chartered accountant and chartered management accountant specializing in financial modeling and strategic advice. Both of these Excel experts offer their latest best practices, tips and tricks around Excel.
When you work with Excel on a daily basis, there's always room to grow and learn more. Read on for our top three Excel best practices for building effective spreadsheets to save you time and keep you organized.
The CRaFT methodology comes from Liam Bastick and refers to the four key principles for building effective spreadsheets. CRaFT is actually an acronym and each letter represents a specific principle to follow while building your spreadsheets.
The "C" refers to consistency, and as Liam points out, it means that when spreadsheets are built with consistency in mind, they should be incorporating pre-defined formats and styles in a coherent way. So for example, this means ensuring all formulas are copied uniformly across ranges and also presenting periodic data and formulas in columns consistently, establishing similar structures for similar spreadsheets.
Consistency allows for fewer mistakes, promotes model integrity and makes it easier for developers (and users) to understand models quickly. This also makes it easier when you are collaborating with other team members and need to generate and update reports quickly.
The "R" refers to robustness, essentially that models need to be free from errors, mathematically accurate and readily auditable. Liam points out that this principle is key since it keeps models simple, consistent and transparent for you and your team. You and your team can work efficiently since there are established formulas and no hidden macros.
The "F" refers to flexibility and reminds users that all models should have the structure to provide invaluable "what if?" scenario analysis. Keeping spreadsheets flexible saves you and your team valuable time later if an analysis needs to be conducted.
Lastly, the "T" refers to arguably one of the most important principles--transparency. Spreadsheets need to be clear, concise and cohesive so that others can understand them when working collaboratively. According to Liam, modelers should create their calculations and output in a user-friendly way.
According to Excel wizard Chandoo, leveraging keyboard and mouse shortcuts while using Excel is key to saving time and minimizing stress (especially when working with large data spreadsheets). In fact, he highlights how getting around spreadsheets easily and quickly through keyboard and mouse shortcuts allows you to keep data prep and clean up in mind when designing your spreadsheet. The easier a spreadsheet is to navigate using shortcuts is really dependent on how you set up the spreadsheet from the first step.
That's why it's important to put the time and effort into designing your spreadsheet effectively first (see step 1 above). As finance and operations professionals (and even outside of that), you're probably leveraging Excel and navigating through different sets of data. Leveraging shortcuts not only saves you time, it helps you get around your spreadsheet in a smarter and more efficient way.
For example, to navigate through your spreadsheets quickly and easily and to get to a specific point within a spreadsheet, select the end key and any arrow on your keyboard to bring you to the last cell of a sequence in the arrow's pointing direction.
Telling the right story with your data is key to informing your teams on the latest business updates and making changes if and when necessary. We've highlighted how going beyond the numbers to shape a powerful narrative with your data is an important skill for finance leaders to master. According to Liam, it's important to have a narrative in mind while building out your spreadsheets in Excel. In Liam's experience, he has found that building a narrative into your spreadsheets and data has been shown to increase comprehension, interest, engagement, recall and it also eases the comprehension of data when it's been reviewed.
As Liam says, "The importance of good storytelling is that it's not all about one thing." This is especially true when you're working with budgets and organizing them through Excel reporting. Liam also argues that when it comes to any kind of report building, "understanding one-offs is critical to understanding business performance and setting financial statements. You've got to maintain a register of the one-off items, both positive and negative, for the current and prior periods to see the actual trends." This is key to managing both on a day-to-day level as well as strategically for the long term for any team.
Whether you're a finance professional or business leader, you've probably leveraged Excel in some capacity. Maybe you need to build a budget report, create a financial forecast or just organize your team's vacation time. Excel is so powerful for your day to day and Vena is the only Complete Planning platform that combines enterprise-class functionality with the comfort, flexibility and power of Excel.
Our Growth Engine technology features a native Excel interface, so you can use the tool you already know to do things you've only imagined. The point being is that Excel is an integral part of the day to day for most teams and it's a tool that helps keep us organized and working efficiently.