Home > Charts > Advanced > Date Range Excel Interactive Totals Chart Show totals in an interactive Excel chart, and use check boxes and drop down lists to show and hide data. Get the free workbook.

Introduction

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.

The Workbook Setup

There are four worksheets in the completed workbook:

• Dashboard
• Sales_Data
• Admin_Lists
• Chart_Data

Below is a brief description of each sheet, and the formulas are explained in the next section.

Dashboard Sheet

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.

Sales_Data Sheet

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.

Admin_Lists Sheet

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.

Chart_Data Sheet

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.

Start the Dashboard Sheet

If you are starting from scratch, follow these steps to create the dashboard sheet:

1. Insert a new sheet, and name it Dashboard
2. Type headings for the criteria cells:
   • B3: Start Date
   • D3: End Date
   • F3: Region
   • H3: Category
3. To identify the criteria entry cells, format cells B4, D4, F4 and H4 with light green fill and a border
4. Make the columns between the green cells narrower

Name the Criteria Cells

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:

B4: StartDate

D4: EndDate

F4: SelReg

H4: SelCat

Add the Check Boxes

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:

1. On the Ribbon, click the Developer tab. (If the tab is not visible, follow the instructions here, to add the Developer tab)
2. Click the Insert command, and in the Form Controls section, click the Check Box

   

3. On the worksheet, click in cell C6, to add a check box.
4. Right-click the check box, and click Edit Text, then delete the caption text.
5. Make the check box narrower, to fit in column C.

   

6. Right-click the check box, and click Copy, then paste in cell E6.
7. In cell D6, type Quantity and in cell F6, type Price.
8. Go to the View tab on the Excel Ribbon, and turn off the gridlines.

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.

Add the Admin_Lists Sheet

Next, follow these steps to create the Admin_Lists sheet:

1. Insert a new sheet, and name it Admin_Lists
2. Type headings for the Named Ranges:
   • B1: Region
   • D1: Category
   • F1: StartYrMth
   • F4: EndYrMth
3. In cells B2:B3, type the region names: East, West
4. In cells D2:D5, type the category names: Snacks, Cookies, Bars, Crackers
5. Name cells B2:B3 as RegionList
6. Name cells D2:D5 as CatList
7. Name cell F2 as StartYrMth
8. Name cell F5 as EndYrMth

Add the Dashboard Criteria

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.

Region List

To create a Region drop down list:

1. On the Dashboard sheet, select cell F4
2. On the Ribbon, click the Data tab, and click Data Validation
3. From the Allow drop down, select List
4. Click in the Source box, and press the F3 key, to see a list of named ranges
5. Click on RegionList, then click OK
6. Click OK, to complete the drop down list of regions

Category List

To create a Category drop down list:

1. On the Dashboard sheet, select cell H4
2. On the Ribbon, click the Data tab, and click Data Validation
3. From the Allow drop down, select List
4. Click in the Source box, and press the F3 key, to see a list of named ranges
5. Click on CatList, then click OK
6. Click OK, to complete the drop down list of categories

Enter the Test Criteria

To set up some criteria for testing, enter the following dates on the Dashboard:

• Start Date: March 1, 2015
• End Date: June 30, 2015

Next, fill in the other two criteria cells:

• Click the arrow in the Region cell (F4), and select East
• Select Cookies as the category from the drop down list in cell H4

Add the Chart_Data Sheet

Next, follow these steps to create the Chart_Data sheet:

1. Insert a new sheet, and name it Chart_Data
2. Type headings for the chart data:
   • B3: ID
   • C3: Date Range
   • D3: Quantity
   • E3: Price
   • F3: MthEnd
3. In cells B4:B39, make a list of numbers, from 1 to 36 -- for a maximum of 3 years of data
4. To identify the chart data cells, select cells B3:F39 and apply an "All Borders" border
5. Also, add borders to cells D2 and E2 -- these cells will be linked to the check boxes on the dashboard
6. Name cell D2 as LinkQty, and name cell E2 as LinkPrice

Link Check Boxes to Cells

The next step is to connect the dashboard check boxes to the cells on the Chart_Data sheet.

1. On the Dashboard sheet, right-click on the Quantity check box, and click Format Control
2. Click in the Cell Link box, and type LinkQty -- the name of the linked cell on the Chart_Data sheet.
3. Click OK to close the Format Control window

   

