Contents
If you have a large list sorted by some column, then it would be nice to automatically separate the resulting row sets with separating horizontal lines for clarity:
In the example above, these are lines between countries, but, in general, between any repeated items in the same column. Let’s look at a few ways to implement this.
Method 1. Simple
The fastest way to do this is very easy with conditional formatting, which will draw the bottom border of the cells if the content of the cell in column A is not equal to the content of the next cell in the same column. Select all cells in the table except for the header and select The main command tab Conditional Formatting – Create Rule (Home – Conditional Formatting — New Rule). Select rule type Use a formula to determine which cells to format (Use formula to determine which cells to format) and enter the following formula in the field:
Pay attention to the dollars in the addresses to fix the column letters, but not the row numbers, because. we only compare countries in column A. There should be no spaces in the formula.
Click the button Framework (Format) and in the opened window on the tab Border (Borders) turn on the line of the desired color on the bottom border. After clicking on OK our rule will work and horizontal dashing lines will appear between the groups of lines
Method 2. With filter support for numbers and dates
A small but very noticeable disadvantage of the first method is that such borders will not always work correctly when filtering the list by other columns. So, for example, if we filter our table by dates (January only), then the lines will no longer be visible between all countries, as before:
In this case, you can get out using the function SUBTOTALS (SUBTOTAL), which can perform various mathematical operations (sum, average, count, etc.), but “see” only filtered cells. For example, let’s sort our table by the last column with the date and draw a dividing line between the days. In conditional formatting, you will have to create a rule similar to the first method, but do not use direct links in comparing cells D2 and D3, but enclose them as arguments in the SUBTOTAL function:
The first argument of the function (number 109) is the summation opcode. In fact, we do not add anything here and do, in fact, a stupid operation like SUM (D2), which, of course, is equal to D2. But this function differs from SUM precisely in that it performs actions only on visible cells, i.e. and the cells remaining after the filter on the screen will be compared, which is what we wanted.
Method 3. With filter support for any data
As you can easily see, the second method also has a drawback: the sum function can only be applied to numbers or dates (which are also numbers in Excel), but not to text. That is, if we want to draw a line between countries, as in the first method, but so that it is displayed correctly after filtering, then we will have to use a much more complicated path. Select the entire table again except for the header, create a new rule based on the formula and enter the following construction in the validation field:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
In the English version it will be:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
By clicking on the button Framework (Format) set a border with a red line on top and click OK. The resulting division by country will work correctly even after filtering, for example, by date:
- Highlight Dates and Times with Conditional Formatting
- How Excel actually works with dates and times
- How to use conditional formatting to highlight cells by condition in Excel