After (almost) 40 years, there is a reason why FP&A and accounting professionals continue to use Excel. It works.
But what happens when your company grows and Excel is used for much more than just number crunching? Despite popular thinking that Excel can’t work for enterprises and beyond, you still don’t need to abandon the Excel ship.
Since the creation of Excel, software—particularly FP&A software—has continuously advanced. However, few —if any—have been able to compete with Excel’s data storing and mathematical processing capabilities. From financial forecasting and financial modeling to budgeting templates and month-end closing, reporting that would have taken hours can be presented in minutes via Excel spreadsheets — as long as you know what you are doing.
To get the most out of your spreadsheets, your accounting and FP&A teams should take a lesson from this article on Excel for finance to guarantee your staff has all the Excel abilities they need at their fingertips. Then, as your enterprise needs grow, you can integrate Excel with an automated solution.
The bottom line? Excel can still be used for finance. Simply discover how to make it better.
1. Conditional Formatting
The coolest Excel tool, conditional formatting, is located on the home ribbon and offers an almost limitless range of options.
This function allows you to format cells based on the conditions you set.
For instance, a formula can be used to assign that all cells inside a range that include a number greater than 45 should be red, and all cells that contain a value lower than 45 should be green.
It’s as simple as:
- Choosing the New Rule option from the Conditional Formatting dropdown menu.
- Then selecting “Use a formula to determine which cells to format.”
- Then, in the “Format values where this formula is true:” box, enter the condition’s formula, and voila!
And, here’s the beauty of conditional formatting — if you change the variables of any green cells to equal 46, the cell will automatically turn red.
How it works in the world of FP&A
Conditional formatting makes it easier for accounting and finance professionals to identify any warning signs. Recall the simple example of the red and green cells. Overdue bills could be in cells with values greater than 45. So, any cell with a red highlight lets you quickly identify which invoices are past due. You’ll also notice that any green cells are less than 45 days after the invoice date. Conditional formatting can be used as a quick glance or a comprehensive deep dive when balancing your books and general ledger.
Conditional formatting allows for a lot of ingenuity on the part of the user and how they choose to implement formatting. So if you need to take a deep dive into your sales data, that’s easy, too!
2. Macros
Now that we have already met the coolest function in Microsoft Excel, we can move on to meet the most powerful function in Excel. With macros, you can automate almost anything in Excel with Visual Basic for Applications (VBA). You can start by taking a quick course to learn VBA, which is a pretty simple programming language to master. In our opinion, VBA is a must-have for finance functionaries.
Don’t worry, though; even if you have no experience with programming, you can still record macros to perform some simple tasks, and you can even create buttons to run these macros and place them on your Quick Access Toolbar (QAT).
Macros are a simple way to automate repetitive tasks. You simply record the steps and save. With your predefined macros, you can click a single button to execute those pesky repetitive tasks. Here’s how:
- Using the Developer Tab: Make the Developer tab and ribbon visible. If you do not see the Developer, right-click any ribbon, and then select Customize the Ribbon. Next, check the Developer item in the right pane of the dialog box. This ribbon has various buttons for working with macros. When you’re ready, click the Record Macro button on the Developer ribbon. This turns the recorder on. It will record everything you do until you turn the recorder off.
- When you click the Record Macro button, you will see the dialog box — fill it out and click OK. You can give the macro a meaningful name (no spaces), and you can specify where it should be stored. This will make the macro available at all times.
- Saving Your File: Starting in Excel 2007, macro files must have the .xlsm extension (“m” for macro). You aren’t allowed to save such a file as an .xlsx file.
- Storing Your Macros: There is a particular file, Personal.xlsb, that’s called your Personal Macro Workbook. This workbook is where you will keep your recorded macros. This file opens automatically as a hidden file whenever you open Excel. Therefore, all of its macros are always available in all your Excel files.
- Writing and Editing Macros: If you want to write your macros, or if you’re going to look at recorded macros, you need to go to the Visual Basic Editor. You can do this from the Visual Basic button on the Developer tab or, more efficiently, with the Alt+F11 keyboard shortcut.
How it works in the world of FP&A
FP&A professionals use automated macros for financial modeling. Defining your problem is the first step in using VBA to create macros for your model. Once you know the insight you need from the model — for example, if you want to forecast sales for Q4 — you can easily structure your logic, input your variables, define your output, and then run the macro. Model complete.
