Dashboard 101 – Conditional formatting in MS Excel

Conditional Formatting in MS Excel

We have already seen the power of MS Excel when it comes to analyzing data. But the results, if not presented well, will not be able to do justice to the effort that you put into analyzing the data. For any data analysis exercise it is extremely important to highlight certain trends in data which can add great value to the  presentation. When we talk about dashboards, their dynamic nature makes it even more important that vital information is not missed out amidst the process of visualizing numerous analyses.

Conditional formatting is a feature in MS Excel that, as the name suggests, allows the user to specify conditions under which certain trends/data of interest can be highlighted in a huge dataset. Thus, it is a feature that adds value to your presentation by enabling visual differentiation of certain part of the data from rest of the dataset.

Ok, so let us now see how it works.

MS Excel provides certain preset formats and conditions for conditional formatting that can be applied to ranges but we’ll focus on the part where we can apply our own rules (by writing down conditions) and define our own formats since this is what we’ll be using most frequently.

Before we start applying conditional formatting, there are certain points which we must know:

  1. Conditional formatting can be applied in the form of conditions with output as TRUE or FALSE on a cell range.
  2.  The conditions are specified as formulas by the user‒ if the condition is true for a cell in the range, conditional formatting will be applied, otherwise, the cell will retain its format.
  3. More than one rule can be applied on the same range of cells and their precedence order can be set by the user
  4. Since we are using formulas to create conditions, we can apply rules to entire rows/columns by using the relative referencing property of MS Excel

We have generated some random product wise quarterly sales data for this example— drug name and quarterly IMS sales data. Now let us apply conditional formatting to the dataset shown in the figure.

 

Now, we want to highlight the row for the product which has the maximum sales in Q4. As shown in the figure, we select the range and in the conditional formatting dialog, enter the formula:

=$K2 = Max ($K$2:$K$6)

This formula evaluates the specified condition for every cell in the selected range. So, for every cell in the selected range, the cell with the same row number in column K is equated against the maximum value in column K. Wherever this condition becomes TRUE, the specified formatting (chosen by the user) is applied to those cells.

So, in the scenario shown, the given condition is satisfied for cells in the last row of the selected range (since cell ‘K6′ has the maximum value in Q4). Hence, the row representing product G is highlighted.

An obvious question at this point may be, why go through all this trouble to highlight a row when you could have done it manually?

We know that a dashboard will have continuously changing data based upon the selections made by the user. Also, the data might be updated periodically if it is a report. Under such circumstances, it would not be feasible to change the formatting and highlight a different row every time you change the selections on the dashboard.

In such cases, conditional formatting comes to the rescue. The rules applied by you would ensure that even if the data changes, the correct set of cells are highlighted (wherever the condition evaluates to TRUE). So, to understand the importance of conditional formatting, if we manually assign a higher value to sales of product D in Q4, the row with product D would get highlighted (shown below).

This is just a very small example of conditional formatting. For a more robust example of conditional formatting follow the series on our SphInX pharma KPI reporting dashboard by clicking here in which we have applied multiple rules and demonstrated how conditional formatting can be used to make effective analysis.

Feel free to reach out to us at query@crabsheet.com in case you face any difficulties in understanding any aspect of conditional formatting and we would be happy to address your queries.

3 thoughts on “Dashboard 101 – Conditional formatting in MS Excel

    1. Keshava

      you can use the today() function and in if statement to achieve this.
      e.g. if(today() = some date,”print this”, “”)

      Reply

Have a question? just leave a comment and we will answer A.S.A.P