Intermediate Excel Course
In this course you will learn a combination of tools and functions that an Intermediate Excel user should be aware of.
This is more than just an Excel 2 course, as it covers the practical uses of Excel at an Intermediate level.
The focus of the Intermediate Excel Course is to understand all the practical tools and formulas that you need.
To see if this course is correct for you, do our free Excel skill level assessment on our sister site (opens in new tab). You should be at our Beginner to Intermediate level.
- Time Req – 16 hrs (completing all examples)
- Skill Level > Beginner
- FAQ
- All Online Courses
- Equivalent Live Course Options – 14-16 Oct 2024
- Ask a Question
The secret to success with Excel is application. Apply what you learn as you go along. To see a more detailed breakdown on what is covered in this Intermediate Excel course, view the ‘About’ section below.
Modules
Ultimate Excel Tool
Lessons
Tips and Tricks
Lessons
Filtering and Sorting
Lessons
The Specials- GOTO and Paste
Lessons
- GOTO Special- find comments, values, formulas and more
- Find errors, conditional formatting and data validation
- Copy Visible Cells Only
- Fill in all the blanks
- GoTo Special Examples
- Paste Special- Values, Formulas with/without formatting
- Paste Special- convert negatives to positives
- Switch rows to columns and columns to rows
- Paste without overwriting blank cells
- Re Use the Format Painter
Tools for text
Lessons
Pivot Tables
Lessons
- Required data format for Pivot tables
- Create a Pivot Table- Step by Step
- Inserting Values into a Pivot Table
- Enhancing the values e.g. percentage of another column Free Preview
- Show the details of a number in the Pivot Table
- Pivot Examples: SUM, COUNT, AVERAGE, Percentage of
- Laying out the Pivot Report in completely separate columns
- Working with the rows in a Pivot Table- dropdown
- Working with the rows in a Pivot Table- field settings
- Normalize Pivot Reports to use elsewhere as a copy paste
- Pivot Examples: rows in a pivot table
- Working with the Columns in a Pivot Table
- Pivot Examples: values, rows and columns
- Working with the Filter in a Pivot Table
- Automatically duplicate your Pivot Tables with different selections
- Slicers in a Pivot Table
- Control all Pivot Tables and Charts at once
- Timelines in a Pivot Table
- Grouping Dates into days, months, quarters and years in a Pivot Table
- Grouping Dates into weeks in a Pivot Table
- Grouping Text in a Pivot Table Free Preview
- Grouping Numbers in a Pivot Table
- Pivot Charts in Excel
- Problems with grouping in Pivot Tables
- Pivot Examples: All features learnt so far
- Pivot Examples: Business Intelligence 1
- Pivot Examples: Business Intelligence 2
Conditional Formatting
Lessons
- Change cell colour based on value
- Change color based on benchmark in another cell
- Highlight all above average cells
- Add comparative bars into cells
- Create heat maps in Excel
- Add risk robots, milestone, up and down icons to Excel cells
- Control the heat maps, icons and data bars in conditional formatting
- Conditional Format Examples
Function and Formula Basics
Lessons
Text Functions
Lessons
- Fitting text inside a cell Free Preview
- Quick entry of rows of data Free Preview
- Quick Drop Down List Free Preview
- Printing Tips Free Preview
- Get Excel to read out the text and numbers Free Preview
- Join text from many cells Free Preview
- Extract parts of the text from cells Free Preview
- Force Excel to see the value (instead of text)
- Change text to lower, UPPER or Proper case
- Remove unnecessary spaces
- Text Examples
Key Lookup Functions
Lessons
Date functions and issues
Lessons
- How Excel Handles Dates
- Safest way to capture a date in Excel
- Pull the day, month or year out of the date
- Create a VALID Excel date with the day, month and year
- Calculate the proper end of month
- Determine what day of the week a date is
- When is the next workday
- How many working days between dates
- What week number is a date
- Make Excel recognise a date
- Date Examples
Charting In Excel
Lessons
- Excel Charting introduction
- Create a chart
- Align chart to the grid lines
- Chart doesn’t look right? First thing to try
- Customise the chart title, area, legend and more
- Vertical Axis- force the scale, reverse the order, labels and more
- Horizontal Axis- dates vs text, reverse order, show all labels
- Series- overlapping, big and small series, gaps
- Chart Examples- Look and Feel
- Add more series to the chart- 3 ways
- Show big and small numbers on the same chart (and be able to see them)
- Show months, quarters AND years on the chart axis
- Chart Examples- Series and Axis
- Bar Charts
- Pie Charts
- Area Charts
- Radar Charts (to compare projects?)
- Chart Examples- Line, column, bar, pie, radar,area
- Show mini trend charts for large reports
Avoiding Errors
Lessons
- What can go wrong with a copy/ paste Free Preview
- The risks with inserting columns and rows (and how to spot them) Free Preview
- Don’t insert or delete a cell (or at least be very careful) Free Preview
- What can go wrong with hidden rows and columns Free Preview
- Errors that arise from linking spreadsheets together Free Preview
- Use Excel’s inbuilt tools and functions
- Next Steps
