Microsoft Excel 2016 – Level 1

This is a beginner’s course and aims to give the new spreadsheet user a thorough grounding in the basics of creating and working with spreadsheets using Microsoft Excel 2016. Particular emphasis is placed on developing accurate and well-designed spreadsheets employing sound design and documentation principles.

At the completion of this course you should be able to:

• navigate your way around Microsoft Excel 2016
• create and work with a new workbook
• open and navigate within workbooks and worksheets
• make changes to data in a workbook
• understand and work with ranges in a worksheet
• copy and paste data in Excel
• use the fill operations available to fill a data series
• move the contents of cells and ranges within and between workbooks
• understand, create and work with formulas and functions
• understand and use formula cell referencing
• use font formatting techniques
• align the contents of cells in a number of ways
• understand and use the number formatting features in Excel
• format rows and columns in a worksheet
• work with elements that make up the structure of a worksheet
• sort data in a list in a worksheet
• filter data in a table
• print your workbook data
• create effective charts in Microsoft Excel
• obtain help for Excel whenever you need it
• understand points to consider to avoid problems in your worksheets

Getting to Know Excel 2016

• Starting Excel From the Desktop
• Understanding the Excel Start Screen
• The Excel Workbook Screen
• How Excel 2016 Works
• Using the Ribbon
• Showing and Collapsing the Ribbon
• Understanding the Backstage View
• Accessing the Backstage View
• Using Shortcut Menus
• Understanding Dialog Boxes
• Launching Dialog Boxes
• Understanding the Quick Access
• Toolbar
• Adding Commands to the QAT
• Understanding the Status Bar
• Exiting Safely From Excel 2016

Creating a New Workbook

• Understanding Workbooks
• Using the Blank Workbook Template
• Typing Text
• Typing Numbers
• Typing Dates
• Typing Formulas
• Easy Formulas
• Saving a New Workbook on Your
• Computer
• Checking the Spelling
• Making Basic Changes
• Safely Closing a Workbook

Working With Workbooks

• Opening an Existing Workbook
• Navigating a Workbook
• Navigating Using the Keyboard
• Using Go To
• Recent Files and Folders

Editing in a Workbook

• Understanding Data Editing
• Overwriting Cell Contents
• Editing Longer Cells
• Editing Formulas
• Clearing Cells
• Deleting Data
• Using Undo and Redo

Selecting ranges

• Understanding Cells and Ranges
• Selecting Contiguous Ranges
• Selecting Non Contiguous Ranges
• Selecting Larger Ranges
• Selecting Rows
• Selecting Columns

Copying Data

• Understanding Copying in Excel
• Using Fill for Quick Copying
• Copying From One Cell to Another
• Copying From One Cell to a Range
• Copying From One Range to Another

Filling Data

• Understanding Filling
• Filling a Series
• Filling a Growth Series
• Extracting With Flash Fill

Moving Data

• Understanding Moving in Excel
• Moving Cells and Ranges
• Moving by Dragging

Formulas and functions

• Understanding Formulas
• Creating Formulas That Add
• Creating Formulas That Subtract
• Formulas That Multiply and Divide
• Understanding Functions
• Using the SUM Function to Add
• Summing Non-Contiguous Ranges
• Calculating an Average
• Finding a Maximum Value
• Finding a Minimum Value
• Creating More Complex Formulas
• What if Formulas
• Common Error Messages

Formula Referencing

• Absolute Versus Relative Referencing
• Relative Formulas
• Problems With Relative Formulas
• Creating Absolute References
• Creating Mixed References

Font Formatting

• Understanding Font Formatting
• Working With Live Preview
• Changing Fonts
• Changing Font Size
• Growing and Shrinking Fonts
• Making Cells Bold
• Italicising Text
• Underlining Text
• Changing Font Colours
• Changing Background Colours
• Using the Format Painter

Cell Alignment

• Understanding Cell Alignment
• Horizontal Cell Alignment
• Vertical Cell Alignment
• Indenting Cells

Number Formatting

• Understanding Number Formatting
• Applying General Formatting
• Formatting for Money
• Formatting Percentages
• Formatting as Fractions
• Formatting as Dates
• Using the Thousands Separator
• Increasing and Decreasing Decimals

Row and column formatting

• Approximating Column Widths
• Setting Precise Columns Widths
• Setting the Default Column Width
• Approximating Row Height
• Setting Precise Row Heights

Working with a worksheet

• Understanding Worksheets
• Changing the Worksheet View
• Worksheet Zooming
• Viewing the Formula Bar
• Viewing Worksheet Gridlines
• Inserting Cells Into a Worksheet
• Deleting Cells From a Worksheet
• Inserting Columns Into a Worksheet
• Inserting Rows Into a Worksheet
• Deleting Rows and Columns
• Working With Multiple Worksheets
• Worksheet Wisdom

Sorting Data

• Understanding lists
• Performing an Alphabetical Sort
• Performing a Numerical Sort
• Sorting on More Than One Column

Filtering Data

• Understanding Filtering
• Applying and Using a Filter
• Clearing a Filter
• Creating Compound Filters
• Multiple Value Filters
• Creating Custom Filters
• Using Wildcards

Printing

• Understanding Printing
• Previewing Before You Print
• Selecting a Printer
• Printing a Range
• Printing an Entire Workbook
• Specifying the Number of Copies
• The Print Options

Creating Charts

• Understanding the Charting Process
• Choosing the Right Chart
• Using a Recommended Chart
• Creating a New Chart From Scratch
• Working With an Embedded Chart
• Resizing a Chart
• Repositioning a Chart
• Printing an Embedded Chart
• Creating a Chart Sheet
• Changing the Chart Type
• Changing the Chart Layout
• Changing the Chart Style
• Printing a Chart Sheet
• Embedding a Chart Into a Worksheet
• Deleting a Chart

Getting Help

• Understanding How Help Works
• Using Tell Me
• Accessing the Help Window
• Navigating the Help Window
• Using Google to Get Help
• Printing a Help Topic
• Other Sources of Assistance

A guide to brilliant spreadsheets

• Good Planning Is Essential
• Organisation and Design
• Writing Effective Formulas
• Documented and Easy to Use

This course assumes little or no knowledge of spreadsheets or Microsoft Excel 2016. However, it would be beneficial to have a general understanding of personal computers and the operating system environment, especially in regard to working with files and folders.