Microsoft Excel Intermediate - In-company / Bespoke

Quick Recap Using references in Excel formulas

• Overview of formulas in Excel
• Relative, Absolute and Mixed cell references

Group editing worksheets

Mastering Excel Tables

• Introducing Excel Tables
• Creating Calculated Columns
• Formatting a Table
• Using Advanced Table Tools
• Using Slicers
• Converting Tables to Ranges
• Removing duplicates

Using names Ranges

• In Excel formulas
• As a way of navigating through the workbook

Advanced Formulas

• Simple IF examples
• Using IF to check if a cell is blank
• Nested IFs
• VLOOKUP
• HLOOKUP
• PMT financial Function
• Database Functions
• Text Functions
• Date Functions

Conditional formatting

• Apply Conditional Formatting
• Customising Conditional Formatting
• Using Icons in Conditional Formatting

Linking spreadsheets and workbooks

• Making a reference to another worksheet
• Making a reference to another workbook
• Editing links
• Troubleshooting links

Analysing databases

• Quick analysis
• Sorting a database
• Apply filters to a database
• Advance filter
• Outline data by levels
• Sorting and Filtering by Conditional Formats

Charts

• Analyse trends in data using Sparklines
• Creating charts from start to finish
• Available chart types
• Create a Chart Template
• Formatting Chart Elements and Styles
• Filtering Charts by Series or Categories
• Adding a Trendline to a Chart

Attaching security to a spreadsheet and workbook

• Protect a worksheet
• Protect a workbook

Introduction to Pivot Tables

• What are Pivot Tables?
• Using recommended pivot tables to analyse your data

Changing Excel’s environment Options