Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

advanced excel – vlookup, hlookup and pivot tables, Study notes of Zoology

University of Adelaide (UA)Zoology

ADVANCED EXCEL – VLOOKUP,. HLOOKUP AND PIVOT TABLES -. EXCEL 2010. Carnegie Mellon University. Author: Liz Cooke. Creation Date: March 16, ...

Typology: Study notes

2021/2022

Uploaded on 07/05/2022

paul.kc
paul.kc 🇦🇺

4.7

(68)

1K documents

1 / 72

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
ADVANCED EXCEL – VLOOKUP,
HLOOKUP AND PIVOT TABLES -
EXCEL 2010
Carnegie Mellon University
Author: Liz Cooke
Creation Date: March 16, 2010
Last Updated: February 25, 2014
Version: 4.0
1
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29
pf2a
pf2b
pf2c
pf2d
pf2e
pf2f
pf30
pf31
pf32
pf33
pf34
pf35
pf36
pf37
pf38
pf39
pf3a
pf3b
pf3c
pf3d
pf3e
pf3f
pf40
pf41
pf42
pf43
pf44
pf45
pf46
pf47
pf48

Partial preview of the text

Download advanced excel – vlookup, hlookup and pivot tables and more Study notes Zoology in PDF only on Docsity!

A DVANCED EXCEL – VLOOKUP,

HLOOKUP AND P IVOT T ABLES -

EXCEL 2010

Carnegie Mellon University

Author: Liz Cooke

Creation Date: March 16, 2010

Last Updated: February 25, 2014

Version: 4.

C ONTENTS

  • General Ledger
    • VLookup
    • HLookup
    • Pivot Table
      • Starting with a blank Pivot Table....................................................
        • Pivot Table Field List ............................................................................................................
      • Creating a Simple Pivot Table
      • Adding another field to the Rows
      • Removing Subtotaling.....................................................................
      • Not show subtotals
      • Moving Fields
      • Pivot Table Formats
      • Expanding/Collapsing Fields
      • Adding a field to the Columns
      • Pivot Table Styles Options..............................................................
      • Pivot Table Styles
      • Adding a field to the Report Filter
      • More Filtering for the Pivot Table
      • Drilling to the Detail
  • Non-Financial Data
    • VLookup (for a range)
    • Pivot Table
      • Starting the PivotTable....................................................................
      • Creating a Simple Pivot Table
      • Adding Another Field
  1. Open the file Vlookup_Hlookup.xlsx.

a. Be sure you on are the VLOOKUP tab.

  1. Now open Activity Codes.xlsx.
  2. The worksheet should look like this.

a. This file contains the actual Department Names associated with the generic Activity Codes from Oracle.

  1. Go back to the Vlookup_Hlookup.xlsx file.
  2. If you look at the column titled “Activity Name” you see the generic Oracle names. What we want to do is replace the generic names with the department assigned activity names.
  1. Because this worksheet contains query results extracted from the Data Warehouse, there are two formatting issues that must be resolved before doing a VLookup.

a. Be sure you are on the VLOOKUP tab in the Vlookup_Hlookup.xlsx file. We will be doing the VLookup in the column titled

. The formatting of this column must be changed to General. b. Highlight the column.

c. On the Home tab, in the Number group, click on the down arrow in the field that shows “General”.

d. Select “General” from the list of formats. General only shows in the panel because it is the first selection from the list.

b. The Activity number is the link between this query in the Vlookup_Hlookup file and the Activity Codes file. The Activity Number in both files must have the same formatting.

vi. Use the scroll bar on the right to move back up to the top of the column. Click on the little square with the exclamation point

to the left of the first cell.

vii. Select the option from the list.

viii. The Activity Number is now numeric and the text indicators are gone.

  1. To begin the VLookup, place the cursor in the first cell under the column heading Activity Name. The cursor is placed here because we are going to replace the generic Activity Name with a specific department assigned name.
  2. Open the tab on the Ribbon.
  3. Click on the Lookup & Reference Category in the Function Library.
  1. A list of available functions will display. Select VLOOKUP.
  2. The Function Arguments Window opens.
  3. The Lookup_value is the value that ties our data file to the Activity Codes file. The Lookup_value is the Activity Number because we want to retrieve the activity description for each Activity Number. The Activity Number exists in both the data file and the Activity file. Note: the column headings do not have to match.

The cursor is placed in the first argument.

Beginning of the formula is displayed in the selected cell.

Information is provided about the function and the particular function argument.

  1. The Function Arguments window remains.
  2. The column with the Activity Code Number must be the first column in the array. The Activity Code is in column B in this worksheet.
  3. Click on the column designator (B). The cursor becomes a black down arrow.
  4. The department names for the activity codes are in column D. Drag the arrow to column D.
  5. A dotted line appears around the selected data.
  6. Excel places the name of the file, worksheet, and the columns selected into the

Table_array field. The symbol next to the field indicates a list of values.

  1. Count the number of columns from the column with the activity code numbers to the data you desire. Activity code is Column 1 in our array and Department Name is Column 3.
  2. Click into the Col_index_num field. Excel returns to the Vlookup worksheet.
  3. Enter a 3 in the Col_index_num field. At this point you will know if your VLookup will be successful.
  4. Excel will preview the result for you.
  5. Click into the Range_lookup field. The choices of entry are True (1), False (0) or omitted.  True (1) or Omitted – if lookup value is not found in the table array, it uses the next largest value that is less than or equal to the lookup value.  False (0) – Looks for an exact match to the lookup value. If not found, the #N/A is returned.
  6. We want an exact match so enter the word false or the number 0 (zero).

b. The activity code was not added to the activity codes file.

  1. Switch to the Activity Codes file.
  2. As you can see from the Activity Codes file, activity code 206 is missing. Let’s add it. Since our VLookup searches for an exact match we can add the new activity code to the bottom of the list in the Activity Codes files.
  3. Add the following to the Activity Codes list c. Creation Date – Today’s date d. Activity Code – 206 e. Oracle Name - Program F f. Department Name - Lion Taming
  4. Go back to the VLOOKUP worksheet.
  5. The VLookup Function is a formula so it will automatically update when you make changes.
  6. Go ahead and close the Activity codes file. Don’t save.

HLookup

HLookup provides the same function as VLookup, that is, it allows you to merge data from one file into another file as we did in the VLookup, or from one worksheet to another as we will do in this example. With HLookup we will be doing a horizontal lookup (in a row) whereas with VLookup we did a vertical lookup (in a column).

We are still working with our Vlookup_Hlookup.xlsx file.

Open the tab.

This worksheet contains summary data by organization across Object Code rollups. If you are familiar with object codes you should recognize the values in the column headings are Parent values because they begin with a letter.

Now open the tab.

This tab contains part of a report and we’ve been asked to provide the amounts. This example is quite simplistic but hopefully you will understand HLookup when complete.

In this worksheet the Organizations are in the columns and the Object Code Parent values are in the rows. Calculations have been inserted for the totals. We are going to use HLookup to complete this worksheet.

  1. Be sure you are still on the tab. Place your cursor on the first cell under the column heading for Zoology.

Organization Numbers

Object Code Parent Values

  1. Look familiar? The Function Arguments is the same except the field Col_index_num is Row_index_num for HLookup. Look at the beginning of the formula displayed in the cell. It begins with HLookup.
  2. With the cursor in the Lookup_value field, click on the parent value A.

Note: The Lookup value should be in the same row as the calculation.

  1. The cell address has been placed in the Look_up field and to the right the actual value is displayed. Also notice that the cell address has been inserted into the formula.
  2. Click into the Table_array field.
  3. With the cursor still in the Table_array field, open the tab

.

  1. The Function Arguments window should still be visible. Excel places the name of the tab ‘Expenses by Category’ in the field.
  2. So with VLookup we highlight our Table_array by columns. In HLookup, we are going to do it by rows. Remember the look_up value must be in both worksheets/files and for HLookup, it must be the first row in the array. In this example, the Lookup_value happens to be in the first row of the worksheet.
  3. Click on the row 1 designator at the left.
  4. When you hover over the row one designator, the cursor becomes a very small black arrow and dotted lines appear around the first row.
  5. When you see the arrow, press on your mouse and drag it down to row 4.
  1. Type the number 2 into the Row_index_num.
  2. At this point, we can see that our HLookup will produce result as Excel display a preview of the Formula result.
  3. Click into the Range_lookup field. This field works exactly the same way as it does in VLookup. We want an exact match so enter a zero into the field.
  1. Click on the button.
    1. The amount is brought into the field and reflected in the total calculations.
  2. What happens if we copy this formula to the next field?
  3. So that doesn’t work. I guess we could create the formula in every cell that could be cumbersome and time consuming if we were working with a lot of data. Instead let’s go back and adjust our formula.
  4. Be sure you cursor in the cell that contains the HLookup formula.