- Got it Pass Team
Excel Data Tool: Sorting, Filtering and Validation
Updated: Dec 23, 2020
Managing MS Excel data is the everyday task to many accountants. I share to you how to use different Excel data tools, such as sorting, filtering and validation to help you working efficiently in front of thousands of rows in a spreadsheet.
Sorting Data
You can sort your data into different orders, using a single column or multiple columns. You access the Sort functions in the ‘Sort & Filter’ group of the ribbon
First you need to select the range that you want to sort, which in this case is A1:E7
Excel will automatically detect that there are column headers and check the box for ‘My data has headers’ so that the headers do not form part of the sort. However, make sure that your header row is not included in the sort and that the headers box has been ticked.
You do not need to have a header row – in that case make sure that the headers box is unticked.
In the ‘Sort By’ dropdown, you choose the first column that you want to sort by e.g. Product. You would normally use the default of ‘Cell Values’ to sort on in the ‘Sort On’ dropdown, but you can also sort by cell colour or font colour using this dropdown.
The ‘Order’ dropdown gives you ascending or descending options
Once you have selected the options in the three dropdowns then click OK and the data will be sorted (Image 2).
You can add further sorting levels by clicking on ‘Add Level’. If you wanted to sort by Product first and then Price you would add a new level for the Price sort.
You can also delete or copy a selected level when fine tuning your sort.
The ‘Options’ button allows you to sort top to bottom or left to right (Image 3).
Filtering
Filtering allows you to display a sub set of your data. To invoke this function, you click on ‘Filter’ in the ‘Sort & Filter’ group of the ribbon. This will put a dropdown for each of your column headers.
To remove the filter, simply click again on ‘Filter’
By clicking on the dropdown on a column header in the data range, there are a large number of options to filter your data.
If, for example, you only wanted to show ‘Apples’ the you untick the ‘Select All’ box and tick the ‘Apples’ box. This will then only show ‘Apples’ data
If the column is a text column, you can use the ‘Text Filters’ for more complicated filtering, and if it is a numeric column, you can use ‘Number Filters’ in the same way.
You can also use the sorting options on the pop-up menu to sort your sub set of data
Data Tools
Text to Columns
This function is found in the ‘Data Tools’ group of the Data tab on the ribbon. This useful function allows you to split a single column of data into multiple columns by using a standard delimiter to split each part of the text.
In this example you have a column of data giving first name and last name, separated by a space.
Select your data and then click on ‘Text to Columns’. This will display a three step Wizard that will allow you to define how your data is to be split and to show you a preview of what your data will look like
Accept the default value of ‘Delimited’ and click on ‘Next’. You will then see ‘Step 2’ of the Wizard (Image 9). Tick the ‘Space’ box as the delimiter, and make sure that all other Delimiter tick boxes are unchecked.
The Preview box will show you how the data will appear in your spreadsheet.
At this stage, you can click ‘Finish’. The third step of the Wizard gives options for date formats and converting text to numbers and dates.
Your single column of names will now show in two columns. (Image 10)
Flash Fill
This function allows you to put in an example of how you want data displayed, and then Flash Fill will continue this example for the rest of the data. The Flash Fill icon is in the Data Tools group on the Data tab of the ribbon.
For example, we have data in two columns of first name and last name.We want to see it as last name first name separated by a comma and a space. The example goes in cell C1
Click on the Flash Fill icon (shown by the arrow above) and the data will be filled in below the example
Remove Duplicates
Data that has been imported into a spreadsheet will sometimes contain duplicate values which you often do not need. The ‘Remove Duplicates’ function will allow you to quickly remove these values
The ‘Remove Duplicates’ icon is in the ‘Data Tools’ group on the Data tab of the ribbon (see arrow below)
In this example, there are several duplicate names. Select the range of data and click on the ‘Remove Duplicates’ icon.
This will display a pop-up menu showing the columns within the data range. You can also indicate whether the range contains headers or not.
In this case we can use all three columns. Click OK and the duplicates will be removed, showing a status message of how many duplicates have been removed and how many unique values remain.
Data Validations
You can use this to specify a rule for what data can be entered into a cell. See where the arrow is pointing in ‘Data Tools’ in the ‘Data’ tab on the ribbon. The ‘Allow’ drop down gives several options, but for this example we will use ‘Whole Number’ and ‘Between’ with minimum set to 10 and maximum set to 20.
You can enter an input message to prompt a user who goes to enter a value into the cell, and you can also create an error message if the rule is breached. These two tabs are optional – if the user breaks the rule, a default message will appear.
Click on OK and that validation rule will now apply to the selected cell
Click on the cell and the input message will appear. Try entering a value outside of 10 to 20 and the error message will appear (Image 17 & Image 18).
Note that if you enter a decimal number, e.g. 15.5, this will still break the validation rule because ‘Whole Number’ was selected.
Consolidate
This function will consolidate two ranges of data into one range. The ranges can be anywhere in the workbook.
In this example there are two simple ranges of data, one under each other. Click on the ‘Consolidate’ icon shown by the arrow below and a pop-up menu will appear.
You can choose a function for consolidation e.g. sum, count, average
Click on the reference arrow and select the first range for the consolidation. Click the ‘Add’ button to add the reference into the consolidation. Click the reference arrow again and select the second range to be consolidated.
Make sure that the ‘Use labels in’ boxes have been ticked and click OK
Your consolidation will then appear (Image 21).
Relationships
You can create relationships between tables within Excel which can be used by Power Pivot (See below). To create a table, you choose ‘Insert’ on the ribbon and then choose ‘Table’ in the ‘Tables’ group
In this example, there are two tables, one showing the product and quantity, and the other showing the product and the supplier. We want to link the product field so that the relationship will show both the quantity and the supplier
The Relationships icon is in the ‘Data’ tab in the ‘Data Tools’ group (see arrow below)
Note that the product field has unique values – duplicates will cause an error
Click on the Relationships icon and a pop-up menu will appear (Image 23). Click on ‘New’ and a pop-up menu will appear allowing your relationship to be defined between the two tables
You use ‘Product’ as the linking column between the two tables (Image 24)
Click OK and your relationship will be created. You can edit or delete the relationship if required (Image 25)
This relationship can now be used in the Power Pivot function
Power Pivot
A Power Pivot table can be used to show data using the relationship between the two tables in the previous example. Using this relationship, click on the ‘Power Pivot’ function in the ‘Data Tools’ group in the ‘Data’ tab of the ribbon (see arrow)
This will display a pop-up menu that detects the two tables and the relationship between them. This shows the data of the two tables within the relationship. You can move between them using the tabs at the bottom of the window (Image 27)
Click on ‘Pivot Table’ in the ‘Formatting Group’ of the ‘Home’ tab and define where you wish to place your pivot table. Right click on ‘Pivot Table’ in the pop-up menu and a further pop-up menu will be displayed
Click on OK and a Pivot Table design pane will appear at the right-hand side of the screen
You can then move the required fields into the pivot table, so that the pivot table is driven from the data in both tables on the spreadsheet.
#exceldatavalidation #exceldatasorting #howtosortnumbersinexcel #howtofilterdatainexcel #exceldatatool
If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).
Got it Pass Team is happy to work with Richard in presenting this article.
About Richard Shepherd-
Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.
Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you.
The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.
135 views