Excel Pie Chart Only Show Non Zero Values
Excel is a powerful tool that can help you visualize data in many different ways. One of the most popular ways to visualize data is by using a pie chart. A pie chart is a circular chart that is divided into slices to represent data. Each slice represents a percentage or proportion of the whole. However, sometimes you may want to exclude data that has a value of zero from your pie chart. In this article, we will show you how to create a pie chart in Excel that only shows non-zero values.
Step 1: Enter your Data
The first step in creating a pie chart is to enter your data into Excel. For this example, we will use a simple dataset that shows the sales of different products:
As you can see, this dataset shows the sales of four different products: A, B, C, and D. To create a pie chart for this data, we need to select the cells that contain the data and then click on the "Insert" tab in the Excel ribbon.
Step 2: Insert a Pie Chart
Next, we need to insert a pie chart into our Excel worksheet. To do this, we need to click on the "Pie Chart" icon in the "Charts" group on the "Insert" tab:
When you click on the "Pie Chart" icon, Excel will give you a few different options for the type of pie chart you want to create.
Step 3: Select the Pie Chart Type
For this example, we will select the "3-D Pie" chart type. To do this, simply click on the "3-D Pie" option in the "Pie" section:
Once you have selected the pie chart type, Excel will insert a blank pie chart into your worksheet:
Step 4: Edit the Data Series
Now that we have our pie chart, we need to edit the data series to only show non-zero values. To do this, we need to right-click on the chart and select "Select Data" from the context menu:
When you click on "Select Data", Excel will open the "Select Data Source" dialog box:
In this dialog box, you can see the data series that are currently being used in your chart. To edit the data series, click on the "Edit" button:
When you click on the "Edit" button, Excel will open the "Edit Series" dialog box:
In this dialog box, you can see the "Series Values" field. This field contains the data that is being used in your chart. To only show non-zero values, we need to edit this field.
Step 5: Edit the Series Values Field
To edit the "Series Values" field, we need to click on the "Collapse Dialog" button (the button with the red circle in the image below):
When you click on the "Collapse Dialog" button, Excel will shrink the "Edit Series" dialog box and allow you to select the cells that contain the data you want to use in your chart:
To only show non-zero values, we need to use a formula in the "Series Values" field. The formula we will use is:
=SERIES(Series Name, Sheet1!Data Range, Sheet1!Data Range<>0, 1)
Replace "Series Name" with the name of your data series (in this example, it's "Sales"), and replace "Data Range" with the range of cells that contain your data (in this example, it's A2:A5).
When you have entered the formula, click on the "OK" button to close the "Edit Series" dialog box.
Step 6: Format the Pie Chart
Now that we have our pie chart with only non-zero values, we can format it to make it look better. To format the pie chart, simply click on the chart to select it, and then use the formatting options in the "Chart Tools" section of the Excel ribbon:
Conclusion
Creating a pie chart in Excel that only shows non-zero values is a simple process that can help you visualize your data more effectively. By following the steps outlined in this article, you can create a pie chart that accurately represents your data and excludes any values that are equal to zero.