Posts tagged as:

Excel

Animation, Interaction and Dynamic Excel Charts

by Jorge

If you want to add animation to your charts that’s a clear sign that you have too much free time. Go out and play with the kids instead. :)

Yes, animation is a powerful attention-grabber, even more powerful than a glossy 3D pie chart in Crystal Xcelsius. And yes, it can actually be helpful (from time to time). But.

A good example of animation in data visualization is the famous Hans Rosling’s TED presentation, where a long-term pattern is clearly seen (at min 4:00):

This works well because the trend is easily identifiable and you don’t care much about the details. It would be much more difficult to make sense of this data if there were multiple trends and short-term variability. (More on the use of animated charts.)

After watching this presentation people often ask me: “Wow! Can we do that in Excel?” Wrong question. The right question would be “Wow! Can our data do that?”.

You can always create a simple animation in Excel, but it’s hard for a non-programmer to get a smooth transition effect. Here is an example from my dynamic Excel dashboard:

Although you can see a pattern emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be used).

Animation is better used if there is a pattern to be discovered, but you need something more: the ability to interact with the data. You must be able to stop, go back, get the details. While you can create a simple animation effect with a for/next loop in VBA, interaction with the chart is much easier using a Google spreadsheet with a motion chart:

(This is just an image. Click here to play with the chart.)

Dynamic Charts in Excel

In Excel, instead of creating a VBA routine, consider using a scroll bar linked to the value you want to change (year, for example):

Using a scroll bar adds some level of interaction because you can scroll back and forth, pause and examine the details for a specific year. Obviously, you need to change the chart data source dynamically.

Let’s recap how to create a dynamic chart in Excel. You can do it by changing values or by changing the data source itself (using a different range).

Option #1: Copying the Data

The range A1:E11 is our data set and we are comparing regions. Moving the scroll bar at the bottom changes the year. Column F is our data source. You can enter this formula there:

=OFFSET($A$1,ROW()-1,MATCH($A$13,$B$1:$E$1,0),1,1)

ROW() gets the current row and MATCH() returns the position of value 2002 (A13) in the range B1:E1. So, the formula in cell F2 reads something like this: start at A1, go down one row, go right three columns and get the data in that cell (range of width 1 and height 1). When the user selects a different year the data is copied to column F.

Option #2: Using a Dynamic Named Range

The second option is to create a dynamic named range. Create a named range and, instead of entering a fixed range, enter this formula:

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$A$13,Sheet1!$B$1:$E$1,0),10,1)

As you can see, it is very similar to the one above, but now the number of rows down is fixed (1) and it returns a range of 10×1 cells (because we have 10 regions, but if the number of regions varies you may use COUNTA(A:A)-1 to count the number of regions, excluding header – and don’t forget to move the year to a different cell…).

When you verify your range this is what you get:

You just need to use this range in your chart (when entering the range you must add the workbook name: “=Book1!SourceData”). You’ll also need a range for the category axis labels:

=OFFSET(Sheet1!$A$1,1,0,10,1)

You can download the spreadsheet here.

Take Aways

If you want to animate your charts make sure you do it because it adds value, not because you want to show off your skills. You can do it in Excel but it looks much better in Rosling’s Trendalyzer or in the Google spreadsheet gadget.

To create animations your data source must change dynamically, and that requires some work (and skills). I advise you to shift your focus from animation to interaction and use all that work to design a better user experience. Do you prefer a “wow!” (animation) or a “wow, thank you!” (interaction that actually helps the user)?

{ 3 comments }

Better Color Palettes for Your Excel Charts [Video Tutorial]

by Jorge

You need a better color palette for your Excel charts, but you are a mere mortal and your artistic skills are less than stellar. Hell, you can’t even choose the right tie for a suit! So, what do you do? (hint: watch the video below)

Maybe we could ask Edward Tufte for advice. In Envisioning Information, he writes:

What palette of colors should we choose to represent and illuminate information? A grand strategy is to use colors found in nature (…). Nature’s colors are familiar and coherent, possessing a widely accepted harmony to the human eye (…). A palette of nature’s colors helps suppress production of garish and content-empty chartjunk.

Better said than done, right? Well, let me tell you a secret: it’s easier than it seems.

