How to add a conditional format that highlights groups in Excel

Learn how to add a marker to display groups in your Microsoft Excel data using a helper column and Excel’s conditional formatting.

The article, Use a border to distinguish groups more easily in Microsoft Excel, shows how to use a conditional formatting rule to apply a red border between groups in an Excel sheet. It’s not as effective as you’d like because the bezel is so thin. In this article, we will use a conditional formatting to apply a fill color to each row in the same group. This technique has a limitation that I will explain at the end of the article.

SEE: Software Installation Policy (Tech Republic Premium)

I use Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. For your convenience, you can: download the demonstration .xlsx and .xls files† Excel for the web supports rules in the conditional formatting feature, but you are limited to the formats you can choose.

How to group the data in Excel

Because we format groups, the data must be grouped before you begin. If your data is already sorted by the grouping values, you can skip this step. The demonstration file isn’t sorted yet, so let’s get started. We want to group by Personnel Values. Click in a cell in the Personnel column. In the Editing group on the Home tab, choose A to Z from the Sort & Filter drop-down list. As a result, the sales and commissions for each staff are grouped together (Image A†

Image A

If the data is already formatted with colored bars, remove the formatting. This is easily done by selecting the data, in this case B3:F13, and choosing No Fill from the Fill Color drop-down list in the Font group in the home table. However, if you are working with a table object, choose None from the gallery in the Table Styles group on the Table Design contextual tab.

Grouping the data by sorting and deleting existing formats is necessary for this technique to work. Now let’s move on to the helper function.

How to Create a Helper Function in Excel

Once the data is in order, you are ready to add the conditional formatting. This is one of the few situations where a single line just can’t get the job done. By the way, with the helper column in place, you can see how it works.

Before we do anything else, let’s discuss what the helper function will do. In a nutshell, it returns the same value for every record in a group, as long as the current Staff value is equal to the value in the cell above it. If not equal, Excel returns the last value plus 1. Later, the conditional rule will highlight only those records where the helper function’s result is odd. You can just as easily mark the even numbered groups. I chose odd because the first group is 1 – an odd number and marking the first group looks better in my opinion.

Let’s start. Using the sheet in Image Ain G3 enter the following function:

=IF(D3=D2,H2,H2+1)

and copy it to the other cells. If you use a table object, Excel automatically expands the table to include this new column if you add this function to column G; you don’t want that to happen. As you can see in Figure Bthe helper value is incremented by 1 each time the value in the Personnel column changes.

Figure B

It’s worth noting that other expressions will work, but I chose this one because the sequential numbers are a visual clue of what they represent. With the helper column in place, it’s time to add the conditional formatting rule.

An Excel Conditional Formatting Rule

Finally, it’s time to create the conditional formatting rule that fills alternating groups in the Excel table:

1. Select the data, B3:F13. (Do not include the column of consecutive values ​​in column H.)
2. On the Home tab, click the Conditional Formatting drop-down list (Styles group) and choose New Rule.
3. In the resulting dialog box, click the option Use a formula to determine which cells to format.
4. Enter the function ISODD ($H3) in the formula control in the lower pane.
5. Click Format and then click the Fill tab.
6. Pick a color – I chose light blue. When choosing, make sure that it does not clash with the background of the leaf, which will serve as an alternating color.
7. Click OK to see the function and format in Figure C†
8. Click OK to return to the Excel sheet.

Figure C

Figure D shows the applied format.

Figure D

Note that you must re-sort when you change the records by changing the personnel value in an existing record or by adding a new record. When adding new records, you need to expand the expression in the helper column.

At this point you should be done, as white is an alternate color. However, you may want to apply a second color.

Add a different color in Excel

At this point, Excel only applies one fill color. The even-numbered groups remain white – or whatever color the leaf background is. Repeat the steps above to add another color instead to fill the unformatted groups. Enter in step 4. in ISEVEN($H3), choose a second color in step 6. As you can see in Figure EI chose a light green, and Figure F shows the results.

Figure E

Figure F

To me this technique seems a bit unfinished because the user has to expand the helper column as they add new records. In addition, the conditional formatting rule does not allow you to choose a different group. To do this, you need to update the function in the helper column. However, if you want marked groups, this is an easy way to get them.