I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
Thanks to Mark Proctor from https://exceloffthegrid.com/ for sharing this tip on a recent webinar.
The standard underline isn’t used a lot in Excel because it only underlines the words used in the cell. Typically borders are used for underlines because they are a consistent length – the whole cell.
When you see the Single Accounting Underline in action you may want to use it for headings rather than borders. It takes a few clicks to apply, so I recorded a macro to make it easier to apply.
The image below is a typical Excel report – borders have been applied and gridlines have been turned off. The gridlines option is a check box on the View ribbon.
To remove borders, select the range and press the keyboard shortcut Ctr + Shift + Underscore.
To apply the Single Accounting underline follow these steps
- Select the range, in this case C2:E2.
- Press Ctrl + 1 (use the 1 on the keyboard – not the 1 on the numeric keypad – right side of keyboard).
- In the Font tab click the Underline drop down and choose Single Accounting – see image below.
- Click OK.
The revised heading row is shown below.
Note the small gap between the underlines. This denotes a column break.
The underline works a little differently for numbers and I am not a fan. See example below.
I prefer the standard single above and double below border format – see image below.
The result is shown below.
This underline is useful for headings, not so useful for numbers.
The macro to apply the underline format to the currently selected range is shown below.
Sub Single_Accounting() ' Apply Single Accounting underline to the selected range Selection.Font.Underline = xlUnderlineStyleSingleAccounting End Sub |
See how to add a macro to the Quick Access Toolbar at this blog post.
Hi Neale,
I agree with you. I use the single accounting underline for headings and then borders for totals (due to how it works with numbers). It’s a great feature, just a shame it’s hidden away in settings that nobody knows about.
Hi,
If you change the format cell to accounting format, Single/Double accounting lines work fine and you don’t need to use borders.
Trouble is I hate accounting format with a passion. The $ goes to the left and doesn’t look good.