Excel: advanced level

Display

• Displaying a workbook in two windows.
• Rearranging windows.
• Showing and hiding a window.
• Splitting a window into panes.

Workbooks

• Creating a custom workbook template.
• Saving a workbook as PDF or XPS.
• Viewing and editing workbook properties.
• Comparing two workbooks side by side.
• Setting the default local working folder.
• Configuring workbook AutoRecover.
• Recovering a previous file version.
• Using the Accessibility Checker.

Specialised data entry

• Entering the same content in multiple cells.
• Using the Equation Editor.
• Creating a hyperlink.
• Following a hyperlink.
• Editing and deleting a hyperlink.
• Creating a custom data series.
• Editing and deleting a custom data series.
• Creating a drop-down list of values.
• Defining permitted data.
• Adding a cell comment.
• Adding a handwritten annotation.
• Splitting cell contents across multiple cells.

Importing data

• Importing data from an Access database.
• Importing data from a web page.
• Mastering advanced Microsoft spreadsheet features.
• Importing data from a text file.
• Refreshing imported data.

Copying and moving

• Copying and transposing data.
• Copying Excel data with a link.
• Performing simple calculations when copying.
• Copying data as a picture.

Rows, columns and cells

• Inserting blank cells.
• Deleting cells.
• Moving and inserting cells, rows and columns.
• Removing duplicate rows.

Named ranges

• Naming cell ranges.
• Mastering advanced Microsoft spreadsheet features.
• Managing cell names.
• Selecting a cell range by name.
• Displaying names and their associated cell references.

Page layout

• Creating a watermark and using views.

Calculations

• Creating a simple conditional formula.
• Creating a nested conditional formula.
• Counting cells that meet a criterion with COUNTIF.
• Summing a range that meets a criterion with SUMIF.
• Using named ranges in formulas.
• Inserting statistical summary rows.
• Calculating with dates.
• Calculating with times.
• Using a lookup function.
• Consolidating data.
• Generating a two-variable data table.
• Using an array formula.

Scenarios and Goal Seek

• Using Goal Seek.
• Creating scenarios.

Auditing

• Displaying formulas instead of results.
• Finding and resolving formula errors.
• Evaluating formulas.
• Using the Watch Window.
• Tracing relationships between formulas and cells.
• Using the Inquire add-in.

Solver

• Discovering and enabling Solver.
• Defining and solving a problem with Solver.
• Displaying Solver's intermediate solutions.

Custom and conditional formats

• Creating a custom format.
• Applying predefined conditional formatting.
• Creating a conditional formatting rule.
• Formatting cells based on contents.
• Removing all conditional formatting rules.
• Managing conditional formatting rules.

Styles and themes

• Creating a cell style.
• Managing cell styles.
• Customising theme colours.
• Customising theme fonts.
• Customising theme effects.
• Saving a theme.

Sorting and outlining

• Sorting by cell colour, font colour or icon set.
• Sorting table data by multiple criteria.
• Using an outline.

Filtering data

• Enabling AutoFilter.
• Filtering by contents or formatting.
• Filtering by a custom criterion.
• Using data-type-specific filters.
• Filtering by multiple criteria.
• Clearing a filter.
• Using an advanced filter.
• Filtering an Excel table with slicers.

Chart options

• Changing the source of category axis labels.
• Managing chart templates.
• Changing category axis options.
• Changing value axis options.
• Creating a combination chart with a secondary axis.
• Editing data labels.
• Adding a trendline.
• Changing text orientation in an element.
• Changing an element's 3D format.
• Changing a 3D chart's orientation and perspective.
• Editing a pie chart.
• Connecting points in a line chart.

Managing objects

• Selecting objects.
• Managing objects.
• Changing object formatting.
• Changing picture formatting.
• Cropping a picture.
• Removing a picture background.
• Changing picture resolution.
• Emphasising text within an object.

Excel tables

• Creating an Excel table.
• Naming a table.
• Resizing a table.
• Showing and hiding table headers.
• Adding a row or column to a table.
• Selecting rows and columns in a table.
• Displaying a total row.
• Creating a calculated column.
• Applying a table style.
• Converting a table to a cell range.
• Deleting a table and its data.

PivotTables

• Choosing a recommended PivotTable.
• Creating a PivotTable.
• Creating a PivotTable from multiple tables.
• Managing PivotTable fields.
• Inserting a calculated field.
• Changing a field's summary function or custom calculation.
• Using totals and subtotals.
• Filtering a PivotTable.
• Grouping PivotTable data.
• Filtering dates interactively with a timeline.
• Changing PivotTable layout and presentation.
• Recalculating a PivotTable.
• Deleting a PivotTable.

PivotCharts

• Choosing a recommended PivotChart.
• Creating a PivotChart.
• Deleting a PivotChart.
• Filtering a PivotChart.

Protection

• Password-protecting a workbook.
• Protecting workbook elements.
• Protecting worksheet cells.
• Allowing specific users to access cells.

Collaborative working

• Introduction.
• Allowing multiple users to edit the same workbook.
• Protecting a shared workbook.
• Editing a shared workbook.
• Resolving editing conflicts.
• Tracking changes.
• Accepting or rejecting changes.
• Removing a user from a shared workbook.
• Stopping workbook sharing.

Excel and the web

• Introduction.
• Saving a workbook as a web page.
• Publishing a workbook.

Macros

• Configuring Excel for macros.
• Recording a macro.
• Running a macro.
• Assigning a macro to a graphic object.
• Editing a macro.
• Deleting a macro.
• Saving a workbook containing macros.
• Enabling macros in the active workbook.

Customising the environment

• Moving the Quick Access Toolbar.
• Customising the Quick Access Toolbar.
• Showing and hiding ScreenTips.
• Customising the status bar.
• Customising the ribbon.
• Exporting and importing a customised ribbon.

Account management

• Account fundamentals.
• Creating a sign-in account.
• Activating a sign-in account.
• Customising a sign-in account.
• Adding or removing a service.