Do You Make These Three
|Reports like this can take a long time to prepare. And readers must study them. There's a better way...|
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...
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...
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.
Looking for Metrics?
If you're looking for new ideas about useful metrics,
check out the "My Metric" feature in each issue of Fortune
James Dyson uses patent filings as a measure of how much companies are spending on R&D. In the first half of 2009, US patent applications were down and China's were up.
Scott Mills, president of BET Networks, watches changes in hotel rates for business travelers. Rising rates signal economic improvement.
Carl Schramm, CEO of a foundation, watches the number of new businesses being formed. Almost all new US jobs come from firms less than five years old. No entrepreneurs, no recovery.
As you find data to support your managers' need for
business insight, just add it to your Excel database and
display it in small Excel charts and tables.
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?
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.
One advantage to using Excel dashboards to cure the three reporting mistakes is that nearly any organization can use dashboards.
"With your help and the information in your eBook, Iím
creating a dashboard report the likes of which my company
has never seen.
"Iím sure Iíll get lots of questions and
will give me a ton of visibility in the
Click here to read other rave reviews.
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.
A Personal Note from Charley Kyd...
"I need to explain a huge reason that drove me to create Excel dashboard reports in the first place:
"I got tired of creating Excel reports that looked like drek!
"My standard Excel reports were accurate and timely. But they were UGLY! In fact, my boss and other managers often read my reports with the same enthusiasm they felt for having their teeth drilled.
I've said that Excel dashboards are quicker and easier to read and update than standard reports. And that's true. But even if it weren't true, I still would have created and used them. This is because I wanted to create professional-quality reports...reports I could be PROUD of!!!
Excel dashboards give me that feeling. And rave reviews from my readers tell me that they feel the same way. Perhaps you will too.
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.
"You're my hero! This is incredibly slick.
"I'm an independent consultant, and this should WOW a lot of clients."
Click here to read other rave reviews.
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.
"I purchased the Dashbord Kit and I think
brilliant. It offers clear and precise advice for
Click here to read other rave reviews.
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.
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!
Here's a sneak peek at what you're going to find when you get your hands on Dashboard Reporting With Excel:
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:
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
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...
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...
Using a database offers Excel users both advantages and disadvantages. The advantages include:
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.
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!
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,
Copyright © 2004 - 2012 by Charles W. Kyd, all rights reserved.