Presenting data in a format that is easy to read in Excel can be a challenge, in particular when you are dealing with larger arrays of data. In this example we will discuss how we can use the conditional formatting functionally in Excel to add dynamic lines within an area to improve the presentation and to make it easier for a user to read and absorb the data.
What is a ‘dynamic’ line?
In the picture below the pink lines highlighting every second line have not been inserted manually but are coded using conditional formatting. The advantage of using conditional formatting for this application is that it does not impact the layer of Excel that a user will manipulate. Even if a user would copy paste data or shift the data around within the table the presentation will still be preserved.
Picture 1. Dynamic lines coded with conditional formatting
Use Conditional Formatting to generate dynamic lines
The conditional formatting tool in Excel is very powerful, in particular if you can master the ‘Formula Is’ functionality. To implement the dynamic lines you need to select the whole range of data – from ‘Base Case’ in the top left corner to “15%” in the lower right hand corner.
Click Format -> Conditional formatting and insert the formula:
=MOD(ROW($D31),2)=1
Choose the applicable format for the dynamic lines by clicking Format.
Picture 2: Conditional Formatting using =MOD(ROW..)..)
Customize the dynamic lines to only highlight every third line
To highlight the advantages of using dynamic lines instead of manual formatting we will show you how to update the conditional formatting to only highlight every third line as in the image below:
Picture 3: Conditional formatting used to highlight every third row
The only change you need to do to the conditional formatting is to update the “2” in the “Formula Is” section to a “3”.
Picture 4: Conditional formatting to highlight every third row.