One of the nice features of Excel Tables is the banded row formatting, which makes it easier to read and scan your data.
Unfortunately Excel Tables aren’t efficient with large data sets (over 100k rows), but we can replicate the banded rows with Conditional Formatting, and we can toggle it on and off at the click of a button like this:
Download the workbook
Toggle Excel Conditional Formatting On/Off
First of all we need a check box or radio button form control. You’ll find the form controls on the Developer tab > Insert:
Note: if you don’t see the developer tab click here for instructions on how to enable the Developer Tab.
Draw the check box form control onto your worksheet.
To edit the default text hold CTRL and left-click the form control; this will display the pull handles to adjust the size, and you can click inside the box to edit the text.
It is a floating object so you can move it around by left clicking and dragging while the pull handles are visible.
Set the Cell Link: Right-click > Format control
Choose a cell to house the status of the check box. Mine is in cell I3:
When the check box is checked the cell link, cell I3, will contain TRUE, and when it’s unchecked it contains FALSE. We use this in our Conditional Formatting formula.
Set Banded Rows with Conditional Formatting
To set the conditional formatting we first select the range of cells we want to apply the formatting to. In my case it’s A6:G48.
Tip: I don’t recommend you format the whole row/column as this will just bloat your file. Better to only format the rows and columns that contain data.
Then on the Home tab > Conditional Formatting > New Rule…:
In the Edit Formatting Rule dialog box (shown below), choose ‘Use a formula to determine which cells to format’:
And in the formula field we use this formula:
In English the formula reads:
Count the number of visible rows in column A (that’s the SUBTOTAL(3… part, where 3 is the COUNTA function for SUBTOTAL), divide the count SUBTOTAL returns by 2, and return the remainder, (that’s the MOD part), which will always evaluate to either 1 or zero AND check to see if cell I3 contains TRUE
Conditional Formatting formulas must always evaluate to either TRUE or FALSE, or a 1 or 0, which is the numeric equivalent of TRUE and FALSE. A TRUE or 1 will apply the format, and FALSE or 0 hides the format.
To summarise the formula; If the MOD(SUBTOTAL formula evaluates to 1 AND $I$3=TRUE then Excel will apply the format.
Let’s look at the key components of the formula.
The SUBTOTAL function in the formula allows us to apply filters to the table and have the banding automatically adjust so that only the visible/unfiltered cells are banded. This is because SUBTOTAL can ignore hidden rows. Learn more about the SUBTOTAL function here.
We use absolute referencing to tell Excel which cells to reference as the conditional formatting moves through each row. This post explains how conditional formatting formulas work.
The MOD Function:
We use SUBTOTAL to return the number argument for MOD, and the divisor is 2.
You can see it below where I’ve entered the MOD(SUBTOTAL part of the formula in column I:
Lastly, we check the value in cell I3 to see if the check box is checked (TRUE), or not checked (FALSE).
Tip 1: Column A must not have any blank cells, otherwise the SUBTOTAL count will be wrong. You can choose a different column for SUBTOTAL, just so long as there are no empty cells.
Tip 2: If you don’t need to filter your data then you can simplify the formula to this:
Tip 3: if you don’t want to toggle the banding on and off then you can omit the second logical test like so:
Or to allow for filtering:
Ok, that’s the formula done, now you can click ‘Format’ to set the fill colour:
You can use the check box technique with other conditional formats. Simply add the AND wrapper like so:
=AND(your conditional format rule, check box cell link = TRUE)
You can also modify the MOD formula to band groups of rows like this:
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.