Level 2 (Intermediate) Excel Course

This course will upgrade your scrappy, self-taught Excel skills so you can design spreadsheets better, faster and smarter?

Intermediate Excel

Course Description

This 1 day intermediate Excel course is designed to improve your productivity within Excel.

You'll discover the most useful and most commonly used parts of Excel - the features that can be used by anybody in any type of business.

You'll master techniques to achieve quick results and produce slicker, more efficient workbooks that are better laid out, more consistent and easier to maintain.

Want To Customise Your Content?

Don't see exactly what you want in the topic list below?

You are welcome to swap topics in and out from the different courses available to create your own custom course.

We can accommodate all requests. Customisation is 100% free. 

Let us deliver a powerful course with useful content that you or your staff will actually use. Tailor the training around your own needs and requirements.

Click to tell us what you need.

Duration

Corporate group training: 1 day.
Private 1-on-1 training: 3-4 hours.

Prerequisites

This course is designed for people who have used Excel regularly for a minimum of 45 days. Please view the contents of the beginners Excel course and make sure that you are comfortable with 80% of the contents before considering this course.

Versions Taught

Our computers are always installed with the latest version of Office. The current version is Excel 365 (desktop).

If you have an earlier or Excel such as Excel 2016 or Excel 2013 we will explain any key differences during the training.

Still using Excel 2010 or earlier? It's time to say goodbye to an old friend and upgrade! Microsoft don't support this version any more.

Related Courses

Level 1 (Beginners) Excel course

Level 3 (Advanced) Excel course

Customise with additional Excel topics

what you get

Here's what you'll learn in this course

Working with Excel databases

  • Entering data using AutoComplete
  • Entering data using Pick From List
  • Creating Spark Lines
  • Flash Fill
  • Filtering data using the standard and advanced filters
  • Sorting rows by value, colour or icon.
  • Sorting data using custom lists
  • Multi-level sorting
  • Re-ordering table columns without the mess
  • Creating automatic subtotals

35 of Excel's Most Useful Formula Functions

  • Brief recap of the five basic functions - SUM, AVERAGE, MAX, MIN, COUNT
  • Using the Function Wizard
  • Smart ways to propagate formulas
  • COUNT functions - COUNT, COUNTA, COUNTBLANK
  • Invaluable COMBO functions - COUNTIF & COUNTIFS, SUMIF & SUMIFS, AVERAGEIF & AVERAGEIFS
  • MAXIF & MAXIFS, MINIF and MINIFS
  • Handy LOGICAL functions for tables - AND, OR, IF
  • Date and Time functions - TODAY, NOW, DAY, MONTH, YEAR, DATE, HOUR, MINUTE
  • ROUNDING functions - ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, ABS, TRUNC
  • PI (for engineers & mathematicians)

Advanced Formatting Techniques

  • Establishing a theme
  • Conditional formatting
  • Using data bars, colour scales and icon sets
  • Two ways to use the Format Painter tool
  • Using styles and style sets to improve consistency and maintainability

Absolute vs Relative Cell Addressing

  • Using AutoFill and Smart tags
  • Absolute cell addressing?
  • The importance of dollar signs
  • F4 - The magic key
  • Partially-absolute addressing - where the magic really happens

Naming Cells & Cell Ranges

  • Naming a cell using the Name box
  • Naming cells using the Name Manager
  • Creating a constant
  • Using named cells for navigation
  • Using named cells within a formula
  • Modifying and deleting named cells
  • Creating names from existing headings
  • Applying names to existing formulas
  • Displaying a list of named cells

Tools To Validate Data

  • Setting the rules
  • Giving instructions to the user
  • Alerting the user of an error
  • Conveying the severity of invalid data
  • Highlighting invalid data

Charts & Graphs

  • Creating a chart using the new Chart Ribbons
  • Modifying a chart
  • Changing the chart type
  • Changing the source of the data
  • Setting the chart options
  • Showing or hiding different chart components
  • Changing the chart location
  • Selecting and formatting a chart component
  • Applying a gradient, texture, pattern or picture fill to a chart component
  • Manipulating 3D charts
  • Using the Chart toolbar
  • Creating and saving custom chart types
  • Previewing and saving a chart as a web page

Grouping & Hiding Data The Sophisticated Way

  • Creating an outline manually or automatically
  • Collapsing and expanding an outline
  • Removing one or all groups
  • Expanding the range of an group
  • Using groups with charts