Excel throws formatting at you from all directions. File formats. Cell formats. Hyperlink formats. They all let you produce exactly your kind of data. But sometimes when Excel tries to help by handling some formatting for you automatically, it creates unforeseen twists that do everything from making workbooks look a little off to making them completely unusable on certain computers. This month, we solve a few formatting problems many users stumble into.

Why did my file work fine this morning but won’t even open on another machine?

You’re probably trying to open an Excel 2007 file on a machine running an older version of Excel. If a file name ends in .XLSX, it’s an Excel 2007 file that requires this version of the program. To make your files backward-compatible, you can choose the traditional .XLS format in the Save As Type drop-down menu after you choose Save As. If you ever find yourself stuck with a new file and only an old machine, you can open the file by using a two-step download-and-patch technique described in the February 2009 edition of this column. I’d like to send a workbook out to colleagues for review as a PDF document, but I can’t find this option anywhere in Excel’s Save As dialog box.

PDF is a popular format that is readable on virtually any computer (provided it has the free Adobe Acrobat Reader program) and is basically unchangeable by anyone viewing it. But Excel can’t create a PDF file until you download an add-in program from Microsoft’s Web site. To get the program, search for “Save As PDF” in the Excel help file, click Print, Share, And Protect Files In The PDF And XPS File Formats, and look for the link under Install And Use The Save As PDF Or XPS Add-In From Microsoft.

I just typed “May 24” in a cell where I want the date to appear, and after I pressed ENTER, the cell’s value changed to 39957.

This means the cell’s number format is out of sync with what you’re trying to accomplish. In this case, the cell is set to the General number format, which converts whatever you type into the underlying numerical value that is Excel’s true language. In Excel’s eyes, every date is counted up from January 1, 1900. May 24, 2009, is 39,957 days from the start date, which means May 25 is 39,958, etc. Excel sees times as decimals because they are fractions of days. To see your data as an actual date, right-click the cell, choose Format Cells, click Date, and choose a format you like.

Actually, I want the information to appear exactly as I type it, with no formatting help from Excel.

In that case, choose the Text option. With this formatting in effect, Excel displays exactly what you enter, with no tricks (such as changing it to some obscure number).

Sometimes it takes some trial and error for me to figure out which format is going to work for the data I just entered. Is there a quicker way to learn the ins and outs of Excel’s formatting?

Yes. Even though the tip above describes the old method of right-clicking a cell and choosing Format Cells, Excel 2007 includes a Number Format drop-down list that makes this process much easier. Click a cell and then click the drop-down arrow in the Home tab’s Number section. You’ll see a graphic list of the basic number formats available, along with your data represented in each of the formats.

When I enter a Web address in a cell, Excel automatically turns it into a live hyperlink. Now every time I try to select the cell for editing—including fixing a typo in the address itself—Excel thinks I’m clicking the live link and tries to go online.

You can avoid activating the link by using the arrow keys to move into the cell. Its contents will appear in the Formula Bar, and you can click up there to edit the contents. If you’re trying to select the cell for operations such as cutting or pasting, you can click the cell and hold down the mouse button until the cursor turns into a cross. Then you can press CTRL-C to copy the cell or perform other operations.

Home Excel 2007 Format Troubleshoot Formatting Problems
Search MS Office A-Z   |   Search Web Pages/ Design A-Z