• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Big data? Create Excel chart dashboards

by on
in Excel Training,Office Technology

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

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/37339/big-data-create-excel-chart-dashboards "

Leave a Comment