If your data is grouped in Microsoft Excel, try displaying a border between the groups. Find out how to make your Excel tables more readable.
A large set of Microsoft Excel data without proper formatting is difficult to read. Table objects automatically apply alternate colors from row to row, which is great for keeping tabs on a specific record, but if you’re working with grouped Excel data, alternating rows aren’t particularly useful. I will show you how to use conditional formatting rule in Excel to apply bright color border to divide groups.
I’m using Microsoft 365 on a 64-bit Windows 10 system, but you can use older versions up to 2007. For your convenience, you can download the .xlsx demo file. Excel for the web will show the conditional format, but you can’t apply a formatting rule yet.
TO SEE: Software Installation Policy (TechRepublic Premium)
How to Convert Excel Data to a Table Object
The easiest way to make Excel data more readable is to convert the data to a Table object (Figure A).
To create an Excel Table object, click anywhere in the data and press Ctrl + T. In the dialog that appears, make sure the range is correct and check or uncheck the My table contains options. headers accordingly.
You are not stuck with the default formatting. You can quickly reformat the entire Excel table in two ways.
- On the Home tab, click the Format as Table drop-down list in the Styles group and select one of the styles from the resulting drop-down list (Figure B).
- On the Table Design contextual tab, choose one of the styles from the Table Styles group. The offers are the same in both options.
Knowing that you can make this quick change is enough; we are not going to assign a new style at this time. What neither of these styles offer is a way to distinguish between groups.
How to display a conditional row in an Excel sheet
When you have a sheet loaded with dozens of rows and several different groups of data, not only will the groups be harder to tell apart, but errors are almost certain.
A quick fix is to apply an Excel conditional formatting rule that will show a red line between groups of data. You can display this red line along the top or bottom border of cells. If you go low, you’ll end up with a line at the bottom of your chart, which you might not want. If the table displays a Total row at the bottom, the red border will replace the double row in the table between the data and the Total row. I’ll show you how to apply the top border rule and mention the bottom border instructions when they are different.
You must sort Excel data by group column for this conditional format to work. With a different sort setting, the lines have no meaning and will add to the noise of the sheet. Subsequently, this technique is best applied to data that is not often sorted.
Now let’s add this red border.
- Select the dataset, but do not include the header row (B3:F13).
- On the Home tab, click Conditional Formatting in the Styles group and select New Rule from the drop-down list.
- In the resulting dialog box, choose Use formula to determine which cells to format in the upper pane.
- In the lower pane, enter the following formula: =$B2$B3
- You want to reference the header cell. This is what displays the border at the top of the cells. Enter =$B3$B4 instead if you prefer bottom border. The $ character is mandatory: if you omit it, the rule will not work as expected.
- Click Format.
- Click the Border tab.
- Select the solid line type at the bottom of the Style list. Unfortunately, you cannot change the line thickness.
- Choose red from the Color drop-down list.
- In the Sample Border box, click the top line (Figure C). If you want the bottom border, click it in the sample area.
- Click OK twice to return to the sheet (Figure D).
You can change the border color and style, but as I mentioned, you can’t increase the weight. Don’t forget to sort if you add new records or modify the data of an existing record.
Use Excel’s conditional formatting feature to display a border between groups of data. In a future article, I’ll show you how to use a Fill property to distinguish between groups.