Intermediate Excel Course
Or log in to access your purchased courses
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 this level.
The secret to success with Excel is application. Apply what you learn as you go along.
If you prefer to attend a live session, visit the MS Excel Advanced Courses Calendar
Modules
Downloads and Course introduction
Ultimate Excel Tool
Lessons
Tips and Tricks- Intermediate
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 Free Preview
- 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
- 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
- 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
- Quick entry of rows of data
- Quick Drop Down List
- Printing Tips
- Get Excel to read out the text and numbers
- Join text from many cells
- Extract parts of the text from cells
- 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
- The risks with inserting columns and rows (and how to spot them)
- Don’t insert or delete a cell (or at least be very careful)
- What can go wrong with hidden rows and columns
- Errors that arise from linking spreadsheets together
- Use Excel’s inbuilt tools and functions
