Maximizing Pivot Table With Excel


Maximizing Pivot Table With Excel

Maximizing Pivot Table With Excel

Course Duration: 2 Days

Pre- Requisites

Must possess the following basic Microsoft Windows knowledge is essential with the following pre requisites:
  • Able to handle the mouse
  • Able to switch between task applications
  • Able to ''Create'', ''Save'', ''Open'', and ''Print'' spreadsheet
  • Able to use ''Undo'' or ''Redo'' function
  • Able to print a spreadsheet with headers and footers added
  • Able to identify Excel's cell addresses
  • Able to create simple formulas (additional, subtraction, multiplication, and division)
  • Able to create simple functions (Sum, Average, Count, Max & Min)


Upon completion of this programme, the participants will be able to:
  • Have a better comprehension in using COUNTIFS, SUMIFS functions.
  • Use IF Function to make decision.
  • Use Nested IF Function to make multiple decisions.
  • Calculate duration between dates using DATEDIF Function.
  • Extracting data using VLOOKUP Function.
  • Visualizing Data with PivotTable and PivotChart.
  • Perform What IF Analysis using Scenario Manager.

Key Contents

Unit 1: Evaluate Data Using Statistical and Math Functions

  • Using COUNTIFS Function
  • Using SUMIFS Function

Unit 2: How to Ensure Data Consistency?

  • Using TEXT Function
  • Using UPPER Function
  • Using LOWER Function
  • Using PROPER Function

Unit 3: Performing Date Calculation

  • Text to Columns Wizard to correct wrong date format
  • Using DATE Function
  • Using the EDATE Function
  • Using DATEIF Function

Unit 4: Making Decision with Logical Functions

  • Using IFERROR Function
  • Using IF Function
  • Using NESTED IF statements

Unit 5: Handling Text Data

  • Segregate Data Using the Text to Columns Wizard
  • Using TRIM Function
  • Using SUBSTITUTE Function
  • Using CONCATENATE Function

Unit 6: Searching Relevant Data ith LOOKUP Functions

  • Using VLOOKUP Function
  • How to Find an Exact Match with VLOOKUP
  • Finding an Approximate Match with VLOOKUP

Unit 7: Create Interactive Report with Pivot Table

  • What is Pivot table?
  • Start with Questions End with Structure
  • Common Pivot Table Practice
  • Creating Pivot Table
  • Formatting a PivotTable
  • Refreshing a PivotTable
  • Grouping PivotTable Data
  • Show Values As Functionality of a PivotTable
  • Creating PivotChart
  • Using Slicers to Manipulate PivotTables (Excel 2010 onward)
  • Using Timelines to Filter Dates (Excel 2013 onward)

Unit 8: Exploring Various Possibilities with Scenarios

  • What is Scenario?
  • Creating a Scenario
  • Saving Multiple Scenarios


 Inquiry - Maximizing Pivot Table With Excel