Create better and advanced formulas

Create formulas that self-maintain with row/column inserts and changes

• Use SUBTOTAL to create reliable subtotals.
• Current month and year-to-date report formulas.
• Use INDEX-MATCH rather than VLOOKUP.
• Return 0 when data is missing rather than #N/A.
• Create more reliable formulas with Table references.

Manipulate data with Power Query

Power Query is integral to Excel and is powerful for importing and manipulating data

• Combine data from multiple sheets into one table.
• Import data from PDF files.

Discover advanced PivotTables

Further improve your PivotTable skills

• Include % of total column.
• Add calculated fields.

Build advanced charts

Further improve your chart skills

• Create combined Column and Line charts on two axes.
• Save chart templates for quick creation of common charts.

Construct well-structured, professional reports

Make reports clear, reduce errors and speed up formatting

• Format with styles for rapid and consistent formatting.
• Learn conventions for indentation, lines and headings.
• Apply conditional formating to highlight notable values.

Protect sheets to prevent unwanted change

If you create spreadsheets for others, you know about this issue

• Prevent unwanted deletion and the overtyping non-input cells.

Create a macro to automate a task

Macros speed up repetitive tasks, make it easier for others and reduce errors

• Automate a task.
• Create on-sheet button to make running of macro easier for user.

Learn more great tips

There are many great tips imparted throughout, but here's more

• Be clear about Edit and Point modes.
• Fill in missing entries.

• Be assured the advice you get is well-proven and utilised.
• Feel free to ask questions.
• Get time away from the office and constant interruptions.
• Enjoy a friendly environment where you don’t feel you’re holding others up.
• Instantly apply what you’ve learned on return to work.