Excel Database Functions Sort

Sort Data in Excel

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel Chapter 5 of 24: 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.

Excel Database

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 (lesson1) 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:

Excel Sort Window

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 (Chapter 3A) to find some quick totals.

Next Chapter: Filters in Excel

Excel Table of Contents
or use the site quick links below