A one-day workshop
This course focuses on pivot tables to comparing percentages, create field calculations, and build reports. It also looks at utilising powerful formulas to generate results from a range of criteria and retrieving data from an external file with lookup formulas.
Also included are what-if analyses tools to meet projected targets and looks at forecasting by generating varying scenarios.
This course finishes with a look at recording macros to quickly automate what would otherwise be repetitive manual tasks. Understanding how macros work and how to utilise them to add an extra dimension to spreadsheets.
- Nest formulas
- Get the most from pivot tables
- Use conditional formatting
- Write array formulas
- Explore the lookup functions
- Calculate by criteria
- Use ‘goal seek’ and ‘scenario manager’ for what-if analysis
- Record macros
Expert trainer
Alan became a professional IT trainer in 1995, when on completion of an NVQ accreditation in IT, the organisation providing the course, Apex North London, asked if he would like to join their team, which he happily did. Alan went on to become a City & Guilds Assessor with them.
Session outline
1. Nesting formulas
- Principals of nesting formulas together
- Combining the AND function, and OR function with an IF function to answer questions based on multiple criteria in your data
2. Data validation
- Using data validation
- Control data input
- Set up criteria for entry of text, number, and date data types
- Create a dropdown list
3. Lookup functions
- Retrieving data from external files using an INDEX and MATCH function
- VLOOKUP and XLOOKUP function
- Understand all the different parts that make up an XLOOKUP
- Exact match look up and closest match lookup
- Control a lookup’s search direction
4. Advanced pivot tables
- Generate subtotals by grouping date, number, and text values
- Run percentage analyse
- Run comparison analysis
- Insert a field calculation into a pivot table
- Setup a worksheet as a report dashboard
5. Advanced conditional formatting
- Using conditional formatting to alert us of variables in data
- Colour table rows based on criteria within it
- Apply colour to approaching dates
- Work with different condition rule types
6. Summing by criteria
- Use a SUMIFS function to sum numbers based on a range of different criteria
7. What-if analysis
- Use Goal Seek to reach financial targets
- Produce forecasted reports with the Scenario Manager
8. Recording macros
- Understand macro security
- What is the difference between a relative referenced macro and no relative?
- Record, run, and edit macros
- Save files as a Macro Enabled Workbook
- A brief look at VBA (Visual Basic for Applications) coding
- Adding buttons to Excel to run macros
