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.
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
- How to Expand Chart Data Range in Excel (5 Suitable Methods)
- Change Chart Data Range in Excel (5 Quick Methods)
- How to Format Data Series in Excel (with Easy Steps)
- Add Data to an Existing Chart in Excel (5 Easy Ways)
- How to Change Data Source in Excel Chart (3 Useful Examples)
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 (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.
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
- How to Create Org Chart From Excel Data (With Easy Steps)
- How to Link Excel Data to PowerPoint Chart (3 Simple Methods)
- Selecting Data in Different Columns for an Excel Chart
- How to Combine Daily and Monthly Data in Excel Chart
- Select Data for a Chart in Excel (2 Ways)
- How to Create Graph from List of Dates in Excel (with Easy Steps)