add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Automatic filtering is a method that allows you to hide records that contain items that do not meet the specified filter criteria.
Excel offers two filtering options
© AutoFiltering of a list according to one or more criteria.
© Advanced filtering according to complex criteria.
Adding Icons to the Toolbar
Add icons from the Data category of the Customize dialog box to the toolbar to allow quick filtering and display of all data that was hidden.
AutoFilter icon
© Show All icon
Once you have added the icon to the toolbar, filtering lists is easy. In the sheet, select the item in the field according to which data will be filtered, and click the AutoFilter icon.
To undo the automatic filter and display hidden rows, click the Show All icon.
To add an icon to the toolbar, select one of the toolbars and right-click. From Customize, select the Commands tab. From the Data category, drag the AutoFilter and Show All icons to the toolbar. Click Close.
AutoFilter
Select a cell list of data. From the Data menu, select Filter, AutoFilter.
A drop-down filtering list is added to the name of every field in the table. To open the list, click the arrow on the right side of the cell. Clicking the arrow displays a unique list of items in the field. By selecting one of these items, you are actually setting the filter criterion.
After the item is selected and the list has been filtered, the color of the filter arrow in the active-filter field changes from black to blue.
Note
Filter on multiple fields – you can select more than one criterion for filtering. After finishing the first filter, filter again by selecting an item from another column.
The number of items available for filtering is limited. Excel cannot filter columns in which the number of items exceeds 999 (not the number of rows). To filter when there are more than 999 items, use Advanced Filter (see below).
Be careful with formulas that have a relative reference. The result of the filter will distort the results of the calculation. Only perform a filter if the formulas have Names or absolute references.
Printing data after AutoFilter
1. Select the data list, before or after performing AutoFilter, by using the shortcut Ctrl+*.
2. From the File menu, select Page Setup.
3. Set the Print Area.
The Print Area is the entire data list. After filtering, only the displayed data is printed.
Saving AutoFilter criteria by using Custom Views
To save AutoFilter definitions as repeated criteria, add the Custom Views icon to the toolbar. It is located in the View category of the Customize dialog box for toolbars.
Saving a custom view
1. Filter the database with the criteria you set.
2. In the icon itself, enter the name of the view you want to save.
3. Press Enter.
Note
Select and define the print area before saving the Custom View. See the explanation in the Custom Views section of Chapter 11, Printing. By using Custom Views to save filtering definitions, you can save complex definitions together with print definitions.
Deleting a custom view
From the View menu, select Custom Views. Select the view you want to delete, and click Delete.
Custom AutoFilter
Custom AutoFilter allows you to set complex criteria for AutoFilter.
Example: Selecting two customers with Custom AutoFilter.
1. Open the filter list in the Customer Name field.
2. Select Custom (third from the top of the item menu for the field).
3. In Show rows where: Customer Name, select equals, and on the right side, select the customer Cisco.
4. Select the Or option (as opposed to the And option).
5. In the second field for Show rows where: Customer Name, select equals, and on the right side, select the customer Amazon.
6. Click OK.
Filtering by wildcard text characters
For example, filter a customer list in which the first character is A. In the Custom AutoFilter dialog box, open the options from the list. Select begins with, and on the right side, type A*. Click OK.
Filtering by the date field
Excel does not sort data according to cell format, but according to cell value. When sorting by date, Excel sorts the date according to its number. For example, the serial number of the date September 9, 2001 is 37164. If the cell format is changed to mmmm, the result of the format is September. When sorting the data list, Excel ignores September and only relates to the number 37164.
With AutoFilter, as opposed to Sorting, Excel relates to the date format and allows you to filter data according to format.
Filtering according to date by changing the format
1. Turn off the AutoFilter. From the Data menu, select Filter, AutoFilter.
2. Copy the Date column.
3. Select two columns to the right of Date, right-click, and from the shortcut menu, select Insert Copied Cells (pasting by inserting copied cells allows you to insert two columns and paste the copied column into them).
4. In Cell D1, type the heading Month, and in Cell E1, type the heading Year.
5. Select the Month field. To select it quickly, select Cell D2, and press Ctrl+Shift+Down Arrow.
6. Press Ctrl+1 (Format Cells).
7. In the Number tab, select Custom.
8. In the Type box, enter the format, mmmm (full month format).
9. Click OK.
10. Select the Year field. To select it quickly, select Cell E2, and press Ctrl+Shift+Down Arrow.
11. Press Ctrl+1.
12. In the Number tab, select Custom.
13. In the Type box, enter the format, yyyy (year format).
14. Click OK.
15. Select one of the cells in the Year field, and click the AutoFilter icon.
The figure below illustrates the results.
Color rows according to criteria
You can use coloring to isolate data in lists and to differentiate between various types of data.
Color lists according to the criteria 1996 and 1997 (years)
1. Make sure the list is set to AutoFilter.
2. Filter the year 1996 according to the following criterion – from the drop-down list for the Year field, select 1996.
3. Select a cell in the list of data – press Ctrl+* (select the current region).
4. From the Formatting toolbar, select Fill Color, and then select any color.
5. Filter the year 1997 according to the following criterion – from the drop-down list for the Year field, select 1997.
6. Select a cell in the data list, and press Ctrl+* (select the current region).
7. From the Formatting toolbar, select Fill Color, and then select a different color from the one you selected before.
8. Turn off AutoFilter.
Caution
The color of the heading row in the list also changes. After coloring the data, select the heading row for the list and apply a different color.
Summing filtered data
Every change you make when selecting criteria for filtering causes the number of rows displayed in the sheet to change (assuming that the number of records in each filter is different). The SUM function sums all rows, including hidden rows. Use the SUBTOTAL function to sum only the data in displayed rows.
1. Click the Show All icon.
2. Select a cell in the Customer Name or Market field, and click the AutoFilter icon.
3. Press Ctrl+* (select the current region).
4. Click the AutoSum icon (sigma).
The SUBTOTAL function is automatically entered below the data column. The formula is =SUBTOTAL(9,F2:F42).
The digit 9 means the data displayed in the column is summed with the SUM function. To change the function of the calculation, change this digit.
You can use the formula list and the formulas’ corresponding numbers in the SUBTOTAL function, as displayed in the figure below. The list was copied from the Help dialog box of the SUBTOTAL function (in the SUBTOTALargument dialog box, click Help).
Example: in the formula =SUBTOTAL(1,F2:F42), the digit 1 represents the AVERAGE function and calculates the average of the totals in the range of cells displayed in the formula.
Num Function
Function
1
AVERAGE
2
COUNT
3
COUNTA
4
MAX
5
MIN
6
PRODUCT
7
STDEV
8
STDEVP
9
SUM
10
VAR
11
VARP
Advanced Filter
The Advanced Filter options include:
© Filtering according to multiple criteria.
© Filtering without the limit of 999 items in a field.
© Filtering unique lists.
Using advanced filter
1. Insert a few empty rows above the database.
2. Copy the heading row of the list, and paste it into Row 1 (see figure below).
3. In Row 2, under the name of the field, enter the filter criteria. See the figure below for an example. The filter criteria for the Customer Name field is AIG Ltd., and the filter criteria for the Quantity field is >100.
4. Define a Name in the data table. Select one of the cells in the table, press Ctrl+*, and then press Ctrl+F3. Enter the Name in the Names in workbook box, and click OK. For example, define the Name Data.
5. Define a Name for the criteria range. Select the range A1:I12 (heading row + criteria row). Define a Name as explained in the paragraph above. For example, define the Name CriteriaRange.
6. From the Data menu, select Filter, Advanced Filter...
7. Select the List Range box, press F3, and paste the name Data.
8. Select the Criteria Range box, press F3, and paste the name CriteriaRange.
9. Click OK.
Canceling advanced filter
Click the Show All icon, or from the Data menu, select Filter, Show All.
Note
© Do not use text that is the same as the criteria field.
© Be careful with formulas that have relative references.
© You can use names to create the filter criteria in another sheet in the workbook. In this case, it is best if you copy the results of the filter to another location. See below.
Copying the advanced filter results to another location
Excel lets you copy filter results to another location. This is excellent when you want to quickly copy the results of Advanced Filtering according to criteria.
In the Advanced Filter dialog box, select Copy to another location. In the Copy to box, select the reference in the worksheet into which the data will be copied.
Using the Database Functions to Sum Data According to Criteria
The Advanced Filter technique hides rows that do not meet the specified criteria.
You can use the SUBTOTAL function together with the Advanced Filter technique to sum data after it has been filtered. Change the summing function by changing the function digit in the SUBTOTAL function.
The formulas in the Database category in the Paste Function dialog box (click the icon, or press Shift+F3) sum data according to criteria. The syntax of the formulas in this category is as follows:
=DSUM (Data, FieldName, Criteria)
The first argument contains the data range, the second argument contains the name of the criteria field, and the third argument contains the criterion.
All the Database functions begin with the letter D (Data): DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DPRODUCT, and DSUM. The DGET function is different from the others because it returns isolated data (like the VLOOKUP function).
Disadvantage of using the Database functions
The Database functions require a large amount of memory. Using the Database functions frequently significantly reduces calculation speed.
Example: the DSUM function
Unique Records
A unique record is different from an ordinary record. Each item in a unique record appears only once.
Example: a unique record of the company’s customers
In order to prepare an aging report, you have transferred the list of invoices and receipts from the company’s accounting system to a sheet in a workbook. The names of customers are repeated several times in invoices and receipts. The customer list in an earlier report that you had prepared is not up-to-date. New customers have been added in the period between the two reports. You want to prepare an aging report with an updated customer list, in which the name of each customer appears only once.
The figure below illustrates a list of customer names that was copied from a tax receipts report.
Filtering a record into one unique record
1. Select Cell A1 (in the figure, Customer Name).
2. From the Data menu, select Filter, Advanced Filter.
3. Select Copy to another location.
4. In the Copy to box, select Cell C1.
5. Select the Unique records only box.
6. Click OK.
Result
A unique record of customers in column C.
Using the COUNTIF function to filter a record into a unique record
1. In Cell B1, enter the text Unique Record.
2. Select Cell B2, and enter the formula =IF(COUNTIF($A$2:A2,A2)>1,1,0).
3. Copy the formula from Cell B2 to Cell B40 (the customer list in Column A extends through Cell A40).
4. From the Data menu, select Filter.
5. Open the filtering drop-down list in Cell B1 by clicking the arrow, and select 0.
6. Notice the unique record in Column A.
Explanation
The COUNTIF function counts the number of cells within a range that meet the given criteria.
For example, the COUNTIF function returns the number of times a customer appears in a list. The IF function uses the results of the COUNTIF calculation. If the result of the calculation is greater than 1, the result of calculating the formula is 1. If it is not, the result is 0.
Because the range runs from an absolute cell ($A$2) to a relative cell (A2), the cell range checked by the COUNTIF functions changes when the formula is copied. With AutoFilter, you can filter the rows according to the criterion 0.
Coloring a unique record
1. Select Cell A2.
2. Select the customer list before filtering. Press Ctrl+Shift+Down Arrow.
3. From the Format menu, select Conditional Formatting.
4. In the Condition 1 box, select Formula.
5. Enter the formula =COUNTIF($A$2:A2,A2)=1 (be careful about absolute and relative references).
6. Click Format, and select the Pattern tab.
7. Select any color.
8. Click OK twice.
Explanation
The COUNTIF function returns 1 the first time a customer name appears. In conditional formatting, the formula is the first argument in the IF function, Logical_text. If the condition exists, you can format the cell as desired.