Written by: Jennifer Wentworth, CPA
I found a great article in the January 2013 edition of the Journal of Accountancy that introduced a really useful feature to me. The article, “Touchy Underlines” by J. Carlton Collins, CPA, discusses how to underline entries on an excel worksheet that don’t extend all the way to the edges of the cell.
In excel there is a way to format cells with single and double underlines that don’t extend all the way to the edges of the cell, but extend past the text, eliminating the need to insert thin columns to create the space. Excel’s Accounting Format was designed for accountants, and it allows you to insert nontouching single and double underlines in adjacent columns. Below are the five steps needed to apply this formatting:
1. Add double underlines. Highlight the total row, right -click on the row, select Format Cells from the pop-up menu, and then on the Font tab, select Double Accounting from the Underline dropdown box, then click OK.
2. Add single underlines. Highlight both the header row and the row above the total row (hold the Ctrl key down to select multiple ranges), right-click anywhere on the highlighted range, and then select Format Cells from the pop-up menu. Next, on the Font tab, select Single Accounting from the Underline dropdown box, then click OK.
3. Format the numerical data. Highlight the numerical data, right-click anywhere on the highlighted range, and select Format Cells from the pop-up menu. Next, on the Number tab, select Accounting from the Category list box, set the Decimal places spinner to 0, select the dollar symbol ($) in the Symbol dropdown box, and click OK. (Note: After applying the accounting format, not only do commas and dollar signs appear, but the single and double accounting underlines will also resize to match.)
4. Remove unwanted dollar signs (optional). Highlight the numerical data where you want to suppress the dollar signs, right-click anywhere on the highlighted range, select Format Cells from the pop-up menu, and then on the Number tab, select None from the Symbol box and click OK.
5. Control text underlines. Notice in the image at the bottom of the previous column that the header underlines are larger than the numeric underlines. To correct this problem, highlight the header row, right-click anywhere on the highlighted range, select Format Cells, and on the Number tab, select the Accounting format from the Category list box. Note: This step may sound strange, but you must format the header text using the accounting format in order for the size of the underlines below the headers to match the underlines in the numeric data. These steps will produce the desired format in adjacent columns, as pictured below.
While these steps may also be tiresome, this approach eliminates blank columns, making it easier to navigate using the End+Arrow Keys combinations, and to eliminates the possibility of entering numbers in columns that can’t be seen.
Reference: Journal of accountancy JANUARY 2013 BY J. CARLTON COLLINS, CPA pg. 68