Getting to Know Microsoft Excel

• Starting Microsoft Excel The Excel Screen How Microsoft Excel Works Using the Ribbon Using Ribbon Key Tips Minimising the Ribbon Understanding the Backstage View Accessing the Backstage View Using Short Cut Menus Understanding Dialog Boxes Launching Dialog Boxes Understanding the Quick Access Toolbar Adding Commands to the QAT Understanding the Status Bar Exiting Safely From Microsoft Excel

Creating a New Workbook

• Understanding Workbooks Creating a New Workbook Typing Text Typing Numbers Typing Dates Typing Formulas Saving a New Workbook Easy Formulas Checking the Spelling Making Basic Changes Printing a Worksheet Safely Closing a Workbook

Working With Workbooks

• Opening an Existing Workbook Navigating a Workbook Navigating Using the Keyboard Using Go To Practice Exercise Practice Exercise The Open Dialog Box Editing in a Workbook Understanding Data Editing Overwriting Cell Contents Editing Longer Cells Editing Formulas Clearing Cells Deleting Data in a Cell Using Undo and Redo

Selecting Ranges

• Understanding Cells and Ranges Selecting Contiguous Ranges Selecting Non Contiguous Ranges Using Special Selection Techniques Selecting Larger Ranges Selecting Rows Selecting Columns Viewing Range Calculations Creating an Input Range

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 Copying Relative Formulas Copying to a Non-Contiguous Range Copying to Another Worksheet Copying to Another Workbook

Filling Data

• Understanding Filling Filling a Series Filling a Growth Series Filling a Series Backwards Filling Using Options Creating a Custom Fill List Modifying a Custom Fill List Deleting a Custom Fill List

Moving Data

• Understanding Moving in Excel Moving Cells and Ranges Moving Data to Other Worksheets Moving Data to Other Workbooks    

Special Pasting

• Understanding Pasting Options Pasting Formulas Pasting Values Pasting Without Borders Pasting as a Link Pasting as a Picture Transposing Ranges Copying Comments Copying Validations Copying Column Widths Performing Arithmetic With Paste Special Copying Formats With Paste Special The Paste Special Dialog Box

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 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 Applying Strikethrough Subscripting Text Superscripting Text

Cell Alignment

• Understanding Cell Alignment Aligning Right Aligning to the Centre Aligning Left Aligning Top Aligning Bottom Aligning to the Middle Rotating Text Indenting Cells Wrapping and Merging Text Merging and Centring Merging Cells Unmerging Cells

Row and Column Formatting

• Approximating Column Widths Setting Precise Columns Widths Setting the Default Column Width Approximating Row Height Setting Precise Row Heights Hiding Rows and Columns Unhiding Rows and Columns

Number Formatting

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

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 Chart Type Creating a New Chart Working With an Embedded Chart Resizing a Chart Dragging 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

Formula Referencing

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

Formula Techniques

• Scoping a Formula
  Developing a Nested Function
  Creating Nested Functions
  Editing Nested Functions
  Copying Nested Functions
  Using Concatenation
  Switching to Manual Recalculation
  Forcing a Recalculation
  Pasting Values From Formulas

Logical Functions

• Understanding Logical Functions
  Using IF to Display Text
  Using IF to Calculate Values
  Nesting IF Functions
  Using IFERROR
  Using TRUE and FALSE
  Using AND
  Using OR
  Using NOT

Number Formatting Techniques

• Using Alternate Currencies
  Formatting Dates
  Formatting Time
  Creating Custom Formats

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

Applying Borders

• Understanding Borders
  Applying a Border to a Cell
  Applying a Border to a Range
  Applying a Bottom Border
  Applying Top and Bottom Borders
  Removing Borders
  The More Borders Options
  Using the More Borders Option

Working With a Worksheet

• Understanding Worksheets
  Changing Worksheet Views
  Worksheet Zooming
  Viewing the Formula Bar
  Viewing the Gridlines
  Viewing the Ruler
  Inserting Cells
  Deleting Cells
  Inserting Columns
  Inserting Rows
  Deleting Rows and Columns
  Switching Between Worksheets

Worksheet Techniques

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

Finding and Replacing

• Understanding Find and Replace Operations
  Finding Text
  Finding Cell References in Formulas
  Replacing Values
  Using Replace to Change Formulas
  Replacing Within a Range
  Finding Formats
  Finding Constants Using Go to Special
  Finding Formulas Using Go to Special
  Finding the Current Region
  Finding the Last Cell

Page Setup

• Understanding Page Layout
  Using Built in Margins
  Setting Custom Margins
  Changing Margins by Dragging
  Centring on a Page
  Changing Orientation
  Specifying the Paper Size
  Setting the Print Area
  Clearing the Print Area
  Inserting Page Breaks
  Using Page Break Preview
  Removing Page Breaks
  Setting a Background
  Clearing the Background
  Settings Rows as Repeating Print Titles
  Clearing Print Titles
  Printing Gridlines
  Printing Headings
  Scaling to a Percentage
  Fit to a Specific Number of Pages
  Strategies for Printing Larger Worksheets

Sorting Data

• Understanding Lists
  Performing an Alphabetical Sort
  Performing a Numerical Sort
  Sorting on More Than One Column
  Sorting Numbered Lists
  Sorting by Rows

Filtering Data

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

