Data can be presented in many formats but learning how to sort data is a crucial skill for any Excel user. This beginners Excel guide will teach you the basics of how to sort your data tables in Excel …
Excel data can present itself to you in a variety of formats, for example Mr Green’s Greengrocer shop has collected their monthly sales figures and put it into a nice Excel table:
The fact that records are kept is great but in the current format the table is really only useful for picking out single items, i.e. you can see that 300 Lemons were sold. What the Excel analyst should be looking to do is present the data in a sorted format, that way it is possible to not only pick out single items but also quickly and easily see what the best and worst selling items are.
To Sort the Data
The first step is to highlight all the data that needs to be sorted by selecting the starting cell of the data table and dragging the mouse across all the data range while keeping the left button pressed:
With the data range still highlighted the next step is to click on the Sort & Filter icon found on the right hand side of the Excel Ribbon and select “Custom Sort” from the options box:
The Sort options box will open; this allows you to state how you want the data sorted:
To Sort the data by the number of units sold in descending order (high to low) complete the following steps. Select “Units Sold” from the drop down menu:
Next change the Sort Order to “Largest to Smallest” and click OK:
The Sort Options box will close after you click OK and the sort will have been applied to the Excel data table you highlighted, as the image below shows the data has now been sorted by Units Sold in descending order:
Now Mr Green can see that Apples is his best-selling Item, closely followed by Carrots. On the other hand he can also quickly see that he does not sell many Mango’s or Blueberries so that is a lot more useful for stock ordering purposes.
If you wanted to sort the data by Sales Income to see which items were returning the most then repeat the steps above but change the sorting column to “Sales Income”:
After clicking on OK the Excel data table sort will have changed to Sales Income:
Now Mr Green can see that Strawberries and Grapes bring in the most money, while those pesky Mangos are not only the lowest sellers but also have the lowest value, time to replace with something more exotic perhaps!
- As an Excel user never underestimate the power of good data presentation skills. A simple sort on a table can provide a lot more insight when produced correctly.
- Consider the right sort to display each data table. Sorting can be done on multiple columns if needed just by adding levels in the custom sort options box like the image below shows:
- Be mindful when adding more data to a table that has already been sorted. If new rows of data are added to your table then you will need to redo the Sort based on the new data range.