MS Excel 2003 Training 101 - All About AutoFilter

Create a filter

Click a cell in the data you want to filter. Then on the Data menu, point to Filter, and then click AutoFilter.

AutoFilter arrows will appear to the right of the column headings on all columns of the data you are filtering.

Note    If you select an entire column instead of a single cell before clicking the AutoFilter command, an AutoFilter arrow will appear only on the selected column, not on all columns of the data.

When you click an AutoFilter arrow, a list is displayed of the items in the column, in alphabetical or numeric order, up to a total of 1,000 items. In the list, click the item you wish to filter on. The rows containing that item will be displayed in the worksheet. All other rows will be temporarily hidden.

After you apply an AutoFilter to one column, you can filter another column, then another column, and so on.

Filtered data is not changed, rearranged, or moved. It's just hidden until you remove the filter.

For best results, worksheet data should be neat and tidy before you filter.

The top row of each column should have a heading that describes the contents of the column, such as "Product Name" or "Employee Name."
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.
Data should not be interrupted by blank rows or blank columns. Individual blank cells are okay.
Data 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.

Note    If a column contains blank cells, a Blank or NonBlank option is available in the AutoFilter list for that column. You can use Blank to see all blank cells in the column, and NonBlank to see all rows without blank cells.

See whether filters have been applied
Because AutoFilter temporarily hides data, you might wonder what has happened to the data if you don't know that a filter has been applied. Excel provides several ways to see that data has been filtered.

Immediately after data is filtered, the results of the filter are displayed in the lower-left corner of the status bar in the form "126 of 2155 records found." After a time, Filter Mode will appear on the status bar instead of the numbers.
If you do not see the status bar, on the View menu, click Status Bar.

Row numbers indicate that some rows are hidden. Also, the row numbers change color, typically to blue, to indicate that the rows are filtered.
The AutoFilter arrow in any filtered column also changes color to indicate that the column is filtered.

Note    If you do not see the results of the filter in the status bar, and the status bar is turned on, the worksheet may contain more than 51 formulas.

If you want to apply a filter when the worksheet contains more than 51 formulas, you can temporarily prevent formulas from calculating while you use AutoFilter. On the Tools menu, click Options. Then click Calculation, and click Manual.

Important    When you are through filtering, be sure to follow these steps again, but select Automatic instead of Manual. If you do not set it back, your formula results may contain errors.

Remove filters

How to remove filters depends on how many filters you have applied, and from how many columns you wish to remove filters.

To remove a filter from one column, click the AutoFilter arrow next to that column, and then click All. That command will display the rows hidden by that filter.
To remove all filters at once, point to Filter on the Data menu, and then click Show All. That command will display all rows hidden by all filters on the worksheet but leave AutoFilter turned on.
To turn off AutoFilter, point to Filter on the Data menu, and then click AutoFilter.
Remember, filtered data is not changed, rearranged, or moved, it's just hidden until you remove the filter.

Note    The order in which you remove single filters controls what data appears. If you don't see what you want when you remove filters in a particular order, you can use Show All to remove all of the filters, and then filter the data again.

Filter for the top or bottom items or percentages
You can use the Top 10 filter on columns with numbers or dates.

1. Click the AutoFilter arrow
in the column that contains the numbers or dates, and click (Top 10…). A dialog box will open.
2. In the box on the left, click Top or Bottom.
3. In the box in the middle, enter a number from 1 to 500.
4. In the box on the right, click Items or Percent.
5. Click OK.

Sort filtered data

Important    Unlike filtering, sorting rearranges data. After you sort data, you must undo the sort to put the data back where it was. If you save a worksheet before undoing the sort, you cannot then undo the sort.

To sort in ascending or descending order
1. Click the AutoFilter arrow of the column you want to sort.
2. Click Sort Ascending or Sort Descending.
3. To undo the sort, click the Undo button on the Standard toolbar, or on the Edit menu, click Undo Sort.

To sort by more than one criterion
1. Click a cell in the data you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort, starting with the most important.
4. Select any other sort options you want, and then click OK.
5. Undo the sort, by clicking the Undo button
on the Standard toolbar, or on the Edit menu, by clicking Undo Sort.

Doing calculations with autofiltered data

If you need to do calculations with autofiltered data, do not use the normal Excel functions, because they will use all the data, not just the rows that you can see. Instead, use the SUBTOTAL function.

MS Excel 2003 Training 101 - Freeze and Split, Filter and Sort

Freeze panes

Keep column names in sight as you scroll through worksheets. To freeze names, make a selection in the worksheet, and then click Freeze Panes on the Windows menu.

To freeze names, do not select the names themselves. To freeze:

Column names   Select the first row below the names.
Row names   Select the first column to the right of the names.
Both column and row names   Click the cell that is both just below the column names and just to the right of the row names.

Tip   You can freeze panes anywhere, not just below the first row or to the right of the first column. For example, if you wanted the information in the first three rows to stay in sight as you scroll, you would select the fourth row and then freeze panes.

