Excel Graphs - Help Needed

Question

Hi team,

I am wondering if you could help please.

Below is the sample information from my 1000+ line data.

I want to create different graphs based on Team and TrainingSubGroup... How do I add filter to the Chart?

For e.g I want to create a graph for team A with subgroupcode C11, then another graph for team A with subgroucode C12 but get the information from one source rather than manually filtering the table and creating multiple tables?

Let me know if you need more info... Thanks a lot!

Answer 1

Hi team,

I am wondering if you could help please.

Below is the sample information from my 1000+ line data.

I want to create different graphs based on Team and TrainingSubGroup... How do I add filter to the Chart?

For e.g I want to create a graph for team A with subgroupcode C11, then another graph for team A with subgroucode C12 but get the information from one source rather than manually filtering the table and creating multiple tables?

Let me know if you need more info... Thanks a lot!

1.

I reckon this can be done with the use of a Pivot Table and Chart.

2.

I’ve reconstructed your data in this file:-

https://www.mediafire.com/file/h4ox6hlp2jghmey/Excel_Graphs_Help_Needed.xlsm/file

Download the file and open it clicking on:-

Enable Editing

 - if necessary / required.

See image below for results.

3.

Worksheet called:-

orig_data

 - is your original data in cells:-

A 1 to F 17

Note that I’ve broken out months and years allowing for possible future development.

4.

In Worksheet called:-

orig_data

 - starting at cell:-

H 1

 - I’ve told you the steps taken.

Note that the Workbook’s at the position after these steps have been taken.

5.

Now go to Worksheet called:-

Table1_piv_ch_and_piv_tab

 - where you’ll find the Pivot Table and Chart.

In this Worksheet what I’ve done can be seen starting at cell D 68.

Note that the Workbook’s at the position after these steps have been taken.

6.

You can select / de-select what’s on the graph by using the toggle buttons in the graph’s lower left-hand corner.

7.

I think that gives you what you want.

Do you agree?

8.

If there’s anything you don’t understand please post back and I’ll try to answer your questions if and when they’re posed.

9.

See image below for the results of steps taken.

Click on image to get it to open in a new browser tab where I hope you’ll see things more clearly.

Answer 2

There is no way to apply a filter to a chart. I see 3 ways:

a) Use the FILTER function and create a separate data area for each chart.

b) Load the data into a data model and create 2 Pivot charts.

c) Copy the chart and remove the related series.

Which one do you need help with?

Andreas.