# Filtering data for graph in Excel

I have a x and y set of data I am trying to filter for a graph. The problem with my data is there are too many points, and I would like to filter out a significant amount of the data. In my case, I have data points corresponding to 5 second increments, but I would like to filter the data to only include points every 20 minutes.

To get the filtered x-axis data, I am using the following IF/ELSE statement:

IF(MOD(K6/20,1)=0,L6,0) Note: K6 is unfiltered x-axis data.

This statement either sets the value in the column to zero if the value isn't a increment of 20, or the value is set equal to actual time value.

For the y-axis I have been using the following code to filter the data. The y-axis values are determined based on the filtered x-axis values.

=IF(\$U6 = 0,"",M6) Note: U6 is filtered x-axis data, and M6 is unfiltered y-axis data.

This statement either leaves the y-axis cell blank if the time value isn't a increment of 20, or the value is set equal to the actual y-axis value.

Both of my statements work, but the problem is Excel associates a blank cell with a value of 0 on a graph. I have also tried "NA" and NULL in the IF/ELSE statement, but I always get the same association to 0 in the graph. Does anyone know how to fix this?

Thanks

• Use a filter (`Data -> Sort & Filter -> Filter`) to hide the rows you don't want to appear (i.e. those whose value is blank in your formula column) – cybernetic.nomad Aug 5 '19 at 21:46
• Are you using the static value NA or the formula NA()? Excel should ignore #N/A, which are the result of NA(). Other values may be interpreted as 0 and plotted. – dav Aug 6 '19 at 19:03
• Thank you, the filter data worked perfectly. Just a note so other people realize, if you have your graph in the same area as your data, move the graph before filtering the data. Otherwise, when you filter the data the graph will shrink with the removal of the rows in excel. – Grady F. Mathews Iv Aug 7 '19 at 13:55

## 1 Answer

I made a spreadsheet that demonstrates a neat way of doing this. You use the `INDEX()` function to retrieve every 20th value.

So my formula for filtering out data reads like this:

`=IFERROR(INDEX(X_Data__Raw_, (Value_No * Filter_Every), 1), NA())`

Where

• `Value_No` is a column of `1, 2, 3, ...` next to the `X_Data__Raw_`column
• `Filter_Every` is `20`

Here is what each part is doing:

• The first argument to `INDEX()` is the column of data, `X_Data__Raw_`
• The second is the row which will be returned, in this case `1, 2, 3, ...` times `20`
• The third is the column, which will always be `1`
• the `IFERROR()` function catches all the times that the row argument is too big, and returns `NA()`, which is then ignored by the chart

I used FormulaChop (Full disclosure: I wrote FormulaChop) to generate the formula above. Here is a screenshot of the output. Here is a link to the spreadsheet.