4. Click OK to close the Format Control window.
5. Next, right-click the Price check box, and link it to the LinkPrice cell.

To test the linked cells:

1. Click each of the check boxes, to remove the check mark, if it has one
2. Go to the Chart_Data sheet, and each of the linked cells should contain "FALSE".
3. Go back to the Dashboard, and add a check mark in each check box
4. Now the linked cells on the Chart_Data sheet, should contain "TRUE".

NOTE: If you clear those cells, or type FALSE, it will also clear the check boxes.

Add Filter Formulas for Data

Next, you will add columns with formulas on the data sheet, to check each record for the selected date range, region and category.

Add Filter Column Headings

1. On the Data sheet, add the following headings in cells I1:N1 - SelDate, SelReg, SelCat, AllOK, CalcQty, CalcPrice. The table will expand automatically to include the new headings.
2. (optional) Colour the heading cells, so they stand out from the other headings

Add Filter Column Formulas

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.

Test the 3 Criteria

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

=--AND([@OrderDate]>=StartDate,[@OrderDate]<=EndDate)

Cell J2, to check if the record is in the selected Region, or no Region was selected

=--OR([@Region]=SelReg,SelReg="")

Cell K2, to check if the record is in the selected Category, or no Category was selected

=--OR([@Category]=SelCat,SelCat="")

Are All 3 Criteria TRUE?

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.

Calculate the Quantity and Price

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:

=[@Quantity]*[@AllOK]

Cell N2, to calculate the Price based on selected criteria:

=[@SalePrice]*[@AllOK]

Check the Results

To see the formula results, scroll down the Data sheet to row 92. That order meets all three criteria:

1. OrderDate is in the date range you entered on the Dashboard.
2. Region is East
3. Category is Cookie

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.

Get Month Start Dates

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:

G2: =EOMONTH(StartDate,-1)+1

G3: =EOMONTH(EndDate,-1)+1

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:

• the starting date is the value in the EndDate cell
• the number of months is -1

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.

Add Formulas for Chart Data

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.

1. Cell C4: =IF(StartYrMth="","",IF(B4=1, StartYrMth, IF(F3<EndDate,F3+1,"")))
2. Cell D4: =IF($C4="","", IF(D$2=FALSE,NA(), SUMIFS(Sales_Data[CalcQty], Sales_Data[YrMth],C4)))
3. Cell E4: =IF($C4="","", IF(E$2=FALSE,NA(), SUMIFS(Sales_Data[CalcPrice], Sales_Data[YrMth],C4)))
4. Cell F4: =IF(C4="","",EDATE(C4,1)-1)

Next, copy all four formulas down to the end of the list, in row 39.

The Formula Results

Based on the criteria and check box selections on the Dashboard sheet, a list of dates and amounts will appear in the formula cells

• Dates: The Date Range column (C) shows a list of month starting dates, for all the months in the selected date range.
• Quantity: If D2 is TRUE, the total quantity for each month is shown, based on the amounts in the CalcQty column on the Data sheet.
• Price: If E2 is TRUE, the total price for each month is shown, based on the amounts in the CalcPrice column on the Data sheet.
• Month End: The MthEnd column (F) shows the month end dates, for all the months in the selected date range.

Remove a Check Mark

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.

Add Names for Chart Data

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.

Static Named Ranges

First, create the following two named ranges -- these are static ranges, so they won't adjust with the data.

• Select cell C3, and name it as ChartStart
• Select cells C4:C39, and name that range as ChartDatesALL

Dynamic Named Ranges

Next, you will create dynamic ranges -- these will adjust with the data.

The ChartData Range

The first dynamic range will contain all the chart data. To define the range, follow these steps:

1. On the Excel Ribbon, go to the Formulas tab, and click Define Name
2. In the Name box, enter: ChartData
3. In the Refers To box, type this formula: =OFFSET(ChartStart,1,0,COUNT(ChartDatesALL),3)
4. Click OK to complete the name

The formula uses the OFFSET function to create a reference:

• This range starts in cell C4, which is one row below the ChartStart cell.
• The number of rows in the range is based on the count of dates in the ChartDatesALL range
• There are 3 columns in the range, for the Date, Quantity and Price

To see the current range for ChartData:

1. On the Excel Ribbon, go to the Formulas tab, and click Name Manager
2. In the list of names, click on ChartData
3. Click in the Refers To box, where the formula is
4. On the Chart_Data sheet, the cells in the ChartData range will be outlined with "marching ants" (a scrolling marquee)

