A one-day workshop
This course takes a balanced look at writing formulas, analysing data and outputting reports. Use Excel’s help tools to find and write formulas and learn how to use rules to automatically format data to flag it with colours.
You will also look at ways to easily compare different sets of data and create charts for a visual representation of data. Use lookup functions to retrieve data from external sources. Those attending will also learn time-saving tips and tricks.
- Calculate with absolute reference
- Group worksheets
- Link to tables
- Use the function library effectively
- Get to grips with the logical IF function
- Use conditional formatting
- Create pivot table reports
- Use data validation
- Master the VLOOKUP function
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. Calculations with absolute references
- The difference between a relative and an absolute formula
- Changing a relative formula to an absolute formula by using $ signs to lock onto cells when copying a formula
2. Grouping worksheets
- Group sheets together to enable the input of data into multiple worksheets
- Write a 3D formula to sum tables across multiple worksheets
3. Linking tables
- Creating links across tables
- Using paste link to link a table to another Excel file
- Using edit links to manage linked tables
4. The function library
- The benefits of writing formulas in the function library
- Using the insert function to search for formulas
- Output statistics with COUNTA and COUNTBLANK
- Count criteria in a list with COUNTIFS
5. Logical IFS function
- Outputting results from tests
- Running multiple tests for multiple result outputs
6. Conditional formatting
- Use rules to apply colour to data to allow text and numbers to standout
- Managing conditional formatting rules
- Copy rules with the format painter
7. View tables side by side
- Compare two excel files together
- Compare two worksheets in the same file together
8. Pivot table reports
- Analysing excel data with a pivot table
- Managing a pivot table’s layout
- Output statistical reports
- Controlling value formatting
- Making a reports visual by adding charts
- Filter reports with slicers
9. Creating charts
- Compare different sets of data in a Combo chart
- Create a Waterfall chart to represent the cumulative effect of a series of positive and negative values
10. The VLOOKUP and XLOOKUP functions
- Retrieve data from external files by using the VLOOKUP function and XLOOKUP function
- Why use an XLOOKUP when a VLOOKUP will do the job?
- Understanding the function arguments
