intermediate hand out

7/31/2019 Intermediate Hand Out

1/10

Notes from Excel Training: Building on the Basics

VLOOKUP in Excel 2003 The VLOOKUP function can help you find specific information in large data tables FORMULA: =VLOOKUP(lookup_value, table_array, col_index_number, [range lookup]

o lookup_value: the value you want to find in the first column of the table arrayo table_array: the table of data that VLOOKUP searches to find the information you

are after. The table_array must contain at least two columns of data. The firstcolumn contains the lookup_values.

o col_index_number: The number of the column in the table_array that containsthe data you want returned

o [range lookup]: A Boolean value (TRUE or FALSE only) that indicates whether youwant VLOOKUP to find an exact or an approximate match to the lookup_value.Typing FALSE will return exact matches only.

Filter in Excel 2003

AutoFilter is an easy way to turn the values in Excel columns into specific filters based onthe cell contents.

To turn on AutoFilter: Highlight the column titles, and go to Data > Filter > AutoFilter To turn off AutoFilter, deselect AutoFilter from the same submenu


2/10

Once AutoFilter is enabled, your columns display with a drop-down arrow to the right In the image below, clicking on the Cookie Prices arrow will show all the entries that are in

the Cookies Prices column. For example, choosing $3 will filter out anything that is not $3

To create a custom filter (for example, only displaying cookies that are more than $3.50),click on Custom and fill in your custom criteria

Pivot Tables in Excel 2003

Pivot tables can be used to summarize, analyze, and present your data. To make a pivot table:Step 1: Select the data and navigate to Data > PivotTable and PivotChart Report


3/10

Step 2: The range is the data that you want to create a pivot table with. Make sure the range iscorrect and click next

Step 3: Choose whether you want to put your pivot table report on a new or existing worksheet

Step 4: Once your pivot table is created, just drag items from the PivotTable Field List to thedifferent sections of the pivot table

To remove an item, drag it out of the pivot table. For example, to view the total price of eachcookie in your data, your pivot table may look something like this:


4/10

Charts

To create a chart:Step 1: Highlight the data you want to use and navigate to Insert > Chart

Step 2: Choose a chart type and make sure the data range is correctly selected

Step 3: Edit the chart options to your liking; for example, adding a title to the X or Y axis


5/10

Step 4: To create your chart in a new sheet, select As new sheet. To create a chart inyour current sheet, select As object in: [your sheet]

To create a pivot table chart: select your pivot table and navigate to Insert > Chart

Excel will create a chart that will allow you to filter the data to your liking, just like a pivottable. NOTE: If you change the filters in your chart, the filters in your pivot table will changeas well

CHART PIVOT TABLE

In the example above, if only Chocolate is selected, the chart and the pivot table will onlyshow the price of chocolate cookies


6/10

Differences between Excel 2003 and 2007

Filtering in Excel 2007 Select the data the same way you would in 2003. On the Excel Ribbon, click the Data tab,

and then click Filter. The dropdown arrows will appear

To remove AutoFilter, click the Filter button again To create a custom filter, navigate to the arrow, and go to Number Filters to choose a

custom filter

A window will pop up just like in 2003. Fill in your criteria and click finish


7/10

Pivot Tables in Excel 2007

Highlight the data you want and go to the Insert tab > Pivot Table

Make sure the range is correctly selected and click OK

Excel will create a pivot table that looks something like this:


8/10

However, to use the classic pivot table layout, right click > Pivot Table Options

Navigate to the display tab and click Classic PivotTable Layout

Excel will now display a pivot table in the same format as 2003


9/10

Charts in Excel 2007

To create a chart in 2007:Highlight your data, navigate to the Insert tab and click on the type of chart you want to create

Excel will automatically create your chart for you

To create a pivot table chart in Excel 2007:Highlight your pivot table and select the chart you want to create


10/10

Excel will automatically create your chart for you. NOTE: Like 2003, filtering items in your pivottable will filter items in your chart, and vice versa

intermediate hand out

Documents