How do you format totals in Excel? Most users will include a line between the list of values and the total. Look at the image below, nothing wrong with that, right?
But, the key question is how would you apply this line? If you use the bottom border then you’re missing a trick.
Using a bottom border below the data may appear to be the most obvious solution. But what happens when you insert a new row between the data and the total? The border sticks to the data above, rather than the total.
Because of this, I find myself re-applying borders over and over.
Here is the key thing to understand about borders: you might think that each cell shares a border with the cells around, but this is not true. Each cell has it’s own 4 borders, therefore the bottom border on Cell B6, is not the same as the top border on Cell B7. Sure, they occupy the same space, but they are different.
If we apply a top border, rather than a bottom border, inserting a new row provides a different result.
Perfect, that’s exactly what we want.
What about the SUM function, why doesn’t that update too?
Borders are only half the issue here. The SUM function used in these screenshots does not update it’s range either – check out my post on relative named ranges to find out how to make the SUM function update too.
Making top border more accessible
In Excel, the bottom border icon is the default within the Home Ribbon.
Which means the top border requires two clicks, or if you’re a shortcut fan, there is always Alt, H, B, P (which is not particularly memorable). But there are other options to save some clicks.
Add top border to the Quick Access Toolbar (QAT)
The QAT has the advantage of always being visible, no matter which Ribbon is active, plus it is easily accessed with the ALT shortcut key.
Click on the drop-down arrow next to QAT, from the menu select More Commands…
Choose All Commands from the Choose commands from drop-down. Scroll down to Toggle Top Border, select it, click Add, then click OK.
The top border icon now appears in the QAT. Depending on its position, it can be accessed by pressing ALT, followed by its position number.
How to apply different border styles?
Here is another question, how do you feel about the default border style? Personally, I prefer to use a mid-grey, rather than black. Or what if your preference is a thicker line? That’s quite a few clicks. So, how about creating a macro, which does it all.
Sub GreyTopBorder() With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .Color = RGB(125, 125, 125) End With End Sub
The code above was initially from the macro recorder, which I adapted to include the color as an RGB code.
Include this code within your Personal Macrobook. Then add it to your QAT using the same process as above. Now you can apply a mid-grey border at the click of a button.
What about borders below headers?
For headers, I try to use single accounting underline wherever possible. Though, it is a bit of a compromise. I prefer the white space created by the single accounting underline, but it is not possible to have a different color underline to the text color. That’s a compromise I’m willing to make.