Charley Kyd, MBA, Microsoft Excel MVP

Briefly, I've... ...written books for McGraw-Hill and Microsoft Press about using  spreadsheets in business. ...worked as a columnist for Inc Magazine. ...invented spreadsheet dashboard reporting for my first book in the early 1980s, and first created Excel dashboards in 1991. ...consulted with companies in many industries and with users in many departments about reporting and analysis. ...used spreadsheets in business since 1979.

Do You Make These Three Mistakes
With Your Excel Management Reports?

Even if you create accurate reports...  Even if you give your managers the data they
need...  You could be making three critical mistakes with your Excel reports. Here's
what you can do about them...

Do you make the following mistakes with your Excel reports? During my early days with spreadsheets, I certainly did. Then I discovered two rules for designing reports, and they cured all three problems.

By the time you finish reading this article, you'll know how to correct these mistakes. This will make your Excel reports faster and easier to prepare, and much easier to read. 

Mistake #1: Failure to set up Excel reports as templates.

Does this happen to you?...

First, you bring data into a spreadsheet -- from your information systems, or a text file, or the Web, or some other source.

Next, you massage the data in various ways. You sort it, change labels, change the units of measure, or whatever.

You enter formulas. You format your worksheet. Perhaps you add charts. And then you make changes.

All this work could take hours.

But finally, you get a result that your boss or other managers need. They find the report useful, and ask you to update it each period.

Now you're trapped. You're already overworked, and now your "one-time" analysis has turned into another periodic report.

Here's how to avoid this mistake:

Set up your reports and analyses to use formulas that return data from a simple database workbook.

By following this rule, you set up your reports as templates. This makes it easy to update each report for a new period. You merely change the report date in one cell of a report, and then recalculate your workbook.

I'll talk more about Excel databases and templates shortly...

Mistake #2: Failure to make Excel reports easy to read.

Picture a stack of typical Excel reports. Don't picture your own reports, which you know well. Instead, picture reports created by other Excel users.

How easy are those typical reports to read and understand? Could you discover the important information hidden in them? And could you do it quickly and easily each period? Or would you need to study those reports carefully -- like homework?

Chances are, you'd have to study those reports for a long time. And chances are, your boss and other managers find it just as difficult to discover the important information in your own reports.

If your reports aren't easy for your managers to read -- quickly -- you're hiding critical business information in plain sight. And you're making your reporting efforts much less useful to your employers.

Here's how to avoid this mistake:

Don't bury your readers in pages with many numbers. Instead, try to show small chunks of data where the meaning of each figure is clear. For example, use small charts that show trends in performance, and small tables.

I'll talk more about the benefits of small charts shortly, and show you some examples...

Mistake #3: Failure to put performance in context.

Excel offers several big advantages over the reporting systems that your IT department controls. One of these is that Excel can contain any data from any source.

Unfortunately, few Excel users take advantage of this power in their Excel reports, and that's a huge mistake.

Think about it. Your reports show what's happening in your organization. But how often do they also answer questions like: Why is this result important? Why is it happening? What if trends continue? Who else is having these problems? Where is performance for this measure better than ours, and why? Is there a better way to achieve our goals?

That is, your managers need to see their organization's performance in context.

Using only your original data seldom can provide the context managers need. Instead, answers usually must come from other sources, which typically are found both inside and outside your company.

Perhaps population or employment trends would add context to your performance. Or public information about your competitors or customers. Or data about your industry. Or perhaps you just need to show data from several internal systems that don't talk to each other.

Think about it. What contextual information do your managers need?

The Easiest Way to Cure the Three Mistakes

Your reports will improve significantly when you (1) use templates, (2) show your information using small figures, and, (3) provide sufficient context. The easiest way to do this is to set up your reports as Excel dashboards. These reports can be as fancy or as plain as you want.

These Excel dashboard reports follow both Rules. Both reports get their data from a simple Excel database (Rule 1), and both use small charts and tables (Rule 2).