Split panes

You split panes by making a selection in the worksheet, and then clicking Split on the Window menu.

You can split panes into:

Two panes above and below each other   Select the row below where you want the split to appear.
Two side-by-side panes   Select the column to the right of where you want the split to appear.
Four panes   Click the cell below and to the right of where you want the split to appear.
To remove the split, click Remove Split on the Window menu. Or double-click the split bar to remove the split.

Note that you cannot split a worksheet and freeze panes at the same time.

Name cells to return to

To name cells that you frequently return to:

1. Select a cell or range of cells.
2. Enter the name in the Name Box
to the left of the Formula Bar near the top of the worksheet.
3. When you need to return to the cell or range of cells, click the arrow to the right of the Name Box, and then click the name.

To change or delete a name:

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook list, click a name that you can change.
3. Type the new name in the box above the Names in workbook list, and then click Add. Select the old name, and then click Delete.
If you just want to delete a name, select the name in the list and then click Delete.

Find All

To use Find All to find all instances of the same thing entered in cells throughout a worksheet:

1. On the Edit menu, click Find. Type what you want to find in the Find what box.
2. Click Find All.
All instances of what you are looking for will appear in a list in the Find and Replace dialog box. Click a specific occurrence in the list and the insertion point goes right to the specific cell in the worksheet.

Tip   You can apply special formatting to cells to make all the instances you've selected stand out and easy to spot. Click the Options button in the Find and Replace dialog box. Click the Format button, click the Font tab, and then select formatting options.

Copy and move worksheets

To copy a worksheet, do one of the following:

Hold down CTRL while you drag the sheet along the row of sheet tabs. When you get to the location where you want to add the copied worksheet, release the mouse button and then the CTRL key. Or,
Right-click a worksheet tab, and then click Move or Copy on the shortcut menu. Click the sheet that you want to copy in the Before sheet list. Then select the Create a copy check box and click OK. Or,
On the Edit menu, click Move or Copy Sheet. Click the sheet that you want to copy in the Before sheet list. Then select the Create a copy check box and click OK.
To move a worksheet, do one of the following:

Drag the worksheet tab of the sheet that you want to move to its new position. Or,
Right-click the worksheet tab that you want to move, and then click Move or copy on the shortcut menu. Click the position that you want to move the sheet to in the Before sheet list, and then click OK. Or,
Click the worksheet tab of the sheet that you want to move. On the Edit menu, click Move or Copy Sheet. Click the position that you want to move the sheet to in the Before sheet list, and then click OK.

Create multiple worksheets with common data or formatting at the same time

1. Group the worksheets on which you want the common data or formatting. To group:
Two or more adjacent sheets   Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets   Click the tab for the first sheet, and then hold down CTRL and click the the tabs for the other sheets.
All sheets in a workbook   Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.
[Group] will appear in the title bar at the top of the worksheet.

2. On the first worksheet, enter all the common data or common formatting that you want.
3. Ungroup the worksheets by clicking any unselected worksheet tab. If no unselected worksheet tab is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.
[Group] will disappear from the title bar to indicate that the worksheets are no longer grouped. The information that you entered on the first worksheet will be on the subsequent worksheets.

Tip   If you've already created your worksheets and you want to duplicate existing data or formatting from one worksheet to multiple worksheets, group the worksheets that you want to duplicate the data from and onto, and then select the cells that contain the content you want to copy. Then on the Edit menu, point to Fill, and click Across Worksheets. This command is only available if you first group worksheets.

Filter and sort data

Data that is in rows and columns can be filtered and sorted. On the Data menu, point to Filter, and then click AutoFilter. An arrow appears at the top of the column in which you've selected data to filter. Click the arrow and select what you want to see. When you filter data, the other data is hidden from view.

You can also sort data differently by clicking the AutoFilter arrow and then clicking either Sort Ascending or Sort Descending in the list.

Print options

To set page breaks:

On the View menu, click Page Break Preview. You can adjust the page breaks by dragging the dotted page break lines.
To include column names on every printed page:

On the File menu, click Page Setup, and then click the Sheet tab. Under Print titles, in the Rows to repeat at top box, enter the row with the column names that you want to repeat at the top of each printed page.
To print just a portion of a worksheet:

On the View menu, click Page Break Preview. Select the area you want to print. On the File menu, point to Print Area, and then click Set Print Area. When you're ready to print, on the File menu, click Print. To clear the print area, on the File menu, point to Print Area, and then click Clear Print Area.
If you want to print a selected area of a worksheet without defining a print area, there's another method. Select the area you want to print. Then, on the File menu, click Print. Under Print what, click Selection. This selection is not saved when you save the file.
To print multiple worksheets at the same time:

Hold down CTRL and click each worksheet tab that you want to print. On the File menu, click Print. In the Print dialog box, under Print what, click Active sheets(s).