Details

---

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)

Benefits

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