There are cases where you only want to capture a selected portion of a raw data table in a graph, as opposed to showing the entire set of values.
Moreover, instead of creating one graph for each section and hardcoding the selection area, charts can be made to be dynamic by referencing a dropdown list such as this:
In this example, we will be using the data table below, which shows the Monthly Revenue of each App.
There are different ways to create this report.
One way is to use Name Manager together with some dynamic formulas like OFFSET() of INDEX() to get the job done.
My preference is to use a data preparation table whenever I can.
This basically retrieves the data to be displayed depending on the dropdown.
As an overview, the chart will be connected to the data preparation table, which in turn retrieves the data from the raw data table.
This approach simplifies the capturing of data since you can easily trace it back to the data preparation table.
Adding the dropdown list
Add a dropdown list for the user to select the Month.
To do this, click a cell and go to Data > Data Validation.
The Data Validation window will pop up.
Under the Validation criteria, select List.
For the Source, highlight what the choices you want to be found in your dropdown list.
In this case, highlight January to December (C4:N4).
Setting up the data preparation table
Once the user selects a month, the data preparation table should display the data corresponding to that month for each App.
To set it up, you will need your App names as the first column.
In this case, we will assume that the App names are fixed and unchanging and all you have to do is copy them over to the Data Preparation table and sort them alphabetically.
Otherwise, you can make this dynamic by using cell references.
Use a cell reference to the selected Month as the header of the Data Preparation Table.
Cell Q4 = B12Proceed to extracting the correct data set by using the combination of the App name and month selected.
The easiest way to do it is to use Excel’s INDEX() and MATCH() functions in finding the answer.
The syntax of the INDEX() function is:
= INDEX(array, row_num, [column_num])- array – the area where the answer can be found, $C$4:$N$9
- row_num – how many rows to move down. The MATCH() function will be used to find the App name at the raw data table.
- column_num – how many columns to the right. The MATCH() function will be used to find the Month at the raw data table.
The syntax of the MATCH() function is:
= MATCH(lookup_value, lookup_array,match_type)- For the row_num parameter, the lookup value is the App name on the data preparation table (P5) and the lookup_array is $B$5:$B$9
- For the column_num parameter, the lookup value is the month name on the data preparation table (Q4) and the lookup_array is $C$4:$N$9
- The match_type is set to 0 since you need an exact match.
The final formula is:
CELL Q5 = INDEX($C$5:$N$9,MATCH(P5, $B$5:$B$9,0),MATCH($Q$4,$C$4:$N$4,0)
Make sure you fix the month name and the arrays before pulling the formula down to the last row.
(A more detailed INDEX()-MATCH() walkthrough can be found through this link.)
Inserting the chart
There are multiple ways to insert a new chart.
One way is to highlight the source data, in this case, the Data Preparation Table, and go to the Insert tab and select a chart type.
In this example, we will use a column chart.
You can make some formatting changes according to your preference.
I’ve done the following here:
- Removed the vertical axis and label
- Removed the chart guidelines
- Added data labels at the top of each data point
- Reduced the gap width to 80%
- Resized the chart
Adding a dynamic chart title
You have the option of adding a dynamic chart header.
For example, you want it to say, “Revenue Comparison for [month]”.
To do this, you have to make a cell reference containing the month.
Concatenate the text with the month selected:
Cell Q3 = “Revenue Comparison for “&Q4
Go to the chart title and make a cell reference to cell Q3.
Adding an average data series
To add a line to the chart to indicate the average, you will need to add a new data series.
To start, add a new column to the Data Preparation table:
Cell R5 = AVERAGE($Q$5:$Q$9)Make sure to fix the cell references and drag the formula down to the last row.
Add a new data series to the chart by selecting the chart and expanding the data source.
The cursor will change to a double-headed arrow once you hover over the corner, and then you can drag it to include the Average column.
Excel then adds these as new columns representing the data series.
Since you want the average to show up as a line instead of columns, right click on the data series and select Change Series Chart Type.
The popup window will show you the chart type for each data series.
Change the Chart Type for the Average series to a Line chart.
You can further make formatting adjustments according to your preference.
One thing you can also do to address cases where the line goes over the data labels is to add a white fill to the data labels.
To do this, click on the data labels and in the Format Data Labels panel on the right, tick the Solid Fill and select the color white.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