For example, this figure shows two dashboard reports that I created in Excel. One is in color, the other in black and white. One uses many charts and one table, and one uses few charts and several tables.

But because both reports get their data from a simple Excel database, and because both use small figures, they...

1. ...can be updated in seconds.
2. ...are quick and easy to read.
3. ...can display data from any source.

My ebook, Dashboard Reporting With Excel, explains how to create Excel dashboard reports like the ones shown here. The New Excel edition is for Excel 2007 and after. The Classic Excel edition is for Excel 2003 and before.

Excel Dashboards Can Work in Any Organization

One advantage to using Excel dashboards to cure the three reporting mistakes is that nearly any organization can use dashboards.

To illustrate, you'll find rave reviews on this page from Excel users who work in a variety of companies. You can find dozens more rave reviews here.

Notice in those reviews how diverse the Excel users are. They work on different continents, for both public and private companies, and in many different occupations. But they're all enthusiastic about the power of Excel dashboard reporting.

You can purchase my dashboard book for Classic Excel (Excel 97-2004) here, and the edition for New Excel (Excel 2007-2010) here.
 

Excel Dashboards Save Time Because They Work As Templates

Using Excel dashboards would be a terrible idea if you needed to create each new report from scratch. This is because each new report would require many hours of work to set up.

But because your dashboards are set up as templates, they actually save you time in three ways.

1. Dashboard templates save you time each reporting period. To update your report you just enter a new report date in a cell, and then recalculate your workbook.

Here's how this works: Formulas in the report workbook use the report date to calculate each date needed in your report. For example, they might calculate the each of the most-recent twelve months, or all the weeks since January 1. Then other formulas return the data for those calculated periods from your simple Excel database workbook. Finally, the charts and tables in your report workbook display those new results.

2. Dashboard templates save you time when you report different groups, like divisions, products, regions, employees, or whatever.

This is because your chart figures automatically scale as you change from very large groups to very small. To see an example of how this works, read the description near the chart figures below.

Formulas in the dashboard workbook make these scaling adjustments, and my e-book explains how it's done. The edition for Classic Excel (Excel 97-2004) is available here, and the edition for New Excel (Excel 2007-2010) is available here. 

3. Dashboard templates save you time when you change your report.

To see how this works, suppose one chart in your dashboard is reporting monthly headcounts and a manager wants to see payroll costs instead.

Your simple Excel database might use a code like "Heads" to label your headcount data, and "Payroll" to label the payroll costs. So to change the chart from headcount to payroll costs, you would merely replace the text "Heads" in a cell of your dashboard workbook with "Payroll".

Also, depending on how you set up your report, you might need to enter the new title of your chart figure in another cell.

So with the proper dashboard design, you can change your report in just a few seconds.

Why This Is NOT an Ordinary Book

Before I wrote this e-book I wrote four "real" books about spreadsheets, which were published by Microsoft Press and by McGraw-Hill. But when I wrote this book, I decided not to go through a publisher, for several reasons.

One reason is that publishers want thick books that stand out on the shelf. This means that I would have had to pad the book with irrelevant content, and perhaps even jokes and cartoons, which my readers would have had to wade through to find the real content. But e-books can be as long or as short as they need to be. The version for Classic Excel is about 150 pages long, and the version for New Excel is about 200 pages long.

Secondly, without a publisher looking over my shoulder, I could write a book like no other computer book on the market. And the results speak for themselves.

My e-book has received rave reviews from my readers, and also from the managers who rely on the dashboard reports my readers now can give them:

Unlike any other Excel book on the market...

1.  It's the only book that shows how to create working Excel dashboard reports for a business audience. Dashboard Reporting With Excel features my unique Excel dashboard techniques that I've developed over the past 20 years, and which readers have used in thousands of businesses.  

2.  It features a systematic, step-by-step approach to creating Excel dashboard reports. Unlike other books that show tips and techniques, Dashboard Reporting With Excel gives a specific, hands-on approach to creating Excel dashboard reports from scratch.

