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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


4 Easy Steps to Skip Data Points in Excel Graph

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.

4 Easy Steps to Skip Data Points in Excel Graph


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.

Read More: How to Import and Use Data into Power Pivot in Excel


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.

Read More: How to Create Excel Chart Using Data Range Based on Cell Value


Similar Readings


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())

Modifiying Formula as An Easy Step to Skip Data Points in Excel Graph

  • 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 (5 Suitable Examples)


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.

Showing Final Result as An Easy Step to Skip Data Points in Excel Graph


Things to Remember

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

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.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


Related Articles

Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo