In the last Controller blog, I discussed how to share workbooks to enable our team to more effectively collaborate. Now that we’ve created our shared workbook, let’s look at some of the advanced formatting in Excel.
As a Controller, I spent a decent amount of time researching how to use advanced functionality to accomplish some of my more menial tasks automatically or create more dynamic reports. Later in the series, I’ll focus on VLOOKUP, HLOOKUP, and Goal-Seek but this 2-part series will start with Conditional Formatting, Sparklines, and Quick Analysis.
Let’s start with Conditional Formatting. The Finance department is always looking for ways to make the information we provide to our internal users more interesting, more dynamic, and easier to read. With the use of Icon Sets in Conditional formatting, you can provide a nice graphic for your users to understand, at a glance, the key financial indicators for your business.
Let’s create a scorecard. First create a formula on which to base the Icon set. The formula is set in the column to the right of the Delta.
To create the rule, place your cursor in the cell(s) to apply the format. In the Home tab \ Style section go to the Conditional Formatting option. Select Icon sets and select the set to use for your scorecard.
Excel will automatically populate the conditional format with the default. To update it for your requirements, go back to the Home tab \ Style section go to the Conditional Formatting option and select Manage Rules.
Once the Manager opens, highlight the rule to edit and select Edit Rule.
The default in this case is Percent. This option, however, is somewhat misleading. For Conditional Formatting, the Percent option takes the percent of all the values, not the value of the percent itself. To get the correct result, change the Type to Number. Finally, to hide the base (percent), select the Show Icon Only option.
To complete the formatting, highlight the updated cell, select Format Painter and highlight the remaining cells.
With this very simple excel formatting, we’ve created a scorecard that quickly shows how each of the company’s key indicators are performing.
In the next of this series, we’ll look at Sparklines and how we can provide more dimension to our financial reporting.