Summary: Analyzing data is easy with Excel's AutoFilter. Here is how to use it:
If AutoFiltering is not already turned on, display the Data tab of the Ribbon and click the Filter tool.
Use the drop-down arrow to the right of a column label to select Number Filters | Custom Filter or Text Filters | Custom Filter. (The names of the options, and thus the choices you make, depend on the composition of your data.) Excel displays the Custom AutoFilter dialog box.
Use the controls in the dialog box to set the criteria you want used for filtering your list.
Click on OK.
You can use the Custom AutoFilter dialog box to set any combination of criteria that you need. For example, you can tell Excel that you want to see any values below, within, or above any given thresholds. The filtering criteria will also work with text values. For instance, you can tell Excel to display only records that are greater than CE. This means that anything beginning with AA through CE will not be displayed in the filtered list.
Excel also allows you to use wildcard characters to filter text values. These are the same wildcards that you can use in other programs. The question mark matches any single character, and the asterisk matches any number of characters. If you wanted to only display records that have the letter C in the second character position, you would use the equal sign operator (=) and a value of ?C*. This means the first character can be anything, the second character must be a C, and the rest can be anything.
AutoFilter is a highly useful tool for when you are analyzing large sets of data.
Back to top
A Fast Way to Delete Blank Rows or Rows with Blank Cells
Back to top
Summary: There is a fast way to delete blank rows or rows with blank cells. Here is how to do this:
On the Home tab, click Find & Select, Go To Special
- Select Blanks
- Click OK.
- Excel selects the blank cells.
- On the Home tab, click Delete, Delete Sheet Rows
The blank rows and the rows with blank cells will now all be deleted.
Summary: There are times when you need to apply a background color to one or more cells in a worksheet. Here is how to do this:
The Fill Color tool (available on the Home tab of the ribbon) has a small bucket and color sample on it. This tool actually has two parts; if you click on the left part (the part with the small bucket and color sample), then the color shown in the sample is applied to the cells you have selected. Note that the color of the font doesn't change, only the color of the cell background.
If you click on the arrow at the right side of the tool, a color palette appears. To select a color, click your mouse on one of the small color squares. This is applied to the cells you have selected and appears in the color sample on the Fill Color tool.
There is another way to change the cell color:
- Select the cells whose color you want to change.
- Press Ctrl+Shift+F.
- Excel displays the Format Cells dialog box.
- Make sure the Fill tab is selected.
- Using the color palette in the dialog box, select the cell color.
- Click on OK.
Both methods get the same result. The choice of which one to use is a personal preference.
|Editing Headers and Footers
Summary: You can easily edit a Header or Footer in Excel by using the tools in the Header and Footer group in the Insert tab of the Ribbon. Here is how to do this:
Back to top After you have created a header or footer, the easiest and quickest way to edit it is to make sure you are in Print Layout view and double-click in the header or footer area.
To return to the document editing mode when you are done making changes to the header or footer, double-click anywhere in the normal document area, or click the Close Header and Footer button on the Design Tab of the Ribbon.
Note that this tab is visible only when you are working in the header or footer.