Dynamically updating charts

sitewolf

sitewolf

Active Member
Joined
May 4, 2012
Messages
304
I've only recently started really using any charts at all, so when I built the sales report spread I just did, I didn't do it correctly so that as I add data for each month, the charts dynamically change.

My spread has state by state as well as month by month totals; I'm wanting to chart each state separately on a month by month basis.
I have my charts...but now, adding a new month, I'm not set for the charts to pick that new month up

Each state has more than one set of data, but currently my table is set up so each state's data is side by side...does that further complicate things?
What I mean is, say there are 3 different sets of numbers I'm tracking for each state- left to right I have Data1, Data2, Data3 for each state followed by the same for the next state in columns, numbers for each of those things by month in rows

i.e.
California Oregon
1 2 3 1 2 3

When I did this spread originally I wasn't thinking I would be continually updating, I thought it was a one time request. My boss liked it so well now he wants me to update it every month.

Would it be better/easier if I changed to 3 tables, one for each set of data?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
I must not shower before I come in here anymore....because most of my questions go unanswered :(
 
Upvote 0
sitewolf said:
I've only recently started really using any charts at all, so when I built the sales report spread I just did, I didn't do it correctly so that as I add data for each month, the charts dynamically change.

My spread has state by state as well as month by month totals; I'm wanting to chart each state separately on a month by month basis.
I have my charts...but now, adding a new month, I'm not set for the charts to pick that new month up

Each state has more than one set of data, but currently my table is set up so each state's data is side by side...does that further complicate things?
What I mean is, say there are 3 different sets of numbers I'm tracking for each state- left to right I have Data1, Data2, Data3 for each state followed by the same for the next state in columns, numbers for each of those things by month in rows

i.e.
California Oregon
1 2 3 1 2 3

When I did this spread originally I wasn't thinking I would be continually updating, I thought it was a one time request. My boss liked it so well now he wants me to update it every month.

Would it be better/easier if I changed to 3 tables, one for each set of data?
Click to expand...

How big is your data set?
Is that 49 states *3 columns an you are adding 49 columns each month?
The easy way it woudl be to transpose the whole row into column and then create dynamic chart.
Can you post large set of your data or upload your workbook to google drive or dropbox?
 
Upvote 0
Code: 
[TABLE="width: 704"]
<tbody>[TR]
[TD="width: 64"]Date[/TD]
[TD="width: 64"]AZ[/TD]
[TD="width: 64"]AZ[/TD]
[TD="width: 64"]CA[/TD]
[TD="width: 64"]CA[/TD]
[TD="width: 64"]CO[/TD]
[TD="width: 64"]CO[/TD]
[TD="width: 64"]FL[/TD]
[TD="width: 64"]FL[/TD]
[TD="width: 64"]GA[/TD]
[TD="width: 64"]GA[/TD]
[/TR]
[TR]
[TD]1-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]570[/TD]
[TD="align: right"]1466[/TD]
[TD="align: right"]54349.26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2849.36[/TD]
[TD="align: right"]5951.96[/TD]
[TD="align: right"]9206.48[/TD]
[TD="align: right"]2905.61[/TD]
[TD="align: right"]3733.82[/TD]
[/TR]
[TR]
[TD]2-2006[/TD]
[TD="align: right"]594[/TD]
[TD="align: right"]18452.75[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]23186.27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14759.06[/TD]
[TD="align: right"]20519.44[/TD]
[TD="align: right"]25770.03[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13359.06[/TD]
[/TR]
[TR]
[TD]3-2006[/TD]
[TD="align: right"]1019[/TD]
[TD="align: right"]3379.18[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]17109.83[/TD]
[TD="align: right"]732.19[/TD]
[TD="align: right"]6175.48[/TD]
[TD="align: right"]5794.91[/TD]
[TD="align: right"]15735.37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1379.84[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]87650.28[/TD]
[TD="align: right"]2520.58[/TD]
[TD="align: right"]4117.01[/TD]
[TD="align: right"]12655.78[/TD]
[TD="align: right"]21206.66[/TD]
[TD="align: right"]8172.88[/TD]
[TD="align: right"]3014.99[/TD]
[/TR]
[TR]
[TD]5-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11280.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22223.65[/TD]
[TD="align: right"]1955.68[/TD]
[TD="align: right"]11963.73[/TD]
[TD="align: right"]17515.01[/TD]
[TD="align: right"]36305.92[/TD]
[TD="align: right"]22029.57[/TD]
[TD="align: right"]5387.27[/TD]
[/TR]
[TR]
[TD]6-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20230.55[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]142686.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13920.23[/TD]
[TD="align: right"]24487.21[/TD]
[TD="align: right"]15091.53[/TD]
[TD="align: right"]6244.91[/TD]
[/TR]
[TR]
[TD]7-2006[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]3405.35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]50294.96[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5382.8[/TD]
[TD="align: right"]38582.99[/TD]
[TD="align: right"]4890.31[/TD]
[TD="align: right"]2720.02[/TD]
[/TR]
[TR]
[TD]8-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7433.87[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]105736.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11383.82[/TD]
[TD="align: right"]9225.11[/TD]
[TD="align: right"]16937.41[/TD]
[TD="align: right"]68004.2[/TD]
[TD="align: right"]8695.63[/TD]
[/TR]
[TR]
[TD]9-2006[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]7390.53[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]40704.12[/TD]
[TD="align: right"]528[/TD]
[TD="align: right"]13684.49[/TD]
[TD="align: right"]8967.95[/TD]
[TD="align: right"]6486.65[/TD]
[TD="align: right"]7807.98[/TD]
[TD="align: right"]1684.72[/TD]
[/TR]
[TR]
[TD]10-2006[/TD]
[TD="align: right"]569[/TD]
[TD="align: right"]12847.01[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]26171.31[/TD]
[TD="align: right"]5386.33[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12931.9[/TD]
[TD="align: right"]16938.03[/TD]
[TD="align: right"]9900.8[/TD]
[TD="align: right"]5841.8[/TD]
[/TR]
[TR]
[TD]11-2006[/TD]
[TD="align: right"]583[/TD]
[TD="align: right"]3979.45[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]48307.31[/TD]
[TD="align: right"]4109.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8833.76[/TD]
[TD="align: right"]8366.25[/TD]
[TD="align: right"]15492.83[/TD]
[TD="align: right"]1137[/TD]
[/TR]
[TR]
[TD]12-2006[/TD]
[TD="align: right"]974[/TD]
[TD="align: right"]5784.79[/TD]
[TD="align: right"]219[/TD]
[TD="align: right"]37357.14[/TD]
[TD="align: right"]1258.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9782.5[/TD]
[TD="align: right"]1197.45[/TD]
[TD="align: right"]9400.5[/TD]
[TD="align: right"]1494.9[/TD]
[/TR]
</tbody>[/TABLE]

sample....right now only 2 data sets per state, but looking to add at least a 3rd

51 states currently X 2 data sets, adding a row for each month...as it is now
 
Last edited:
Upvote 0
sitewolf said:
Code: 
[TABLE="width: 704"]
<tbody>[TR]
[TD="width: 64"]Date[/TD]
[TD="width: 64"]AZ[/TD]
[TD="width: 64"]AZ[/TD]
[TD="width: 64"]CA[/TD]
[TD="width: 64"]CA[/TD]
[TD="width: 64"]CO[/TD]
[TD="width: 64"]CO[/TD]
[TD="width: 64"]FL[/TD]
[TD="width: 64"]FL[/TD]
[TD="width: 64"]GA[/TD]
[TD="width: 64"]GA[/TD]
[/TR]
[TR]
[TD]1-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]570[/TD]
[TD="align: right"]1466[/TD]
[TD="align: right"]54349.26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2849.36[/TD]
[TD="align: right"]5951.96[/TD]
[TD="align: right"]9206.48[/TD]
[TD="align: right"]2905.61[/TD]
[TD="align: right"]3733.82[/TD]
[/TR]
[TR]
[TD]2-2006[/TD]
[TD="align: right"]594[/TD]
[TD="align: right"]18452.75[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]23186.27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14759.06[/TD]
[TD="align: right"]20519.44[/TD]
[TD="align: right"]25770.03[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13359.06[/TD]
[/TR]
[TR]
[TD]3-2006[/TD]
[TD="align: right"]1019[/TD]
[TD="align: right"]3379.18[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]17109.83[/TD]
[TD="align: right"]732.19[/TD]
[TD="align: right"]6175.48[/TD]
[TD="align: right"]5794.91[/TD]
[TD="align: right"]15735.37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1379.84[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]87650.28[/TD]
[TD="align: right"]2520.58[/TD]
[TD="align: right"]4117.01[/TD]
[TD="align: right"]12655.78[/TD]
[TD="align: right"]21206.66[/TD]
[TD="align: right"]8172.88[/TD]
[TD="align: right"]3014.99[/TD]
[/TR]
[TR]
[TD]5-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11280.62[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22223.65[/TD]
[TD="align: right"]1955.68[/TD]
[TD="align: right"]11963.73[/TD]
[TD="align: right"]17515.01[/TD]
[TD="align: right"]36305.92[/TD]
[TD="align: right"]22029.57[/TD]
[TD="align: right"]5387.27[/TD]
[/TR]
[TR]
[TD]6-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20230.55[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]142686.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13920.23[/TD]
[TD="align: right"]24487.21[/TD]
[TD="align: right"]15091.53[/TD]
[TD="align: right"]6244.91[/TD]
[/TR]
[TR]
[TD]7-2006[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]3405.35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]50294.96[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5382.8[/TD]
[TD="align: right"]38582.99[/TD]
[TD="align: right"]4890.31[/TD]
[TD="align: right"]2720.02[/TD]
[/TR]
[TR]
[TD]8-2006[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7433.87[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]105736.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11383.82[/TD]
[TD="align: right"]9225.11[/TD]
[TD="align: right"]16937.41[/TD]
[TD="align: right"]68004.2[/TD]
[TD="align: right"]8695.63[/TD]
[/TR]
[TR]
[TD]9-2006[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]7390.53[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]40704.12[/TD]
[TD="align: right"]528[/TD]
[TD="align: right"]13684.49[/TD]
[TD="align: right"]8967.95[/TD]
[TD="align: right"]6486.65[/TD]
[TD="align: right"]7807.98[/TD]
[TD="align: right"]1684.72[/TD]
[/TR]
[TR]
[TD]10-2006[/TD]
[TD="align: right"]569[/TD]
[TD="align: right"]12847.01[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]26171.31[/TD]
[TD="align: right"]5386.33[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12931.9[/TD]
[TD="align: right"]16938.03[/TD]
[TD="align: right"]9900.8[/TD]
[TD="align: right"]5841.8[/TD]
[/TR]
[TR]
[TD]11-2006[/TD]
[TD="align: right"]583[/TD]
[TD="align: right"]3979.45[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]48307.31[/TD]
[TD="align: right"]4109.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8833.76[/TD]
[TD="align: right"]8366.25[/TD]
[TD="align: right"]15492.83[/TD]
[TD="align: right"]1137[/TD]
[/TR]
[TR]
[TD]12-2006[/TD]
[TD="align: right"]974[/TD]
[TD="align: right"]5784.79[/TD]
[TD="align: right"]219[/TD]
[TD="align: right"]37357.14[/TD]
[TD="align: right"]1258.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9782.5[/TD]
[TD="align: right"]1197.45[/TD]
[TD="align: right"]9400.5[/TD]
[TD="align: right"]1494.9[/TD]
[/TR]
</tbody>[/TABLE]

sample....right now only 2 data sets per state, but looking to add at least a 3rd

51 states currently X 2 data sets, adding a row for each month...as it is now
Click to expand...

How would you like to show your data on chart?
Do you gave one cahrt per state?
All series on one chart?
Shall I assume that when 1-2207 will be added you want this data to show on chart?
 
Upvote 0
actually, I'd originally been charting one data set per state because the numbers from one data set to the next can be widely varying (harder to see what's going on in a set where the numbers are in the hundreds when another set with numbers in the ten thousands is on the same chart).....so currently, it's 51 states, 2 charts per state (plus a couple totals charts by groups of states and overall totals)

I mean, it works as I have it.....I'd just like to not have to manually edit the range on over 100 charts every month :p

Oh, and yes...every month from 2006 to current is showing...
My REAL ideal would allow the user to type a beginning and ending month and have the charts dynamically change accordingly, but for now I'd be happy with just not having to update 100 chart ranges
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Neep help setting up table for plot graph
Replies
0
Views
90
supermom28115
S
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
192
Nick Loughton
N
P
  • Question
Dynamic chart based on month
Replies
7
Views
316
skillilea
S
N
  • Question
Stacked Chart to show value and status YTD
Replies
2
Views
294
neilo1969
N
R
  • Question
Formatting data series for chart created via Slicer and Pivot Table
Replies
6
Views
295
awoohaw
awoohaw

Forum statistics

Threads
1,214,924
Messages
6,122,293
Members
449,077
Latest member
Rkmenon

Share this page

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