If you're planning a wedding, or another type of event, you might need a seating plan. Get this free Excel seating plan with charts, and modify it to show your guest names and table arrangement. See how to use the sample file, and how to add more guests and tables.
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.
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.
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.
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.
The contents of the selected range will appear on the dashboard.
Items for the Drop Down List
The list items for the drop down are stored in a range named ShowList, on the ChartInfo sheet.
Then, on the dashboard sheet, a data validation list is based on the ShowList name.
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:
The Chart Data – East item was selected, and its range is named EastData
Add a Linked Picture
Next, a named formula -- ShowChart was created. It uses the INDIRECT function to return the range from the SelRange cell:
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.
In the screen shot below, Chart Date – East was selected, so the EastData range is shown.
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
- How to Build Excel Dashboards
- 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:
- Excel 2003,
- Excel 2010 (2 item drop down)
- Excel 2010 (multi-item drop down)
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?
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!
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 pizza pie chart!
Sometimes, you need to make a pie chart in Excel. No, it might not be the best way to present your data, but sometimes you have to go with the chart type that someone else chooses.
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.