Skipping Data in a chart

Question

I have data with corresponding dates, the data box is becoming to large. Can I show only the every 3rd peice of data while retaining the line chart

Answer 1

By better reading your request, I see now your problem opposite: keep all the data in chart, but reduced in the table, OK? In that case, the solution is to hide each but n-th row on the datasheet and ensure (in chart Options) that your chart will depict all the data.

The macro below does the hiding.

My apologies

PB

Option Explicit

Sub HideButNth()

Dim R As Long, RF As Long, RL As Long

Static N As Long

RF = Selection.Row

RL = Selection.End(xlDown).Row

If N = 0 Then N = 2

N = Application.InputBox("Select the first data cell!!!" _

& vbCrLf & "and input N for unhidden each Nth", "Hiding data", N, , , , , vbLong)

Application.ScreenUpdating = False

Range(Rows(RF), Rows(RL)).Hidden = False

If N <= 1 Then Exit Sub

R = RL

Do

If R Mod N <> 0 Then Rows(R).Hidden = True

R = R - 1

Loop Until R <= RF

Application.ScreenUpdating = True

End Sub

Answer 2

Your (y-)data being most probably arranged in columns, you have to create an auxiliary column array, containing values that will be drawn instead of original ones. Provided the original data contained in column B begin in the row 3, let auxiliary column be the C. Somewhere (here into C2) write down the number interval you would like to apply (each third value – input 3). Into C3 write the following formula

=IF(MOD(ROW()-ROW($B$3)-C$2,C$2)=0,B3;"")

copy down (C3 select, double click on a small square bottom right), and switch y-data in your chart from column B to C.

By changing the number in C2, you can easily control the dilution of original data.

Regards

PB

Answer 3

Hi,

If you do not have any confidential data in the file then you may upload the sample data to the SkyDrive folder and provide us the link so that we can assist you better.

Refer the link below to upload the sample data to the sky drive:

http://social.technet.microsoft.com/Forums/en-US/w7itproui/thread/4fc10639-02db-4665-993a-08d865088d65

Note: Do not share the document which has confidential information.

Thank you.