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

Challenge 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.