Charting Techniques

• Understanding Chart Layout Elements
  Adding a Chart Title
  Adding Axes Titles
  Positioning the Legend
  Showing Data Labels
  Showing a Data Table
  Modifying the Axes
  Showing Gridlines
  Formatting the Plot Area
  Adding a Trendline
  Adding Error Bars
  Adding a Text Box to a Chart
  Drawing Shapes in a Chart

Lookup Functions

• Understanding Data Lookup
  Functions
  Using XLOOKUP
  Using VLOOKUP
  Using VLOOKUP For Exact Matches
  Using HLOOKUP
  Using INDEX
  Using MATCH
  Using IFERROR
  Understanding Reference
  Functions
  Using INDIRECT

Text Function

• Using UPPER
• Using LOWER
• Using PROPER
• Using LEFT
• Using RIGHT
• Using SEARCH
• Using LEN
• Using TRIM
• Using CONCATENATE
• Using CONCAT

Setting Excel Options

• Understanding Excel Options Personalising Excel Setting the Default Font Setting Formula Options Understanding Save Options Setting Save Options Setting the Default File Location Setting Advanced Options

Labels and Names

• Understanding Labels and Names Creating Names Using Text Labels Using Names in New Formulas Applying Names to Existing Formulas Creating Names Using the Name Box Using Names to Select Ranges Pasting Names Into Formulas Creating Names for Constants Creating Names From a Selection Scoping Names to the Worksheet Using the Name Manager Documenting Range Names

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  

Chart Object Formatting

• Understanding Chart Object Formatting Selecting Chart Elements Using Shape Styles to Format Objects Changing Column Colour Changing Pie Slice Colour Changing Bar Colours Changing Chart Line Colours Using Shape Effects Filling the Chart Area and the Plot Area The Format Dialog Box Using the Format Dialog Box Using Themes

Data Linking

• Understanding Data Linking Linking Between Worksheets Linking Between Workbooks Updating Links Between Workbooks

Data Consolidation

• Understanding Data Consolidation Consolidating With Identical Layouts Creating an Outlined Consolidation Consolidating With Different Layouts Consolidating Data Using 3D SUM Functions

Pivot Tables

• Understanding Pivot Tables Creating a PivotTable Shell Dropping Fields Into a PivotTable Filtering a PivotTable Report Clearing a Report Filter Switching PivotTable Labels Formatting a PivotTable Report Understanding Slicers Creating Slicers

Summarising and Subtotalling

• Creating Subtotals Using a Subtotalled Worksheet Creating Nested Subtotals Copying Subtotals Using Subtotals With AutoFilter Creating Relative Names for Subtotals Using Relative Names for Subtotals

PivotTable Techniques

• Using Compound Fields Counting in a PivotTable Report Formatting PivotTable Report Values Working With PivotTable Grand Totals Working With PivotTable Subtotals Finding the Percentage of Total Finding the Difference From Grouping in PivotTable Reports Creating Running Totals Creating Calculated Fields Providing Custom Names Creating Calculated Items PivotTable Options Sorting in a PivotTable

PivotCharts

• Creating a PivotChart Shell Dragging Fields for the PivotChart Changing the PivotChart Type Using the PivotChart Filter Field Buttons Moving PivotCharts to Chart Sheets

Goal Seeking

• Understanding Goal Seek Components Using Goal Seek Practice Exercise Practice Exercise Grouping and Outlining Understanding Grouping and Outlining Creating an Automatic Outline Working With an Outline Creating a Manual Group Grouping by Columns

Solver

• Understanding How Solver Works Installing the Solver Add-In Setting Solver Parameters Adding Solver Constraints Performing the Solver Operation Running Solver Reports

Recorded Macros

• Understanding Excel Macros Setting Macro Security Saving a Document as Macro Enabled Recording a Simple Macro Running a Recorded Macro Relative Cell References Running a Macro With Relative References Viewing a Macro Editing a Macro Assigning a Macro to the Toolbar Running a Macro From the Toolbar Assigning a Macro to the Ribbon Assigning a Keyboard Shortcut to a Macro Deleting a Macro Copying a Macro

Recorder Workshop

• Preparing Data for an Application Recording a Summation Macro Recording Consolidations Recording Divisional Macros Testing Macros Creating Objects to Run Macros Assigning a Macro to an Object

• Working in the Cloud
• Understanding Microsoft Online
• Office Online and Microsoft 365
• Accessing Microsoft Online From the Home Page

• Accessing OneDrive
• The OneDrive Screen
• Uploading Files
• Opening Files From OneDrive
• Editing OneDrive Files
• Deleting Files
• Creating a New Document
• The Office Online Screen
• Understanding the Backstage
• Saving a Document
• Reading View vs Editing View
• Opening an Existing Document
• Working Collaboratively
• Accessing Desktop Version in Office Online

• The Excel Online Screen
• Selecting in Excel Online
• Applying Basic Font Formatting
• Applying Alignment
• Applying Number Formatting
• Understanding Data Editing
• Inserting and Deleting Cells
• Understanding Formulas
• Editing Formulas in a Workbook
• Working With Excel Online
• Inserting Tables
• Inserting Charts
• Working With Chart Labels and Axes
• Creating Surveys
• Sharing Surveys
• Editing Surveys
• Inserting Comments
• Working With Comments