Dynamic Dates on Chart

Barren_Wuffett

New Member
Joined
Aug 6, 2019
Messages
12
Hey everyone, I have a fixed table with dates (x-axis on desired chart) that go for several years into the future, but data (y-axis) that only goes a few months. This will be a working file where new data is added each month. I'd like an area chart to show the data across the dates that have already happened (only the dates that have data). But if I select all of the data for the chart, the x-axis goes all the way out to the last date, which has no data. I'd like the chart to only show the dates for which I have data (x-axis automatically gets longer when new data is inputted each month). I know I could just adjust the chart data area every time, but I'd prefer it to be dynamic. Any ideas?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
IF Your Y-Axis Data s in Column A from A2 to A100 AND Your X-Axis Data s in Column B from B2 to B100
Go to Formula tab and then Name Manager, New And Then add one Names without Space e.g. ChartValues
And at Referto Section Write (change sheet2 to your sheet name):
Excel Formula:
=OFFSET(Sheet2!$B$2,,,COUNTIF(Sheet2!$B$2:$B$100,"<>" & ""))
AND again for Y-Axis go to New And Then add one Names without Space e.g. ChartMonths
And at Referto Section Write (change sheet2 to your sheet name):
Excel Formula:
=OFFSET(Sheet2!$A$2,,,COUNTIF(Sheet2!$B$2:$B$100,"<>" & ""))

Now go to Insert Tab and Select Chart Type, Go to Chart Design Tab and Select Data
Remove Previous data for that part and again add New Series, at Series Value Write:
Excel Formula:
=Sheet2!ChartValues
And At Horizontal axis Label Hit Edit and write:
Excel Formula:
=Sheet2!ChartMonths

Now your dynamic chart ready.
 

Barren_Wuffett

New Member
Joined
Aug 6, 2019
Messages
12
Thanks, Maabadi. I see what you're doing. Can you help me change your instruction to match what's actually in my workbook? I have several categories for the Y-Axis, and my content is in rows, not columns. Dates are in C16:AF16 as of now. And my data is in C17:AF21. Category labels are in B17:B21.

Thanks!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. Please Update your Acount Details to we know you use which OS & Excel Version.
2. Please Upload example File with 5-15 rows with XL2BB ADDIN (Preferable) or At free hosting site e.g Google drive, Onedrive or www.Dropbox.com and Insert Link Here.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
AND I think you have multiple Category for X-AXIS Not Y-AXIS
For ChartMonths Use this formula
Excel Formula:
=OFFSET(Sheet2!$C$3,,,,COUNTIF(Sheet2!$C$4:$AF$4,"<>" & ""))
AND for Values (Categories from 1 to 10) add Names (I add Here Category1 to 10). For Category1:
Excel Formula:
=OFFSET(Sheet2!$C$4,,,,COUNTIF(Sheet2!$C$4:$AF$4,"<>" & ""))

AND for other Category Add row number 1 by 1.
Category 2 is $C$5 & Sheet2!$C$5:$AF$5
AND So on.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try This:

Book2

If you want change X & Y column go to select Data and Switch Row/Column
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

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
Top