Choose Excel Dashboard Chart in Drop Down List

You can make an interactive dashboard in Excel, even if you can’t use macros, or prefer to avoid them. Select a chart or chart data from a drop down list, and the selected item will instantly appear on the dashboard – no macros required.

See how to build this dashboard, watch the video, and download the sample file to see the completed version.

dashboardselect08

Name That Range

In the screen shot above, “Chart Data – East” is selected in the drop down list, and that data will appear on the dashboard, replacing the current data.

There are 4 choices in the drop down, and on a separate worksheet (ChartInfo), there are four matching ranges, shown below.

  • ChartData
  • EastData
  • ChartRange
  • ChartEast

The contents of the selected range will appear on the dashboard.

dashboardselect03

Items for the Drop Down List

The list items for the drop down are stored in a range named ShowList, on the ChartInfo sheet.

dashboardselect07

Then, on the dashboard sheet, a data validation list is based on the ShowList name.

dashboardselect06

Create a Lookup Table

In the column to the right of the drop down list items, the matching range names for each item are entered.

Above the lookup table, cell P5 is named SelRange, and it has an INDEX/MATCH formula that returns the range name for the item selected in the drop down list:

=IFERROR(INDEX(P7:P10,MATCH(O5,ShowList,0)),P7)

The Chart Data – East item was selected, and its range is named EastData

dashboardselect02

Add a Linked Picture

Next, a named formula — ShowChart was created. It uses the INDIRECT function to return the range from the SelRange cell:

=INDIRECT(SelRange)

dashboardselect05

Finally, on the dashboard sheet, a linked picture was added, and its link was set to ShowChart. Now, when you select an item from the drop down list, the linked picture changes, to show the range that matches the selected item.

dashboardselect01

In the screen shot below, Chart Date – East was selected, so the EastData range is shown.

dashboardselect04

Video: Choose Dashboard Items from Drop Down List

This example is an update of an earlier workbook, in which you could choose between 2 items – a chart, or the chart’s data. The video below shows how to set up that version.

The steps are similar for the new 4-item version, with the differences noted in the instructions above. Download the sample file (link below), to follow along with the video.

Or watch on YouTube: Show Excel Chart or Data on Dashboard With No Macros

Free Excel Dashboard Webinars

If you’d like to learn more about building Excel dashboards, Mynda Treacy is offering two free one-hour webinars. Get the details, and sign up for a date and time that is convenient for you.

  1. How to Build Excel Dashboards
  2. Dashboards with Power Query and Power Pivot.

Mynda has also opened registration for her acclaimed Excel Dashboard course, and you’ll get 20% off, if you sign up by today — July 30th. The dashboard course is a great investment, and you can read my review for the highlights, and more information.

Download the Sample File

To see how the interactive dashboard works, you can download the sample file from the Excel Sample Files page on my Contextures website. In the Charts and Graphics section, look for CH0002 – Show or Hide Excel Chart. There are 3 versions of the file, so download the ones that you need:

  1. Excel 2003,
  2. Excel 2010 (2 item drop down)
  3. Excel 2010 (multi-item drop down)

___________________

Excel Chart Compares High and Low Scores

Last weekend, I helped someone who needed a chart for a fantasy football league, to show the highest and lowest win/loss scores for each week. To help explain what it should look like, they posted a picture of a hand drawn chart, similar to the picture below.

By the way, I created the sketch in Excel, by using the Marker option, in the Artistic Effects. Who knew that Excel was so artistically talented?

Continue reading “Excel Chart Compares High and Low Scores”

Quick Charts and Free Upgrades

After you put data onto a worksheet, you might need to add a chart, for a visual summary of the data. In the screen shot below, there is sales data for 4 regions, and a column chart from that data.

You can quickly see that things are going better in the East region, than in the North. Probably all the snow up there kept people away from the stores!

quickchart01

Continue reading “Quick Charts and Free Upgrades”

Make a Better Pizza Pie Chart

While flipping through a magazine on the weekend, I saw this pizza pie chart. Yes, the pizza looks delicious, but the toppings are split down the middle. That makes the vote appear to be 50-50, not 58-42. Fun idea, but bad chart!
pizzapiechart01
The picture was making me hungry. I didn’t want to ruin my dinner, by going out for a pizza snack, so I decided to make a better pizza pie chart in Excel.
Continue reading “Make a Better Pizza Pie Chart”

How to Make a Pie Chart in Excel

Sometimes, you need to make a pie chart in Excel. No, it’s not the best way to present data, but sometimes you have to go with the chart type that someone else chooses.

  • Maybe your boss is demanding that you make one for the annual report.
  • Perhaps your professor is basing 50% of your final grade on building the perfect pie chart.
  • Your sister needs help with a report for her dessert of the month club, and a pie chart seems okay for that!

So, if you’ve never built a pie chart in Excel, or it’s been so long that you’ve forgotten the steps, here’s how you can do the best job possible.

Set Up Your Data

What should your data look like, if you want to build a pie chart? It should have the following:

  • just one row or one column of numbers
  • (optional) one row or column with text that describes the numbers.
  • (optional) heading cells for the numbers and text

Warning – Only use a few numbers, or the pie chart won’t be readable. I’d pick 6 as the upper limit, but let your conscience (or your boss) be your guide.

