Create Dynamic Charts with Combo Box in Excel

*Disclaimer: For this video I have used data from the Google Merchandise Store Google Analytics which is publicly available.

Combo Boxes in Excel create drop down menu options and dynamically populate tables and charts.

In this video I will show how to create a dynamic mini dashboard in Excel with the Combo Box. The drop down menu will give us the option to select All Devices, Desktop, Mobile and Tablet and the table and charts will dynamically change to reflect the number of sessions and the revenue made for each device or all devices. The date range is January 2017 to June 2017 and the data is broken down by month.

The most important part of creating a combo box is to properly configure the input range and cell link (demonstrated in the video). The other important part if to understand the Index function in Excel. For the combo box the Index function contains an array, which includes the column or certain rows in a column, and a row_num which would be the cell we have selected as a Cell Link. All of these are demonstrated in the video.

Styling and designing is just to give ideas and show you options but you can style it in any other way that suits your reporting.

Excel Dashboard – Website Traffic by Channel

Why It Is Important to Report on Website Traffic by Channel?

If you are investing in different marketing channels to drive traffic to your website it is important to understand how each channel is performing against the other channels. Performance can be measured by many different metrics – the number of sessions sent to the website, the number of micro or macro conversions or the number of qualified leads for a certain period of time and certain budget.
This dashboard is a basic excel dashboard that will compare the number of sessions that a website received by each marketing channel every month for an entire calendar year. You can use this template and build a dashboard for each metric that represent your KPIs, overall sessions, goal completions, eCommerce, leads, etc.

Interactive Excel Dashboard

Website Traffic by Channel Excel Dashboard

This dashboard is built with

  • A pivot table
  • A pivot chart
  • Slicers
  • Custom styling and formatting

To start you will need a table with all your raw data.

Website Traffic By Channel Raw Data

The raw data table contains 12 rows for each marketing channel with traffic data for every month of the calendar year. I have used random numbers to build this dashboard as an example.
You can customize it and use colors and variations that suit your reporting.