How to create a new Excel color palette

I’m going to show you how to create a palette of colors found in nature ( you can create a palette of colors not found in nature, if that suits you better). Here are the steps:

  1. Select a photo you like. If you happen to live in a big city, finding nature can be challenging. Google for “[your location]+park” and you may get lucky. Go there, take some photos and download them to your computer. If that looks like a lot of work, you can try Flickr. Search for “autumn colors” and you’ll get some useful results. I’ve picked the one on the right;
  2. Upload it to an online palette generator. There are many, so choose whichever you like. I like Genopal because it is clean (no ads) and extracts exactly the number of colors we need (eight).
  3. Convert color codes to RGB. Copy the color codes to Excel and use the HEX2DEC() function to get the RGB values.
  4. Create the Excel palette. In Excel 2003, replace the existing colors with the new ones. In Excel 2007 create a new color theme.

This is it. You can try it in your next chart.

A new Excel palette: the making of

If you need the details, here is a step by step tutorial:

Is this good enough?

As you can see, the results are heavily dependent on the photo you choose, so you should try different photos and test the resulting palette in a chart. If you find it hard to come up with a good color scheme, you can use this method to create a basic color palette that you can tweak to meet your needs.

Other Excel and Chart Color Resources

Jon Peltier wrote two interesting posts about managing the Excel color palette (here and here) and Stephen Few shares some Practical Rules for Using Color in Charts (PDF). Bonavista’s Chart Tamer includes a professionally designed palette.

Photo credit: Xavier Fargas

{ 4 comments }

Are Excel Charts Hurting Your Business? 10 Mistakes You Should Avoid.

by Jorge

Are Excel charts, and Excel in general, a commodity, with no competitive advantage? Only if you want it to. Why? Because a vast majority of users:

  • Have the data analysis skills of a toddler (or less);
  • Can’t go beyond chart defaults;
  • Functions? They know how to click the SUM() button;
  • Don’t know what a dynamic chart is;
  • Think pivot tables are too complex;
  • VBA? No way!

I believe you can squeeze some competitive advantage out from Excel if you avoid some basic mistakes everyone else makes. Here is a very short list (some generic, some chart-specific).

#1. Assuming that Excel Can Do It.

Because a spreadsheet is such a loose environment, people often assume that all things numeric can be done using Excel and that no specific skills are needed. Using Excel as a database tool is an obvious example. If you can’t stop singing Ode to Joy every time you see one million rows in Excel 2007 that’s a clear sign that you are on the wrong track.

#2. Assuming that Excel Can’t Do It.

Most people aren’t aware of how powerful Excel is and use it almost as a pocket calculator. If you routinely have to manage quantitative data, learning a little more always pays off. As an example, you can use it to create complex executive dashboards or, at least, as a dashboard prototyping tool.

#3. Not Having a Go-To Person.

Not everyone needs to be an expert, but having someone that understands the business and proactively tries  to find better ways to perform common (or not so common) tasks should be a requirement.

#4. The Excel Islands.

If you have a data source you can connect your sheet to, do it. Don’t make the mistake of copy / pasting the data into your sheet. That’s a bad practice and it can undermine your organization’s entire information system.

If you connect your spreadsheet to the data source (via ODBC, for example) you are ensuring the integrity of your data and minimizing maintenance costs. If you are creating a new table, use a simple database tool like Access. And if you really, really, need to use Excel, make sure you create a table that can easily be exported and used by a different tool.

#5. The Uncharted Archipelago of Excel Workbooks.

Search for “book*.xls” in your hard drive. How many do you find? Get rid of all those useless files now.

#6. Bypassing IT.

We all have our little fights with IT, but we must understand their role and the reasons behind their actions sometimes make sense… Excel users often lack knowledge to understand data structures, data access, documentation, security (IT 101, basically). If your IT don’t want to conquer the world and actually helps users, you should work hand in hand with them.

#7. Not Using VBA.

I’m sorry to break this to you, but if you spend your time analyzing data and Excel is your primary tool, then you must use VBA. I’m not telling you should be a programmer, far from it. But you should be able to record a macro and make some simple tweaks. There are many repetitive tasks that can easily done using three lines of code.