Three More Dynamic Ranges

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:

1. Name: ChartDates   Formula: =OFFSET(ChartData,,0,,1)
2. Name: ChartQty       Formula: =OFFSET(ChartData,,1,,1)
3. Name: ChartPrice   Formula: =OFFSET(ChartData,,2,,1)

Starting from the ChartData range:

• the ChartDate range moves 0 columns to the right -- it starts in the same column as the ChartData range.
• the ChartQty range is 1 column to the right
• the ChartPrice range is 2 columns to the right.

All three ranges have a width of 1 column, instead of the 3 columns in ChartData.

Create a Column Chart

Next, you'll create a column chart, based on the data that is currently visible on the Chart_Data sheet:

1. On the Chart_Data sheet, select the visible data and headings in the table -- cells C3:E7
2. On the Ribbon, click the Insert tab
3. Click the Column Chart command, then click 2-D Clustered Column chart

   

4. Cut the chart, and paste it onto the Dashboard sheet.

Test the Interactive Chart

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:

• Click on the the Price check boxes, to add a check mark. The Price columns will appear in the chart
• Change the Region to West, to see the Cookies sales in that region.
• Change the Category to Bars, to see the Bars sales in the West region.

Add Dynamic Ranges to Chart

Next, you'll adjust the chart, so it uses the dynamic ranges that you created.

Change the Chart Data Range

First, you'll set the chart source data to the dynamic range, ChartData.

1. Click the chart, to select it
2. On the Ribbon, under Chart Tools, click the Design tab
3. Click the Select Data command



4. In the Chart Data Range box, you will see the existing formula, with its static references:

=Chart_Data!$C$3:$E$7



5. Change the formula, to replace the cell references with range names:

=Chart_Data!ChartData

Change the Quantity Series

Next, you'll set the data for the Quantity series.

1. In the Legend Entries list, click on Series 1 (previously Quantity)
2. Click the Edit button, to open the Edit Series window.
3. Click in the Series Name box, then go to the Chart_Data sheet, and click on cell D3.
4. In theSeries Values box, change the formula, to replace the cell references with the range name:

=Chart_Data!ChartQty

5. Click OK.

Change the Price Series

Next, you'll set the data for the Price series.

1. In the Legend Entries list, click on Series 2 (previously Price)
2. Click the Edit button, to open the Edit Series window.
3. Click in the Series Name box, then go to the Chart_Data sheet, and click on cell E3.
4. In theSeries Values box, change the formula, to replace the cell references with the range name:

=Chart_Data!ChartPrice

5. Click OK.

Change the Date Labels

The final step is to edit the date references.

1. In the Select Data Source window, in the Horizontal (Category) Axis Labels section, click the Edit button.
2. In the Axis Labels window, change the Axis Label Range, replacing the cell references with the ChartDates named range:

=Chart_Data!ChartDates

3. Click OK, to close the Axis Labels window, then click OK to close the Select Data Source window.

Test the Revised Chart

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:

• Change the End Date to 5/31/15 -- the chart should change, to show only 3 months.
• Change the End Date to 8/31/15 -- the chart should change, to show 6 months of data.

Create a Dashboard Title

To finish the dashboard, you'll add a formula at the top, to show a title that includes the selected date range.

• Paste in the following formula into cell B1:
• ="Sales: " & TEXT(StartDate,"dd-mmm-yyyy") & " to " & TEXT(EndDate,"dd-mmm-yyyy")
• Format cell B1 in a large, bold font. In the screen shot below, Calibri 14 Bold is used.

Create a Chart Title

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:

1. Click on the chart's title, to select it.
2. Click in the Formula bar, and type an Equal Sign (=)
3. Click on cell B1, which contains the dashboard title
4. Press Enter, to complete the formula

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.

Add Criteria Labels on Chart

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:

1. Click on the chart, to select it.
2. On the Ribbon's Insert tab, click the Text Box command
3. In an empty part of the chart, drag to create a small text box -- just big enough to show the region name



4. Click in the Formula bar, and type an Equal Sign (=)
5. Click on cell F4, which contains the Region name
6. Press Enter, to complete the formula

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.

Download the Workbooks

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.

Excel Chart Utility

If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.

More Chart Links

Jon Peltier's Toolbox Add-in

Interactive Chart, Basic

Charts, Cluster Stack Utility

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall