Microsoft Excel Advanced

MICROSOFT EXCEL 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 INPUT TABLES

• One input data tables
• Two input data tables

MICROSOFT EXCEL 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
• Repositioning chart title
• Repositioning 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 AND HYPERLINKS

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

LINKING & EMBEDDING WITHIN MICROSOFT EXCEL

• 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

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

SORTING, FILTERING & TOTALLING DATA WITHIN MICROSOFT EXCEL

• Sorting data by multiple columns at the same time
• Applying a preinstalled 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
• Subtotalling
• Removing subtotals
• Expanding and collapsing outline detail levels

TRACKING AND REVIEWING CHANGES IN MICROSOFT EXCEL

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

MICROSOFT EXCEL SCENARIOS

• Scenario manager
• Scenario summary reports

VALIDATING WITHIN MICROSOFT EXCEL

• 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

• 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

• Recording an Excel macro

MICROSOFT EXCEL 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