Microsoft Excel Level 2

Microsoft Excel Level 2 – Course Outline

​

About This Course

This one-day course is designed to build on the skills gained from attending Excel Level 1.

 

Delegates will learn to control their worksheets through navigation techniques, calculate using more-advanced logical functions and increase presentation impact using charts, graphics and data tables. 

 

Target Audience

Delegates should have previously attended the Level 1 course or have relevant experience of the software.

 

At Course Completion

After completing this course, students will be able to:

 

• Work with Tables

• Use named ranges in formulae

• Work with Comments

• Using logical, text, date and data analysis functions

• Using graphs to analyse data

• Working with graphics

• Sort and filter data

• Use statistical functions to filter data tables

• Create outlines and subtotals

• Create 3D referencing between worksheets and workbooks

 

Course Content

 

Unit 1 – Working with Excel Tables

• Using Excel Tables

• Formatting a Table

• Add a Totals row

• Create a calculated column

• Using Table Splitters

 

Unit 2 – Using Named Ranges

• Creating named ranges

• Using range names within formulae

• Using named ranges with the GoTo command.

 

Unit 3 – Adding Comments

• Creating, editing and deleting cell comments

• Displaying comments permanently on screen

• Printing and formatting cell comment

 

Unit 4 – Building Formulae and Using Functions

• Create formulae containing the function, SUMIF(S), IF, COUNTIF(S)

• Create formulae using nested IF statements

• Create formulae using the AND and OR functions

• Using the VLOOKUP, HLOOKUP, XLOOKUP, and INDEX and MATCH functions

 

Unit 5 – Using Text and Date Functions

• Use the CONCAT and CONCATENATE functions

• Use the text to columns feature to separate text into columns

• Working with time and date functions

​

Unit 6 – Working with Graphs

• Create a Bar chart from data within your spreadsheet

• Create a Pie chart from data within your spreadsheet

• Change the type of chart within your workbook

• Change the look and colours within the chart; apply a gradient to the shading

• Add a Trendline

• Using Sparklines

 

Unit 7 – Working with Graphics

• Add a picture into a worksheet

• Using SmartArt

• Grouping objects

• Object Ordering

 

Unit 8 – Sorting and Filtering

• Sorting by icon and by menu

• Sorting using custom lists

• Auto Filtering

• Advanced Filtering

• Using Database functions

 

Unit 9 – Outlining and Subtotals

• Using AutoOutline

• Applying and removing automatic subtotals

 

Unit 10 – Linking and Grouping

• Group Worksheets

• Create formula links to cells on different worksheets

• Create formula links to cells on different workbooks

 

 

Course duration: one day