Chart x axis data selection

[cvincent]

Using Excel 2007. My X axis is picking up dates from my spreadsheet to chart corresponding $ amounts. The spreadsheet has a daily date in every 5th column that is being charted. Each day new columns are added to cols E thru H, and the new date is then calculated in Col E (=J+1). The older dates then move to the right.

Likewise, my chart reads from right to left; i.e the oldest date is on the right, and the most current on the left. Is there a way to have my chart only pick up the data for each Sunday date, so that I am only charting every 7 days? I have tried hiding the columns Mon thru Sat, but that leaves large gaps in my chart, plus we would like to have the data showing in the spreadsheet. I have major and minor units set at 7, but it is still pickiing up every day instead of just one day/week. The chart is getting too large to chart every day.

Any help on this is appreciated.

Thank you.

 

[Steve=True]

Hi Cvincent,

Welcome to the forums!

One possible solution is to create a new chart range that looks at your data and gets every 7th value.

If your data looks like this picture, then put this formula in cell B6 and copy across and down as needed.
=INDEX($B2:$R2,1,1+COLUMN(A1)*7-7+0*ROW(A1)-0)

Excel 2010
	A	B	C	D
6	Data	1/1/2012	1/8/2012	1/15/2012
7		3	1	3
Sheet3

Cell Formulas
Range	Formula
B6	=INDEX($B2:$R2,1,1+COLUMN(A1)*7-7+0*ROW(A1)-0)
B7	=INDEX($B3:$R3,1,1+COLUMN(A1)*7-7+0*ROW(A1)-0)
C6	=INDEX($B2:$R2,1,1+COLUMN(B1)*7-7+0*ROW(B1)-0)
C7	=INDEX($B3:$R3,1,1+COLUMN(B1)*7-7+0*ROW(B1)-0)
D6	=INDEX($B2:$R2,1,1+COLUMN(C1)*7-7+0*ROW(C1)-0)
D7	=INDEX($B3:$R3,1,1+COLUMN(C1)*7-7+0*ROW(C1)-0)