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.

Testimonials

"Your dashboard kit is excellent." Francis Hayden Any Title I Feel Like Francis Hayden Ltd "I bought your e-book, "Dashboard Reporting With Excel" last year – a fantastic purchase!" Kevin Hodge Key Account Mgr General Mills "Your book is fantastic! Have learned more from that book than I have in 20 years of using spreadsheets." Albert J Rossman Senior Manager, Financial Integration Abbott Laboratories "What a great book! "Now I can apply my newfound charting skills to quickly create dashboards on how my trust line of business is performing." Darryl Meyers Senior VP Wells Fargo Private Client Services   "A godsend! I wish I had purchased your ebook a lot sooner." Peter Cassinelli Analyst/Programmer SCA Systems Ltd.   "The book is a joy! Your writing is simple, elegant, and clear. So mahalo nui loa, as we say in Hawai`i – thank you for your very great effort." Toddy Hagans Data Specialist Hamakua Health Ctr "All I can say is fabulous! It's unbelievable what I have been missing for years. "The Camera functionality alone makes the small cost of your book and files worth every penny and more. "Excellent, many thanks." John Rixon Assoc Business Manager Kraft Foods Inc   "Your book was of immense help to me. I produce dashboards on a regular basis in my life as a CFO and generally have reduced time on spreadsheet hell." Shaun Spalding CFO Autolocator (Moscow, Russia)   "I purchased the Dashbord Kit and I think it’s brilliant. It offers clear and precise advice for practical purposes." Sami Järvensivu Business Controller Fläkt Woods Ltd. Finland   "Fabulous. Simply fabulous. "Having built large systems with Excel, including significant VBA code, I was already very familiar with most of the topics. But it's your implementation of all of it combined that is truly stellar." Alexander Cavalaris Fin'l Rptg Analyst Earth Tech Canada   "The dashboard e-book is absolutely awesome! "I didn’t realize Excel could do what I wanted it do! "I’ve started setting up our dashboards to update the look of our annual report." Heidi L. Rockwood Admin Assistant to the VP University of Illinois "I purchased your book this morning! Love it!" Dallan Lax Owner Streamline Business Solutions, LLC   "Your book is excellent. It offers clear and precise advice for practical purposes. It is highly recommended." Flemming Petersen Business Controller Geberit A/S "You made me look like a superstar with this. Nice work." Bill Lalor National Manager of Technical Support, COSCO Container Lines Americas "I learned more from your excel dashboards pdf in 1 day, then I learned in months at investment banking and management consulting." Adam Altman Chief Operating Officer a strategic management company "Your e-book is FANTASTIC!!! The techniques informed by you really break everything down to simple easy to understand steps, besides enhancing my knowledge of Excel (never heard of the camera tool before). "With just an initial reading (and cannibalizing from your dashboard!), I created a dummy dashboard in 2 days, which I have attached herewith in PDF format. "I look forward to the detailed reading regarding setting up and connecting to databases, after which I am sure I will be able to create more effective reports." Manoj Kapashi Cost Analyst, Musculoskeletal Transplant Foundation "...one of the most useful tools in my Excel tool belt. I have built several dashboards that had a positive impact on my career. Thank you!"" George Bryant Acuren North Slope Project Manager BP plc "EXCELLENT! "Thank you for putting together a fabulous tool. Wow! Wow!" John Wright Systems Analyst One Washington Financial "All I can say is Wow! This is amazing! I have done dashboards before but never like these." Jerry Kohl Owner Brighton Collections

Catalog   

Do You Make These Four 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 four critical mistakes with your Excel reports. Here's
what you can do about them...

by Charley Kyd
Microsoft Excel MVP

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 four 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 the Web, or your information system, or a text file, or some other source.

Next, you massage the data in various ways. You sort it.Or change labels. Or 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.

So here's Rule One:

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.

Honestly, how easy are those reports to read and understand? Could you discover the important information hidden in that stack of reports? 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.

So here's Rule Two:

To make reports easy to read and understand, use small charts that show trends in performance, and use short tables where necessary.

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?

Mistake #4: Failure to make Excel reports easy to change.

Because our economic environment is changing quickly, your reports also need to change quickly. Unfortunately, if you're like most Excel users, you haven't designed agility into your Excel reports.

The sad truth is that many Excel reports can't be changed much at all. Perhaps there's not enough room on the page. Perhaps the reports are designed to use data only from one source. Perhaps changes would require too much work. Whatever the reason, many users find it easier to create new reports than to change the old ones. This means more reports for you to create, and more pages for your readers to read.

And here's the worst part of this problem: Most of your readers probably know how difficult your reports are to change. So they hesitate to ask for changes they'd really like to see, and they don't get the information they really need.

If you follow the two Rules, you can change your reports in seconds. To replace one small chart with another, you don't actually replace the chart. Instead, you just point it at different data in your database. In seconds, your chart displays the new data you do need.

The Easiest Way to Cure the Four Mistakes

The easiest way to cure these mistakes 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 uses few charts.

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.
4. ...can be changed quickly.

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 four 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 about my dashboard products 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...

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 - 2012 by Charles W. Kyd, all rights reserved.