Updating charts over time

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Hi all, bit of a long shot, but...

Does anybody know if I can update a chart automatically as more data is populated, over time? In Excel 2016.

I have a set of data in various pivot tables - these Pivots update when I open the workbook each time using auto-refresh within the Options menu of the Pivots.

This Pivoted data then populates a normal table with various formulas in it, which lift data from the Pivots as and when it is updated.

Because I am looking at data with a time stamp (quarterly), I have pre-populated my normal table up until the end of 2020; so anything between today and the end of 2020 is showing as a zero figure (using IFERROR formula to get this) - this is so I can avoid having to add formulas and rows to my normal table each quarter.

I have charts created, using data in the normal table, which basically have data ranges covering data for dates that have passed.

If I want to update the chart in three months to cover another (yet to happen) quarter, then I will manually change the chart data range to cover this new quarter and the chart then updates.

IS there a way at all to make this happen automatically, so if the yet-to-happen quarter that is currently presenting with zero figures becomes populated with actual figures, it shows on my chart?

I don't want to have a chart with yet-to-happen quarters on it, hence the restricted and specific chart data range at the moment.

I have a lot of different charts you see, hence the question (if it was one, manual updates would be fine).

Thanks!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I may not be understanding the intricacies of your sheet but the two ways I've done this in the past are:

(1) Have the Horizontal Axis based on an Excel Table. As the Table extends the Axis grows.

(2) Have the Horizontal Axis defined for my 12 month cells but suppress the content to nulls for unused months and in the Select Data Source click on "Hidden and Empty Cells" and select "Show empty cells as Gaps".

CDEFGHIJKLMNO
Re-homed
Dogs
Cats

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Jan-19[/TD]
[TD="align: center"]Feb-19[/TD]
[TD="align: center"]Mar-19[/TD]
[TD="align: center"]Apr-19[/TD]
[TD="align: center"]May-19[/TD]
[TD="align: center"]Jun-19[/TD]
[TD="align: center"]Jul-19[/TD]
[TD="align: center"]Aug-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]75[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]72[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]79[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=IF(SUM(E2:E3)<1,"",EOMONTH(D1,0)+1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"]---ditto---[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O1[/TH]
[TD="align: left"]=IF(SUM(O2:O3)<1,"",EOMONTH(N1,0)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


<img src="https://i.ibb.co/gZ24Mqn/Graph-Example.jpg">
 
Upvote 0
Wow, thank, took me a while but works well.
Only problem is I am looking at quarters, not months.
Any idea what the formula will be for this?
i.e. Jan-19 will be Jan-Mar '19, then the next will not be Feb-19, but Apr-Jun '19 etc.?
 
Upvote 0
I'm sorry but it seems this suppression of empty cells in charts has stopped working for me.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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