#8. Excel is the only charting tool.

There are no neutral tools. They force upon you a certain way of doing things that you may not be comfortable with (or you shouldn’t). The Chart Wizard is one of the stupidest wizards I’ve ever met. If you need to spend time removing defaults there is something wrong with the defaults. Excel charts emphasize:

  • Stupid Defaults versus Cleaner Formatting Options
  • Static versus Dynamic
  • Marketing versus Insights
  • Disposable versus Reusable
  • One versus Multiple
  • Large versus Small
  • Island versus Continent
  • Presentation versus Discovery

I’ll detail this in a future post but, like PowerPoint, Excel charts do have their own cognitive style. If you want to do things differently you can, but you are choosing a path of endless pain. So, give some users the option to use a different charting tool or, as a bare minimum, use a better wizard.

#9. Forgetting the 3R’s.

Reduce, reuse, recycle. Yes, I’m talking about Excel charts. How many disposable charts are cluttering your hard disk? Go green when creating your charts:

  • Charts are typically much larger than needed, so you can safely reduce their size;
  • When you create a chart make sure that it can be reused when the data updates;
  • A chart for Market A is probably very similar to a chart for Market B. By recycling the chart from market to market you don’t need do create new charts and pollute your hard drive.

#10. Not Making Things To Work Together

Lists like this one are useless, unless you know how the basic system works. Try to see the big picture and it will be much easier to understand where each piece belongs to:

  • If a function needs a range address, you will know that another function can provide the address (first step to create dynamic charts);
  • If you are repeating the same stupid task, you’ll find a way to automate it;
  • etc.

Bottom line: make sure you know what you should use Excel for, find inefficiencies and give power users the option to use specialized tools.

{ 11 comments }

How a Bad Excel Dashboard Made me a More Skilled Excel User

by Jorge

Some years ago, as part of my (then) new job, I had to maintain a monthly updated Excel dashboard. It was a maintenance hell, I hated it, but I couldn’t change it because of my poor Excel skills.

“This is stupid, there must be a better way”, I kept saying to myself.

So, I searched, and searched, and searched, and within a few months I became the most skillful Excel user in my company and I could solve my initial problem. An all day long update turned into a ten minute task. I revamped the entire dashboard, but I kept the same user interface.

An Excel Dashboard is a Jigsaw Puzzle. Learn How to Solve It.

Back then, I was able to use some of the more common formulas, like most Excel users do. But if you want to create Excel dashboards you must understand how everything fits and works together. If you don’t, expect nothing less than a spreadsheet hell. You should never underestimate that.

I hate repetitive and time-consuming tasks, and I avoid them like the plague. If I suspect that a co-worker, after asking for a report, will come back and ask for different scenarios, I usually offer that functionality from the start. It’s a win-win situation: it doesn’t take longer than a static report, I avoid extra work and the user loves to play with his/her new toy. :)

Make Sure You Market Your Skills

As I said, I kept the same user interface in that dashboard. It proved to be a huge mistake, from a personal marketing/career perspective. After all these years, I still believe I made a remarkable job but, because I kept all the changes behind the scenes, they went unnoticed by the users — including the boss. Well, marketing and promotion is a big step out of my comfort zone, and office politics is not exactly what I do best…

Lessons Learned

That old dashboard was created by an above-average Excel user, but he failed to understand this basic concept: an Excel dashboard is a jigsaw puzzle, and fewer pieces makes it simpler to solve (for example, a simple pivot table can often replace dozens of formulas).

Go beyond the individual formulas. Create a project that forces you to learn how they work together (that’s what my dashboard tutorial is all about).

And never make your outstanding job invisible, use your Excel skills to work less but try to make sure that too much Excel will not harm your career…

{ 0 comments }

Fighting IT? Prototype an Executive Dashboard in Excel

by Jorge

I am a moderately advanced Excel user. This means “a dangerous person” for the IT department, but I like this daily fight, and Excel dashboards are among my preferred weapons. Let’s see how they can be used.

Excel is the best tool for executive dashboard prototyping, because of its flexibility and development costs. Creating a fully functional prototype is not hard and it should be available for user feedback in a matter days. So, make sure that, every time you spot a dashboard project, a prototype in Excel is included.

