Microsoft Office Training Materials – Excel 2013 Advanced Training Manuals

Microsoft Office 2013 Training Resources

Microsoft Excel 2013 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

Microsoft Excel 2013 input tables

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

Microsoft Excel 2013 charts

• Creating a combo line and column 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
• Formatting the chart plot area using a picture
• Formatting the chart area using a picture

Microsoft Excel 2013 and hyperlinks

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

Linking & embedding within Microsoft Excel 2013

• 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 into Microsoft Excel 2013

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

Sorting, filtering & totalling data within Microsoft Excel 2013

• Sorting data by multiple columns at the same time
• Applying a pre-installed custom sort
• Creating a customized list and performing a custom sort
• Deleting an Excel 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 in Microsoft Excel 2013

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

Microsoft Excel 2013 scenarios

• Scenario manager
• Scenario summary reports

Validating within Microsoft Excel 2013

• 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 techniques in Microsoft Excel 2013

• 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 comments
• Deleting comments
• Showing and hiding comments

Macros within Microsoft Excel 2013

• Recording an Excel macro

Microsoft Excel 2013 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