Who Should Attend?

Perfect for those who are comfortable with the basics of Excel, our Excel Intermediate course is a fantastic two-day option for those looking to build extensive spreadsheets for workplace and day to day use. Designed and delivered by industry leading trainers, this course will teach you features such as VLOOKUP, pivot tables and how to work and manipulate large data.

Prerequisites

Terms & Conditions

Generally, our courses run from 09:30 until 16:30 unless otherwise stated. Some courses will be more than 1 day so please read the dates at the time of booking.

After booking via Eventbrite you will receive notification from them of your purchase. You will also receive joining instructions from the SquareOne team, confirming dates, times and location. We will also at that time check on any special needs or dietary requirements you may have.

In the unlikely event that this course is cancelled – then SquareOne will work with you to schedule new dates.

Overview of shortcuts

• Recap of Shortcuts and Tips
• Customising the Ribbon to include useful shortcuts
• Refresh on everyday Formulae
• Recap of Absolute Cell Referencing

Range Names

• Create Range Names
• Navigate using Range Names
• Print Range Name Areas
• Create Formulae using Range Names

Creating Spreadsheets

• Learn how to setup 3D spreadsheets which have identical design and data on each sheet
• Setup Print design for all sheets, including Headers and Footers
• Copy, Move, Rename, Colour Sheet Tabs
• Hide and Unhide Tabs
• Link data from one sheet to another with Formulae
• Group and Edit all sheets

Linking Data

• Copying worksheets
• Tips for Copying to New Spreadsheets
• Linking data from one file to another

Advanced Functions

• IF Statements (includes nested IFs)
• VLOOKUP and HLOOKUP
• COUNTIF

Conditional Formatting

• Apply Shading, Icon Sets and DataFills to cells based on criteria
• Format Painter

Data Validation

• Setting up Restriction on how Cells are Inputted as Dates, Numbers or Time
• Setting up Cell Entries as a List
• Range Names and Tables in Data Validation
• Limit the Characters of a Cell Entry
• Create Error Messages

Working with Large Data

• Split your Worksheet
• Freeze Panes
• Tips on Formatting
• Hiding and Unhiding Columns and Rows
• AutoFilter including tips and tricks
• Sort and Custom Sort
• Removing Duplicates

Date Functions

• Entering date Formulae
• Today Formula
• Customising Date Formats

Text Functions

• Use the Trim, Proper, Left and Right Functions
• Concatenate Text
• Text to Columns
• Paste Special Options
• Flash Fill

Tables

• Creating Tables
• Formatting Tables
• Totals in Tables

Pivot Tables

• Preparation of Data
• Create Pivot Tables
• Create Dynamic Pivot Tables
• Format Data
• Group Data in Pivot Tables
• Sorting inside Pivot Tables
• Conditional Formatting in Pivot Tables
• Creating Tabs from Data
• Refresh Data
• Report Layouts
• Report Subtotals
• Slicers
• Pivot Charts

Charting

• Creating Charts Using a Function Key
• Creating Charts Using the Insert Tab
• Changing the Chart Type
• Formatting Charts
• Adding Titles and Customising
• Sparkline’s