Maximizing Pivot Table With Excel

Pre-requisites

Must possess the following basic Microsoft Windows knowledge with the following prerequisites:

• 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 (addition, 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
• Extract data using VLOOKUP Function
• Visualize 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 DATEDIF 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 with 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 Pivot Table
• Refreshing a Pivot Table
• Grouping Pivot Table data
• Show Values As functionality of a Pivot Table
• 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