You have a spreadsheet full of information but it’s all muddled up and you want to group together all the surnames beginning with A, for example. How do you do it?
Well, you could search for them all and copy/cut and paste them so that they are together but that’s a lot of unnecessary work. Especially when you can Sort or Filter them instead.
Here is a step-by-step guide to sorting and filtering in Excel.
Step 1 – Format as a Table
- Highlight the area containing the data you are working with.
- Go to the Home
- Click on Format Table on the ribbon.
- This will bring up a selection of table style examples.
- Choose a table.
- A pop-up box will appear asking you to check that the data range is correct if it is then select yes.
- Put a tick in the box if the table has headers (you do need these to sort or filter the data, if your table doesn’t have headers yet then they will be created with the title Column1, Column2 etc).
- Click OK and your data will be formatted as a table.
Step 2 – Sorting your data
Use the Sort facility to sort your data into alphabetical order.
- Click on the header of the column you want to sort.
- Go to the Data
- In the Sort and Filter section of the ribbon select A to Z or Z to A, depending on whether you want the data to be sorted descending or ascending.
- Click on the Sort
- A dialogue box will appear that will ask you:
- Which column you wish to sort from
- The value you wish to sort on
- What order you wish to sort to
- By using this dialogue box, you can sort on more than 1 level which means that you can sort by surname, then the first name, for example:
- Click on Add Level and populate data as above.
- Click on the down arrow on the column header of the column you wish to sort by.
- Choose Sort A to Z or Sort Z to A.
- You can also sort by colour if you have highlighted your data.
Step 3 – Filter your data
Use the Filter facility to look for specific data in your table.
- Your column headers should have different names.
- Highlight the table area.
- To create the drop-down arrows in the column headers that allow you to filter the data, go to the Data tab and click on Filter in the ribbon. NB. If you click on Filter again the drop-down arrows will disappear as the filter has been removed.
- Left click on the drop-down arrow on the column header of the data you wish to filter.
- A list will appear that contains the values of the cells in the column.
- To select data put a tick in the tick box.
- You can de-select data by clicking the tick box again to remove the tick.
- You can select all and you can perform a search for the data you are looking for.
- Filter by Cell Color to find all cells of a specific colour.
- Filter using Text Filters to customise your filter and look for data equal to, not equal to, beginning with, ending with, containing, not containing or create your own custom filter.
- If you have obtained all the data required and want to see the whole sheet again click on the drop-down arrow again in the column header and select the ‘Clear Filter From…..’
And there we go, how to Sort and Filter. It saves a lot of time and makes finding data a lot easier.
(Originally posted on 09/11/2017)