How to Make a Marimekko Chart in Excel

In a Marimekko chart, the column widths show one set of percentages, and the column stacks show another set of percentages. For example, show the size of market segments for computer sales, and company sales within each segment. See how to build a Marimekko chart in Excel, either manually, or with an add-in.

Continue reading "How to Make a Marimekko Chart in Excel"

Create an Interactive Excel Chart

If you add a chart to a dashboard, it’s helpful to let people choose what they’d like to see in the chart. In this example, you’ll choose a date range, and select other criteria, and the interactive Excel chart automatically adjusts to show total values for the selected data.

Continue reading "Create an Interactive Excel Chart"

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"

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. To add context to those numbers, you can create an Excel line chart with target range.

Continue reading "Create an Excel Line Chart with Target Range"