3.  The Excel methods presented in the book are PROVEN through extensive use by the author over 25 years of consulting, and by more than twenty thousand readers working in every type of organization in 145 countries. After reading this book, anyone can create time-saving, professional-quality Excel dashboard reports, with no programming.

4.  It's the only Excel book that shows how to present your data so your readers can understand it quickly and easily. In contrast, many other Excel books -- and many expensive Business Intelligence software products -- emphasize circus-like graphics that distract from the business insight that managers desperately need.

5.  It covers more material than I taught in my tele-seminars, for which students from all over the world paid $325 to attend. Now you can have this expanded content in 150 pages that you can learn at your own pace, and at a much lower price.

6.  It gives readers an unfair advantage. My rave reviews include several from people who didn't want their names used. Others have sent rave reviews that they've asked me not to publish, and I've agreed, of course. Some of these secret fans are just shy, but others have told me a more important reason: They view Dashboard Reporting With Excel as a competitive advantage within their company, and they don't want others to know where their new Excel skills came from.

7.  It offers a one-year money-back guarantee. Try to get your local book store -- or a major publisher -- to give you a guarantee like that!

This is the Only Book on the Planet Where You Can Get This Information

Here's a sneak peek at what you're going to find when you get your hands on Dashboard Reporting With Excel:

How to Create and Use Small Charts

To make reports easy to read, we need to use small charts and other figures. However, if all Excel users were asked to create a small chart for a report, about 99% of them would create a chart that's something like this.

But when you get your hands on Dashboard Reporting With Excel, you'll learn how to create Excel chart figures like the three figures below.

Yes, the "Headcount" chart is prettier than this ugly one. But it offers other significant advantages:

• It Shows the Ending Period Label. The last reporting period for these charts is September, 2008. But the ugly chart's last label is for August. Failing to label the current period not only confuses your reader, it makes your charts look very unprofessional.
  
  Notice that the Headcount chart correctly labels the ending period, as your readers expect in a professional report..
   
• It Uses Readable Date Labels. The ugly chart's date labels are slanted, which makes them hard to read. And if you make that chart much smaller, the labels will switch to vertical, which makes them even more difficult to read. Also, using slanted and vertical labels takes up way too much space, leaving less for the actual chart.
   

  Your dashboard also could use a gray color scheme like this. My book explains color-management strategies for either version of Excel.

  When you read Dashboard Reporting With Excel you'll learn how to fix this problem. You'll learn how to force Excel to display your labels horizontally. And you'll learn the correct way to display your date labels in two rows, as shown here. (The book also shows you the incorrect way, so you'll know what not to do, and why.)
   

• It Scales the Y-Axis Values. The first two charts above each show about 1,000 people. But what if you need to display values in the millions or billions?
  
  By default, the ugly chart would include many more zeros. That's a problem because all those zeros leave less room for the actual chart. They also force your readers to count "thousands, millions, billions" in their heads to figure out just how large the values shown in the Y axis really are.
  
  But the professional Excel charts drop all those zeros and just tell us that the values are in "Thousands of People" or "Millions of Units" or whatever. Business magazines use the same approach.
  
  Keep in mind, however, that we need to avoid Mistake 1. That is, we need to set up our reports as templates so that when our data changes, our charts adjust automatically. So here, we need the unit-of-measure label, and also the chart's Y axis values, to adjust automatically as our data changes.
  
  That's exactly how these charts work, as you can see when you compare the two gray figures.
  
  When you get Dashboard Reporting With Excel, be sure to read Chapters 2 and 3, which explain small-chart techniques in detail.
  
  

How to Use Excel's Camera Tool

Take a look at this snippet from the workbook you'll learn to build in Chapter 8 of Dashboard Reporting With Excel. Do you see anything unusual here?

Notice that both columns of numbers are contained within column H of the worksheet. How is this possible?

The answer is simple. I used Excel's Camera Tool.

