Excel Introduction
Duration: 1 day
Location: on-site (we come to you)
Discover the power of Microsoft Excel with our beginner-friendly introduction course. Designed for those new to Excel, this course will equip you with the essential skills to navigate, organise, and understand data efficiently. By the end of this course you will be able to create a basic spreadsheet including formatting and adding formulas.
This course provides a solid foundation for anyone interested in learning Excel.
Course Outline:
Navigate the Excel screen
Worksheet and Workbooks
- Understanding the difference between a workbook and a worksheet
- Creating, Saving, and Closing Workbooks
Using Excel
- Selection Techniques
- Entering information
- Editing information in cells
- Undoing and redoing an action
Manipulating rows and columns
Inserting rows into a worksheet
- Inserting columns into a worksheet
Copying, Moving and Deleting Information
Worksheets
- Inserting and deleting a new worksheet
- Copying or moving worksheets between workbooks
Freezing row and column titles
Using Excel Functions to perform basic calculations
- What is a function?
- SUM, AVERAGE, MAX, MIN and COUNT functions
Writing Formulas
- Create a Formulas
- Copying formulas
- Relative and Absolute cell referencing
Formatting
- Font formatting
- Alignment formatting
- Format Painter
- Number formatting
Checking spelling in a Worksheet
Printing
- Comparing workbooks side by side
- Previewing a Worksheet
- Setting Up the Page
- Setting the Page Options and Margins
- Adding Headers and Footers
- Inserting and Removing a Page Break
- Printing a Worksheet
Excel Intermediate
Duration: 1 day
Location: on-site (we come to you)
Take your Excel skills to the next level! This course goes beyond the basics to help you confidently manipulate and analyse data. Learn how to master popular functions like IF and LOOKUP, explore techniques to visualise your data effectively, and discover powerful tools to streamline your workflows. Whether you’re managing spreadsheets or creating reports, this course equips you with the skills to work smarter and more efficiently in Excel.
Course outline:
Review of Excel Formulas
- Using AutoCalculate
- Using AutoSum
- Using AutoFill
- Using Functions
- Entering Formulas
Naming a range of cells
- Rules for naming cells and ranges
- Naming cell range(s) in a worksheet
- Named ranges within formulas
- Navigating through workbooks using named ranges
- Creating named ranges automatically based on cell values
- Deleting named cells/ranges
Using IF function
Using Lookup function
Using Date and Time Functions
Managing Databases and Lists
- Sorting a Database
- Applying Custom Sorts
- Using Filters
- Working with Filtered Data
- Flash Fill
- Generating Subtotals Automatically
- Quick Analysis
Inserting Charts
- Inserting a chart
- Editing a chart
Linking and Embedding worksheet data
Formatting and Display Techniques
- Using the Format Painter
- Using Cell and Table Styles
- Using Conditional Formatting
Excel Advanced
Duration: 2 days
Location: on-site (we come to you)
Unlock Excel’s most powerful tools and features in this advanced course. Dive deep into embedded functions, data validation, and dynamic arrays to streamline complex tasks. Master extracting data with advanced Lookup functions, create insightful reports with PivotTables, and automate routine commands using non-VBA macros. This course is designed to transform how you manage and analyse data, making you an Excel expert.
Course outline
Working with Functions
- IF functions
- Combining the IF function with AND, and OR
- Nested IFs
- Combining the IF function with ISBLANK, and ISERROR
- Combining the IF function with ISTEXT, ISNUMBER, and ISBLANK
- Round function
- Joining text strings with CONCAT function
- Date and Time functions
- Using SUMIF and SUMIFS
Working with Tables and Filtering Data
- Sorting/Filtering
- Using Wildcards (? *)
- Filter for Unique Values / Remove duplicates
- Highlight unique or duplicate values using conditional formatting
- Format as Table, and apply Slicers
Data Types
Data Validation
- Create a drop down list
- Circle invalid data
- Input Messages/Error Alerts
- Dependant drop down lists
Dynamic Arrays
- Spilling, Filter, Sort, Sort by and Unique
Lookup and Reference
- VLOOKUP – exact match & closest match
- XLOOKUP – exact match, closest match, using wildcards & returning multiple columns
- MATCH & INDEX
Working with Pivot tables
- Creating and editing Pivot tables
- Updating information in a Pivot table
- Grouping Pivot table data
- Filtering Pivot table data using the Slicer
- Creating Pivot charts
Auditing Worksheets
- Trace errors in formulas
- Tracing precedents and dependents
- Displaying all formulas in a worksheet
- Evaluating formulas
Protecting Worksheets and Workbooks
- Understanding levels of security
- Protecting worksheets and workbooks
Working with Custom Views
Working with Macros (non-VBA)
- Recording, running, and assigning Macros
- Understanding relative and absolute referencing in Macros
- Assigning a macro to the Quick Access toolbar or a button