Formatting Numbers in Excel
Making Financial Presentations Look Professional
Excel offers tools to make your document look professional and appropriate. Knowing how to use these tools can help put you one step ahead of the competition.
Methods of Formatting in Excel
There are 3 ways to find the formatting tools in Excel:
- Select format on the menu toolbar. This will give you options on formatting a cell, row or column. If you have multiple cells to format, highlight them and then select the format option.
- Right click on the cell or highlighted column, row or group of cells that need formatting, and select format cells.
- Use the Formatting Toolbar. Make sure it is active by selecting it in View Toolbars. This toolbar will provide icons for the most common formatting instructions.
Formatting Numbers
The default setting for numbers in Excel is general, meaning that there is no formatting. Most financial documents are formatted for ease in understanding the information. In the United States, the standard convention is to use dollar signs before the number, and commas every 3 digits to the left of the decimal point.
To make the documents look great, arrange the data in columns, with the titles at the top, and centered. Row descriptions look best on the left, but can be placed in the middle if there are many columns.
In a column of numbers that represent money, format the first row with dollar signs. Rows after the first should be formatted with commas only, to avoid a cluttered appearance.
Before a total, subtraction, or any calculation, there should be a line, or border. This indicates that the lines above lead to the calculation. A dollar sign can used on the total line. This is not required, but helps the total to stand out. If using this convention, use a dollar sign after every border.
It is the designer’s choice whether the format should be in cents or rounded to the nearest dollar. Using whole dollars looks cleaner, if exact precision is not required.
On the final line of the spreadsheet, consider using a double line as a signal to the reader that the column is complete.
Tips and Tricks in Formatting in Excel
- Users have the ability to place the numbers anywhere in the cell, but right justifying the numbers is the best choice. Centering the numbers makes them harder to compare.
- The Format Painter icon is a shortcut to copy formats only. The icon is on the standard toolbar and looks like a paintbrush. It is especially useful for copying the formatting from one column to another. Highlight the original area and click the icon, then click simply click the same cell in the corresponding column. Excel has many other powerful shortcuts to help with copy and paste.
- Excel will default format any cell that looks like a date as a date. Once the cell as been incorrectly formatted as a date field, it will have to be formatted as a number using one of the methods above.
Although the quality of the information is most important, a well-designed document will get the reader's attention, and help to communicate your points effectively.
