Excel Intermediate

Conditional formatting

• Applying and clearing conditional formatting
• Top ten items
• Working with Data Bars, Colour Scales and Icon Sets
• Creating and editing Sparklines

Formula referencing

• Absolute versus relative referencing
• Creating absolute and mixed references

Labels and names

• Creating names for cell ranges
• Using names to select ranges
• Using names in formulas

Statistical Functions

• Using COUNT, COUNTA and COUNTIF
• Using MODE, MEDIAN, STDEV

Logical Functions

• Using IF to display text and to calculate values
• Nesting IF Functions
• Using IFERROR, AND, OR and NOT

Math Functions

• Using INT, ROUND and PRODUCT
• Using SUMIF, SUMIFS and SUMPRODUCT

Summarising and subtotaling

• Creating and using subtotals

Creating charts

• Creating a new chart
• Working with an embedded chart
• Resizing a chart
• Printing charts
• Creating a chart sheet
• Changing the chart type and chart layout
• Changing the chart style
• Deleting a chart

Pivot Tables

• Creating a PivotTable
• Filtering a PivotTable report
• Clearing a report filter
• Formatting a PivotTable report
• Creating slicers

PivotTable techniques

• Using compound fields
• Counting in a PivotTable report
• Formatting PivotTable report values
• Working with PivotTable totals
• Grouping in PivotTable reports
• Creating calculated fields
• PivotTable options

PivotCharts

• Creating a PivotChart
• Changing the PivotChart type
• Using the PivotChart filter field buttons

Exercise Files

Summary of common shortcut keys

Full list of shortcut keys

Excel file of all keyboard shortcuts

Please note there is no pre-course work for this course.