Microsoft Excel 2013 Intermediate

Course code: MSEXC2013ITM
Duration: 1 day
 
Overview
This course expands the users’ knowledge of Microsoft Excel and covers methods to work more efficiently.  It is particularly suited to anyone who has attended an Introductory or Beyond Basic course or used Excel basics for a reasonable length of time. Throughout the course there will be a focus on the practical application of the topics within a business context.
At the end of the course the delegates will be confident with formulae and basic functions, able to manipulate and chart data, and work with multiple sheet workbooks.
 
Pre-requisites
A good knowledge of the basic functions within Excel is essential.   Delegates must be able to create a basic worksheet, move and copy data and should be able to create simple formulae such as =A3*B3,  =sum(A2:A13)
 
Delegates will learn how to:
Use shortcuts to work more efficiently
Create formulae to calculate worksheet data
Use functions including IF and VLOOKUP
Manipulate data using sort options, filters, subtotals and simple pivot tables
Work with charts
Change printing options
 
COURSE CONTENT
 
Essential shortcuts
Customising the Quick Access Toolbar
Navigation and selection shortcuts
Inputting shortcuts
AutoFill and custom lists
Moving and copying shortcuts
 
Formulae
Refresher on formulae
Troubleshooting formulae - circular references, relative and absolute references
Refresher for using dates in formulae
Refresher on linking cells
 
Using Excel functions
Refresher on key functions; SUM, AVERAGE, MAX, MIN, COUNT
Refresher on basic IF functions using text and formulae in the results
Writing a nested IF function
Multiple condition IF functions
Lookup tables with VLOOKUP and HLOOKUP
Database functions
 
Working with data
Review of data entry techniques
Creating and using range names
Sorting lists
Filtering and advanced filter
Grouping worksheets to enter data through multiple worksheets
Analysing using conditional formatting
Outlining to handle large worksheets
Creating subtotals and nested subtotals
Creating a simple PivotTable to analyse data
 
Charts
2D and 3D charts
Adding and removing data
Formatting
Creating a custom chart
Setting chart defaults
Insert Sparklines
 
Printing
Refresher on printing and previewing options
Troubleshooting page setup options