• 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


Image 1: Sorting Function in 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).


Image 2: Sorting on "Product"

Image 3: Add one level in sorting



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


Image 4: Filter in "Sort & Filter" group of the ribbon

Image 5: Dropdown menu in filtering

Image 6: Untick "Select All" and tick "Apples"

Image 7: Filtering results



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)


Image 8: Text to column in Data Tools group of the ribbon

Image 9: Step 2 in "text to column"

Image 10: Result after clicking "Finish"

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


Image 11: Flash Fill icon is in the Data Tools group on the Data tab of the ribbon

Click on the Flash Fill icon (shown by the arrow above) and the data will be filled in below the example


Image 12: Flash Fill result


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)


Image 13: ‘Remove Duplicates’ icon is in the ‘Data Tools’ group on the Data tab of the ribbon

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.


Image 14: Pop-up menu in Remove Duplicates function

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.


Image 15: Remove Duplicates result

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.


Image 16: Data Validations in "Data Tools"in the Data tab on the ribbon

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.


Image 17: Entering a value outside of 10 to 20

Image 18: Error message appears

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


Image 19: Click on the ‘Consolidate’ icon shown by the arrow below and a pop-up menu will appear

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).


Image 20: Second range of data selected

Image 21: Consolidation result appears


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


Image 22: Relationships icon is in the ‘Data’ tab in the ‘Data Tools’ group

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)


Image 23: Click on the Relationships icon and a pop-up menu will appear

Image 24: You use ‘Product’ as the linking column between the two tables

Image 25: You can edit or delete the relationship if required

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)


Image 26: ‘Power Pivot’ function in the ‘Data Tools’ group in the ‘Data’ tab of the ribbon

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)


Image 27: Pop-up menu that detects the two tables and the relationship between them

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


Image 28: Pop-up menu on creating PivotTable

Click on OK and a Pivot Table design pane will appear at the right-hand side of the screen


Image 29: Pivot Table design pane appears on the right hand side

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.

534 views