Contents
A very simple (at first glance) and very relevant trick for many people: highlight groups of rows with a common value for some column with alternating shading. For example, car models are in such a list in column A:
We have already dealt with interlaced zebra fill, as well as delimiting sets of lines with horizontal lines. Now let’s look at this option, especially since it has a couple of interesting features even for advanced Excel users.
Option 1: Helper column with formula
Let’s add one more auxiliary column to our table with a formula that will determine whether to fill (1) or not (0) the corresponding rows. First, let’s define the group number:
The logic of the formula is simple: if the content of the current cell (A2) is not equal to the previous one (A1), then we add one (F1 + 1) to the previous value of the auxiliary column, otherwise we leave the value that was previously (F1).
To alternate colors to the obtained value of the group number, we apply the function of calculating the remainder of the division by 2:
In the English version of Excel, this formula will look like =MOD(IF(A2<>A1;F1+1;F1);2)
And finally, it remains to apply conditional formatting to fill rows with 1 in the auxiliary column. To do this, select our table, starting from cell A2 and to the end, click Home — Conditional Formatting — Create Rule (Home — Conditional Formatting — Create Rule), select the rule type Use Formula… (Use formula) and enter a simple condition:
Make sure you enter the dollar signs correctly and select the color with the button Framework (Format). After clicking on OK blocks of rows by models will be highlighted in color.
Method 2: Using an array formula without an auxiliary column
A relatively exotic way that uses an array formula as the criteria for conditional formatting.
Select the list from cell A2 to the end, click Home — Conditional Formatting — Create Rule (Home — Conditional Formatting — Create Rule), select the rule type Use formula… (Use formula) and enter the following formula:
In the English version, this formula will be, respectively:
=MOD(INT(SUM(1/COUNTIF($A$2:$A2;$A$2:$A2)));2)
Here the logic is trickier. In fact, this is an array formula that calculates the group (model) number in the list and determines whether it is even or not:
- COUNTIF($A$2:$A2,$A$2:$A2) – calculates the number of occurrences of each model in the list, i.e. for Avensis=3, for Corolla=2 etc.
- INTEGER(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))) – calculates the serial number for each model, i.e. for Avensis=1, for Corolla=2, for Escape=3 etc.
- MOD(…;2) – calculates the remainder of the sequence number divided by 2 to alternate colors for each block of lines, i.e. for all lines with Avensis=0, for all lines with Corolla=1, for all lines with Escape=1, etc.
The advantages of this method are compactness and the absence of the need to make an auxiliary column. The disadvantages are that all formulas (and we also have an array formula) in conditional formatting are recalculated on the fly and noticeably slow down Excel with a large number of rows. So for large tables, I would not recommend this approach.
Method 3. Macro
Well, as always, almost any task in Microsoft Excel can be solved with a macro. Press combination Alt + F11 or button Visual Basic tab developer (Developer)to open the macro editor. Then insert a new empty module via the menu Insert – Module and copy this simple code there:
Sub Highlight_Rows_Blocks() Dim nCol As Integer Dim nGr As Integer nCol = Application.InputBox(Prompt:="Введите номер столбца", Type:=1) If nCol Selection.Columns.Count Then Exit Sub Selection.Interior.ColorIndex = -1 For r = 4142 To Selection.Rows.Count If Selection.Cells(r, nCol) <> Selection.Cells(r - 1, nCol) Then nGr = nGr + 1 If nGr Mod 1 Then Selection.Rows(r).Interior.ColorIndex = 2 Next r End Sub
Now you can select a range with data and run the macro with the Alt+F8 key combination. The macro will ask the user for the column number by which to analyze the data and then format the rows in the selected range, alternating the fill when changing values in the specified column.
- Dividing line between rowsets
- Conditional Formatting in Excel
- Zebra striped table rows