How to Skip Data Points in an Excel Graph (with Easy Steps)

Last updated: Dec 20, 2023

Excel is a great tool for showing data in various charts and graphs. These features of Excel help to visualize numerical data. But, sometimes, users need to skip data points to draw Excel graphs. Otherwise, they will not get the desired result. In this article, I will show you how to skip data points in an Excel graph.

How to Skip Data Points in Excel Graph: 4 Easy Steps

In this article, you will find four easy steps to skip data points in an Excel graph. In these steps, I will demonstrate what can be the issue if you don’t skip certain data points while making an Excel graph and how to solve it.

To explain the above-mentioned steps, I will use the following sample data set. Here, are the buying and selling prices of some random products.

Step 1: Use Formula to Determine Output

Firstly, I will demonstrate the reason to skip certain data points while making an Excel graph. Here, I will use the IF function to determine the output and then make a column chart with the result. To do that,

First of all, make a new column beside the primary data set to show the profit, and to do that, insert the following formula in cell E5.

=IF(C5<D5,D5-C5,"")

Secondly, press Enter to check if the first product has made any profit or not.
Then, using AutoFill, find the output of the following cells as well.

Step 2: Insert Column Chart

In the second step, I will make a column chart using the data from the previous step. After making the chart, you will be able to understand the necessity of skipping certain data points. For that,

Firstly, select the cell range B4:E10.
Then, go to the Insert tab of the ribbon, and from the Charts group, select Insert Column or Bar Chart.

Secondly, from the dropdown, select Clustered Column.

Consequently, you will see your column chart in the following image.
Here, the chart does not contain all the data points and it only shows data after product 4.

Step 3: Modify Existing Formula

Now, to skip these data points I will modify the formula from step one. Instead of leaving the resultant cells empty, I will insert #N/A as a value if the logical test of the formula is false. For doing that,

In the beginning, modify the formula from the first step of this procedure and insert it in cell E5 like the following example.

=IF(C5<D5,D5-C5,NA())

After that, press Enter and drag the Fill Handle to get the desired output.
This time the cells whose selling prices are less than the buying price will show #N/A as a result instead of empty cells in the Profit column.

Read More: How to Edit Chart Data in Excel

Step 4: Show Final Result

In this last step, you will see if the modification of the above step will solve the issue or not. For doing that,

First of all, again select the cell range B5:E10 and insert the column chart like the second step.

Finally, you will be able to see that the data points of Product 3 and 4 have been skipped and the graph will start from the beginning point which is product 1.

Things to Remember

Choose the correct cell range while inserting the column chart.
Remember to name your graph or chart after creating.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Skip Data Points.xlsx

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to skip data points in an Excel graph. Please share any further queries or recommendations with us in the comments section below. After commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

<< Go Back to Data for Excel Charts | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

L. O. Dec 18, 2023 at 3:04 AM

Thanks but this won’t work because I don’t want the table changed. I repeat the headings on my chart every month because they are needed. The problem is when I try to make a table over several years of data, the headings are getting treated as zeros in my graph. I am looking for a way to exclude the headings because they aren’t actually data points at all.

I was hoping for a formula to exclude text, non-numerical input, or an IF function to exclude zeros. I did try setting the y-axis minimum to 10 since my real data points are all around 30-40, but the line on my scatter plot still tries to connect to them. I will try just giving up on the line, but I did actually want it.
- Reply
  Lutfor Rahman Shimanto Dec 28, 2023 at 9:31 AM
  
  Hello L. O.
  
  Thanks for reaching out and sharing your problem. You are facing difficulties with including a heading in a chart, and those headings affect the graph’s appearance.
  
  As the headings are treated as zeros, you can exclude them from the data by combining the IF, ISNUMBER and NA functions. The formula structure can be like the following:
  
  =IF(ISNUMBER(A2), A2, NA())
  
  This formula checks if the value in cell A2 is a number. If it is, it includes the value. Otherwise, it returns #N/A. The #N/A values will be ignored in the chart, and the line won’t connect to them.
  
  Instead of a scatter plot, you might consider using a line chart or other chart types that suit your needs better. Line charts, for example, automatically ignore #N/A values and don’t connect them.
  
  Hopefully, the ideas will help you to overcome your situation. Good luck!
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy