1. 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
2. Non-Financial Data
- VLookup (for a range)
- Pivot Table
- Starting the PivotTable
- Creating a Simple Pivot Table
- Adding Another Field
3. Advanced Excel – VLOOKUP, HLOOKUP AND PIVOT TABLES - EXCEL 2010
4. Contents
- General Ledger
- VLookup
- HLookup
- Pivot Table
- Non-Financial Data
- VLookup (for a range)
- Pivot Table
5. General Ledger
- VLookup
1. When you use a lookup function in Excel you are basically saying “Here’s a value. Go to another location and find the same value. Then show me specific information related to that value.”
2. You work for the Zoology. Zoology uses the generic activity codes in Oracle to analyze certain types of activities. You prepare some data for the department head and you would like to replace the generic Oracle activity names (e.g. Program C) with the department assigned names.
3. First we will need to open our data files.
4. Click on the Office Button.
5. Select Computer then under Network Location select Classroom Share or Hearth Room Share
6. Go to the desktop and locate the folder Data for Excel 2010 class.
7. Open the GL Data Folder.
8. Open the file Vlookup_Hlookup.xlsx.
- Be sure you on are the VLOOKUP tab.
9. Now open Activity Codes.xlsx.
10. The worksheet should look like this.
- This file contains the actual Department Names associated with the generic Activity Codes from Oracle.
11. Go back to the Vlookup_Hlookup.xlsx file.
12. 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.
... (Continued in next message)
