Microsoft Excel Data Analysis (Part 1): Sort Data

Data Analysis, as defined by Wikipedia, is a process of inspecting, cleaning, transforming, and modeling data with the goal of discovering useful information, suggesting conclusions, and supporting decision-making.

Normally, data is raw, messy, apparently random and useless until it is organized. Analysis of data is usually done to process, organize, structure and present data in a given context for the purpose of obtaining useful information.

In these tutorial series, we shall be considering various Excel features for analyzing data, such as:

Sort 

Filter 

Conditional Formatting

Tables

Pivot Tables

Charts

What-if-Analysis

Solver

Analysis ToolPak

Sort:

In Excel you can sort your data in ascending or descending order, by one column or multiple columns, and even by color. 

Sorting is a quick way to reorganize your data. For instance, you can organize a list of sales information by total sales and quickly view low-sales or high-sales areas.

Sort on One Column

Follow the steps below:

1. Click any cell in the column you want to sort. Here, we want to sort by Sales Column.

2. To sort values from smallest to largest (ascending order), go to Data tab, in the Sort & Filter group, click AZ

Result:

Note: to sort the other way round (descending order), click ZA.

Sort on Multiple Columns

Follow these steps:

1. On the Data tab, in the Sort & Filter group, click Sort.

2. In the Sort dialog box, select City from the 'Sort by' drop-down list.

3. Click Add Level.

4. Select Sales from the 'Then by' drop-down list.

5. Click OK.

Result. Records are sorted by City first and Sales second.

Do you observe that sorting by one or multiple columns, you can easily and quickly see the city and/or sales person with largest or smallest sales. 

Sort data by color

If data or data cells are shaded in colors, we can sort the records by their respective cell or font colors.

Take below table for example:

1. Click any single cell inside the data set.

2. On the Data tab, click Sort.

3. In the Sort dialog box, select Sort by City (or any other column), sort on Cell Color (you can also sort on Font Color and Cell Icon), and select the yellow color (or any color you like) for the first level.

4. Click 'Copy Level' as many times as the number of colors and select colors you want each time.

5. Click OK.

Result: The data are sorted by cell colors

Watch out for next lesson. Follow me or subscribe to keep updated.