Course overview
Delegates will learn the Excel basics: It will allow you to be able to perform basic calculations with confidence using formulas, be able to navigate around Excel much quicker and learn the shortcuts. It will show you how to create and format professional spreadsheets, build simple formulas, calculate totals and lots more.
Topics covered
Fundamentals
- Starting Excel
- What’s new in Excel
- Understanding the Excel screen
- Giving commands and using command shortcuts
- Creating workbooks
- Opening a workbook
- Previewing and printing a worksheet
- Saving workbooks
- Closing a workbook
- Using help
- Exiting a workbook
Editing a worksheet
- Editing cell contents
- Copying and moving cells
- Controlling how cells are moved or copied
- Collecting items to move or copy
- Checking spelling
- Inserting cells, rows, and columns
- Deleting cells, rows, and columns
- Using undo and redo
- Finding and replacing content
- Adding comments to cells
Getting started with worksheets
- Navigating worksheets
- Entering labels and values
- Selecting a cell range and entering data in a cell range
- Overview of Formulas and cell references
- Entering formulas including Sum, Average & Count / Formula error messages
- Entering content automatically
- Freeze Panes
- Absolute Cell References
- Hide / unhide worksheets
Formatting a worksheet
- Formatting Text
- Formatting Values
- Basic sorting / Basic filtering
- Merge / unmerge cells
- Adjusting Row Height and Column Width
- Working with Cell Alignment
- Adding Cell Borders and Background Colours
- Copying Formatting
- Applying and Removing Cell Styles
- Using Document Themes
- Applying Conditional Formatting
Delivery methodology
(Face-to-face or virtual delivery available)
Small groups
Ideally 6-8 delegates to maximise engagement and interaction levels.
One-to-one
We also provide one-to-one 90 minute support workshops to provide targeted individual support (specific topics to be selected).
Tailored sessions
Talk to us about designing bespoke workshops for your team
Course overview
Once you know the basics of Excel this course aims to give you the knowledge to be able to use more advanced logical formulas including lookups to find, extract and compare data. You will be able to organise your data in tables, before learning how to evaluate it with advanced filtering techniques. You will also learn how to get started with Pivot Tables and present your data as charts.
Topics covered
Recap exercise on Absolute Cell Referencing
Working with tables
- Creating a table
- Adding and removing data
- Working with the total row
- Sorting a table
- Filtering a table
- Removing duplicate rows of data
- Formatting the table
- Using data validation
- Converting to a range
Managing workbooks
- Using workbook views
- Selecting and switching between worksheets
- Inserting and deleting worksheets
- Creating headers and footers
- Hiding rows, columns, worksheets, and windows
- Setting the print area
- Printing a selection, multiple worksheets, and workbooks
- Creating a template
- Protecting a workbook
- Working with data ranges
Working with pivot tables
- Creating a pivot table
- Specifying pivot table data
- Changing a pivot table’s calculation
- Working with pivot table layout
- Grouping pivot table items
- Updating a pivot table
- Creating a pivot chart
- Using slicers
Getting started with worksheets
- Choosing and selecting the source data
- Choosing the right chart
- Inserting a chart
- Editing, adding, and removing chart data
- Changing chart data
- Using sparklines
More functions & formulas
- Formulas with multiple operators
- Inserting and editing a function
- AutoCalculate and manual calculation
- Defining names
- Using and managing defined names
- Displaying and tracing formulas
- Understanding formula errors
- Using logical and lookup functions such as IF and VLOOKUP
Delivery methodology
(Face-to-face or virtual delivery available)
Small groups
Ideally 6-8 delegates to maximise engagement and interaction levels.
One-to-one
We also provide one-to-one 90 minute support workshops to provide targeted individual support (specific topics to be selected).
Tailored sessions
Talk to us about designing bespoke workshops for your team
Course overview
Our experts will show you how to transform disorganised data into useful information along with the skills to quickly make professional spreadsheets. You will learn how to automate tasks with macros, bring together multiple data sources, troubleshoot complex formulas and lots more.
Topics covered
Refresher on VLOOKUP’s and Tables
Text manipulation
- Understanding text formulas
- Format data using Text to Columns
- Annotate Text from lists
- Remove formatting within cells
Consolidation
- Bringing multiple data sources together Connecting data to external sources including PowerBI
Working with the web and external data
- Inserting a hyperlink
- Importing data from an access database or text file
- Importing data from the web and other sources
- Working with existing data connections
Customising Excel
- Customising the ribbon
- Customising the quick access toolbar
- Using and customising autocorrect
- Changing Excel’s default options
- Creating a custom autofill list
- Creating a custom number format
Analysing data
- Creating scenarios
- Creating a scenario report
- Working with data tables
- Using goal seek
- Using solver
- Using text to columns
- Grouping and outlining data
- Using subtotals
- Consolidating data by position or category
- Consolidating data using formulas
Working with macros
- Recording a macro
- Playing and deleting a macro
- Adding a macro to the quick access toolbar
- Editing a macro’s visual basic code
- Inserting copied code in a macro
- Declaring variables and adding remarks to vba code
- Prompting for user input
- Using the if…then…else statement
Working with data ranges
- Sorting by one column
- Sorting by colours or icons
- Sorting by multiple columns
- Sorting by a custom list
- Filtering data
- Creating a custom autofilter
- Using an advanced filter
Delivery methodology
(Face-to-face or virtual delivery available)
Small groups
Ideally 6-8 delegates to maximise engagement and interaction levels.
One-to-one
We also provide one-to-one 90 minute support workshops to provide targeted individual support (specific topics to be selected).
Tailored sessions
Talk to us about designing bespoke workshops for your team