Since most business intelligence applications are notorious for their lack of basic chart formatting options, it shouldn’t be hard for you to create a simply set of charts that the IT is unable to implement. If needed, use some advanced Excel charting techniques (including dummy series), but make sure they add real value to the user experience. Interactive features like visual what-if analysis are always cool and the users love them.

When presenting your project, do your best to convince your audience that you are technology-agnostic and all you care about is to create the best answer to users needs.

IT will try to change your project, naturally. Try to avoid the “security bomb” (their favorite). You know how poor their expensive BI toys are, and you should know what they can and can’t do with them. Minor concessions can earn you some points. When they tell you they can’t implement your core ideas be prepared to fake genuine surprise, compare costs (again) and emphatically say that their options clearly don’t meet the organization’s needs.

Pissing off the IT department is one of the most enjoyable games in corporate life, but be a gentleman and don’t make them look stupid. They don’t usually have a good sense of humour and take their quest to conquer the world very seriously. If you really want to implement the dashboard, don’t make it an island if you can avoid it (connect it to the tables in the IT infrastructure, instead of copy/pasting data). 

Seriously: Excel is a great tool for dashboard prototyping. You can easily create multiple alternative user interfaces, get feedback from users or find design flaws. The end result should be much better than trying to capture some ill-defined requirements and send them to the IT, where user interface design usually ranks very low in their priorities list.

{ 16 comments }

14 Misconceptions About Charts and Graphs

by Jorge

Misconception #1: A Better Chart Starts With… the Chart

Wrong. It starts by asking yourself if you really need one. Perhaps a statistical measure of some sort is good enough, perhaps you should use a table. If your job is to find patterns in a data set and build shared knowledge about it, what really matters is how efficiently the message is sent, and how efficiently it is received by the audience (two different things).

Misconception #2: You Should Master the (Technological) Tools of the Trade

No, you don’t. Just because you know how to create a chart in Excel it doesn’t mean that you know how to create a chart. If you use Microsoft Excel as your charting software then yes, you should learn more Excel (to spend more time with the kids). But you must go beyond technology, or else you end up creating some very stupid charts. Please note that a vast majority of Excel training courses will not teach you what it should (best practices). It will only tell you how to make “cool” graphs, like a 3D exploded pie chart.

Misconception #3: Defaults are good enough

They aren’t. Each chart must be tailored to the specific data set, audience and message. For instance, try to create a graph that clearly displays a large number of series and you’ll fail if you use the defaults (but can do it with clever color coding). And if you use recognizable defaults, like the Excel 2003 charts, you’ll look very, very, lazy (at best).

Misconception #4: Vendors obviously implement the very best templates

(I’ve heard this one recently, and I found it so incredibly naive that I had to write about it.) They don’t. About 90% of the Excel 2003 chart gallery is junk, and you must heavily reformat the remaining 10% to get something useful. Select other tools, like Crystal Xcelsius and the scenario is even worse. And I am unable to create in Cognos something that remotely resembles a chart (people tell me that version 8.4 is a little better).

Misconception #5: Better charts are just “prettier” charts

I hear this all the time. A good chart may look “prettier”, but that’s just an unintended consequence of a design that communicates better. In information visualization, prettiness must be a by-product of function. The very concept of a “better communicator” is sometimes difficult to comprehend, and trying to explain it is a waste of time, because people need to see it in action. You must take the user by the hand and guide him/her. You must force comparisons: “what can you learn about x using this chart?” and “what can you learn about x using that chart?” “how long did it take you to learn x using this and using that?”.

Misconception #6: It’s All About the Wow Factor

It is not. Many marketers and graphic designers fail to understand this. Marketers are hopeless in their relentless search for the wow factor and the eye-catching, “professional-looking” graphs, and graphic designers should know better, but they prefer to sacrifice data on the altar of Beauty (form is everything, data is a nuisance).

The dominant view among visualization experts (namely Tufte and Few) is that “form follows function“: every ornament in a graph should be eliminated, every object must serve a clear purpose, efficiency should be maximized (labeling series instead of using a legend, for instance). Given the extremely low graphic literacy levels among the general population, this may not always be the best approach.

