Subscribe to RSSArchive for the ‘Excel Dashboards’ Category:
Excel Dashboard Sample: Advice about Using Excel’s Camera Tool
Of all the Excel features that users don’t use very often, the Camera tool probably is the most powerful. This tool, also known as a Picture Link, returns a real-time image of any range in Excel.
This tool is so useful that I devoted a full chapter to it in my ebook, “Dashboard Reporting With Excel.”
The [...]
Excel Dashboard Sample: Making Top-Item Charts Easier to Read
Managers need to know where the greatest opportunities and the worst problems can be found. This is why top-item figures are so popular.
These figures typically are titled Top-Ten Reports, or 80-20 Reports. To create them, Excel users sort a category by the value of interest for each item, typically with the largest value first, and [...]
Excel Dashboard Sample: How to Structure Your Report Workbook
In Excel Dashboard Sample: Weekly & Monthly Top-Ten Activity Reports I introduced two Excel dashboard reports created by Chris Helfrecht. In this post, I’ll describe a critical aspect of his report workbook: the workbook structure.
All Excel reports perform at least four tasks. Good reports assign these tasks to four sections, with specific worksheets defining each [...]
Excel Dashboard Sample: Weekly & Monthly Top-Ten Activity Reports
When Chris Helfrecht sent these two sample Excel dashboards, he wrote that he tried to follow my methods. He also wrote that people in his company have been very enthusiastic about his new dashboards.
I recognized a lot of my ideas in his samples. I also found some new ideas. Some of these I like a lot, and some not so much. In future blogs, I’ll explain the details of his dashboard workbook, and suggest ways that I would change it.
For now, take a look at the two examples, and I’ll get into the details in the next post.
Top Weekly Transactions for a Medical Device Company. (Click to see a larger image.)
In case you missed the caption, you can click on either image to see a larger version. Then click your browser’s Back button to return to this page.
Top Monthly Transactions for a Medical Device Company. (Click to see a larger image.)
About the Excel Dashboard Author
Chris Helfrecht has worked in Finance for ten years and as an Oracle Developer for seven years. He currently works as a Quality Systems Analyst for a large medical device company. He writes that Excel is a very useful tool for his purposes because users aren’t afraid of it, and it offers excellent reporting features.
You can email him by correcting this address format: chrishelfrecht at hotmail dot com.
Don’t Let Bad Spreadsheet Design Hurt Your Career, Part 3
I just received a dashboard report that I also need to discuss. So for the next few weeks I’ll turn into a spreadsheet critic.
Splattered Data
Imagine dropping a can of paint onto a parking lot from a 50-story building. Every car in the lot would be splattered. Randy’s workbook does the same thing with its data. So do many other workbooks I’ve seen.
The workbook has more than a dozen report pages. Most of them contain manually entered, raw data intermingled with formulas. And both the data and the formulas have been carefully formatted for presentation. This design approach can be big a problem for several reasons:
—If your raw data is splattered all over your workbook, your data is virtually impossible to update accurately or quickly.
—Your raw data often must be transformed in many ways before it can be presented. For example, you might need to change its unit of measure, scale it, apply adjustments, and so on. However, if you change the raw data itself, in random places around your workbook, the data no longer matches the source. This makes error-checking virtually impossible.
—Changing the structure of your report — your output — also changes where your raw data can be found. This turns the updating process each period into an Easter Egg Hunt.
—It’s a lot of work to format, display, and transform raw data that’s splattered all over your workbook!
—Etc.
Here are a few ways these problems can affect your career:
—Poor workbook design can cause you to spend a lot of time to update your reports and analyses. So you’re not nearly as productive as your co-worker who uses more efficient techniques.
—Similarly, poor design can cause you to take a lot longer than others would when your boss asks you to change a report.
—Poor design tends to produce more errors.
—Poor design makes it more difficult for you to explore your data and find hidden problems and opportunities that your company should know about.
—Even if your boss isn’t a spreadsheet expert, it’s easy to recognize a cluttered workbook. And people correctly distrust such clutter.
—Etc.
Therefore, wherever possible, you should separate your data from its presentation.
Excel Was NOT ‘Originally Designed as a Personal Productivity Tool’
If you read the business press or Business Intelligence (BI) forums, or if you listen to most IT managers, you’ll soon hear that “Excel was originally designed as a personal productivity tool.”
Well, it ain’t true!
I know this “fact” isn’t true for three reasons.
First, I was there at the beginning. In the mid 1980s I worked as a columnist for Inc, Business Software, and LOTUS magazines. So when Business Software assigned me to write a cover story about the introduction of Excel for the PC, Microsoft was happy to cooperate. They allowed me to spend an entire afternoon with the manager of the Excel project.
At that point, Lotus 1-2-3 dominated the marketplace. Microsoft’s only goal was to offer a spreadsheet that was technically superior to Lotus 1-2-3, and easier to use. Excel was “originally designed” to kill Lotus 1-2-3. And a significant part of their strategy was to delight the business market.
Second, VisiCalc, Lotus 1-2-3, Symphony, Multiplan, and Excel all were designed and promoted as business-management tools, not merely as personal productivity tools. To illustrate what everybody knew about spreadsheets back then, here are some titles of books that I still have on my shelf from those early years of the spreadsheet:
- The Power of VisiCalc Real Estate (1981)
- Doing Business With VisiCalc (1982)
- VisiCalc, Advanced Version, Worksheets for Business (1983)
- Multiplan Models for Business (1983)
- Solving Marketing Problems With VisiCalc (1984)
- 1-2-3 Managerial Worksheets (1984)
- Business Worksheets for Lotus 1-2-3 (1984)
- 1-2-3 for Business (1984)
- A 1-2-3 Business User’s Guide, Planning and Budgeting for Higher Profits (1984)
- Preparing Your Business Plan with Symphony (1985)
- Advanced Business Models with 1-2-3 (1985)
- Salesbook Spreadsheets (1985)
- Money Management Worksheets for 1-2-3 / Symphony (1985)
- Marketing Mix Analysis with Lotus 1-2-3 (1986)
- Controlling Your Cash Flow with 1-2-3 or Symphony (1986)
- Business Statistics Using Lotus 1-2-3 (1987)
- Business Planning & Forecasting with Microsoft Excel (1988)
- Lotus 1-2-3 Release 2.2 for Accounting (1989)
Also, of course, there are my own two books from that era:
- Financial Modeling Using Lotus 1-2-3 (1986)
- Microsoft Excel Business Sourcebook (1990)
The third reason I know that Excel was not originally designed as a “personal productivity tool” is that the first statement I can find on Google about this idea — which supposedly has been around from Excel’s beginning — doesn’t appear until 2006. The statement was in an article in Search Data Management, which quoted Robert Kugel of Ventana Research. If you’re not familiar with him, Kugel probably is the most widely quoted trivializor of Excel.
So if a BI vendor or your IT people try to trivialize Excel by saying it was “originally designed as a personal productivity tool”, don’t let them get away with it. The statement ain’t true!
How to Sort Data in Reports Automatically Using Excel Formulas
Many Excel reports include tables that show sorted results. Usually, these tables were sorted manually in Excel, using the Data, Sort command. However, reports would be a lot easier to maintain and update if formulas (not macros) could sort the data automatically.
There’s a simple way to do this. But to make the method work reliably, you’ve got to fix the behavior of the RANK function.
This figure shows a partial solution, and illustrates the problem with RANK. Columns A, B, and G show actual data. Enter the following formulas for the cells shown, and copy the formulas down their columns as needed:
C3: =RANK(A3,$A$3:$A$6,0)
E3: =MATCH(G3,$C$3:$C$6,0)
H3: =INDEX($B$3:$B$6,E3)
I3: =INDEX($A$3:$A$6,E3)
Sorting Excel data with formulas without fixing the RANK function.
The formulas in column C rank the sales values, where the largest value is ranked 1 and the smallest in the table is ranked 4.
The easiest way to understand column E is with an example. Look at cell G6. This marks the fourth sorted row in the table at the right. Cell E6 tells us that the values for this row can be found in the second row of the table at its left. The other values in column E provide similar information.
Columns H and I use the information in column E to return the appropriate values.
The reason row 4 of the spreadsheet has #N/A values is because the RANK function assigns identical rankings to identical values. This is a problem because Coats and Pants have identical sales this month. So the RANK function assigns the same #1 ranking to both products in column C, and assigns no #2 rank value at all. Therefore, when the formula in cell E4 looks for a #2 ranking, it returns #N/A.
This problem is easy to fix, however. Just insert a new column for an adjusted sales value and then rank those adjusted values. Here are the formulas for the cells shown:
B3: =A3+0.000001*ROW()
D3: =RANK(B3,$B$3:$B$6,0)
Sorting Excel data with formulas after fixing the RANK function.
Column B adds a tiny amount to each Sales value, an amount based on the number of each row in the table. This forces each value in Sales2 to be unique, which forces the Rank values in column D to be unique. And this gives us our automatically sorted values in columns H:J.
When you use this technique, be sure to estimate the maximum number of rows in your table when you choose the decimal fraction shown in the formula for cell B3. That is, if you’re going to have hundreds of thousands of rows of data (which New Excel allows) make sure that you still have a small number when you multiply your decimal fraction by a row number of about one million.
Don’t Let Bad Spreadsheet Design Hurt Your Career, Part 2
Bad spreadsheet designs can hurt your career. I’ve seen it happen.
So in Don’t Let Bad Spreadsheet Design Hurt Your Career, Part 1, I began to discuss problems in a workbook sent to me by an Excel user I call Randy. I hope this will help you to find and fix similar problems in your own workbooks.
Range Names
Randy’s workbook has more than a dozen worksheets, with many formulas that link to data in other worksheets in the workbook. But the workbook doesn’t use range names at all.
This is a problem for at least four reasons. First, cell addresses in formulas give no hint about what value you intend to link to. But using a range name, on the other hand, provides some idea about the type of data you intended to use. And that information will reduce the errors in your formulas.
Second, using range names often is easier to do. For example, if the range name CurMonth contains the date serial number for the current month, it’s very easy to enter =CurMonth in any cell that needs to use that value.
Third, as you create your workbook, you occasionally will need to change the source range that formulas link to. If those formulas use only cell addresses, you’ll need to change every single formula. But if those formulas use range names, you simply redefine the range name to point to the new source range.
Finally, it’s not unusual for large workbooks to be split into several interlinked workbooks. This is extremely risky to do if one workbook links to another workbook using cell addresses.
The reason this is risky is obvious when you think about it. Suppose BookB.xls contains the formula:
=[BookA.xls]Sheet1!$A$1
If you close BookB.xls, insert a new row 1 in BookA (moving the old row 1 to row 2), then open BookB.xls again, BookB now should link to cell A2, because that’s where the original data now is. But BookB still links to cell A1, because it was closed when the changes were made. However, if BookB links to range names in BookA, there are no errors at all.
So, learn about range names and use them.
I’ll see you in the next post.
Don’t Let Bad Spreadsheet Design Hurt Your Career, Part 1
Responding to A Free Offer to Help Excel Users Improve Your Job Prospects, a reader sent me a workbook this morning. I’m glad he sent it, even though it contains no dashboards. This is because it illustrates many bad practices I’ve seen in Excel reports over the years.
I’ll call my visitor Randy.
Assuming that he created the workbook himself, Randy certainly seems to know his profession. And he did several things in his workbook that I like. But on balance, he’s made many common design mistakes that overshadow his professional skills.
I don’t know whether Randy’s spreadsheet work has hurt his career. But I’ve seen examples where other Excel users with good professional skills have been hurt by similar problems with their spreadsheets.
In this post, and in several future posts, I’ll discuss what I think is good and bad about Randy’s workbook.
I’m not doing this to pick on Randy, by the way. I’ve seen all of the bad things in other workbooks over the years; and there’s a good chance that many of them also are in your workbooks.
Profile Sheet
The first sheet in his workbook contains a profile of Randy’s organization. It contains the name of his company, the date, the currency, and other facts. This is a great start, and it’s similar to the information I put into my Control sheets.
Unfortunately, when he displays the date, the currency, and other profile information in other worksheets, Randy doesn’t link to the facts he’s entered in his Profile page.
This oversight is a problem, because much of that profile information can change over time. When it does, Randy must find every cell in the workbook that contains the information, and then change it. Using Search & Replace for this task is risky, because the command can change cells that shouldn’t be changed and overlook cells that should be changed.
When Excel overlooks cells that should be changed, it’s not because there’s something wrong with Excel’s Search & Replace command. It’s because if you display the same value in different cells you’re likely to enter that value differently in those cells. If your company is “Acme”, for example, you might enter it as “Acme” in one cell, and as “Acme, Inc.”, “Acme Inc.”, “Acme Corp.”, and so on in other cells. Similarly, billions of different date serial numbers could all display as Oct-2009.
If you have control-type or profile-type information that’s repeated in several pages in your workbook, put that information in one worksheet and then link to that data from other sheets in your workbook.
There’s a lot more in this workbook to talk about, which I’ll do in future posts.
How to Format Dates in X Axes of Mini-Charts in Excel Reports
When most Excel users create charts, they make them way too large. For many reasons, using mini-charts is much easier to read.
To illustrate, this Excel dashboard report contains 28 charts:
An Excel dashboard report with 28 mini-charts. Click the image to see it full size.
This report, which is included in the IncSight DB plug-n-play dashboard kit, contains roughly ten times the number of charts included in a typical Excel report. But when you view the image in full size, you’ll see how easy the charts are to read.
One challenge in creating charts like the ones above is that of handling the X axis correctly. Generally, when you create an Excel chart and then reduce it to the actual size of the report above, the X axis labels are slanted. This makes them difficult to read and takes up too much space in the chart area. The figure below shows a better way to handle the X axes.
This figure shows that the X axes of Excel mini-charts can be easy to read.
There are three key steps to setting up the X-axis labels shown here. The first step is to display 13 months of data, rather than 12. When we do this, Excel labels both the first and last month in the chart.
The second step is to set up your data with the date labels you need. In the figure above, column A contains date serial numbers for the values shown. Column B contains formulas that transform the date values into the text labels we need to display in our chart. Here are key formulas for the cells shown:
B2: =TEXT(A2,”mmm”)&CHAR(13)&TEXT(A2,”yyyy”)
B3: =TEXT(A3,”mmm”)
(If your computer is set up for a language other than English, you might need to replace “mmm” and “yyyy” with the format labels required for your language.)
The CHAR(13) part of the formula in cell B2 forces a carriage return when the text is displayed in a chart.
Copy cell B2 to cell B14, then copy cell B3 to the range B4:B13.
The third step is to force Excel to align the labels horizontally. To do so, select the X-axis labels in the chart, press Ctrl+1 to launch the Properties dialog, and then:
- In New Excel, choose the Alignment tab. Then click the up arrow once in the Custom Angle spinner control; then click the down arrow once.
- In Classic Excel, choose the Alignment tab. Then click the up arrow once in the Degrees spinner control; then click the down arrow once.
After you choose OK, you’ll see that the labels are horizontal, not slanted. This is because changing the spinner control up and down as I’ve described turns the automatic alignment setting off and specifies an alignment value of zero, which is what we want.
This is a quick description of one of several steps needed to create mini-charts like those shown at the top of the page. Chapter 2 of my e-book, Dashboard Reporting With Excel, explains the entire process.