Excel 2010 Intermediate

Microsoft Excel training
Course Code: 
MSEXC2010ITM
Duration: 
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.
Prerequisites: 
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)
Course Content: 

 
 
Delegates will learn how to:
Use shortcuts to move work more efficiently
Create formulae to calculate worksheet data
Use functions including =IF, =VLOOKUP
Manipulate data using filters, subtotals and pivot tables
Work with charts
Change printing options
Create and work with templates
 
COURSE CONTENT
 
Essential shortcuts
Customising the Quick Access Toolbar
Navigation and selection shortcuts
Inputting shortcuts
Creating and using custom lists
Moving and copying shortcuts
Creating and using range names
 
Formulae
Refresher on formulae
Troubleshooting formulae - circular references, relative and absolute references
Refresher for using dates in formulae
Refresher on 3D formulae to calculate across multiple sheets
Using the Watch window
 
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
 
Working with data
Sorting lists
Grouping worksheets
Entering data through multiple worksheets
Using conditional formatting
Creating subtotals and nested subtotals
AutoFilter and advanced filter
Using a simple pivot tables to analyse data
Using lookup tables =VLOOKUP, =HLOOKUP
Using database functions
 
Templates
Creating and editing a basic template
Using a template
Using protection to restrict user entry
 
Charts
2D and 3D charts
Adding trendlines
Creating a custom chart
Setting chart defaults
 
Printing
Refresher on printing and previewing options
Troubleshooting page setup options