1. Scroll-free Fonts
Scrolling through all the available fonts to find the one you want can be a pain. If you know the name of it, you can simply click into the box containing the active font and begin typing the name of your font. As soon as the font name comes up you can hit Enter to select that font.
2. Paste Column Widths
Cutting and pasting data into a new worksheet can cause headaches because Excel defaults to the standard column widths even if the data being pasted doesn’t fit into it. Cells with text appear truncated, and those with numbers display ##### to indicate that the entire number can’t be displayed properly:
Avoid this problem by clicking on the dropdown beneath the Paste button and selecting the icon for “Paste with Column Widths”:
3. Show me the money
How often have you had to reformat the numbers in your spreadsheet to appear as dollar amounts, with the $ sign and two decimal places? Next time, select the cells and use the keyboard shortcut Ctrl+Shift+$ to change this formatting:
4. E pluribus, Unum
Printing your spreadsheet to a sensible number of pages seems to require some guesswork. You can tell Excel to make the printout one page wide, but how do you know how many pages tall it should be?!
The trick is to set the number of pages wide (in the Page Setup dialog) and delete the number of pages tall. Excel will figure that out on its own:
If you prefer to use the buttons on the Page Layout ribbon, set the height to “Automatic”:
5. Delete stray spaces
Anytime you import data, you have to contend with stray spaces–they can appear at the beginning of the text, the end of the text, or somewhere in the middle. Sometimes you won’t even see them, but these spaces can wreak havoc with sorting and filtering, as well as with COUNTIF, VLOOKUP, and other functions.
The TRIM function deletes any spaces at the beginning or end of the text, and leaves only one space between words. Simply enter the TRIM function in a helper column, with the target cell as the argument
Copy the formula down and replace the original text with the trimmed text.
That’s more like it!
Do you have a favorite formatting shortcut? Share it in the comments.