Show totals in an interactive Excel chart, and use check boxes and drop down lists to show and hide data. Get the free workbook.
In this interactive chart, enter a data range on the dashboard sheet, select criteria from the Range and Category lists, then click the Quantity and Price check boxes, to control which data is shown in the chart. There is no programming required, just formulas.
This technique works best for small to mid-sized tables. Formulas are added to the data table, so the workbook will slow down if you have thousands of rows of data.
There are four worksheets in the completed workbook:
Below is a brief description of each sheet, and the formulas are explained in the next section.
This sheet has the interactive chart, check boxes to show or hide the Quantity and Price, and cells for the date range and criteria.
There is a formula in cell B1, to calculate a chart title, based on the start and end dates.
This sheet has a named Excel table -- tblSales -- with sales order data. In columns A to E there is sales data, but no formulas. For each sales order, we have an order date, region, category, quantity and total sale price.
NOTE: The "Data-Only" workbook has this sheet, with the data columns, and no formulas. It does not have the Dashboard, Admin_Lists or Chart_Data sheets.
In the completed workbook, there are formulas in columns F to L, to create values for the interactive chart.
This sheet has a two lists, in named ranges -- RegionList and CatList. Those lists are used to create drop down lists for criteria, on the dashboard.
There are two cells with formulas, and those cells are also named: StartYrMth and EndYrMth.
This sheet has links to the check boxes, and formulas to calculate the date range and the total amounts for the chart. Dynamic named ranges are defined, and used in the chart series.
If you are starting from scratch, follow these steps to create the dashboard sheet:
Next, name each of the green criteria cells. (Select the cell, click in the Name Box, at the left of the formula bar, type a one-word name, then press Enter)
Later, you'll use these names in formulas:
Next, you'll add two check boxes, for Quantity and Price. When the box is checked, its data will appear in the chart. To add a check box:
NOTE: You could add data validation in the start date and end date cells, to ensure that valid dates are entered, and the end date is on or after the start date.
Next, follow these steps to create the Admin_Lists sheet:
Next, follow these steps to add drop down lists in the Region and Category criteria cells on the dashboard sheet. The drop down lists are created with the Data Validation feature.
To create a Region drop down list:
To create a Category drop down list:
To set up some criteria for testing, enter the following dates on the Dashboard:
Next, fill in the other two criteria cells:
Next, follow these steps to create the Chart_Data sheet:
The next step is to connect the dashboard check boxes to the cells on the Chart_Data sheet.
To test the linked cells:
NOTE: If you clear those cells, or type FALSE, it will also clear the check boxes.
Next, you will add columns with formulas on the data sheet, to check each record for the selected date range, region and category.
On the Data sheet, add the following formulas in the filter columns. The formulas are entered in the first data row, and will fill down automatically.
The first three formulas check the criteria, and return TRUE or FALSE. The two minus signs (double unary) at the start of the formula change TRUE to 1 and change FALSE to 0.
Cell I2, to check if the record is in the selected date range
Cell J2, to check if the record is in the selected Region, or no Region was selected
Cell K2, to check if the record is in the selected Category, or no Category was selected
In cell L2, the formula checks to see if all 3 criteria tests are TRUE. If so, the Sum those cells will be 3, which matches the Count of those cells.
=--(SUM(Sales_Data[@[SelDates]:[SelCat]]) = COUNT(Sales_Data[@[SelDates]:[SelCat]]))
The result will be 1 if all criteria are TRUE, and 0 if not all are TRUE.
In the final two columns, the original quantity and price are multiplied by the 1 or 0 in column L. So, if all 3 criteria are TRUE, the original quantity and price are returned, otherwise the result is 0.
Cell M2, to calculate the Quantity based on selected criteria:
Cell N2, to calculate the Price based on selected criteria:
To see the formula results, scroll down the Data sheet to row 92. That order meets all three criteria:
Because all three criteria were TRUE, there is a 1 in the AllOK column (L), and the CalcQty and CalcPrice column show the original amounts.
To create the chart, you'll be showing a total for each month in the date range. To get the first date in the start and end months, add the following formulas on the Lists sheet:
The EOMONTH function gets the last date in a month, based on a starting date, and offset by a specified number of months.
In the formula in cell G3:
Our end date is June 30, 2015, so the last day of the month before that is May 31, 2015
Then, the formula adds 1 to that date, to get the first day of the next month -- June 1, 2015.
To create the chart, you'll be showing a total for each month in the date range. This will be done with formulas that check the selected criteria, and the cells that are linked to the check boxes.
Go to the Chart_Data sheet, and enter the following formulas, to calculate the dates and quantities for the chart data.
Next, copy all four formulas down to the end of the list, in row 39.
Based on the criteria and check box selections on the Dashboard sheet, a list of dates and amounts will appear in the formula cells
If you remove the check mark for Quantity or Price on the Dashboard sheet, that column shows #N/A, and those values won't be shown on the chart.
Next you will create some named ranges on the Chart_Data sheet. These names will allow you to build a flexible chart, based on the rows that have totals showing, and ignoring the other rows.
First, create the following two named ranges -- these are static ranges, so they won't adjust with the data.
Next, you will create dynamic ranges -- these will adjust with the data.
The first dynamic range will contain all the chart data. To define the range, follow these steps:
The formula uses the OFFSET function to create a reference:
To see the current range for ChartData:
Next, you will create three more dynamic range, for the Date, Price and Quantity data. These ranges will be offset from the ChartData range that you created in the previous step.
Follow the same steps to define these 3 names:
Starting from the ChartData range:
All three ranges have a width of 1 column, instead of the 3 columns in ChartData.
Next, you'll create a column chart, based on the data that is currently visible on the Chart_Data sheet:
Currently, this is a static chart, so it will only show 4 months of data, even if you change the date range. You will adjust the chart later, so it uses the dynamic ranges that you created.
However, the other criteria will work correctly, so you can test those:
Next, you'll adjust the chart, so it uses the dynamic ranges that you created.
First, you'll set the chart source data to the dynamic range, ChartData.
Next, you'll set the data for the Quantity series.
Next, you'll set the data for the Price series.
The final step is to edit the date references.
Now that the dynamic ranges have been added, you can test the date range settings, to see if the number of months in the chart changes..
To test the dates:
To finish the dashboard, you'll add a formula at the top, to show a title that includes the selected date range.
Next, you will link the chart's title to the dashboard title, so it also shows the selected date range.
To link the chart title to the dashboard title:
The chart title will change from the default "Chart Title", and will show the result of the formula in cell B1.
You can format the chart title, if you want a different font or size.
As a optional step, you can also show the selected criteria on the chart, by adding text boxes that are linked to the criteria cells. This would be helpful if you move the chart to a different sheet, or want to print the chart separately.
To show the criteria, follow these steps:
Repeat the steps, to add a text box with the Category name, linked to cell H4.
Then, move the text boxes, or resize them, if necessary.
In the screen shot below, I added space below the chart title, and move the text boxes there.
1. Completed Workbook: To see how the completed chart works, you can download the Interactive Totals Chart workbook. The file is zipped, and is in xlsx format, with no macros.
2. Data-Only Workbook: To start from scratch, and follow along with this tutorial, you can download the Interactive Totals Chart Data workbook. It only has the Sales_Data sheet. The file is zipped, and is in xlsx format, with no macros.
Last updated: October 24, 2016 1:47 PM
Contextures RSS Feed