Excel Lesson 4 of 30: Sorting the Data in Excel

If Excel recognizes your set of data as a database
You can do this

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

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