Big data? Create Excel chart dashboards

We’re not suffering from a lack of data. We are, however, struggling to make sense of it all in a way that facilitates business decisions and infuses momentum into our projects. Making data consumable by those who need to use it can sometimes take the form of charts. But, how many workbooks and worksheets do you need to open and scan to find them?

A chart dashboard can be the solution. A brief definition might be: a graphical representation of diverse data sources that inform decisions. It’s easy to get carried away with dashboards, including more and more elements until it’s no more understandable than staring at the numbers in a worksheet. Planning is important.

Plan to succeed

Sketch out what you’re thinking. Check with end-users to grasp what they would like to have in a dashboard. Break out the markers (electronic or smelly) and draw what you think it should look like. Then, annotate your drawing with the file names and locations of your data sources. Placement is important! Put the most important charts across the top. Mini­­mize scrolling as much as possible. Choose color schemes that are ­meaningful to the end-user (e.g., red = ­negative, green = positive).

What not to do next

Don’t start with the actual worksheet that will hold your dashboard. That’s last, along with any titling or captioning.

Create your charts

The fastest way to create a chart is to place your cursor in the middle of the data you want to chart and press F11 (PC only). This creates a chart on a separate worksheet, giving you plenty of room to add and tweak elements without worrying about the final size. Name the chart “worksheet tab” or something similar, using abbreviations to make it easier to locate. You might also want to name the data worksheet tab it links to the same color.

Creating the dashboard

Create a blank worksheet for your dashboard. From each chart worksheet, select the chart. On the Design Chart Tools (or Pivot Chart Tools) contextual tab, choose Move Chart. Click the Object in: radio button and select your dashboard worksheet. Repeat this for each chart. Don’t worry about size and appearance yet. They will all overlay each other, but you can click and drag to separate them.

Tweaking tips

  1. Zoom way out to position charts on the worksheet.
  2. Test visibility by zooming to your desired level for the final product, which might not be 100%.
  3. Use Drawing tools or Format tools to size and align each chart with another, as needed. Treat them like objects you just drew on the page.
  4. Turn off interactivity, if needed, on your Pivot Charts by going to the Analyze tab and deselecting Field List and Field Buttons.


Contributor: Melissa P. Esquibel, Microsoft® ­Certified Trainer