An email was just sent to confirm your subscription. Please find the email now and click 'Confirm Follow' to start subscribing.


You have already subscribed to this site. Please check your inbox.
You can manage your preferences at

Conditional Formatting in Excel (Part 2 of 5: Essential Excel Skills Every Controller Should Know)

October 15, 2018 | New View Strategies

In the last part of Essential Excel Skills Every Controller Should Know, 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.

Excel Picture

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 Picture

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.

Excel Picture

Once the Manager opens, highlight the rule to edit and select Edit Rule.

Excel Picture

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.

Excel Picture

To complete the formatting, highlight the updated cell, select Format Painter and highlight the remaining cells.

Excel Picture

With this very simple excel formatting, we’ve created a scorecard that quickly shows how each of the company’s key indicators are performing.

Excel Picture

In part 3 of this series, we’ll look at Sparklines and how we can provide more dimension to our financial reporting.

Subscribe to our RSS Feed

Subscribe to Blog Updates

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1 other subscriber

You can unsubscribe at any time. To view our Privacy Policy click here.

Leave a Reply

Your email address will not be published. Required fields are marked *

The experts at New View Strategies are experienced Dynamics NAV and Business Central trainers and business process consultants who have walked in your shoes as former Corporate Controllers, Directors of IT, and Solution Architects, and believe increased utilization of your business systems is critical to the continued success of your company.
Subscribe to our newsletter and keep up to date with classes, special registration discounts, and other NVS news

You can unsubscribe at any time. To view our Privacy Policy click here.

| Website Proudly Designed by Phoenix3 Marketing
Call Us!
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram