5 Killer Formatting Tricks

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.

fonts-typehere

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:

PSCW-noCW       Yuck!

Avoid this problem by clicking on the dropdown beneath the Paste button and selecting the icon for “Paste with Column Widths”:

PSCW-menu  arrow  PSCW-results2

The keyboard shortcut is: AltHVW

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:

money-preformatting         money-ctrlshiftdollar         money-formatted

Ch’ching!

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:

epluribus-pagesetup

If you prefer to use the buttons on the Page Layout ribbon, set the height to “Automatic”:

epluribus-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.

trim-needstrim

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

trim-trimB2

Copy the formula down and replace the original text with the trimmed text.

trim-results

That’s more like it!

Do you have a favorite formatting shortcut?  Share it in the comments.

Advertisements

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in eDiscovery, Everybody, formulas and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s