EIEIOTECHNOVATION
← Training programmes

EIEIO / APPLICATION TRAINING

Microsoft Excel 2016 Intermediate & Maximising Pivot Table 2016

Course outline

2 days
Discuss this programme →

Duration

2 days

Overview

This course provides students with the knowledge and skills to examine and manipulate raw data through the use of Microsoft Office Excel PivotTables and PivotCharts. This Intermediate Microsoft Excel training class is designed for students to gain the skills necessary to work with formulas, functions, worksheets and tables. This course provides students with the knowledge and skills to examine and manipulate raw data through the use of Microsoft Office Excel PivotTables and PivotCharts.

Audience

This course is intended for Information Workers, suitable for intermediate of Excel 2016 user and is recommended for users to already have the basic knowledge of Excel 2016 usage.

At Course Completion

After completing this course, you will be able to:

  • Understand the benefits of PivotTable Reports.

Convert raw data in an Excel worksheet into meaningful PivotTables and PivotCharts.

  • Manipulate the PivotTable layout.
  • Use grouping.
  • Create custom calculations.
  • Use formulas and create formulas outside of a report.
  • Create a PivotChart and publish it to a Web site.

Module 1: Working with Formulas

  • Concept of Formulas
  • Understanding Relative and Absolute Cell References
  • Understanding Named Ranges
  • Defining Named Ranges
  • Editing Named Ranges
  • Deleting Named Ranges
  • Using Named Ranges in Formulas
  • Using Array Formulas
  • Using 3D References

Module 2: Working with Functions (I)

  • Concept of Functions
  • Using Advanced Functions
  • Using Logical Function: IF
  • Using Logical Function: AND
  • Using Logical Function: OR
  • Using Logical Function: IFS, SWITCH

Module 3: Working with Functions (II)

  • Using Statistical functions: AVERAGEIF, COUNTIF
  • Using Lookup Functions: VLOOKUP ,HLOOKUP, LOOKUP
  • Using Text functions: TEXTJOIN, CONCAT
  • Using Statistical Functions: MAXIFS and MINIFS

Module 4: Auditing Worksheets

  • Tracing Formula Precedents and Dependents
  • Showing Formulas
  • Evaluating Formulas
  • Using Error Option Buttons
  • Running an Error Check

Module 5: Working with Table

  • Converting Data in range as a Table
  • Total the Data in Table
  • Removing Duplicates from Table
  • Using Sort and Filter

Module 6: Make Your Data Work for You

This module provides an overview of the benefits of PivotTable reports and how you can create one with ease.

Lessons

  • Maximize the Power of PivotTable
  • Ask the Questions
  • Get the Answers
  • Review

After completing this module, students will be able to:

  • Understand what needs to be done before creating a PivotTable.
  • Understand the ways you can use PivotTables to get the type of data you need.

Module 7: A Quick Tour

This module explains how to take raw data in an Excel worksheet and make it more meaningful with PivotTables.

Lessons

  • Determine What You Need to Know
  • The Source is Important
  • Ready, Set, Go
  • The PivotTable Layout
  • What Goes Where
  • Result of Our PivotTable
  • Viewing Our Data Differently
  • Review

Lab : Practice

After completing this module, students will be able to:

  • Prepare an Excel worksheet before using the PivotTable wizard.
  • Use the PivotTable and PivotChart wizard.
  • Understand the PivotTable layout, and which fields need to be dragged to which area of the layout.
  • Perform a simple PivotTable manipulation to change the way data is presented.

Module 8: Manipulating the PivotTable

This module explains how to rearrange the PivotTable layout, work with fields, and refresh data.

Lessons

  • Rearrange the Layout
  • What We Get
  • Add a New Field
  • Remove a Field
  • Add or Remove a Field Using the Wizard
  • Rename a Field
  • Format Numbers
  • Change the Sort Order and Top 10 AutoShow
  • Refresh Data
  • Review

Lab : Practice

After completing this module, students will be able to:

  • Manipulate fields of a PivotTable to view raw data in different ways.
  • Change the format of data in a PivotTable.
  • Use the Sort and Top 10 command.
  • Update a PivotTable report when the source data changes.

Module 9: Digging Deeper

This module explains how to use grouping to further summarize PivotTable information, how to work with subtotals and grand totals, and how to format the finished PivotTable.

Lessons

  • Create a Quarterly Group
  • Group Data Manually
  • Display Subtotals
  • Hide/Display Grand Total
  • Format a PivotTable Report
  • Review

Lab : Practice

After completing this module, students will be able to:

  • Group and ungroup data.
  • Group data manually.
  • Create a subtotal field.
  • Hide and display grand totals.
  • Format a finished PivotTable for readability.

Module 10: Beyond the Basics

This module explains how to create additional data fields and create and use custom calculations.

Lessons

  • Creating a Second Data Field
  • Use Count to Summarize Data
  • Display Numerical Data as a Percentage of the Total
  • Create a Custom Calculation
  • More Functions in Custom Calculation
  • Review

Lab : Practice

After completing this module, students will be able to:

  • Add data fields.
  • Use the Count function to get more detailed information about sum totals.
  • Display numerical data as a percentage of the total.
  • Create custom calculations.

Module 11: More PivotTable Calculations

This module explains how to create new fields and items using the Calculated Field and Calculated Item formulas, and how to create formulas outside of a report.

Lessons

  • Using Formulas
  • Create a Calculated Field
  • Create a Calculated Item
  • Use PivotTable Data Outside the Report
  • Use the GETPIVOTDATA Function
  • Turn Off the GETPIVOTDATA Function
  • Review

Lab : Practice

After completing this module, students will be able to:

  • Use formulas to create new fields and items.
  • Create and use calculated fields.
  • Create and use calculated items.
  • Use PivotTable data outside the report.
  • Use the GETPIVOTDATA function.

Module 12: Optional Topics

This module explains how to create a PivotChart report and publish it to a Web site, add fields to a PivotTable browser, use a PivotTable list, and use multiple ranges as source data.

Lessons

  • At Work
  • In Training

Lab : Practice

After completing this module, students will be able to:

  • Use the PivotTable and PivotChart wizard to create a PivotChart.
  • Enhance the look of a PivotChart by using the Vary Colors By Point option.
  • Share a PivotChart with colleagues by publishing it to a Web site.
  • Describe the difference between a PivotTable and a PivotTable List.
  • Use data from different worksheets and ranges.

Module 13: Answers to Review Questions Lessons

  • Review 1
  • Review 2
  • Review 3
  • Review 4
  • Review 5
  • Review 6
  • Review 7

Module 14: Using the Place Card Lessons

  • At Work
  • In Training

Programme scope, delivery arrangements and suitability are confirmed during enquiry.