Statistics for Beginners – Introduction to Excel Sorting and Filtering

(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

Sort dataFigure 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.

Sort dialog box

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.

Sorted data

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.

Sort multi-levelFigure 4 – Multiple level sort

This time the data will be sorted as shown in Figure 5.

Sort two sort keysFigure 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.

Remove duplicates

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.

Data with filtersFigure 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.

Filter dialog boxFigure 8 – Filter dialog box

The result is shown in Figure 9.

Filtered data

Figure 9 – Filtered data

 

Statistics for Beginners – Introduction to Excel Charts

 

Statistics for Beginners – Introduction to Excel Sorting and Filtering

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

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

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!