(Basic Statistics for Citizen Data Scientist)
Sorting and Filtering
Excel provides a number of basic capabilities for sorting and filtering data in a worksheet. These capabilities are accessible from the Data ribbon.
Example 1: Sort the data in the range A3:D10 of Figure 1 by income
Figure 1 – Data to be sorted
Highlight the range A3:D12 and select Data > Sort & Filter|Sort. When the dialog box shown in Figure 2 appears, choose Income for the Sort by field and make sure that the My data has headers field is checked.
Figure 2 – Sort dialog box
Once you click on the OK button the data in range A3:D12 will be overwritten with the data in sorted order as shown in Figure 3.
Figure 3 – Data sorted by Income
Now suppose that in the case of ties we wanted the entries to be sorted in alphabetic order by the person’s name. Notice that three people have an income of 35,000 and two have an income of 45,000. In neither case are the entries in alphabetic order by name. To perform a multilevel sort you need to press Add Level button in Figure 2. The dialog box will change to give you the opportunity to supply two sort keys. Fill in the entries as shown in Figure 4.
Figure 4 – Multiple level sort
This time the data will be sorted as shown in Figure 5.
Figure 5 – Data sorted by Income/name
Observation: If you want to remove any rows from a range with an empty cell or a cell with non-numeric data, you can sort the range first. All the rows with an empty cell or cell with missing data will be moved to the end of the list where they can easily be removed. This is a way of removing rows of data with missing elements.
Example 2: Remove any duplicates from the Income column of Figure 1.
Highlight the range D3:D10 and select Data > Data Tools|Remove Duplicates. The result is shown in Figure 6.
Figure 6 – Data without duplicates
Example 3: Extract from the data in Figure 1 all the people who have income between 35,000 and 45,000 inclusive.
Highlight the range A3:D12 and select Data > Sort & Filter|Filter. The data will change as shown in Figure 7.
Figure 7 – Data after Filter is chosen
Click on the downward arrow in cell D3 and then select Number Filters on the dialog box that appears and then Greater Than Or Equal To… When the dialog box shown in Figure 8 appears, fill in the fields as shown as press the OK button.
Figure 8 – Filter dialog box
The result is shown in Figure 9.
Figure 9 – Filtered data
Statistics for Beginners – Introduction to Excel Sorting and Filtering
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.