Microsoft Excel 2013 - Introduction

TOPIC DETAILS

Getting To Know Microsoft Excel

• Start Excel from the desktop
• Understand the excel start screen
• The Excel workbook screen
• How Excel 2013 works
• Use the ribbon
• Show and collapse the ribbon
• Understand the backstage view
• Access the backstage view using shortcut menus
• Understand dialog boxes
• Launch dialog boxes
• Understand the Quick Access Toolbar
• Add commands to the QAT
• Understand the status bar
• Exit safely from Excel 2013

Your First Workbook

• Understand workbooks
• Use the blank workbook template
• Type text
• Type numbers
• Type dates
• Type formulas
• Easy formulas
• Save a new workbook on your computer
• Check the spelling
• Make basic changes
• Safely close a workbook

Working With Workbooks

• Open an existing workbook
• Navigate a workbook
• Navigate using the keyboard
• Use Go To
• Recent files and folders
• Understand data editing
• Overwrite cell contents
• Edit longer cells
• Edit formulas
• Clear cells
• Delete Data
• Use undo and redo

Cells and Ranges

• Understand cells and ranges
• Select contiguous ranges
• Select non-contiguous ranges
• Select large ranges
• Select rows
• Select columns
• Understand copy in Excel
• Use fill for quick copy
• Copy from one cell to another
• Copy from one cell to a range
• Copy from one range to another
• Understand filling
• Fill a series
• Fill a growth series
• Extract with flash fill
• Understand moving in Excel
• Move cells and ranges

Formulas And Functions

• Understand formulas
• Create formulas that add
• Create formulas that subtract
• Formulas that multiply and divide
• Understand functions
• Use the SUM function
• Sum non-contiguous ranges
• Calculate an average
• Find a maximum value
• Find a minimum value
• Create more complex formulas
• What If formulas
• Absolute versus relative referencing
• Relative formulas
• Problems with relative formulas
• Create absolute references
• Create mixed references
• Common error messages

Worksheet Appearance

• Understand font formatting
• Work with live preview
• Change fonts
• Chang font size
• Grow and shrink fonts
• Make cells bold
• Italicise text
• Underline text
• Change font colours
• Change background colours
• Use the format painter
• Understand cell alignment
• Horizontal cell alignment
• Vertical cell alignment
• Indent cells
• Understand number formatting
• Apply general formatting
• Format for money
• Format percentages
• Format as fractions
• Format as dates
• Use the thousands separator
• Increase and decrease decimals

Worksheet Layout

• Approximate column widths
• Set precise columns widths
• Set the default column width
• Approximate row height
• Set precise row heights
• Understand worksheets
• Change the worksheet view
• Worksheet zoom
• View the formula bar
• View worksheet gridlines
• Insert cells into a worksheet
• Delete cells from a worksheet
• Insert columns into a worksheet
• Insert rows into a worksheet
• Delete rows and columns
• More than one worksheet
• Worksheet wisdom

Sorting and Filtering

• Understand lists
• Perform an alphabetical sort
• Perform a numerical sort
• Sort on more than one column
• Understand filter
• Apply and use a filter
• Clear a filter
• Create compound filters
• Multiple value filters
• Create custom filters
• Use wildcards

Printing

• Understand printing
• Preview before you print
• Select a printer
• Print a range
• Print an entire workbook
• Specify the number of copies
• The print options

Creating Charts

• Understand the chart process
• Choose the right chart
• Use a recommended chart
• Create a new chart from scratch
• Work with an embedded chart
• Resize a chart
• Reposition a chart
• Print an embedded chart
• Create a chart sheet
• Change the chart type
• Change the chart layout
• Change the chart style
• Print a chart sheet
• Embed a chart into a worksheet
• Delete a chart

Getting Help

• Understand how help works
• Access the help window
• Navigate the help window
• Use the Office website for help
• Use Google search to get help
• Print a help topic
• Other sources of assistance

Guide to Brilliant Spreadsheets

• Nothing beats good planning
• Organisation and design
• Spot on formulas
• Document and easy to use
• The appropriateness of spreadsheets

Microsoft Excel 2013 - Intermediate

TOPIC DETAILS

Fill Techniques

• Create a custom fill list
• Modify a custom fill list
• Delete a custom fill list
• Extract with flash fill
• More complex flash fill extractions
• Extract dates and numbers

Worksheet Techniques

