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.
- 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 Limit Data Range in Excel Chart
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.
- 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.
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.
- How to Change Chart Data Range in Excel
- How to Edit Data Table in Excel Chart
- How to Change Data Source in Excel Chart
- How to Change X-Axis Values in Excel
- How to Change Chart Data Range Automatically in Excel
- How to Sort Data in Excel Chart
- How to Group Data in Excel Chart
- How to Remove One Data Point from Excel Chart
- How to Hide Chart Data in Excel