Software

10 ways to format Excel data so that people can actually understand it


Tell the truth, now. When you see a new worksheet arrive in your Inbox, your pulse doesn't exactly quicken, does it? I'm guessing that you don't lean forward in your chair, eager to open the file and see what exciting new numbers the worksheet is likely to present.

Or maybe you do have a pulse-pounding reaction to worksheets, but it's due to anxiety, not excitement. What am I supposed to see in all this data? How hard is this going to be to decipher? Am I really smart enough to figure all this stuff out? What time is lunch?

You can lessen this type of math anxiety for those who view your worksheets by using some simple techniques in Excel to get your data message across clearly. Here are a few tips to get you started.

Note: This information is also available as a PDF download.

#1: Give your worksheet a good title

The ultimate goal of your worksheet is to present information in a way others will understand, right? Give readers a head start by titling your worksheet clearly. A good title lets readers know immediately what the content of the worksheet is going to show them -- that way, they won't have to work so hard. "2008 Projections" isn't bad, but it leaves a lot of things out. What kind of projections? Sales? Hired Employees? New Store Openings? Be as clear as you can and, if necessary, add a subtitle. And if you plan to circulate the worksheet by e-mail, be sure that you title the worksheet file with something that reflects the content of the sheet itself. That way, when people open the file, they'll know what to expect.

#2: Wake people up with color

Sure, black and white does the trick. But. It's. Boring. For those of us who aren't numbers people to start with, pouring over a black-and-white grid stuffed with an ocean of numerals is torture. Jazz it up by splashing a little color on the page. Choose an energizing color -- green, orange, purple -- for your column heads. Do something special with the title -- maybe format it in a complementary color, add a picture at the top, put a background behind it to make it pop. Just be sure the color you add doesn't actually detract from the readability of the page. You want the color and design of the worksheet to draw people into the page, not make them forget what they're looking at.

Excel makes it easy to add a picture to the background of the worksheet as well. This is really a neat effect, if you have a reason to use it. As a general rule, don't put pictures behind your data just for the fun of it -- readers have to work harder to see what's in the foreground. But if you want to promote something fun, such as a summer sales competition, you can raise the energy by adding a special picture behind the scenes.

#3: Let the format lead the eye

Have you ever felt your heart sink because you've got five minutes to prepare for a meeting and the worksheet you're looking at makes no sense? Chances are, the problem is one of three things:

  • You can't immediately tell what you're looking at.
  • You can't tell where to start on the page.
  • You don't have a context for the information.

The way in which you format your worksheet can solve all three of these potential problems for you. As a general rule, the largest items on your page will get the first look. Then, the eye will look for the next largest, and the next largest after that. The actual content -- the data in the cells -- is likely to be what your readers see last.

You can use this idea to your advantage when you plan the format of your page. Make the title stand out, good and bold, and make sure it says something clearly about the content you're showing. Next, enlarge the column and row heads just a bit and put them in a second color. Readers will quickly scan the column and row headings to get a sense of how the information on the worksheet is organized. This will help them see what's most important on the page and where they should begin.

If you have extra information you want to be sure readers see -- for example, a note explaining why you're displaying projected 2008 data even though it's only 2007 -- you can set it off by enclosing it in a border or using a second color. This will draw the reader's eye to the note after they've taken in the most important items on the page.

#4: Apply a theme

Excel 2007 benefits from the hip new (or should I say, expanded and improved) themes feature that has been added throughout the core Office 2007 applications. Themes exist in Office 2003, but they were limited in design and functionality (and much less fun).

When you apply a theme to your Excel 2007 worksheet (by clicking the Page Layout tab and choosing Themes), you make a design choice that includes the font, color, table style, and effects of the information on your worksheet. The great thing about this feature is that you can dramatically change the look and feel of your information simply by opening the Themes gallery and clicking another choice.

#5: Don't cram your charts

What would we do without charts in Excel? Charts are a saving grace for those who need to provide their information in the easiest possible way for readers to understand. A chart quickly shows readers what's most important in your data (or at least, what's most important in what you want to show right now) and presents each data item in relation to other items in the chart.

One mistake that Excel chart novices often make is to try to show absolutely everything under the sun in a single chart. You've got one shot, right? The best way to ensure that your readers will keep viewing your information is to make sure they understand what they're looking at. And that means paring your chart down to the essentials and not littering the bars or pie slices with all sorts of labels and percentages and other nonessential items. Show what you need to get your point across and no more. Your readers will thank you for it.

#6: Diagrams are helpful

