Excel Intermediate

Course Information

Overview

This course is designed for learners who already have foundational knowledge and skills in Microsoft Excel and who wish to take advantage of some of the higher-level functionality in Excel to analyze and present data. Upon completion of this course, you will be able to leverage the power of data analysis and presentation to make better informed, intelligent organizational decisions.

Course Requirements

Learners should have used Excel before and be familiar with creating basic formulas, using Autofill along rows or columns and working with Absolute Cell References (e.g. $C$5) to refer to fixed figures – such as VAT rates or performance targets.

What You Will Learn

In particular you will be able to:

Create advanced formulas
Analyze data by using functions and conditional formatting
Organize and analyze datasets and tables
Visualize data by using basic charts
Analyze data by using PivotTables, slicers, and PivotCharts

1. Excel Setup & Printing Issues

Worksheet Margins
Worksheet Orientation
Worksheet Page Size
Headers and Footers
Header and Footer Fields
Scaling Your Worksheet to Fit a Page(S)
Visually Checking Your Calculations
Displaying Gridlines When Printing
Printing Titles on Every Page
Printing Row and Column Headings
Spell Checking
Previewing a Worksheet
Viewing Workbooks Side By Side
Zooming the View
Printing Options
Setting the Number of Copies to Print
Selecting a Printer
Selecting Individual Worksheets or the Entire Workbook
Selecting Which Pages to Print
Single or Double Sided Printing
Collation Options
Page Orientation
Paper Size
Margins
Scaling
Printing

2. Excel Functions and Formulas

Getting Help with Functions
Nested Functions
Consolidating Data Using a 3-D Reference Sum Function
Mixed References within Formulas

3. Excel Time & Date Functions

Inserting the Current Time and Date
Today Function
Now Function
Day Function
Month Function
Year Function

4. Excel Mathematical Functions

Round Function
Rounddown Function
Roundup Function

5. Excel Logical Functions

If Function
And Function
Or Function

6. Excel Mathematical Function

Sumif Function

7. Excel Statistical Functions

Count Function
Counta Function
Countif Function
Countblank Function
Rank Function

8. Excel Text Functions

Left Function
Right Function
Mid Function
Trim Function
Concatenate Function

9. Excel Financial Functions

Fv Function
Pv Function
Npv Function
Rate Function
Pmt Function

10. Excel Lookup Functions

Vlookup Function
Hlookup Function

11. Excel Database Functions

Dsum Function
Dmin Function
Dmax Function
Dcount Function
Daverage Function

12. Excel Named Ranges

Naming Cell Ranges
Removing a Named Range
Named Cell Ranges and Functions

13. Excel Cell Formatting

Applying Styles to a Range
Conditional Formatting
Custom Number Formats

14. Manipulating Worksheets within Excel

Copying or Moving Worksheets betweenWorkbooks
Splitting a Window
Hiding Rows
Hiding Columns
Hiding Worksheets
Un-Hiding Rows
Un-Hiding Columns
Un-Hiding Worksheets

15. Excel Templates

Using Templates
Creating Excel Templates
Editing Excel Templates

16. Paste Special Options within Excel

Using Paste Special to Add, Subtract, Multiply & Divide
Using Paste Special ‘Values’
Using Paste Special Transpose Option

Dates & Prices

Attend one of our public Excel courses:

Small Class Sizes

Maximum of 5 students per course.

Reference Manual

High quality reference manual supporting the topics covered.

Post Course Support

Unlimited post course email support on the course topics.

Delivery Options:

Virtual Online

Training Centres

Private Courses

We can arrange your own private Excel course.

Tailored

Have us build a custom private course tailored to your needs.

Cost Effective

If you are looking to training a group of people private courses can be very cost effective.

Post Course Support

Unlimited post course email support on the course topics.

Delivery Options:

At Your Offices

Virtual Online

Training Centres

What Our Clients Think

The trainer was a really good instructor, he took the time to help us with individual issues and also tried to cater the course to meet our collective needs.

Ben Knight - Atlas Copco Ltd

Really good trainer. He made the training interesting and fun.

Wendy Sipson - Hampshire Constabulary

The trainer was an amazing lecturer and the vba course was very usefull and well structured. Thank you.

Veronika Panton - Haven Power

I found the course very relevant to a lot of the day-to-day work I do.

Sian Stephenson - Aldi Stores Ltd

Very good introduction. Well paced and suited a range of experience levels.

Felicity Howdle - Health Education England

Very clear instruction, pace was kept moving, great snippets of advice given.

Robert Montgomery - Liberty Aluminium Technologies

Related Courses

Excel Power Query Introduction

Introduction 1 Day

Learn the basics of the M language and how to use Power Query to organise and query your data inside Excel.

View Course

Excel Introduction

Introduction 1 Day

This introductory Microsoft Excel course is ideal for beginners who want to learn how to produce spreadsheets, work with data and perform basic calculations.

View Course

Intermediate 1 Day

This course has been developed for people wanting to use Excel to perform calculations with a variety of common worksheet functions, filter, sort and summarise database lists, format and modify charts, and conditionally format cells.

View Course