Excel 2013 Advanced Training Materials 12 Hrs.
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 |
