How to Use Conditional Formatting in Excel

Managing finances can be easier when you have a dashboard to track expenses. In this guide, we’ll walk through creating a simple expenditure dashboard in Excel using only conditional formatting. This approach is beginner-friendly and avoids complex formulas or macros, highlighting the power of conditional formatting to make your data visually intuitive.

Step 1: Plan Your Dashboard Layout

Before jumping into the spreadsheet, you will need to identify the data you will be needing as well as the key insights you will derive from your dashboard:

1. Identify the data you’ll need: To quickly determine the data you need, you have to put down your variables or what will turn out to be your column headers or titles in your spreadsheet; such as outlined below.
   • Date of expenditure
   • Expense category (e.g., Food, Rent, Utilities)
   • Amount spent
   • Budgeted amount
2. Determine the key insights: By key insights we refer to the inference or conclusions you want to derive from your dashboard; it could simply be what you want your dashboard to show you. It could be that you want to know when you are spending over budget (Over spending) or spending under budget (under spending).

Step 2: Set Up Your Data Table

• Open Excel
• Create your data table columns. The column headings are derived from the data you derived in Step 1; As outlined below, just that this time we need to add one extra column (Difference) to help us understand the Difference between the amount budgeted and the amount spent.
  • Date
  • Category
  • Amount Spent
  • Budget
  • Difference (calculated as Budget – Amount Spent)

Step 3: Apply Conditional Formatting

• Select the “Difference” column.
• Go to Conditional Formatting > New Rule > Format all cells based on their values.
• Choose a red fill to signal overspending.

Notice that colour Scale the -1,500 have a deeper colour indicating over spending and the colour scale went from darker red to light yellow where we had a positive 2,000? Conditional formatting gives us a colour-based visualisation, making it easy to identify trends quickly.

Use a Colour Scale for Spending Levels

• Select the “Amount Spent” column.
• Apply a Colour Scale:
  • Green for lower amounts (5000).
  • Yellow for medium amounts (e.g., between 5000-30000).
  • Red for high amounts (e.g., over 30000).

Leave the “Amount Spent” column highlighted and repeat the process, from number 1 to number 5; but this time, you want to achieve this goal – Yellow for medium amounts (e.g., between 5000-30000).

To achieve this, when you click on “Conditional Formatting”, click on “Between” as seen in the figure below.

Enter the lower limit number (in this case 5000) and the upper limit number (in this case 30000), then click OK.

To achieving this goal – Red for high amounts (e.g., over 30000).

1. While the “Amount Spent” column is still highlighted, Click on Conditional Format, then click on “Highlight Cells Rules”, then click “Greater Than”
2. You have the “Greater than” box, then enter the 30000 in the space provided. Then Click OK.

At the end of Step 2 we will have a dashboard like the one below;

3. Visualize Budget Usage

• Select the “Amount Spent” and “Budget” columns.
• Add a Data Bar for each:
  • Choose colours that contrast (e.g., blue for budget, orange for spending).

Let’s see a step-by-step process for actualizing Step 3

1. Select the “Amount Spent” column.
2. Click on “Conditional Formatting”, then click on “Data Bars”, then select blue data bars for the “Amount Spent Column.

Once that is done, blue data bars are implemented in your column.

To add data bars on the Budget column, repeat the same process, just this time, select the orange data bars.

Congratulations, you have successfully finished and implemented Day one (1) of the 60 Days Excel Challenge.