Google Sheets has a lot of options for charts and other visual illustrations which can be used for all kinds of reasons and seasons–pie charts being one of the most common. Knowing how to plot a nice pie chart is a skill often taken for granted. Learning how to sort pie charts by percentage in Google Sheets is therefore one thing we can hardly afford to ignore.
Pie charts are a useful visual element in data representation. They make understanding data much easier than bare numbers. They are widely used for general purposes as well as in business and mass media. Their ubiquity thus makes it pertinent to use them–well and effectively.
Pie charts can be easily created in any spreadsheet, such as Google Sheets and Microsoft Excel. But designing and formatting them to make them more visually appealing and readable require some tuning and tweaking with the data of the chart. Not all pie charts are equal; some are more effective at communicating information than others.
So, we are going to learn a few tips on how to sort a pie chart by percentage in Google Sheets to make it effective–and visually appealing.
Pie charts are best used only when the number of items is few, say, below ten; for at large numbers the slices become cluttered making them difficult to read. They also don’t convey subtle and small differences very well. For this, a bar graph may be more suitable.
How to make pie chart in Google Sheets and sort pie charts by percentage in Google Sheets
Here’s a quick step to sort pie charts by percentage in Google Sheets:
- Calculate the percentage of the individual items
- Sort the percentage data in descending or ascending order
- Go to the Insert tab and select chart
- Customise the chart according to your preference.
To learn in more detail how to make a pie chart in Google Shee and sort the pie chart by percentage in Google Sheets, let us consider a typical monthly expenditure as the data to be represented on the chart and plot the chart step by step and sort the pie chart by size.
You can make a copy of this spreadsheet and follow along with the tutorial.
Step 1: Entering the data to create a pie chart in Google Sheets
We enter the data and calculate the sum of the amount of the items using the SUBTOTAL function.
Step 2: Calculating the percentage to sort the pie chart by size
We then calculate the percentage of each item to the total amount. To do so, we divide the amount of the item by the total amount.
The dollar sign (“$”) is added so that the formula doesn’t change when copied. We then drag the cell to the last item to calculate the percentage for each item. Then click on the percent symbol to convert the decimal to percentage.
Step 3: Sorting the data to order the pie chart by value
We then sort the data either in ascending or descending order. It is better that the data be sorted in descending order so that the largest option appears first.
The data can be sorted using a number of methods. Here we’ll use the SORTN function to sort our data in descending order. We can place the sorted data in the same sheet or a different sheet. In this example, we use a different sheet. So we type in the following formula on a new sheet:
=SORTN('Monthly expenses'!A3:C9,7,0,'Monthly expenses'!C3:C9,false)
Here, ‘Monthly expenses’ is the name of the sheet from which we want the data sorted, A3:C9 is the range, 7 indicates the number of rows to return, C3:C9 is the column range from which we want to sort the data (the percentage data, in our case), and false sorts the data in descending order.
Step 4: Creating the pie chart in Google Sheets
Having sorted the chart in by size in descending order, we select the data range to be plotted (here, we select the percentage values, ie, C4:C10, since we want the pie chart to be sorted by percentage).
Then go the to “Insert” menu and select “Chart”. A pie chart will be automatically created. This pie chart is sorted by the size of the pie according to the weightage of each item. If another type of chart is created, we can go to the “Chart type” on the “Chart editor” option and select “Pie chart”. And add labels by selecting the items.
Step 5: Customising the pie chart sorted by size
We then go the the “Customise” option on the Chart editor menu for customising options.
Here there are different options such as chart style, slice label, colour option for each slice, chart title and so on.
Avoid bright and fancy colours for the slices and make them contrasting enough to be visualised distinctly. You can use this site for choosing the colour scheme.
Having customised the chart according to our preferences, we can close the Chart editor menu.
So there it is, our pie chart in Google Sheets sorted by percentage and neatly customised. And that is how we sort a pie chart by percentage in Google Sheets.
Pie charts can make data visualisation and interpretation much easier and quicker. And they come handy in multiple scenarios. So knowing how to create an intuitive and visually appealing chart is hugely essential. Google Sheets has various tools to help us do that. Visit our blog for many more articles about Google Sheets.
Now that you’ve learned how to insert and sort pie charts by percentage in Google Sheets, take your learning to a higher level starting with one of the articles below.