• Insert and delete worksheets
• Copy a worksheet
• Rename a worksheet
• Move a worksheet
• Hide a worksheet
• Unhide a worksheet
• Copy a sheet to another workbook
• Move a sheet to another workbook
• Change worksheet tab colours
• Group worksheets
• Hide rows and columns
• Unhide rows and columns
• Freeze rows and columns
• Split windows

Page Setup

• Strategy for printing worksheets
• Understand page layout
• Use built-in margins
• Set custom margins
• Change margins by dragging
• Centre on a page
• Change orientation
• Specify the paper size
• Set the print area
• Clear the print area
• Insert page breaks
• Use page break preview
• Remove page breaks
• Set a background
• Clear the background
• Set rows as repeating print titles
• Clear print titles
• Print gridlines
• Print headings
• Scale to a percentage
• Fit to a specific number of pages

Applying Borders

• Understand borders
• Apply a border to a cell
• Apply a border to a range
• Apply a bottom border
• Apply top and bottom borders
• Remove borders
• The more borders command
• Use the more borders command
• Draw borders
• Draw a border grid
• Erase borders
• Format the drawing pencil

Essential Functions

• Key worksheet functions
• Use IF with text
• Use IF with numbers
• Nest IF functions
• The CHOOSE function
• The LOOKUP function
• Use count functions
• The round function
• Round up and round down
• Manipulate functions
• The MOD function
• The TODAY function
• The NOW function
• The DATE function
• The PMT function

Complex Formulas

• Scope a formula
• Long-hand formulas
• Prepare for complex formulas
• Create the base formula
• Add more operations
• Edit a complex formula
• Add more complexity
• Copy nested functions
• Switch to manual recalculation
• Paste values from formulas
• Document formulas

Financial Functions

• Understand financial functions
• Use PMT
• Use FV
• Use NPV
• Use PV
• Use RATE
• Use EFFECT
• Use NOMINAL

Date and Time Functions

• Understand date and time functions
• Use NOW
• Use HOUR and MINUTE
• Use TODAY
• Calculate future fates
• Use DATE
• Use calendar functions
• Use WEEKDAY
• Use WEEKNUM
• Use WORKDAY
• Use EOMONTH

Defined Names

• Understand defined names
• Define names from worksheet labels
• Use names in typed formulas
• Apply names to existing formulas
• Create names using the name box
• Use names to select ranges
• Paste defined names into formulas
• Define names for constant values
• Create names from a selection
• Scope names to a worksheet
• Use the name manager
• Document defined names

Number Formatting Techniques

• Apply alternate currencies
• Apply alternate date formats
• Format clock time
• Format calculated time
• Understand number format
• Understand format codes
• Create descriptive custom formats
• Custom format large numbers
• Custom format for fractions
• Pad numbers using custom format
• Align numbers using custom formats
• Customise the display of negative values

Conditional Formatting

• Understand conditional formatting
• Format cells containing values
• Clear conditional formatting
• More cell formatting options
• Top ten items
• More top and bottom format Options
• Work with data bars
• Work with colour scales
• Work with icon sets
• Understand sparklines
• Create sparklines
• Edit sparklines

Global Seeking

• Understand goal seeking
• Use goal seek

The Quick Analysis Tools

• Understand quick analysis
• Quick format
• Quick chart
• Quick totals
• Quick sparklines
• Quick tables

Worksheet Tables

• Understand tables
• Create a table from scratch
• Work with table styles
• Insert table columns
• Remove table columns
• Convert a table to a range
• Create a table from data
• Insert or delete table records
• Remove duplicates
• Sort tables filter tables
• Rename a table
• Split a table
• Delete a table

Pivot Tables

• Understand PivotTables
• Recommend PivotTables
• Create your own PivotTable
• Define the PivotTable structure
• Filter a PivotTable
• Clear a report filter
• Switch PivotTable fields
• Format a PivotTable
• Understand slicers
• Create slicers
• Insert a timeline filter

Chart Elements

• Understand chart elements
• Add a chart title
• Add axes titles
• Reposition the legend
• Show data labels
• Show gridlines
• Format the chart area
• Add a trendline
• Add error bars
• Add a data table

Chart Object Formatting

• Understand chart formatting
• Select chart objects
• Use shape styles
• Change column colour schemes
• Change the colour of a series
• Change line chart colours using shape effects
• Colour the chart background
• Understand the format pane
• Use the format pane
• Explode pie slices
• Change individual bar colours
• Format text
• Format with WordArt
• Change WordArt fill
• Change WordArt effects

