Months/Quarters Dynamic Chart

excelfreak87

New Member
Joined
Feb 20, 2018
Messages
5
Dear All,

I am working on an Excel dashboard. One of the requirements is to display monthly and quaterly data on a single graph depending on user's selection. If the user wants to see volumes per months, the graph should present monthly data. If the user chooses to see the quaterly data, the graph should show quarterly volumes. I tried with pivot charts and slicers but it doesn't work the way as I would like it to be. Do you have any ideas how I could implement this requirement? I really appreciate your help. :)

Many thanks,
S.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
dateamountmonthquarter
01/01/201732Jan1
18/01/201735Jan1apr2
04/02/201738Feb1aug3
21/02/201741Feb1dec4
10/03/201744Mar1feb1
27/03/201747Mar1jan1
13/04/201750Apr2jul3
30/04/201753Apr2jun2
17/05/201756May2mar1
03/06/201759Jun2may2
20/06/201762Jun2nov4
07/07/201765Jul3oct4
24/07/201768Jul3sep3
10/08/201771Aug3
27/08/201774Aug3
13/09/201777Sep3monthtotalquartertotal
30/09/201780Sep3jan671237
17/10/201783Oct4feb792280
03/11/201786Nov4mar913435
20/11/201789Nov4apr1034445
07/12/201792Dec4may56
24/12/201795Dec4jun121
july0
aug145
col Dcol Esep157
oct83
which chart typequarternov175
dec187
1237row 31
2280the helper tables above can be hidden away
3435
4445depending on choice this table
#N/Awill be 4 numbers and 8 #N/A's
#N/A(which will not plot on a chart)
#N/Aor 12 numbers
#N/A
#N/A
#N/Abase the chart on this table D31:E42
#N/A
#N/A

<colgroup><col><col span="3"><col><col span="13"></colgroup><tbody>
</tbody>
 
Upvote 0
dateamountmonthquarter
01/01/201732Jan1
18/01/201735Jan1apr2
04/02/201738Feb1aug3
21/02/201741Feb1dec4
10/03/201744Mar1feb1
27/03/201747Mar1jan1
13/04/201750Apr2jul3
30/04/201753Apr2jun2
17/05/201756May2mar1
03/06/201759Jun2may2
20/06/201762Jun2nov4
07/07/201765Jul3oct4
24/07/201768Jul3sep3
10/08/201771Aug3
27/08/201774Aug3
13/09/201777Sep3monthtotalquartertotal
30/09/201780Sep3jan671237
17/10/201783Oct4feb792280
03/11/201786Nov4mar913435
20/11/201789Nov4apr1034445
07/12/201792Dec4may56
24/12/201795Dec4jun121
jul133
aug145
col Dcol Esep157
oct83
which chart typemonthnov175
dec187
167row 31
279the helper tables above can be hidden away
391
4103depending on choice this table
556will be 4 numbers and 8 #N/A's
6121(which will not plot on a chart)
7133or 12 numbers
8145
9157
1083base the chart on this table D31:E42
11175
12187

<colgroup><col><col span="3"><col><col span="13"></colgroup><tbody>
</tbody>
 
Upvote 0
Many thanks for your help!
Just one more question, I am having a problem as the #NAs plot on the chart, despite having NA() function in the formula. I use excel 2016 and somehow putting NA() function doesn't work.
I'd appreciate a piece of advice on this.

Thank you!
 
Upvote 0
found this by googling

[FONT=&quot]Have you checked the "Hidden and empty cells" settings in your excel chart ?[/FONT]
[FONT=&quot]It should be set to "Gaps".[/FONT]
[FONT=&quot]- Click on the boundary of your excel chart[/FONT]
[FONT=&quot]- Click on "Select Data" under "Chart Tools >> DESIGN " [/FONT]
[FONT=&quot]- At the bottom left corner of the newly populated box, you will see a button titled "Hidden and empty cells"[/FONT]
[FONT=&quot]- Click that button[/FONT]
[FONT=&quot]Change the settings. Your problem should get solved.[/FONT]
 
Upvote 0
Yes, I did go through all the options you described and they worked well in the older excel versions that I used but seems they don't work for excel 2016:(
 
Upvote 0
I put in the formula that in case of an error it should display a blank. However, the quarter bars look a bit strange on the graph - they are not evenly distributed as I'd imagine. I don't know if this can be overcome somehow.

https://ibb.co/hpFXoH
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top