Microsoft Excel. Unlock insights and tell the story in your data.

This short course will extend your knowledge of Excel beyond Microsoft Excel: Level 1 and provide you with the skills and knowledge to produce more effective and productive workbooks. Learn essential functions as well as how to manage list data, cover formulas and function techniques, PivotTables, intricate formatting, setting complex printing options and using intricate charting features. Completion of this course provides sufficient knowledge to progress onto Microsoft Excel: Level 3.

We encourage you to use the CCE Excel level self-assessment tool if you are unsure which course level to enrol in.

Aims

This course aims to provide more experienced users with proficient skills in Excel’s three major strands: formulas, list management and charts, as well as providing tips to assist efficiency.

Outcomes

By the end of this course, you should be able to:

• use the fill operations available to fill a data series
• use a range of techniques to work with worksheets
• protect data in worksheets and workbooks
• use common worksheet functions
• understand and create simple PivotTables
• create more complex formulas and functions
• apply a range of number formatting techniques to worksheet cells
• apply conditional formatting to ranges in a worksheet
• use goal seeking to determine the values required to reach a desired result
• understand and use Excel's Quick Analysis tools
• create and work with tables
• use a range of elements and features to enhance charts
• select and change the format of objects in a chart.

Content

Filling Data

• Creating a Custom Fill List
• Modifying a Custom Fill List
• Deleting a Custom Fill List
• Extracting With Flash Fill
• More Complex Flash Fill Extractions
• Extracting Dates and Numbers

Worksheet Techniques

• Hiding a Worksheet
• Unhiding a Worksheet
• Copying a Sheet to Another Workbook
• Changing Worksheet Tab Colours
• Grouping Worksheets
• Hiding Rows and Columns
• Unhiding Rows and Columns
• Freezing Rows and Columns
• Splitting Windows

Protecting Data

• Understanding Data Protection
• Providing Total Access to Cells
• Protecting a Worksheet
• Working With a Protected Worksheet
• Disabling Worksheet Protection
• Providing Restricted Access to Cells
• Password Protecting a Workbook
• Opening a Password Protected Workbook
• Removing a Password From a Workbook

Essential Functions

• Key Worksheet Functions
• Using IF With Text
• Using IF With Numbers
• Nesting IF Functions
• The LOOKUP Function
• Using Counting Functions
• The Round Function
• Rounding Up and Rounding Down
• Manipulative Functions
• The MOD Function
• The TODAY Function
• The NOW Function
• The DATE Function
• Using VLOOKUP
• Using VLOOKUP for Exact Matches
• Using HLOOKUP

PivotTables

• Understanding PivotTables
• Recommended PivotTables
• Creating Your Own PivotTable
• Defining the PivotTable Structure
• Filtering a PivotTable
• Clearing a Report Filter
• Switching PivotTable Fields
• Formatting a PivotTable

Complex Formulas

• Scoping a Formula
• Long-Hand Formulas
• Preparing for Complex Formulas
• Creating the Base Formula
• Adding More Operations
• Editing a Complex Formula
• Adding More Complexity
• Copying Nested Functions
• Switching to Manual Recalculation
• Pasting Values From Formulas
• Documenting Formulas

Number Formatting Techniques

• Applying Alternate Currencies
• Applying Alternate Date Formats
• Formatting Clock Time
• Formatting Calculated Time
• Understanding Number Formatting
• Understanding Format Codes
• Creating Descriptive Custom Formats
• Custom Formatting Large Numbers
• Custom Formatting for Fractions
• Padding Numbers Using Custom Formatting
• Aligning Numbers Using Custom Formats
• Customising the Display of Negative Values

Conditional Formatting

• Understanding Conditional Formatting
• Formatting Cells Containing Values
• Clearing Conditional Formatting
• More Cell Formatting Options
• Top Ten Items
• More Top and Bottom Formatting Options
• Working With Data Bars
• Working With Colour Scales
• Working With Icon Sets
• Understanding Sparklines
• Creating Sparklines
• Editing Sparklines

Goal Seeking

• Understanding Goal Seeking
• Using Goal Seek

The Quick Analysis Tools

• Understanding Quick Analysis
• Quick Formatting
• Quick Charting
• Quick Totals
• Quick Sparklines
• Quick Tables

Worksheet Tables

• Understanding Tables
• Creating a Table From Scratch
• Working With Table Styles
• Inserting Table Columns
• Removing Table Columns
• Converting a Table to a Range
• Creating a Table From Data
• Inserting or Deleting Table Records
• Removing Duplicates
• Sorting Tables
• Filtering Tables
• Renaming a Table
• Splitting a Table
• Deleting a Table

Chart Elements

• Understanding Chart Elements
• Adding a Chart Title
• Adding Axes Titles
• Repositioning the Legend
• Showing Data Labels
• Showing Gridlines
• Formatting the Chart Area
• Adding a Trendline
• Adding Error Bars
• Adding a Data Table

Chart Object Formatting

• Understanding Chart Formatting
• Selecting Chart Objects
• Using Shape Styles
• Changing Column Colour Schemes
• Changing the Colour of a Series
• Changing Line Chart Colours
• Using Shape Effects
• Colouring the Chart Background
• Understanding the Format Pane
• Using the Format Pane
• Exploding Pie Slices
• Changing Individual Bar Colours
• Formatting Text

Intended audience

Designed for existing users of Excel who would like to expand their skills and knowledge and learn more of the techniques associated with creating better and more productive workbooks.

Prerequisites

Assumes prior skill and knowledge required to create, edit, print and chart simple worksheets, as well as understand ranges and the copying process.

You should be also familiar with the basic functions of a computer operating system such as navigating the environment using a mouse and keyboard, starting applications, copying and pasting objects, formatting text, creating folders, opening and saving files, familiarity with the accessing the Internet and web browsers.

Delivery modes

• Face-to-face, presenter-taught training
• Online training via the platform Zoom

Delivery style

Face-to-face classes (CCE, Newtown)

These classes run in a computer lab and you do not need to bring your own device.

Face-to-face classes (external venues including PARKROYAL, Cliftons and CBD)

These classes run in a classroom and you need to bring your own device.

Online classes

You will need your own device.

Materials

You will be provided with a link to access and download your own personalised eBook prior to class.

Teaching instructions and materials are specifically tailored to Windows PC users.

Additional information

Recommended operating system: Windows or Mac
Recommended software for PC: MS Excel 2013, 2016, 2019, 365
Recommended software for Mac OS: MS Excel 2016, 2019, 365
Recommended browser: Chrome

Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.