Office 2013 Training Manuals – Microsoft Excel 2013 Advanced Courseware

Microsoft Excel 2013 Courseware

Course outline

Pivot tables

• Creating and using a pivot table
• Filtering and sorting data within a pivot table
• Automatically grouping data in a pivot table and renaming groups
• Manually grouping data in a pivot table and renaming groups

Input tables

• One-input data tables
• Two-input data tables

Charts

• Creating a combined line and column chart
• Adding a secondary axis to a chart
• Changing the chart type for a particular data series
• Adding a data series to a chart
• Removing a data series from a chart
• Re-positioning chart title
• Re-positioning the chart legend
• Moving and formatting chart data labels
• Modifying chart axis scales
• Formatting an axis to display using commas
• Inserting images into chart columns
• Inserting images to chart bars
• Formatting the chart plot area using a picture
• Formatting the chart area using a picture

Hyperlinks

• Inserting a hyperlink
• Editing a hyperlink
• Removing a hyperlink

Linking & embedding

• What is embedding and linking?
• Linking data within a worksheet
• Linking cells between worksheets within a workbook
• Linking data between workbooks
• Linking data from Excel to a Word document
• Linking an Excel chart to a Word document
• Updating, locking and breaking links

Importing text files

• What is a delimited text file?
• Importing a delimited text file

Sorting and filtering data

• Sorting data by multiple columns at the same time
• Applying a pre-installed custom sort
• Creating a customized list and performing a custom sort
• Removing a customised list
• Using AutoFilter
• Using AutoFilter to perform multiple queries
• Top 10 AutoFilter
• Removing all AutoFilters from a worksheet
• Advanced filter criteria
• Sub-totalling
• Removing subtotals
• Expanding and collapsing outline detail levels

Tracking and reviewing changes

• Enabling or disabling the ‘track changes’ feature
• Sharing, comparing and merging worksheets

Scenarios

• Scenario manager
• Scenario summary reports

Validating

• Data validation – whole number
• Data validation – decimal number
• Data validation – list
• Data validation – date
• Data validation – time
• Data validation – text length
• Customising a validation input message and error alert
• Removing data validation

Auditing

• Tracing precedent cells
• Tracing dependent cells
• Identifying cells with missing dependents
• Showing all formulas in a worksheet, rather than the resulting values
• Inserting and viewing comments
• Editing and deleting comments
• Showing and hiding comments

Macros

• Macro to change the page set-up
• Macro to apply a custom number format
• Macro to format a cell range
• Macro to insert fields into the header or footer
• Assigning a macro to a button on the quick access toolbar
• Deleting macros

Passwords & security issues

• Adding ‘open’ password protection to a workbook
• Adding ‘modify’ password protection to a workbook
• Removing an ‘open’ password from a workbook
• Removing a ‘modify’ password from a workbook
• Password protecting cells and worksheets
• Hiding formulas
• Un-hiding formulas