In the screen shot below, you can see two sets of data showing sales per region.

  • At the top, the data is arranged vertically, with numbers in a single column.
  • In rows 9 and 10, the data is arranged horizontally, with numbers in a single row.

We could create a pie chart from either set of numbers.

piechart01

Insert the Chart

After your data is set up, follow these steps to insert a pie chart:

  • Select any cell in the data.
  • On the Excel Ribbon, click the Insert tab
  • In the Charts group, click Pie
  • Then, click the first pie option, at the top left. Do not be lured by any of the other options, like exploded pie, or worst of all, a 3-D pie.

A pie chart will be inserted on the active sheet, in the middle of the Excel window.

piechart03

The chart will show the heading from the number column as a chart title, and a legend with the text descriptions, if your data includes those.

Move the Chart

The new chart is selected, so you can move it to a different location, if you don’t want it in the middle of the sheet.

  • Point to the border of the chart, or to the chart area – a popup description tells you what you’re pointing at.
  • Then, drag the chart to the location where you want it.

piechart04

Resize the Chart

When the chart is selected, you can also resize it, to make it larger or smaller.

  • Point to the one of the dotted handles on the border of the chart, where the pointer will change to a two-headed arrow.
  • Then, drag the handle in or out, to change its size.

You can resize the chart again later, after making all the formatting changes.

piechart05

Add Labels to the Chart

By default, the chart has a legend at the side, that shows the text description for each slice of the pie. To make it easier to read, you should put a label on each slice, and get rid of the legend. That way, people won’t have to look back and forth, from the legend to the pie, trying to match the colours.

To add labels, right-click on any slice in the pie, then click Add Data Labels, in the popup menu.

piechart06

Each slice will show its numeric value. You’ll make those labels look better in a minute.

piechart07

Delete the Legend

Now that the pie slices have labels, we don’t need the legend at the right.

To delete the legend, right-click on the legend, then click Delete.

piechart08

Change the Data Label Contents

The data labels currently show just the value for each slice, in a small, dark font. We’ll change the information that shows, and make the labels easier to read.

Right-click on any label, and click Format Data Labels

piechart09

  • In the Format Data Labels window, click the Label Options category, at the left.
  • In the “Label Contains” section, check the items that you want to see on each pie slice. If you aren’t sure what each item is, add a check mark, to see it in the preview window.
    • I’ll include the Category Name and Percentage in this example.
    • Keep it simple – don’t try to show all the information in each label
    • I left “Show Leader Lines” selected, even though it won’t have any effect in this chart – all its labels will be inside the pie.
  • In the “Label Position” section, choose the position that’s best for your chart.
    • I’ve selected Inside End, because these slices are all fairly big.
    • For a pie with narrower slices, select Best Fit instead.
  • Click Close, to apply the changes

piech
art10

Format the Data Label Font

Now that the data labels show the content you want, format them, so they’re easier to read.

  • Right-click on one of the data labels, to show the popup menu and formatting bar.
  • In the formatting bar, select a font that’s large enough to read – 14 pt in this example.
  • If there is room, format the font as Bold
  • Select a font colour that contrasts best with the pie slice colours – I picked white.

piechart11

Pie Chart Alternative

If you have the option of choosing a different chart type, consider making a Bar chart or Column chart, instead of a pie chart.

These charts make it easier to compare the values, as you can see below.

piechart12

Video: Make a Pie Chart in Excel

Watch this video to see the steps for creating and formatting an Excel pie chart.

Or, watch the video on YouTube: How to Make an Excel Pie Chart

__________________

Create an Excel Line Chart with Target Range

With an Excel line chart, you can show the sales results from a date range, to see how things have gone. For example, in the chart shown below, you can see the sales quantities for the first six months of the year.
chartlinetargetrange02

Add a Target Range

To give a better picture, you might also want to show what the sales targets were for each month of the year.
In the next chart, a blue band with the target range has been added, and you can quickly see which months were over or under the target amount.
chartlinetargetrange

Set up the Data

The sales data for the chart is in columns A:B in the worksheet shown below. The high value for the monthly target is entered in column C for each month, and the low value in column D.
Then, in column E, a formula calculates the difference between the high and low:
= C4 – D4
chartlinetargetrange03

Create the Chart With Target Range

Once the data is set up, select all the data and headings, except for the Target High column.
Create a stacked column chart from all the data, and change the Sales amounts to a Line chart type.
chartlinetargetrange05
For the two Target series, hide the series that is the low value, and leave just the “Difference” amount – the range between the low and high values.
chartlinetargetrange04
With a little formatting, you can change the column chart’s appearance so the target range looks like a solid block, instead of individual columns.
Change the gap width to 0%, set the border to no line, and change the colour if you’d like something other than grey.
chartlinetargetrange06

Video: Create a Line Chart with Target Range

Watch this short video to see the steps for making an Excel line chart with target range.

Or watch on YouTube: Create an Excel Line Chart With Target Range

Download the Sample File

To see how the chart works, you can download the sample file from my Contextures website. On the Excel Sample Files page, go to the Charts and Graphics section, and look for CH0008 – Show Target Range on Line Chart
____________________________________