Misconception #7: A good chart displays the actual values

No. If you label each data point you get a useless table over a useless chart. Labels are not only a distraction but often actually hide patterns in the data. Short labels and annotations can, and should, be used to identify or explain outliers or other interesting data points and circumstances. If your audience expects to see the underlying data then add a link to the table.

Misconception #8: Good Charts Should Be Read at a Glance

No, they don’t. The more complex, the longer it takes. It really doesn’t matter if it takes a second or an hour. What matter is how efficiently the graph  communicates. If a chart takes for ever to be read look for bottlenecks: the series are not easily identifiable, patterns are hidden, demands on the working memory are high, etc.

Misconception #9: The More Detail the Better

What we see as detail can be seen by someone else as clutter. Clutter is the natural child of loss aversion and is is very difficult to remove. If you have 12 competitors your audience will want to see the market share for each of them, even if it doesn’t make any sense. Tufte says “to clarify, add detail”, and yes, 12 competitors in a line chart can be made clear and useful, but you must know how to categorize them and provide a framework to help the user (you can use a large number of categories in a pie chart, for instance).

Misconception #10: It’s All About Selling Your Point, No Nuances

In The three laws of great graphs Seth Godin says that “there is no room for nuance [in a presentation]” and your charts should reflect that. Maybe it is just me, but I hate it when I am not allowed to draw my own conclusions because the data made available by the presenter is too biased towards his/her own points of view. Depending on the situation, a clear path that is supported by a lot of details is much better than a yes/no pie chart.

Misconception #11: You Have to Have Color, Lots of Color

Wrong. Color is a very difficult subject. Large surfaces of primary colors like we often see in presentations should be avoided because they are hard on the eyes and, because everything stands out, nothing stand out. A good option is to use grays for non-data elements like grid lines, and pale colors for color-coding. As a rule of thumb, color should always carry some meaning. Use primary colors to highlight a data point or some other small detail.

Misconception #12: A Single Chart is Enough

It is not. We live in an increasingly complex world, and traditional charts are very simple tools. While we wait for a new set of charts to be invented, we can use interaction (see below) and multiple charts to create a richer picture. That’s why scatter plot matrices, small multiples or trellis displays, and specially those multiple variations of executive dashboards are much more powerful than a simple chart.

Misconception #13: Charts Are Interchangeable

They aren’t. You can use a column chart or a line chart to display a time series, but while a line chart performs better than a column chart when reading trends, it is easier to compare data points using a column chart. Most visualization experts will tell you that you should use a bar chart instead of a pie chart (also because it is easier to compare data points), but a pie chart gives you the perception of a whole that is absent in a bar chart. Every graph has its own strengths, and you should select the one that suits your needs.

Misconception #14: Create It and Forget It

Don’t. Making sense of your data is a process of exploration and discovery. A pattern in a subset may be hidden by a noisy background. Different measures may lead to more complex insights. Creating a chart that the user can interact with should always be your primary goal. Unfortunately, that’s beyond the skills of an intermediate Excel user (if you what to learn about interactive charts my Excel dashboards may be a good starting point).

*

This post lists 14 widespread misconceptions about charts, but probably is a very incomplete list and you may not agree with all of them. What misconceptions would you add/remove?

*

