Excel - advanced (In-House)

1 Nesting formulas

• Principles of nesting formulas together
• Using IF with AND or OR to answer questions
• Nesting an AND function in an IF
• Nesting an OR function in an IF

2 Advanced pivot table reports

• Grouping dates, numerical and text items
• Running percentage analyse
• Running analyses to compare data
• Inserting Field calculations
• Finishing off with a user-friendly dashboard

3 Advanced conditional formatting

• Colour table rows based on criteria in it
• Applying colour to approaching dates
• Exploring the different rule types

4 Lookup functions

• Going beyond the VLOOKUP function
• Lookups that retrieve data from left or right
• The versatile INDEX and MATCH functions
• Retrieving data from columns with duplicates

5 Calculate by criteria

• Using SUMIFS to sum by criteria
• Finding an average by criteria with AVERAGEIFS
• Use SUMPRODUCT to multiply then add different values

6 What-if analysis

• Use Goal Seek to meet targets
• Forecast reports with the Scenario Manager

7 Recording Macros

• Macro security
• Understanding a Relative References macro
• Recording, running and editing macros
• Saving files as Macro Enabled Workbooks
• Introduction to VBA code
• Making macros available across workbooks
• Add a macro button to the Quick Access toolbar