Excel 2007 includes another way to show your data visually. Now you can use SmartArt to create professional-looking diagrams for flowcharts, org charts, and much more. Creating a SmartArt diagram is similar to creating a chart -- you choose the type of diagram you want to use, add your text, and then add bars, lines, suns, moons, whatever. You can tailor the diagram in an almost unlimited number of ways (including adding pictures to the shapes in the diagram).

#7: Take advantage of conditional formatting

I admit that I am easily amused by simple, colorful things. So I love the improved conditional formatting in Excel 2007. This is another feature that's been dramatically enhanced and made easier to use in the newest version. Now you can use a variety of visualizations to help show trends in the data you're presenting. You can use symbols, color bars, and more. To add conditional formatting to your worksheet, select the cells you want to showcase and click Conditional Formatting in the Styles group of the Home tab. Select the style of formatting you want to apply, and Live Preview will show how the formatting will look on your data. Nice.

#8: When in doubt, spell it out

If you are working on a complex worksheet and just can't see how everyone will understand what you want them to focus on without a note or two, you can easily add comments to cells in the worksheet. Simply select the cell or range you want to add a note to, right-click, and choose Insert Comment. A small text box opens on top of your worksheet so that you can type the note you want to add. Your readers will know a comment exists at that point in your worksheet because a small red triangle appears in the upper-right corner of the cell. When a reader hovers the mouse pointer over the triangle, the note opens.

#9: Frame it

It's an old trick, but it still works -- if you want to draw a reader's eye toward something on the page, box it. Newspapers do it, magazines do it, Web pages do it. If the design is done well, readers think they are getting something "extra" in that little box -- and we all love getting something extra.

Select the cells you want to border and right-click on your selection. Choose Format Cells and select the Border tab. Test out different border styles and choose one that frames but doesn't overwhelm your worksheet design. Click OK when you've found the style you want to use.

One caveat: Use the box idea sparingly. If you create more than one box on the page, readers might get confused about which one to read first and maybe even give up on reading any of it.

#10: Invite feedback

Finally, if you really want to make sure people are getting the message of your worksheet, ask. E-mail one or two coworkers and ask them to take a quick look at your data; then find out whether:

  • It was easy to understand what the worksheet is about.
  • They could clearly see what was most important on the page.
  • They have any suggestions for ways you could make it easier to understand.

So what's the result of all this? People will smile at you more when they pass you in the hallway, because you made their day easier -- and helped them feel smarter -- by making your information easier to understand. And who knows, you might even get a free lunch out of the deal.


Katherine Murray is the author of many computer books (including the in-the-box documentation for Microsoft Office 2007 Professional and Small Business Editions). Her most recent book, Microsoft Office Word 2007 Inside Out, with coauthors Mary Milhollon and Beth Melton (Microsoft Press, 2007), has just hit the stands. She also writes digital lifestyle articles for various Microsoft sites and publishes a blog called BlogOffice that shares Microsoft Office ideas, how-tos, and tips.

About

Katherine Murray is a technology writer and the author of more than 60 books on a variety of topics, ranging from small business technology to green computing to blogging to Microsoft Office 2010. Her most recent books include Microsoft Office 2010 P...

6 comments
adj1
adj1

"For those of us who aren't numbers people to start with, pouring over a black-and-white grid" - Those of us who are numbers people have probably also learnt how to spell poring I would find this exercise perhaps more persuasive if the actual tables were better organized: for instance no.7 has a list of items which appear to be in no particular order, not even alphabetical, and the figures associated with them show no great pattern. Had you thought of ordering the items by their total number or by the outstanding number to be checked (which appears to be the principal point you want to make, judging by the coloured bars) ?

ThomasBP
ThomasBP

Thanks for the hints. I agree that it is indeed worthwhile to care a little extra for the readers of your worksheets - at least when there are more than a handful of intended readers. I'd like to add a personal favorite: Do keep the sums (and averages etc) to the left and the top, so that your readers don't have to scroll endlessly to find the "answers".

grmccormack
grmccormack

I'd like to think I do most of those things, like colourfull borders etc ... But it all takes time - time that you might get criticised as 'wasting' when other look at your spreadsheet. I do use borders but not much else - avoiding confrontation with colleagues... Thanks for the article Greg

DBlayney
DBlayney

I agree totally :-)) It usuaully requires a little more organisation to get this right as Autosum does not work "downwards" or "right-to-left". Freezing panes to keep totals and headings visible at all times is essential too.

BrianScattergood
BrianScattergood

If your spreadsheet is very large, it is helpful to its users if the headers remain at the top of the screen when scrolling down through it. I have done this with my data archive spreadsheet at work, which now contains more than 2000 rows.

ed
ed

You are correct that you need to be careful of the order of the calculations and the results of calculations usually need to be at the bottom or right. However, you could have a block at the top referencing the results, using something like =G19

Editor's Picks