[Update: Jon has been writing extensively about Excel 2003 and Excel 2007 (by the way, it's a great resource that helps us to see through the marketing noise). I said in the comments below that I prefer to use Excel 2007 charts to post images in this blog. He doesn't agree and he tries to prove in his last post that charts in Excel 2003 are actually better. He uses good examples to prove his point but I still believe that this (Excel 2007):

looks better than this (Excel 2003):

Yes, probably there is an "overaggressive anti-aliasing", but the line in Excel 2003 is too "crispy" for my taste. Again, it is just a matter of creating images for a blog, not exactly for serious work...]

{ 22 comments }

Shocking Revelation: Excel 2007 is Useless

by Jorge

Conditional formatting vs standard bar chart in Excel 2007Excel 2007 is useless. This was a shocking revelation when I tried to create the charts for last post (Chart Design: Abortion Ratios 1980-2003).

It was my first serious attempt at using Excel 2007. I failed miserably and gave up. I had to do everything in Excel 2003 and then open the file in Excel 2007 for some polishing.

I new there was some senseless options in conditional formatting (actually, it is worse than I expected, as you can see on the left chart). And Stephen Few said everything I needed to know about Excel 2007 new chart engine. And, by that time, Microsoft bought Dundas. Isn’t that enough to raise some red flags?

But I had to see it for myself. I will not talk about the ribbon. I keep it hidden, so it really doesn’t bother me. And having to learn a new interface is something that I don’t mind – let’s call it brain workout.

There are several small but frustrating things, like trying to select multiple charts as objects, but what really pissed me off was that empty macro I got, after recording some chart formatting. I couldn’t believe it and tried several times, before googling for this “error”. It isn’t one (you can compare both versions here and get more bad news, as I did).

I suspect that this is just the tip of the iceberg. And it is not the major changes in features or the new user interface. It is the small things, left behind just to annoy you.

Bottom line: stay with Excel 2003. Charts in Excel 2007 look much better due to the new rendering engine, but do you really want to buy Office 2007 just to polish some charts?

Please share your thoughts in the comments. I’d like to know if (and how) you can do productive work with Excel 2007.

{ 50 comments }

Poll: Do you use VBA in your Excel files?

by Jorge

I just added a poll to the sidebar on the right (RSS readers might need to visit the site to see it). I’d like to know if you use recorded macros or write VBA in Excel.

I ran an informal poll among friends and colleagues with catastrophic results and I’d like to compare results.

Please add a comment to your vote.

{ 5 comments }

How to create a dashboard in Excel

by Jorge

Excel dashboards and executive reports are powerful, fairly easy to design and a great ways to improve your Excel and data visualization skills. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined (and then send project to IT for implementation).

Once you know what will the Excel dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating it:

  • how to bring data into your spreadsheets;
  • how to manage the data and link it to the dashboard objects, like charts and tables;
  • how to design the dashboard report.

Let’s take a look at each of them.

How to bring data into your Excel dashboard

Yes, Excel is a very flexible tool, but to create an Excel dashboard you can’t just paste some data and add a few charts, can you? You must maintain and update it, and if you want to decrease the cost associated with that tasks you must impose some structure to your data.

Usually the data will (should) not be entered directly into the spreadsheet. This means that you must copy /paste the data from the source (not wise, really) or connect the spreadsheet to the data source. There is a standard way to communicate with external databases called ODBC. You can use it to connect your dashboard to a table in Access or Oracle, for example.

Once you have that connection established, every time the data changes at the source it also changes in the spreadsheet, after refreshing. Keep in mind that a good practice is to minimize the amount of data you bring into your spreadsheet. If you don’t need it, don’t use it. However, if there are calculations to be performed, try to do them at the source.

Data can be imported using two basic structures: a flat file and a pivot table. As a rule of thumb, I would say that a pivot table tends to create larger files but the calculations will be faster. The flat file will be smaller but, if you need complex formulas to select the data, performance will suffer (you must test this in your own project).

How to manage the data and link it to the dashboard objects

If you use pivot tables you can extract the data using the GETPIVOTDATA function. If you use a flat file, there is a vast array of formulas you can use, like database formulas (DSUM, DGET), lookup (VLOOKUP, MATCH, INDEX) or math formulas (SUM, SUMIF, SUMPRODUCT).

Having many formulas and calculations is dangerous for your dashboard integrity. You can decrease this by using pivot tables. That’s one of the reasons I choose pivot tables by default.

Named Ranges

Do I have to tell you that you should document your work? Ok, I’ll tell you: yes, you should. But you can simplify that boring task by using named ranges. I am sure this is a familiar example: “=Sales-Expenses” is better than “=$G$4-$H$5”.

Named ranges not only help you to make your formulas cleaner and easier to read, but they also play a key role in data management for Excel dashboards. Example: let’s say that the next update adds a new column to your table. If you don’t use named ranges you’ll have to change every single formula and add that extra column. With dynamic named ranges, the extra column is immediately available. This means less, but more reliable work.

Dynamic Charts

I am a truly believer in dynamic charts, and dashboards that the user can’t interact with doesn’t make much sense to me. All my dashboards have controls to change markets, change products, change sales territories, whatever. What is important is that the user must be able to interact with the dashboard and build his/her knowledge by exploring the available data.

If you are creating interactive charts you will probably need dynamic ranges. You can do it using the OFFSET() function.

Macros

OK, you are not a programmer (I am not) but you should consider using some macros to speed up some repetitive tasks. Also, some cool things that you can add to your dashboards can only be done using macros, like greeting the users and selecting the right profile when they open the dashboard (you may want to read the discussion around the use of VBA in Excel dashboards).

How to design the dashboard report

If you like the sort of charts that you find in marketing-oriented tools like Crystal Xcelsius let me tell you this: they don’t work. There is a first wow! and then their uselessness starts creeping in.

This is not only a matter of opinion: I tried (and failed) to replicate my Demographic Dashboard (see below) using Xcelsius and detailed the process in a series of posts (1, 2, 3, 4, 5), so I know why these tools don’t work. Don’t try to use Excel to mimic them.

Excel 2003 chart defaults are ugly and you should avoid them like the plague (and make sure you select the acceptable formats). Throughout this blog you’ll find many charting tips, and tips to improve your Excel dashboard, so I will not elaborate much on this. The basic premise is this: a chart is used to discover actionable patterns in the data, and you should do your best to show those patterns.

This means that you should remove everything that has no added value (3D effects, backgrounds, etc), create a hierarchy of focus and context data (make relevant data stand out, using color carefully) and add at least a basic form of interaction (let users select the series, for example).

Books

The reference in dashboard design if of course Stephen Few’s book, Information Dashboard Design. Other recommended reading are John Walkenbach’ Excel Bible (2003 or 2007) and Excel Charts (2003 or 2007); Tomothy Zapawa’ Excel Advanced Report Development (2003 or 2007); Michael Alexander’s Excel 2007 Dashboards and Reports for Dummies and Jon Peltier’s blog.

An Excel Dashboard Example: The Demographic Dashboard

This is an example of dashboard reporting using many advanced functions:

As I said above, it is difficult sometimes to put everything to work together in an Excel dashboard project. With the Demographic Dashboard, I wanted to show how to connect the dots and create a fully functional dashboard. So, I used a very interesting dataset from the US Census Bureau (population by sex, age and country, 1996-2050) to design it.

Actually, there are three dashboards… I took advantage of Excel’s built-in flexibility to create three versions of the same dashboard. They all look the same, but each one uses a different set of techniques:

  • The VBA version: This was the first one. It uses Visual Basic for Applications and pivot tables. It is faster than the other ones, but it relies on VBA to perform some tasks, and that may put off some users (I’m just using recorded macros, nothing too complex);
  • The VBA-Free version: After some discussion on the merits of using VBA (Excel Dashboards: Do You Need VBA?) I decided to add a VBA-free Excel dashboard; It mainly uses pivot tables and the powerful GETPIVOTDATA function. It is a well balanced version: it is slower than the previous version but since it doesn’t use VBA some users don’t have to leave their comfort zone;
  • The LookUp Table: It uses a spreadsheet database (basically a table that you paste into a sheet) and lookup functions (VLOOKUP, MATCH, OFFSET) to get the data for the dashboard report. Personally I don’t feel very comfortable with spreadsheet databases (more maintenance, more error prune, slower) but it is a good starting point to learn advanced Excel techniques (and sometimes is it your only option).

For a deeper discussion on the pros and cons of each version you may want to read the post Performance vs flexibility in Excel: Demographic Dashboard Lookup Edition.

Wrap-Up

Excel is a great tool for any type of executive reporting. It allows for fast, flexible and cost-effective dashboard implementation (or prototyping). Intermediate users should be able to design and implement an executive dashboard in a matter of days (or even hours, for the first draft), provided that they know how to select the right techniques.

An Excel Dashboard is the Perfect Excel Learning Tool

You can read a million tips and still miss that wow! moment when you see all those small pieces working together. That’s why designing a dashboard is one of the best ways to learn Excel. If you want to get the details but also the whole picture, get my Demographic Dashboard Tutorial and start your Excel wow moment now!

{ 34 comments }