Microsoft Excel 2013 - Advanced

TOPIC DETAILS

Setting Excel Options

• Understand Excel options
• Personalise Excel
• Set the default font
• Set formula options
• Understand save options
• Set save options
• Set the default file location
• Set advanced options

Protecting Data

• Understand data protection
• Provide total access to cells
• Protect a worksheet
• Work with a protected worksheet
• Disable worksheet protection
• Provide restrict access to cells
• Password protect a workbook
• Open a password protected workbook
• Remove a password from a workbook

Importing and Exporting

• Understand data import
• Import from an earlier version
• Understand text file formats
• Import tab delimited text
• Import comma delimited text
• Import space delimited text
• Import Access data
• Work with connected data
• Unlink connections
• Export to Microsoft Word
• Export data as text
• Insert a picture
• Modify an inserted picture

Data Linking

• Understand data link
• Link between worksheets
• Link between workbooks
• Update links between workbooks

Grouping and Outlining

• Understand group and outline
• Create an automatic outline
• Work with an outline
• Create a manual group
• Group by columns

Summarising and Subtotalling

• Create subtotals
• Use a subtotal worksheet
• Create nested subtotals
• Copy subtotals using subtotals
• With AutoFilter
• Create relative names for subtotals
• Use relative names for subtotals

Data Consolidation

• Understand data consolidation
• Consolidate with identical layouts
• Create a linked consolidation
• Consolidate from different layouts
• Consolidate data using the SUM function

Data Tables

• Understand data tables and what-if models
• Use a simple what-if model
• Create a one-variable table
• Use one-variable data tables
• Create a two-variable data table

Scenarios

• Understand scenarios
• Create a default scenario
• Create scenarios
• Use names in scenarios
• Display scenarios
• Create a scenario summary report
• Merge scenarios

PivotCharts

• Insert a PivotChart
• Define the PivotChart structure
• Change the PivotChart type
• Use the PivotChart filter field buttons
• Move PivotCharts to chart sheets

PivotTable Features

• Insert a PivotChart
• Define the PivotChart structure
• Change the PivotChart type
• Use the PivotChart filter field buttons
• Move PivotCharts to chart sheets

Advanced Filters

• Understand advanced filter
• Use an advanced filter
• Extract records with advanced filter
• Use formulas in criteria
• Understand database functions
• Use database functions
• Use DSUM
• Use the DMIN function
• Use the DMAX function
• Use the DCOUNT function

Validation Data

• Understand data validation
• Create a number range validation
• Test a validation
• Create an input message
• Create an error message
• Create a drop down list
• Use formulas as validation criteria
• Circle invalid data
• Remove invalid circles
• Copy validation settings

Information Functions

• Understand information functions
• Use the CELL function
• Use the ISBLANK function
• Use the ISERR function
• Use the ISODD and ISEVEN functions
• Use the ISNUMBER and ISTEXT functions
• Use the TYPE function

Text Functions

• Understand text functions
• Use the PROPER function
• Use the UPPER and LOWER functions
• Use the CONCATENATE function
• Use the LEFT and RIGHT functions
• Use the MID function
• Use the LEN function
• Use the SUBSTITUTE function
• Use the T function
• Use the TEXT function
• Use the VALUE function

Controls

• Understand types of controls
• Understand how controls work
• Prepare a worksheet for controls
• Add a combo box control
• Change control properties
• Use the cell link to display the selection
• Add a list box control
• Add a scroll bar control
• Add a spin button control
• Add option button controls
• Add a group box control
• Add a check box control
• Protect a worksheet with controls

Sharing Workbooks

• Share workbooks via the network
• Share workbooks via OneDrive
• Save to OneDrive
• Share workbooks
• Open shared workbooks
• Enable tracked changes
• Accept or reject changes
• Disable tracked changes
• Add worksheet comments
• Navigate worksheet comments
• Edit worksheet comments
• Delete comments

Recorded Macros

• Understand Excel macros
• Set macro security
• Save a document as macro enabled
• Record a simple macro
• Run a recorded macro
• Relative cell references
• Run a macro with relative references
• View a macro
• Edit a macro
• Assign a macro to the toolbar
• Run a macro from the toolbar
• Assign a macro to the ribbon
• Assign a keyboard short cut to a macro
• Delete a macro
• Copy a macro