Software optimize

10 Excel formatting decisions that can have unfortunate repercussions

Formatting your worksheets can make your data easier to read and understand -- but certain formats can cause big headaches if you aren't careful.

Formats provide visual clues that support a sheet's assumptions. For instance, if the sheet treats a value as a percent, you might want to display that value as a percentage. Excel doesn't require formats, but they help users interpret what they see. Formats add value to a sheet and the user's experience, but a few come with a bit of baggage. Here are 10 formatting decisions that require special consideration to ward off potential problems.

1: Rounding values

When you round values, Excel's mathematical operations sometimes return results that appear incorrect. For instance, Figure A shows the same sheet with the values rounded and not rounded. There's nothing wrong with Excel, it's doing what it's supposed to. Excel evaluates the stored values, not the displayed values, in mathematical operations.

Figure A

Rounding affects values in a big way.

The rounding functions offer an awkward solution, but the best solution is to avoid rounding, unless there's a specific reason for doing so. In other words, don't round just because you think all those neatly aligned values look better.

2: Enabling fixed decimal mode

A frequent support call involves a sheet that automatically assigns decimal places, regardless of the input value. For instance, the user enters 123, but Excel stores and displays that value as 1.23. This is usually a result of enabling the fixed decimal option and then forgetting to turn it off when you're done. In Excel 2010, you can find this option as follows:

  1. Click the File tab and choose Options (under Help).
  2. In the left pane, select Advanced.
  3. In the Editing Options section, check the Automatically Insert A Decimal Point option. If it's checked, uncheck it to disable the feature.

In Excel 2003, choose Options from the Tools menu and click the Edit tab to access this option. This feature is definitely helpful if you need to enter many decimal values. I recommend that you use it; just remember to turn it off to avoid confusion later.

3: Using a large font instead of zooming

Don't enlarge a sheet's font so you can see things better on the screen. Font size isn't just a screen attribute; the print version also relies on the font size. To enlarge data on screen, zoom in by holding down the [Ctrl] key and turning your mouse wheel. Or use the Zoom tool in the Zoom group on the View tab. In Excel 2003, you'll find Zoom on the View menu.

4: Hiding data

Users sometimes apply a white font with a white background to hide data. (I've heard these cells referred to as ghost cells.) It might be great for the original user, but it's difficult for someone else to edit or maintain the sheet. If you suspect a sheet of hiding values this way, you can quickly unhide them as follows:

  1. On the Home tab, click Find & Select in the Editing group and choose Find. Or press [Ctrl]+F. In Excel 2003, Find is on the Edit menu.
  2. If the Format button isn't visible click Options.
  3. From the Format drop-down, select Format.
  4. Click the Font tab.
  5. In the resulting dialog, choose white from the Color drop-down.
  6. Click OK and then click Find All.
  7. Select all the referenced cells in the lower pane by pressing [Ctrl]+A.
  8. Click Close, and Excel will select all (any) cells that have a white font format.

With the affected cells selected, you can quickly change the font color for them all at the same time.

5: Using the Text format

The Text cell format treats every value as text. Now, you're probably thinking, What's wrong with that? In theory, nothing. However, if you assign the Text format to a column or row, you might get some unexpected results down the road because formulas that reference a Text cell will return a Text value. You probably think that's okay, but trust me, it might not be -- and troubleshooting the problem later could take a while. When in doubt, use General.

6: Using lots of color

There's absolutely nothing wrong with black, white, and shades of gray. A spot of color, for the right reason, can be helpful -- read that again, I said for the right reason. Adding color for the sake of color or because it helps satisfy the artist in you is bad. Just don't do it. Too much color is distracting and confusing, and it reduces usability. You want an efficient and accurate worksheet, not a colorful one.

7: Applying too many formats

Users spend a lot of time applying extraneous formats. Often, that time comes at the price of adequate testing. The only formats you need are those that clarify your sheet's assumptions. Formats that help users are good. Everything else is unnecessary.

8: Using tables to format

Excel tables showed up in Excel 2007. They're an enhanced list feature (from the pre-Ribbon format). I recommend that you use them, as long as you don't use them just for formatting purposes. The problem isn't tables, exactly. The problem is that tables don't have access to more advanced features, such as Subtotals. When you need the advanced table features, by all means use them to analyze and filter your data. Just don't limit your functionality for a little formatting ease that you can apply manually. It's not a good tradeoff.

9: Using custom formats willy-nilly

Custom formats are useful, but I recommend you use them sparingly and test thoroughly. If you don't completely understand a format's purpose, you might get some unexpected results. That's why I recommend thorough testing when applying custom formats. For example, a casual user might apply the custom format 0#### to force a leading zero during data entry. This format may or may not have the desired results, as shown in Figure B. Test! Test! Test!

Figure B

These two custom formats handle the same values differently.

10: Applying percent format

I recommend applying the percent format during the data entry process. To do so, you simply follow the value with the percent sign. For example, to enter 1 percent, type 1% and press [Enter]. The other method is to apply the format before or after the fact, which can lead to some surprises, as you can see in Figure C.

Figure C

When you apply the percentage format determines how Excel converts and displays the input value.

When you apply the percentage format to existing values, Excel multiples that value by 100 and displays the result with the percentage sign. For example, if you enter 1, Excel evaluates the expression 1*100 and displays 100%. When you format an empty cell (before data entry), Excel converts values equal to or greater than 1 to percentages but multiples numbers smaller than 1 (and not preceded with a 0) by 100. When you enter 1, Excel converts to a percentage--1%. When you enter.3, Excel converts by multiplying by 100--3*100 equals 30%. That's why Excel returns the same value, 10%, whether you enter 10 or .1 in a preformatted cell.

Formatting before or after data entry works when the user understands the rules and expectations, but unfortunately, most don't. Training is the key, but forcing the format during data entry is one way to ensure that the user gets the expected value.

Additional resources

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
CharlieSpencer
CharlieSpencer

Be careful with background fill colors. Unlike font colors, when you do a sort the background colors don't get sorted with the data. The fill color remains in the cell, regardless of what data gets sorted in or out of it.

Suresh Mukhi
Suresh Mukhi

I recently had an issue with the thousands separator ( comma ) with a spreadsheet I was using that needed to be saved to a CSV file to be imported by another program. It treated the comma in a number cell as a delimiter and got imported wrong in the destination program's database. Example, if a cell contained 1234 which was formatted to 1,234. It separated the 1 and the 234. That caused a MAJOR issue.

kviccaji
kviccaji

Very good tips, all of them. A few more tips / comments. About #4: I use the custom number format ;;; to prevent the values from displaying in cells. If the sheet is protected, the value won't display in the formula bar as well. About #8: Does this refer to MS-Word ? I haven't heard of page-numbering schemes in Excel. And one thing I prefer: in horizontal cell alignment, use "Centre-across-selection", instead of "Merge-and-centre" because of problems with using the latter option during column selection, data sorting, and other unexpected behaviour. Even merging cells across rows should be avoided, except in limited situations (like dashboard design).

steve
steve

Good tips here!! Yes formatting is important, and when constantly importing into Excel it would be nice to use template technology and save alot of time!! see www.datapresentation.com/products/ez-format.cfm for a complete solution.