Dreamweaver course in north London

Microsoft Excel: Intermediate

This hands-on training course has been designed to give delegates a solid understanding of more advanced Excel tools and concepts in order to increase their knowledge and productivity.

Users with a basic understanding of Microsoft Excel who want to consolidate and expand on their knowledge of the basic features in a short space of time.

Delegates on this course should have a good working knowledge of the topics covered in the Introduction course.

Course outline

Working with Multiple Worksheets and Workbooks

  • Using multiple worksheets
  • Navigating between sheets
  • Moving and copying sheets
  • Linking worksheets
  • Creating 3-D formulas
  • Consolidating data


Working with dates

  • Entering date function
  • TODAY function
  • NOW function
  • Date and time formats
  • Using dates in formulas


Conditional Formulas and Formatting

  • The IF function
  • Conditional formatting
  • Creating named ranges
  • Using named ranges in formulas
  • COUNTIF(s), SUMIF(s), AVERAGEIF(s)


List Management

  • Working with lists
  • Creating dynamic ranges
  • Sorting and filtering lists
  • Sorting by multiple columns
  • Using AutoFilter
  • Tables


Documenting and Auditing

  • Best practice spreadsheet design
  • Viewing, adding, editing and deleting comments
  • Auditing features
  • Tracing formula errors
  • Protecting a worksheet
  • Protecting cell ranges


Creating Charts

  • Inserting Charts
  • Using the Chart Recommendation Feature
  • Editing Charts
  • Using Chart Tools
  • Using the Quick Analysis Tool
  • Add and Format Objects
  • Create a Custom Chart Template Creating and modifying charts
  • Adding trendlines
  • Visualising multiple data sets
  • Using Sparklines
  • Show and hide data labels.
  • Show and hide the legend.
  • Show and hide the chart title.
  • Add a picture or shape to a chart.
  • Change the way text displays in a chart.
  • Change the fill color of a chart.
  • Add and format objects in a worksheet.
  • Create a custom chart template.
 
 

Advanced Formulas

  • Using Named Ranges in Formulas
  • Using Formulas That Span Multiple Worksheets
  • Entering a Formula Using Data in Multiple Worksheets
  • Using the IF Function
  • Using the PMT Function
  • Using the PROPER, UPPER, and LOWER Functions
  • Using the PROPER Function
  • Using the LEFT, RIGHT, and MID Functions
  • Using the LEFT and RIGHT Functions
  • Using Date Functions
  • Using the YEAR, MONTH, and DAY Functions
  • Using Named Ranges in Formulas
  • Entering a Formula Using Data in Multiple Worksheets
  • Name and label cells and ranges of cells.
  • Use names and labels in formulas.
  • Create formulas that span multiple worksheets.
  • Use the conditional IF function and its variants in formulas.
  • Use the PMT function to calculate payments for loans.
  • Use the LEFT, RIGHT, and MID functions to return characters from the start or end of a string, or a specific number of text characters.
  • Use various date functions.


Working with Lists

  • Converting a List to a Table
  • Removing Duplicates from a List
  • Sorting Data in a List
  • Filtering Data in a List
  • Adding Subtotals to a List
  • Group and ungroup data.


Working with Illustrations

  • Working with Clip Art
  • Using Shapes
  • Adding Shapes
  • Working with Icons
  • Working with SmartArt
  • Using Office Ink
  • Add pictures to your worksheets.


Working with Tables

  • Format Data as a Table
  • Move between Tables and Ranges
  • Modify Tables
  • Define Titles
  • Creating and Modifying a Table in Excel


Advanced Formatting

  • Applying Conditional Formatting
  • Using Conditional Formatting
  • Working with Styles
  • Creating and Modifying Templates
 
 
 

We recommend the following courses that relate to this class: Microsoft Excel Advanced and Microsoft Excel Introduction

Class Type Live Tutor-led Virtual Classroom
Course Duration 3 Days (10 hours in total)
Course Days and Times Monday 9am to 1pm,  Tuesday 9am to 1pm, & Wednesday 9am to 11am
   
Private Course Price £500 for up to 4 Learners, then £100 per additional learner
Dates Will require 5 days notice to schedule training - Please call centre on 0208 443 2888 to arrange dates