advanced excel – vlookup hlookup and pivot tables PDF

2017 Excel Lab Exercises Practice and Take home

Excel Practice Exercises Lab Session #1. Simple arithmetic. 3. Extending an Microsoft excel 2007 or later as follows: =SUMIFS(C2:C9 A2:A9

Microsoft Excel 2016 LEVEL 1

Exercise – “Welcome to Excel Read the instructions and complete the exercises to learn and practice more ...

Microsoft Excel Part 1: Creating Workbooks

Jan 9 2020 Microsoft Excel 2016: Part 4. View Options ... Let's use WeekOne worksheet in the same Excel Part4 Functions.xlsx workbook to practice.

ms-excel-exercises.pdf

MS Excel Exercises. Page 2. BIS202 Exercises. 2

Page. Exercise 1. Objectives: ▻ Introduction to MS Excel files Workbooks

Columns and Rows.

EXCEL PRACTICAL -1 Q. 1 The following worksheet contains Roll

EXCEL PRACTICAL -1. Q. 1 The following worksheet contains Roll.Nos. & Marks in 5 Advanced filter window is displayed. 6. Under – Action select Copy to ...

Essential Spreadsheets Exercises (pdf)

This exercise can only be done using Google Sheets or Excel for Microsoft 365 (the functions required are not available in Excel 2019 on University managed

Excel Competency Exam Practice Test The contents of this exam are

A worksheet ____ is a collection of two or more selected worksheets. a) index b) roster c) group d) cluster. 17. A reference that refers to the same cell or

Microsoft Excel 2016 LEVEL 2

Arguments are the data used in functions to make calculations. Practice formulas and functions in the training exercise file (workbook). To get the training

Excel-Fundamentals-Manual.pdf

necessary to print the worksheet and the data together. Page 58. Microsoft Excel Chapter 10 - Practice Exercise. PRACTICE EXERCISE SAMPLE. Creating Charts. A.

advanced excel – vlookup hlookup and pivot tables

Feb 25 2014 Drag the arrow to column D. 25. A dotted line appears around the selected data. 26. Excel places the name of the file

Microsoft Excel Part 1: Creating Workbooks

Intermediate Microsoft Excel: Practice 1

Microsoft Excel Practice Exercises. Page 1. Intermediate Microsoft. ®. Excel: Practice 1 Save the spreadsheet and name it: Excel Intermediate Practice 1 ...

Excel Advanced

When doing so a new sheet will automatically be added to your workbook. • It is good to use a new worksheet for the pivot table so that your source data doesn'

Microsoft Excel for Beginners

16-Jan-2018 Microsoft Excel is a spreadsheet program. We use it to ... There are a set number of cells within a Microsoft Excel worksheet.

2017 Excel Lab Exercises Practice and Take home

Excel Practice Exercises Lab Session #1. Simple arithmetic for multiple criterion if you have Microsoft excel 2007 or later ...

Microsoft Excel 2016 LEVEL 1

In this class you will learn the basics of Excel and work on a What is Microsoft Excel? ... complete the exercises to learn and practice.

Excel Practice Spreadsheet

Once you have launched Microsoft Excel you should see the program interface with a blank worksheet labeled “Workbook1”. Page 2. • Choose File > Open then

Word and Excel Practice Exercise

This exercise requires you to use the following: • Microsoft Excel for creating tables scatter plots

sample-excel-worksheet-practice.pdf

27-Jan-2020 PDF Learn EXCEL exercises with solutions Free PDF. Practice the ... Guide to using Microsoft Excel Excel Practice Spreadsheet Only Sample.

advanced excel – vlookup hlookup and pivot tables

25-Feb-2014 Drag the arrow to column D. 25. A dotted line appears around the selected data. 26. Excel places the name of the file worksheet

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

General Ledger ........................................................................... 3

VLookup

.................................................................................................. 3

HLookup

................................................................................................ 14

Pivot Table ............................................................................................. 26

Starting with a blank Pivot Table .................................................... 26

Pivot Table Field List ............................................................................................................28

Creating a Simple Pivot Table ........................................................ 32 Adding another field to the Rows ................................................... 35 Removing Subtotaling ..................................................................... 35 Not show subtotals .......................................................................... 36 Moving Fields ................................................................................. 37 Pivot Table Formats ........................................................................ 40 Expanding/Collapsing Fields .......................................................... 41 Adding a field to the Columns ........................................................ 44 Pivot Table Styles Options .............................................................. 46 Pivot Table Styles ........................................................................... 47 Adding a field to the Report Filter .................................................. 49 More Filtering for the Pivot Table .................................................. 53 Drilling to the Detail ....................................................................... 59 Non -Financial Data ................................................................... 60 VLookup (for a range) ........................................................................... 61

Pivot Table ............................................................................................. 66

Starting the Piv

otTable .................................................................... 66 Creating a Simple Pivot Table ........................................................ 67

Adding Another Field

..................................................................... 70 2

General Ledger

VLookup

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." 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.

First we will need to open our data files.

Click on the Office Button.

2. Select Computer, then under Network Location select Classroom Share or Hearth Room Share 3. Go to the desktop and locate the folder Data for Excel 2010 class. 4.

Open the GL Data Folder.

3 5.

Open the file Vlookup_Hlookup.xlsx.

a. Be sure you on are the VLOOKUP tab. 6.

Now open Activity Codes.xlsx.

The worksheet should look like this.

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

Go back to the Vlookup_Hlookup.xlsx file.

9. 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. 4 10. 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 VLooku

p 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. 5

Vlookup_Hlookup File Activity Codes File

i. The Activity Number in this query is text as indicated by the little diamond on the left top corner of the cell. ii. The Activity Code in the Activity Codes file is numeric. iii. In the VLOOKUP tab, place the cursor on the first activity code under

Activity Number.

iv. Notice the little square that appears to the left of the cell containing a diamond shape with an exclamation point inside. v. Highlight the rest of the column by either dragging the cursor down or clicking on the down arrow while pressing Ctrl/Shift. 6 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. 11. 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. 12.

Open the tab on the Ribbon.

13. Click on the Lookup & Reference Category in the Function Library. 7 14. A list of available functions will display. Select VLOOKUP. 15.

The Function Arguments Window opens.

16. 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. 8

GL Data Activity Codes

17. While you cursor is in the Lookup_value field, click on the first under the column heading Activity Number. (Note: the Activity Number should be in the same row). 18.

Click into the Table_array field. The table array is the table of information containing the data we want to retrieve into our worksheet.

19.

The definition shown now changes to Table_array.

20. With your cursor sitting in the Table_array field, switch to the Activity Codesquotesdbs_dbs3.pdfusesText_6

[PDF] advanced excel – vlookup hlookup and pivot tables

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

CONTENTS

VLookup

HLookup

Starting the Piv

Adding Another Field

General Ledger

VLookup

First we will need to open our data files.

Click on the Office Button.

Open the GL Data Folder.

Open the file Vlookup_Hlookup.xlsx.

Now open Activity Codes.xlsx.

The worksheet should look like this.

Go back to the Vlookup_Hlookup.xlsx file.

VLookup.

We will be doing the VLooku

Highlight the column.

Vlookup_Hlookup

The Activity Number

Vlookup_Hlookup File Activity Codes File

Activity Number.

Open the tab on the Ribbon.

The Function Arguments Window opens.

The Activity Number exists in

Note: the column headings do not have to

The cursor is placed in the first

Beginning of the formula is

Information is provided about the function

GL Data Activity Codes

The definition shown now changes to Table_array.