Five easy Excel formatting tricks

Improve the appearance and usability of your Excel worksheets with these simple format options.

I've never trained an elephant, but I imagine the process is similar to that of getting your Microsoft Excel worksheet to look just right. Here are five of my favorite Excel formatting tricks.

Double-click to fit columns and rows
When you enter or paste text and numbers into Excel, the cells don't expand to fit their contents. The fast way to autofit columns and rows is to hover your mouse over the header border between the column and its neighbor to the right, or between two rows at the far left of the worksheet. When the resize icon appears, double-click.

Convert one column into two
Suppose you have a list of full names in a column, and you'd like to separate the first names from the last names. In Excel 2003, select the column and click Data*Text to Columns. In Excel 2007, click the Data tab, and select the Text to Columns button. In both versions, choose Delimited (unless all the entries are the same length, in which case you can select Fixed width), click Next, and check Space (or whichever option applies; see the screen below). You can leave "Treat consecutive delimiters as one" checked. Click Next again to view data-formatting options, and then Finish.

Microsoft Excel's Convert Text to Columns wizard
Select Space (or whatever separates the items in the column you wish to split) to create two columns where there once was one.

At this point, you may want to change the order of the columns. To do so, simply select the column header, right-click the selection, and choose Cut. Now click the header of the blank column you want to place the cut cells in, right-click, and select Insert Cut Cells.

Paste formatting with one keystroke
If you'd like several disconnected cells to share a format, such as bold text and a background color, it can be a hassle to select each cell one at a time, open its cell-format dialog box, and make the changes you want. Instead, reformat one of the cells, and then select all of the others by pressing Ctrl, and clicking them one by one. Once they're all highlighted, press F4 to apply the formatting to all of them at once.

See your page breaks
I've been surprised so often when trying to print a worksheet that I automatically preview everything before I send it to the printer. You can get Excel to give you a visual cue about your page layouts by having it display page breaks. This option is the default in Excel 2007, but if your page breaks aren't showing, click the Office button, select Excel Options at the bottom of the window, choose Advanced in the left pane, scroll in the right to the display options, check Show page breaks, and click OK. Page breaks don't appear by default in Excel 2003 worksheets, so to show them, click Tools*Options*View, check Page breaks under Window options, and click OK.

Freeze your column headings
Scrolling through a big worksheet becomes a guessing game once you lose sight of the column headings. To keep them in view as you move down the rows in Excel 2003, select the row directly below the headings, and click Window*Freeze Panes. In Excel 2007, click the View tab, choose the Freeze Panes button, and select Freeze Top Row.

Monday: Get more out of your browser.

About the author

    Dennis O'Reilly began writing about workplace technology as an editor for Ziff-Davis' Computer Select, back when CDs were new-fangled, and IBM's PC XT was wowing the crowds at Comdex. He spent more than seven years running PC World's award-winning Here's How section, beginning in 2000. O'Reilly has written about everything from web search to PC security to Microsoft Excel customizations. Along with designing, building, and managing several different web sites, Dennis created the Travel Reference Library, a database of travel guidebook reviews that was converted to the web in 1996 and operated through 2000.

     

    Join the discussion

    Conversation powered by Livefyre

    Show Comments Hide Comments
    Latest Galleries from CNET
    ZTE's wallet-friendly Grand X (pictures)
    Lenovo reprises clever design for the Yoga Tablet 2 (Pictures)
    Top-rated reviews of the week (pictures)
    Best iPhone 6 and iPhone 6 Plus cases
    Make your own 'Star Wars' snowflakes (pictures)
    Bento boxes and gear for hungry geeks (pictures)