Touchy underlines
Q: Because I think the solid underlines on my Excel-based financial reports look less professional, I insert thin, blank columns between my data so the underlines don’t touch one another. This process of inserting and reformatting columns is tiresome. Is there another way to produce the underline breaks I want without having to insert blank columns between my data? The screenshots in the next column provide an example of my data before (with solid underlines) and after inserting blank columns (with underlines that don’t touch).
A: There is a slightly better option. 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 format using your example data:
1. Add double underlines. Highlight the total row, right-click on the total 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 perform other operations such as sorting, filtering, and subtotaling.
