Microsoft Excel 2013 Advanced Level 1 - Power User

Course code: MSEXC2013PUADV1
Duration: 1 day
 
Overview
This course looks at complex calculations and advanced methods of data management.  These advanced features allow users to automate spreadsheets and manipulate large amounts of data. 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 able to manage large workbooks, use information from other workbooks or applications and create macros to automate repetitive tasks.
 
Pre-requisites
Delegates must have attended an intermediate level course or have gained similar knowledge in a business environment. It is essential that delegates can competently create formulae, use functions SUM, AVERAGE, IF and perform formatting and editing operations.
 
Delegates will learn how to:
•             Extend your knowledge on using Excel functions
•             Create a custom view
•             Import, export and consolidate data from other applications or workbooks
•             Protect your data
•             Create and work with PivotTables and PivotCharts
•             Record simple macros to automate repetitive tasks
 
COURSE CONTENT
 
Recap of Essentials
Creating and using range names
Conditional formatting
Linking cells and updating links
 
Working with Excel Functions
Refresher on functions
Using IF with other functions (AND, OR)
Useful functions: ROUND, INT, COUNTIFS, SUMIFS, IFERROR
Date functions: TODAY, YEAR, MONTH, DAY, NETWORKDAYS
Text functions: LEFT, RIGHT, MID, UPPER, CONCATENATE
 
Auditing a Worksheet
Finding, viewing and identifying formulae and functions
Tracking formulas
Watch and evaluate formulas
 
Views
Refresher on viewing your data
Creating custom views
 
Importing, Exporting and Consolidating Data
Importing data from other applications
Importing text
Separating data from one cell into different columns
Consolidating data
 
Protecting your Data
Protecting cells in a worksheet
Password protect a workbook
Controlling cell contents with data validation
Allowing users to edit a specified range
 
PivotTables and PivotCharts
Creating PivotTables
Changing the layout
Formatting
Refreshing the PivotTable data
Filtering using Slicers and Timelines
Creating a PivotChart
 
Macros
Recording and saving a simple macro
Using relative and absolute referencing in macros
Creating buttons on the worksheet
Adding macro buttons to the toolbar