Excel 2013 Advanced Training Materials

Sample TutorialMicrosoft 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

Download sample IT Courseware

 

Rate this post