Use PDF files for Distributing Dashboard Reports

I’ll just come out with it: you should share PDF files, not Excel files when distributing dashboard reports.

Sacrilege! Say it isn’t so! I know, this is pretty odd advice coming from someone who professes that Excel is one of the best tools available. But there are times when you have to remember that your focus is providing and distributing the information, not the vehicle that your audience uses to view information. It pains me to say it, but they don’t care how efficient your formulas are, or what controls or tricks you employed in Excel to develop the report that they’re inspecting.

Efficiencies in Excel spreadsheet development are important, and that has been discussed to no end on the Internet, this site included. But all of that becomes completely irrelevant when you remove 100% of the recalculation time for your real end users by providing the final charts, metrics, and calculations to them as printed pages.

These days when you distribute PDF files to your report readers, you empower them to be able to take your beautiful works of art with them everywhere they go on their smart phones, albeit at the cost of interactivity. I’m confident that it’s a trade-off they’ll gladly accept. Even better, you don’t have to spend time teaching them how to use your file, nor spend time building in safeguards and usability enhancements, outside of what you want for yourself and the core functionality of the document, that is.

After all, for every Excel user that as comfortable with it as you are, there’s at least one other in that same office or company that always seems to need help regardless of how easy to use you thought that you’d made it. You know it and I know it. That’s the reality and unfortunately it’s not likely to change any time soon. When you make it easy for them (by eliminating Excel altogether in this case) you make it easy for yourself too.

There are a number of additional benefits that distributing a PDF file offers you:

1. The presentation of your dashboard style report will be pixel perfect.
2. The readers won’t be able to accidentally alter the data or otherwise corrupt the workbook.
3. While there are a number of techniques to keep the file size down (and thus increase the performance) of Excel dashboard report files, they’ll never compare in size to the amount of analysis that you can pack into a PDF file that’s just a few megabytes in size.

Color Code Different Sections

At one time my group distributed a printed and spiral bound monthly financial analysis package to each manager of the various operations groups and other business leaders. To separate the different sections within the document, tabbed cardboard dividers were used. To cut costs for both materials and preparation labor, and to save on our environmental impact, I proposed that we color code reports for different departments, product lines or other important topics. This will allowed users to instantly know when they were looking at the correct section of the document for their numbers, and the dividers were no longer necessary.

Personally, I prefer a very minimalist color scheme for dashboard reporting: black, shades of grey and a splash of red when required, like this. But many prefer the style that Charley Kyd promotes. At the time, my audience preferred the latter, so I took advantage of it.

When reporting for different business lines, I used VBA code to change the color of the elements on the page: the title area, the charts, the tables. Excel is much more flexible now with its available color choices than when I was building my first dashboard reports with Excel 2003, but that’s a bit of a double edged sword. Making choices can a terrible distraction. Pick a few basic color combinations and get on with it.

I’m no graphic designer, so here’s what I did to achieve what I considered to be a professional result: I plagiarized. Well, not exactly plagiarized, but rather sought inspiration from experts. I knew that design experts are often hired for their assistance in preparing the annual reports for large public companies, so I spent a bit of time downloading some sample reports from the larger companies, looking for combinations that I liked and that I could emulate rather well with my Excel dashboard. So in essence I got expert advice for free!

As to the specific VBA code, preparing that wasn’t overly difficult either, as I used the macro recorder to have much of the code generated for me by Excel and then I modified only certain aspects of that generated code to suit my purposes. I’m confident that you’ll have no trouble handling that.

Automate with PDF Production with VBA

As I said earlier, my first “real” dashboard reporting systems were built with Excel 2003, and I must say that I had a heck of a time developing the required code to produce PDF files back then. The only thing that enabled me to do it was that I also had Adobe Acrobat installed on my system, and after an inordinate amount of online research, a ridiculous amount of hours to be honest, I finally determined how to automate the Adobe Distiller program. It might have been easy if my company had permitted me to register as a developer with Adobe but the cost was viewed as prohibitive.

Luckily for you, these days you won’t need to go through any of that.

You see as of Excel 2007 (SP2 I think), and now in Excel 2010, you can now produce PDF files with as little as a single line of VBA code:

This singular ability enables you to easily generate all of the benefits discussed earlier. What does that line do? It prints the active worksheet, as you’ve defined the printed area to be, to a good quality PDF file. You can easily change the file name and location. In fact, that’s key to your new reporting system.

It might take you a bit of work to develop a full PDF creation routine that expands on that single line, but it’ll be well worth it. Then when you click a “Make PDF File” button, your program will begin stepping through all of your important data points, recalculating the dashboard, and then preparing a PDF file for that data point. Then it will move on to the next data point and repeat the process until it has created a PDF file for each data point.

Meet the PDF Toolkit (PDFTK)

If you haven’t heard of it before, allow me to introduce you to an absolutely indispensable tool for those who don’t have high end PDF software like Adobe Acrobat: PDF Toolkit (PDFTK). Heck even Acrobat users can make good use of this one.

PDFTK is a utility that lets you do practically anything that you’ll ever need to do with PDF files: merge, split, rotate, and more. It’s free, and you don’t need Acrobat to be able to use it.

Download and install PDFTK now.

Make the work easier to repeat

My favorite approach to PDFTK, given my inclination towards batch files, is to script it on the command line (see examples). Of course, it’s easy to incorporate any of that into some VBA code too.

Now, seeing as you’re still with me, I’m going to reward you by doing something silly and literally give away a bunch of valuable program code. It still needs some details to be filled in, but the skeleton should help immensely.

Clearly, this is a simplified shell of an example but it shouldn’t be too difficult, especially with a good VBA book on your desk such as Excel 2010 Power Programming with VBA (Mr. Spreadsheet’s Bookshelf), to make it work for your specific needs.

Your raw data might have originated in report files, Excel tables, Access databases, other database formats, or even PDF files, but you persevered and created models that allowed you to capture and manage that data, and create summaries in formats that may never have been previously envisioned to help you and your group to gain new insights into your operations. Then you used the skills that you learned in developing Excel dashboard reports to present that data visually in ways that let you easily control the included data.

Even if you opt to forgo the convenience and speed that VBA programming can bring to your project, distributing the final results of your efforts with PDF files will demonstrate to your entire group that, in no uncertain terms, you excel with Monarch.