Excel SUMPRODUCT Function

Creating a Dynamic Data Table and Chart

By Angela Henderson, Associate Director of Institutional Research & Effectiveness, Stetson University

Have you ever been asked to present data that is still changing? Sorting tables and creating graphs can be time consuming, especially if you have to make multiple revisions. Excel's SUMPRODUCT function is a great solution to this dilemma. It allows you to create a dynamic summary table and presentation graphic that accurately reflects the data as it changes.

At the base level, SUMPRODUCT multiplies selected arrays to provide a sum. While this sounds a bit cryptic, it can be exceptionally useful in creating dynamic data presentations.

This tip provides step by step guidance on how to use a combination of Excel’s SUMPRODUCT, advanced filters, and data validation functions to create dynamic tables and charts.

We have a dataset (shown below). To determine the number of seats still available in a course for each department, we could filter the dataset on term and then by department. However, this would require you to refilter the dataset every time you want to examine a different department’s offerings.

sp1.JPG
Instead, we can create a dynamic summary table to allow us to view only the data we need.

To create a summary table that shows the numbers of seats available for each department, begin by creating a unique list of departments. This can be accomplished using the Advanced filter option available on the Data tab. Click on “Advanced” to open the Advanced Filter menu.


In the Advanced Filter menu, select “Copy to another location.”

In the box next to “List range” enter the range of cells containing the data you want in the list, including the header. In this case, we are creating a list of departments, so we select the entire range of cells with data in column B.

In the box next to “Copy to” enter the location of the first cell where you want the list to appear.

Check the box next to “Unique records only” to eliminate duplicates from the final list.

Clck OK. The list of unique departments now appears in column K. Sort the list alphabetically and you have the basis for your dynamic department table.


Add appropriate column headers, a total line at the bottom of the table, and a field for term selection above the table.



Now let’s use the advanced filter again to obtain a unique list of terms included in the dataset. Repeat the process used to generate the unique department list to create a unique term list. This list will be used to generate a drop down selection list for the table.

To create the drop down list, select the empty cell next to Term (cell L1 in this example).

Click on the Data tab again and click on “Data Validation.”


In the Data Validation menu, select “List” from the drop down box under “Allow.”

In the box under “Source” indicate the range of cells to appear in the list.


Click “OK.”

You now see the first list value in the drop down menu and the arrow next to the cell to select a different term. Now we just need to add our formulas to drive the data table.


As we want our table to generate data based on term and department, we will use SUMPRODUCT to obtain the sum of available seats and the number of courses with available seats. 

The SUMPRODUCT formulas for each column are shown above, simplified by the use of named ranges. For more information on named ranges, visit the support site.

Our summary table now generates data based on the department listed in the first column and the term selected from the drop down menu.

While we’re at it, let’s add a dynamic chart to accompany the table.


Select the range of cells containing data in the “Department” and “Available Seats” columns and click on Insert on the ribbon.

Click on the type of chart you want to create. Due to the length of the department names, let’s create a bar chart to show the data.


The resulting chart is shown below. It still needs some work.

Right click on the list of department names and select “Format Axis” from the menu.


In the Axis Options menu, put a check next to the option to show “Categories in reverse order” and then click “Close” to exit the menu box.


Departments are now shown in the appropriate alphabetical order.  


Click anywhere on the chart to activate the Chart Tools “Layout” menu tab in the ribbon.

Click on “Chart Title” and add the appropriate title to the chart.

Now, to add a dynamic term indicator to the chart, click on the “Insert” tab on the ribbon and select “Text Box.”

Place the text box frame under your chart title.


With the text box active, click on the formula bar, enter “=” and click on the cell above the summary data table containing the term indicator (cell L1).

 

We now have a dynamic table and chart which update automatically based on term selection from the drop down box we created.



 

 Comments

 
To add a comment, Sign In
Total Comments: 8
 
Shawn posted on 3/13/2015 9:42 AM
This is a great tip, simplifies a process that has always taken me additional steps to accomplish. Thank you, Angela!
Annie posted on 3/13/2015 2:37 PM
This is fantastic. I have been trying to figure a way to do this for something else that I constants filter and add data too. Thank you so much!
Annie posted on 3/13/2015 2:37 PM
*constantly not constants
Ishuan posted on 3/13/2015 2:52 PM
Wonderful visual and summary stats tool!
Lee posted on 3/13/2015 3:28 PM
Thanks Angela! Great tip - I learned a couple of new things about Excel that will help me in my work.
Keith posted on 3/16/2015 12:35 PM
Very helpful use of advanced filters in excel. We use this to create visual dashboards in excel for our data sets, which makes reporting meaningful visualizations fast and easy. Thank you for sharing this concept with the community.
Lisa posted on 4/28/2015 1:07 PM
I have not used advanced filters yet, so this is a great introduction to them. I will definitely be able to use this tip. Thank you!
Iroshan posted on 6/3/2015 12:56 PM
Brilliant, was there a particular reason pivot charts were not used?