Sort Data in Excel

NOTE: If Excel recognizes your set of data as a database you can sort the data

Before you get into functions and formulas, here is a powerful data analysis tool that you can use: sorting data.

In the small database above you can sort the data to discover the largest quantity, you can sort the data by date, by product or by buyer. Once the data is sorted you can use the calculator (lesson 1) to find some quick totals by product or  by client. You can do the same thing with a large database with thousands of records (lines). You can sort the data by date AND product to see what sells best on certain dates.

If you try to sort column "A" and the other columns don't follow or the title cells are part of the sorting, it is because Excel doesn't recognize your set of data as a database. Go back to the requirements page. Once this is done here is how you sort data.

To sort data by date select cell A2 (first record or data row) and go to the icons on the toolbar and click on the A/Z one. The data is sorted by date beginning with the earliest. Go back to the toolbar and click on the other icon and the records are sorted by date beginning with the  latest date. Notice that all the records with the same date are together one after the other.   What are the busiest days?

Select B2 and use the icons to sort the records alphabetically by client. All the records pertaining to one client are together. You can select those records and copy/paste them on another sheet.

Select C2 and use the icons to sort the records alphabetically by product.

You want to see the largest sale? Select D2 and click on the icon Z/A and the first record is the one with the largest amount. The A/Z icon will sort the records starting with the smallest amount.

Now let's say that you want to sort the records by client AND by date to see how frequently a client buys. Click anywhere in the database. Go to the menu " Data/Sort" and the following window appears:

From here you can sort the data using three different criteria. In the text boxes select your criteria and the order and then  click on "OK" . Once the data is sorted you can use the calculator to find some quick totals.

Learn more on Excel
See the list of small downloads

There are  five sections in this website

Section 1: About Databases: 4 lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 7 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.