MorningStar Education presents:. Microsoft ® Office Excel ® 2003 Training. Sorting and Filtering Data. Contents. Overview: Working with data easily Lesson 1: Sorting data Lesson 2: Filtering data Lesson 3: Using Advanced Filters. Overview: working with data easily.
Excel is Microsoft’s premier spreadsheet application, part of Microsoft Office Suite, and the most popular spreadsheet in the world.
Excel 2003 is simple to use. Many features are automated, and anyone with a basic knowledge of spreadsheets can begin using Excel in minutes.
This course assumes you have a basic knowledge of Excel and are familiar with spreadsheet architecture.
and ending with
click on the header “Last” in column A,
then in the menu bar, “Data”,
The Sort dialogue box will open.
Make sure the “My data range has” header row radio button is selected, or Excel will sort your data WITH the header row.
Then just click “OK.” Your data is sorted alphabetically, in descending order, by last name from Smith to Buffet.
which inventory items cost less than a certain amount,
or which employees work the least amount of hours.
Excel includes several tools you can use to analyze your data so you can make better decisions.
Use headings The top row of each column should have a heading that describes the contents of the column, such as "Product Name" or "Employee Name."
Don\'t mix The data in each column should all be of one type only. Do not mix text in a column with numbers, or numbers in a column with dates.
Keep separate The data you want to filter should be on its own worksheet. If that\'s not possible, it should be separated from other data by a blank row or blank column.
To see only the five highest zip code numbers, click the drop down menu in the “ZIP” column, then “(Top 10…).”
The “Top 10” dialogue box will open. Change the number of items to “5,” then click OK.
Your data is now filtered by the five highest zip code numbers.
Your original worksheet reappears!
Using Advanced Filters
This Excel file, “customer spreadsheet,” contains data on products, customers, and sales for a restaurant food supply company.
I only want to see these figures for two customers: Savea and Quick.
Finally, I only want to see sales that were over $500 in each quarter.
Let’s set up an advanced filter ONLY for the three products I mentioned: Boston Crab Meat, Camembert Pierrot, and Uncle Bob’s Organic Dried Pears.
I’ll need a criteria range of at least four rows: one for the header and three for the criteria.
Click on the first cell of the spreadsheet, A1, and select the first four rows.
Then click on “InsertRows” in the menu bar. Four rows will be inserted into the criteria range.Let’s do this step by step.
In the next several rows, type in the names of the three products: Boston Crab Meat, Camembert Pierrot, and Uncle Bob’s Organic Dried Pears.
Now click “DataFilterAdvanced Filter” to set the list and criteria ranges. The Advanced Filter dialogue box will open.
List range: the area on the worksheet that contains the original data.
Criteria range: the area on the worksheet that contains the specific criteria that is being filtered.
The list range for this worksheet starts at the first cell of the header, A5, and ends at the last filled cell, F952.
The criteria range begins at A1 and ends at A4.
Do the same for the criteria range.
Your data is now filtered by the specific product names specified.
And/or operators work by specifying whether a specific criteria is to be filtered with another criteria (“and”) or whether it is to be filtered instead of another criteria (“or”)
If a record meets all the criteria in a row in a criteria area, it will pass through the filter.
Criteria on different rows are joined with an OR operator. In the second example, thecustomer must be MegaMart OR product must be Cookies OR total must be greater than 500.
By using multiple rows, you can combine the AND and OR operators. In the third example, thecustomer must be MegaMart AND product must be Cookies ORproduct must be Cookies AND total must be greater than 500.
Select the first 25 rows of the worksheet (one for the header and 24 for the criteria).
Click “InsertRows” in the menu bar. Your rows will appear above the worksheet header.
Select the worksheet header, and copy and paste it into the first row.
Each value for the quarter must be listed separately, as an “AND” operator.
It’s easiest to type the values in, then repeat them by selecting them. The values for the quarters can be typed in once, then copied.
The Advanced Filter Dialogue box will appear.
In the list range, type
In the criteria range, type:
Your spreadsheet is now filtered by product, customer, and sales by quarter!