I don’t know if you do, but I remember my mother telling me, “First impressions are important. Look sharp.”
What mom said applies to every consultant report you create and present. If it looks shabby or rushed the client is not going to put much trust in the report or in you.
These are the top 23 best tips on Excel formatting taken from research on top-ranked Excel websites and judged by 30 years of using Excel (back when it came on about 14 diskettes).
Even if you consider yourself an Excel expert, you should keep a “beginner’s mind” as you scan this list of 23 Excel formatting tips.
“In the beginner’s mind there are many possibilities, but in the experts there are few.”
Shunryu Suzuki (1905-1971)
Excel Themes are just one way to save time and create consistent attractive formatting.
Help another Excel user. Pass this on.
Quick Formatting
Formatting quick keys and shortcuts
1. Format numbers as you type
You don’t need to go to the ribbon or use a shortcut key for a few common formats. In a new cell, just type with the format you want. For example,
Type currencies with a $ and the number of decimals, ex. $435.67, to enter a number and apply currency format.
Type 0 followed by space and a fraction, ex. 0 3/4, to enter a fraction and apply fraction format instead of a date format.
Type the percentage followed by the percent sign, ex. 95%, to enter a percent.
2. Save time with these Excel formatting shortcut keys
Most newbies know the shortcut, Ctrl+B for bold. But, there are many more shortcuts.
Break to new line while editing Alt + Enter
Open format dialog box Ctrl + 1
Bold Ctrl + B
Italic Ctrl + I
Underline Ctrl + U
Table format Ctrl + T
Strike through Ctrl + 5
Open Style dialog box Alt+‘ (apostrophe)
General format Ctrl+Shift+~
Currency format Ctrl+Shift+$
Percentage format Ctrl+Shift+%
Date format Ctrl+Shift+#
Time format Ctrl+Shift+@
Number format Ctrl+Shift+! (two decimals)
Exponential format Ctrl+Shift+^
Border selected cells Ctrl+Shift+&
Remove border Ctrl+Shift+_
3. Quickly copy and reuse formats
There are multiple ways to quickly copy formats.
For one or more cells, use Format Painter. Select cells containing the format to be copied. On the Home tab, in the Clipboard group, click the Format Painter. Drag across the cells you want formatted.
To apply the copied format to multiple areas, double-click on the Format Painter tool, then drag across as many areas as you want. Press Esc to remove the format from your mouse pointer.
For a large area, use Copy and Paste Special. Copy a large area containing the multiple formats you want to reapply to another location. To apply, select the top left of the region you want formatted, then on the Home tab, in the Clipboard group, click Paste, Paste Special, then click the Formats option.
4. Format individual words in long text in cells, chart titles, or object labels
This is a great tip for adding emphasis to specific words in headers, titles, or labels. You do not have to format all the words in a cell or chart title the same. You can select a few words and format them separately.
Words and sections of text can be formatted independently.
Select the cell or label containing the text to edit, then in the edit bar, use the floating format menu, or use format commands on the ribbon to apply formats. Not all formats are available. You can format fonts, size, colors, bold, italic, and underline.
5. Apply Themes for quick, pleasing combinations of fonts, colors, and size
Themes are predefined, artistically pleasing combinations of fonts, colors, and font sizes. Themes apply to the entire worksheet. If the worksheet is already formatted, then the old formats update to match the new Theme. As you move the pointer across the Theme panel your worksheet will change to show you the new appearance.
Excel Themes apply multiple formats with a consistent theme.
Apply a new Theme to your entire worksheet by selecting the Page Layout tab, then in the Themes group, select Themes and move the mouse pointer across the miniature Themes. Watch your worksheet change. Click on the Theme you like the appearance of.
Press Ctrl+Z if you want to return to the original formatting.
6. Apply header and alternating row formats to Excel Tables
Excel Tables make managing lists easier. If you are unfamiliar with Tables, you can learn more about Excel Tables. Preformatting for Excel Tables includes title and alternating row formats.
To turn a list into an Excel Table, select a cell in the list and press Ctrl+T, or,
1. Select a cell in the list.
2. Choose the Insert tab, Tables group, and click the Table tool.
3. In the Create Table dialog box, verify the range of cells and check whether your table has headers. Click Ok.
The table will automatically have a table format applied to its headers and alternating rows.
Once you have created an Excel list you can apply a preformatted layout,
1. Select a cell in the table.
2. In the Table Tools Design tab, from the Table Styles group, select a table format, then click Ok.
Predefined formats make it easy to apply header and alternate row formats to Excel tables.
7. Create a Custom Table Format
Although the predefined Table Styles are pretty good, you may want to create your own that matches your company’s identity. To create a custom Table Style, display the Table Styles shown in the figure above. Select the New Table Style command at the bottom, then enter a new name for your custom table format. Select each Table Element you want to change and reformat it, then click Ok.
8. Clear Formats
To clear ALL formats from a selection, select the cells from which you want to remove formatting, on the Home tab, in the Editing group, select Clear, then Clear Formats.
Press Ctrl+Z if you want to return to the original.
9. Keep the current formatting when refreshing PivotTables
It is very frustrating to format a PivotTable and then see all your formatting disappear when the PivotTable is refreshed with new data. Here is how to preserve the formatting you apply.
1. Right-click body of the PivotTable.
2. Select PivotTable Options.
3. In the PivotTable Options dialog box select the Preserve Cell Formatting on Update check box.
10. Double-click to adjust column widths and row heights
To adjust the height of an entire row or width of an entire column so everything fits, click the row or column heading to select the entire row or column. Double-click the row separator line or the column separator line in the heading area to automatically adjust the row or column to fit the content.
If the height or width is too tall or too wide, then drag the row or column separator to the correct position.
See tip 22 “Fit long numbers and dates into narrow cells” for a tip that first long numbers and dates into narrow cells.
Custom Formats
Don’t find what you need in predefined numeric and date formats? Create your own formats.
11. Create custom Excel formats for numbers, dates, times, more
Once you understand the basic syntax, custom formats are easy to add to the Custom list in the Format Cells dialog box. Here are instructions on how to add your own custom formats to the Format Cells dialog box.
Use the Format Cells dialog box to create custom formats.
12. Apply colors in custom numeric formats
You can create custom numeric and date formats to fit any need. But, before you do, take a look through the predefined date and time formats in the Format dialog box, right-click, Format Cells. You can select the Custom formats and enter your own formats using the same symbols you see in the predefined formats.
Create your own custom numeric formats in the Number tab of the Format Cells dialog box.
The syntax for custom numeric formatting is,
Positive format;Negative format;Zero format;Text format
Special characters inside each segment specify how numbers or dates appear. For example,
$#,##0.00_);[Red]($#,##0.00)
In this example, the positive number is formatted as currency with a space left at the end for the parenthesis from a negative number. Negative numbers are formatted as red currency enclosed in parenthesis.
Custom formats do not have the full range of colors available in other formatting methods because they originated in the earliest versions of Excel. You still have a selection,
[Black] Black type.
[Blue] Blue type.
[Cyan] Cyan type.
[Green] Green type.
[Magenta] Magenta type.
[Red] Red type.
[White] White type.
[Yellow] Yellow type.
[COLOR x] Where x is a value from 1 to 56.
The numeric COLOR values refer back to the original low-resolution version of Excel.
13. Custom date and numeric formatting symbols
Excel has a wide variety of symbols available to create custom numeric and date formats, but you really only need to know a few.
A good way to start learning custom formatting is to look at the predefined formats in the Format Cells dialog box and note their construction.
Most formats use a basic few symbols.
Find the basic numeric symbols here at Critical to Success.
Find the basic date and time symbols here at Critical to Success.
A full list of all custom formatting symbols is available at Microsoft.
14. Create user prompts and “in context” help with Excel’s custom formats
Create user prompts with either the Data Validation command or with custom formats. The custom format method is backward compatible to all versions of Excel.
For example, the custom format for a Federal Tax ID looks like,
“TIN “###-##-####;”Enter nine positive digits”;”Enter your tax ID”
If the user enters a positive nine digits, then it appears as
TIN 123-45-6790
If the user enters a negative number, the message “Enter nine positive digits” appears. If a zero is entered as the default, then the prompt “Enter your tax ID” appears.
15. Hide numbers using a custom Excel format
Hide numbers, but still use them in calculations, with a custom format that looks like this,
;;;
That is three semicolons with nothing in between.
This hides the number because there is no formatting. You can still use the number in calculations, but be warned. New formulas that reference this will hide their results as well because they inherit the format. To make the result appear, just apply a normal numeric format to the results cell.
16. Hide zeros in cells or worksheets with a custom Excel format
You can hide zeros throughout your worksheet with the Advanced Excel Options command as explained here. This usually is not a good idea. It is better to selectively hide zeros by creating a custom format that has no format specified in the zero’s place of the format syntax. For example,
#;#;
This is number sign, semicolon, number sign, semicolon.
This displays positive and negative numbers where the number sign is, but because there is no custom format in the zero location, zeros do not display.
17. Create a secure (hidden) data entry cell in Excel
Want to have a data entry cell where viewers cannot see the numbers entered? You can. Use this method to hide data entered in a cell. No VBA macros required, just a custom format.
This does not provide full protection, only visual protection. If someone wants to learn the cell contents they only need to create a formula that references the “hidden” cell.
18. Format with K or M to indicate thousands or millions
When you have LOOONG numbers and want to shorten them, one method is to leave the full number with all its digits in the cell (so other formulas have access to the full precision) and use a custom format to change the display.
For versions of Excel 2003 and earlier check this custom formatting example at Allen Wyatt’s great site, Excel.Tips.Net.
For versions of Excel 2007 and later, with the ribbon, use this example at Allen Wyatt’s site.
19. Format for Zip codes in Excel
Zip code formatting is included in newer versions of Excel in Format Cells dialog box under the Special Category list.
20. Format for Social Security number (USA)
Social Security number (USA) formatting is also included in newer version of Excel in the Format Cells dialog box under the Special Category list.
21. Format for in-country or international telephone numbers
As a consultant to Global 1000 companies I was putting in 150,000 air miles a year and trying to keep track of all the conversations, promises, projects, and contacts in MS Outlook and Excel. The process of typing phone numbers was an irritation that bothered me a lot in one worksheet until I figured out how easy it is to create a custom format for any phone number and its international prefix and country code.
Use Custom Formatting with & to create dynamic text titles and chart labels
Combine custom formats with dynamic text, and use them in chart and report titles or labels. What a great combination for dynamic titles that tell a story and dates and numbers that fit into tight spaces.
22. Fit long numbers and dates into narrow cells
Doesn’t it drive you crazy when a number doesn’t fit in a cell and making the cell wider messes up the beautiful layout you’ve created in another part of the worksheet? Arrgh!
Here’s a workaround that solves this problem. Turn the “too wide” number into text so it pushes through the side of the cell just like text does. When you need the text-number for a calculation, just turn it back into a number. Another example is in this article, “Creating dynamic Excel timelines that scroll.”
23. Combine text with custom formatted numbers and dates for headings, chart titles, and more
When you need a text, header, or chart title/label that includes dynamic dates or numbers, use a combination of the TEXT function and custom formats.
For example, this formula,
=”Timeframe: “&TEXT(H5,”mmm dd”)&” – “&TEXT(H16, “mmm dd”)
Where H5 contains the first date and H16 contains the second date produces the dynamic chart title,
Timeframe: May 01 – Jul 10
Where the dates change as data changes. It’s explained in the article, “Use TEXT and & to combine text, numbers, and dates.”