Excel Advanced

Montclair State University

Advanced Excel Filtering

Pivot Table Report

Filtering is a quick and easy way to find and work with a subset of data in a list. A filtered list displays only the rows that meet the criteria you specify for a column. Microsoft Excel provides two commands for filtering lists: • AutoFilter, which includes filter by selection, for simple criteria • Advanced Filter, for more complex criteria Unlike sorting, filtering does not rearrange a list. Filtering temporarily hides rows you do not want displayed. When Excel filters rows, you can edit, format, chart, and print your list subset without rearranging or moving it. Auto Filter When you use the AutoFilter command, AutoFilter arrows appear to the right of the column labels in the filtered lis t. How to filter a list. Using the AutoFilter arrows Clicking an AutoFilter arrow displays a list of all unique, visible items in the column, including blanks (all spaces) and nonblanks. By selecting an item from a list for a specific column, you can instantly hide all rows that don't contain the selected value. Quickly filtering values If you are filtering a list of numbers, you can quickly view the largest values in the list by clicking the Top 10 item in the AutoFilter list. To resume viewing everything in the column, click All.

A PivotTable report is an interactive table that you can use to quickly summarize large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest.

How to Filter a List

Using Worksheets

Display a subset of rows in a list by using filters 1. 2. 3.

4. 5.

Click a cell in the list you want to filter. On the Data menu, point to Filter, and then click AutoFilter. To display only the rows that contain a specific value, click the arrow in the column that contains the data you want to display. Click the value. To apply an additional condition based on a value in another column, repeat steps 3 and 4 in the other column. To filter the list by two values in the same column, or to apply comparison operators other than Equals, click the arrow in the column, and then click Custom. For information about displaying rows by comparing values, click .

Creating a PivotTable report 1. 2. 3. 4.

5. 6. 7.

Open the workbook where you want to create the PivotTable report. If you are basing the report on a Microsoft Excel list or database, click a cell in the list or database. On the Data menu, click PivotTable and PivotChart Report. In step 1 of the PivotTable and PivotChart Wizard, follow the instructions, and click PivotTable under What kind of report do you want to create? Follow the instructions in step 2 of the wizard. In step 3 of the wizard, determine whether you need to click Layout. Do one of the following: If you clicked Layout in step 3, after you lay out the report in the wizard, click OK in the PivotTable and PivotChart Wizard – Layout dialog box, and then click Finish to create the report.

Use worksheets to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet. Sheet tabs The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. Inserting a new worksheet • To add a single worksheet, click Worksheet on the Insert menu. • To add multiple worksheets, hold down SHIFT, and then click the number of worksheet tabs you want to add in the open workbook. Then click Worksheet on the Insert menu.

Advanced Excel—2

Montclair State University

Macros

Editing a Macro

Automating tasks you perform frequently If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands.

Before you edit a macro, you should be familiar with the Visual Basic Editor. The Visual Basic Editor can be used to write and edit macros attached to Microsoft Excel workbooks. 1. On the Tools menu, point to Macro, and then click Macros. 2. In the Macro name box, enter the name of the macro. 3. Click Edit.

Recording a Macro

Running a Macro

1.

1. 2.

2.

3.

4.

5.

6. 7.

On the Tools menu, point to Macro, and then click Record New Macro. In the Macro name box, enter a name for the macro. The first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator. To run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open. In the Store macro in box, click the location where you want to store the macro. If you want a macro to be available whenever you use Excel, store the macro in the Personal Macro Workbook in the Excel Startup folder. To include a description of the macro, type the description in the Description box. Click OK. If you select cells while running a macro, the macro will select the same cells regardless of which cell is first selected because it records absolute cell refe rences. If you want a macro to select cells regardless of the position of the active cell when you run the macro, set the macro recorder to record relative cell references. On the Stop Recording toolbar, click Relative Reference . Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again. Carry out the actions you want to record. On the Stop Recording toolbar, click Stop Recording .

3. 4. 5.

Open the workbook that contains the macro. On the Tools menu, point to Macro, and then click Macros. Show Me In the Macro name box, enter the name of the macro you want to run. Click Run.

Absolute Referencing A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references. References to data in other programs are called remote references. Absolute references If you don't want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula mu ltiplies cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows: =A5*$C$1