The Camera Tool has been part of Excel since its early days, but few Excel users know it exists. This valuable feature allows you to create a live picture of a range in a workbook. That's important when you create small tables in your dashboards, because it allows you to position your tables anywhere you want in your report without worrying about the underlying row-and-column structure of your spreadsheet.

Be sure to read Chapter 5, where you'll learn other reasons to use Excel's Camera Tool. You can...

• Rotate large tables so you can print landscape figures in a portrait report.
• Create lists of results in Excel, lists that change dynamically as your data changes.
• Set up traffic-light figures that display different drawing objects as your results change.
• And a lot more...

By the way, if you're not in Finance, don't be distracted by the "Profit & Loss" label in the figure above. As one of my early readers wrote me recently...

How to Create and Use Simple Excel Databases

Using a database offers Excel users both advantages and disadvantages. The advantages include:

• We users can create many reports that are lived-linked to data from a single source.
• We can update that source much more quickly than we could update many values copied to many Excel reports.
• We don't have to search for data when we need it, because it's usually in our database.

The major disadvantage of databases is that most of them require special training to set up and use. This is training that few Excel users have the time or interest to obtain. So we typically must get help from the IT department or from "database professionals."

Luckily, simple Excel databases have all the advantages of traditional database software, but none of the disadvantages.

To create simple Excel databases, you just add a few range names and some formatting to a data-filled area of spreadsheet. Then, to use the data in your reports, you typically use Excel's INDEX and MATCH functions in formulas.

The process is really simple, but really powerful.

Chapters 6 and 7 in Dashboard Reporting With Excel cover Excel dashboards. As you read through these pages, think about ways you can apply this information in other ways. Many of the techniques that I reveal in these chapters also will improve your standard reports.

How to Build Excel Dashboards from Scratch

This topic might be obvious, but I need to state it clearly. Dashboard Reporting With Excel not only discusses all the elements you'll need when you create your Excel dashboard, it explains how to create your dashboard from scratch.

And I do mean from scratch!

If you're like most Excel users, you have no idea what a good dashboard report might look like. You don't know what colors might look good together, nor which fonts to use.

So I begin with some simple design advice: "Steal" design ideas from charts and figures in business magazines. In fact, the report that you'll create in Chapter 8 relies on colors and fonts from a figure I found in Business Week.

If you don't like dashboard I designed in Chapter 8, that's no problem. In Chapter 9 you'll see how Excel can closely match other figures from other business magazines. So if you don't like my design, you can create your own.

That's the power of Excel!

Next Steps...

This has been just an abbreviated description of the heavy-duty content in Dashboard Reporting With Excel. After all, the book contains even more power-packed material than my $325 tele-seminar about dashboard reporting.

You can own Dashboard Reporting With Excel for not even half ($162) the cost of the course. Not even for one-tenth ($32) of what those students paid.

You can get your copy of Dashboard Reporting With Excel for the modest price of only $27. That's a savings of $298 off the price of the course.

So now, the only question left to ask is ...

When do you want to "WOW!" your managers or clients with your own professional-quality Excel dashboard reports?

My guess is that you answered: "It couldn't be soon enough!" So I strongly urge you to get Dashboard Reporting With Excel today.

Quite frankly, if you want to demonstrate your Excel skills clearly, there isn't another book out there -- at any price -- that can do so more effectively than Dashboard Reporting With Excel can.

It doesn't matter whether you're a seasoned manager, an experienced Excel user, an Excel newbie, or a student. Dashboard Reporting With Excel can teach anyone the right way to create professional-quality Excel dashboard reports.

I suggest that you grab your copy of Dashboard Reporting With Excel today -- so you can accelerate your readers' insight about your organization, save many hours of reporting time, and learn Excel techniques that can bring an incredible benefit to your career.

Wishing you prosperity and success,

Charley Kyd

Copyright © 2004 - 2010 by Charles W. Kyd, all rights reserved.