Excel 2016 - Level 2

The skills and knowledge acquired in Microsoft Excel 2016 - Level 2 enable users to expand their knowledge and learn how to create more productive workbooks. It covers formulas and function techniques, advanced formatting and charting features and...
$380
Brand
SKU:
E16L2
Duration:
1 Day
Delivery:
Virtual Classroom
Adding to cart… The item has been added
Request a Quote

Excel 2016 - Level 2

The skills and knowledge acquired in Microsoft Excel 2016 - Level 2 enable users to expand their knowledge and learn how to create more productive workbooks. It covers formulas and function techniques, advanced formatting and charting features and pivoting data using Pivot Tables.

Audience

This course assumes the learner has the knowledge required to create, edit, and print simple worksheets. Understanding ranges and copying is also important.

Skills Covered

Upon successful completion of this course, you will be able to:

  • create and use defined names in a workbook
  • use logical functions to test whether a statement is true or false
  • use the date and time functions to perform calculations
  • use a range of text functions
  • sort data in a list in a worksheet
  • filter data in a table
  • group cells and use outlines to manipulate the worksheet
  • create summaries in your spreadsheets using subtotals
  • create and work with tables
  • apply conditional formatting to ranges in a worksheet
  • create effective charts in Microsoft Excel
  • use a range of elements and features to enhance charts
  • select and change the format of objects in a chart
  • understand and create simple PivotTables
  • create and edit a PivotChart
  • construct and operate PivotTables using some of the more advanced techniques
  • create and edit a PivotChart
  • create more complex formulas and functions
  • use advanced filters to analyse data in a list
  • understand and use Excel's Quick Analysis tools

Course Content

Excel 2016 - Level 2

  • Lesson 1: Defined Names
    • Understanding Defined Names
    • Defining Names From Worksheet Labels
    • Using Names in Typed Formulas
    • Applying Names to Existing Formulas
    • Creating Names Using the Name Box
    • Using Names to Select Ranges
    • Pasting Defined Names Into Formulas
    • Defining Names for Constant Values
    • Creating Names From a Selection
    • Scoping Names to a Worksheet
    • Using the Name Manager
    • Documenting Defined Names
  • Lesson 2: Logical Functions
    • Understanding Logical Functions
    • Using IF With Text
    • Using IF With Numbers
    • Nesting IF Functions
    • Using IFERROR
    • Using TRUE and FALSE
    • Using AND
    • Using OR
    • Using NOT
  • Lesson 3: Date and Time Functions
    • Understanding Date and Time Functions
    • Using NOW
    • Using HOUR and MINUTE
    • Using TODAY
    • Calculating Future Dates
    • Using DATE
    • Using Calendar Functions
    • Using WEEKDAY
    • Using WEEKNUM
    • Using WORKDAY
    • Using EOMONTH
  • Lesson 4: Text Functions
    • Understanding Text Functions
    • Using the PROPER Function
    • Using the UPPER and LOWER Functions
    • Using the CONCATENATE Function
    • Using the LEFT and RIGHT Functions
    • Using the MID Function
    • Using the LEN Function
    • Using the SUBSTITUTE Function
    • Using the T Function
    • Using the TEXT Function
    • Using the VALUE Function
  • Lesson 5: Sorting Data
    • Understanding Lists
    • Performing an Alphabetical Sort
    • Performing a Numerical Sort
    • Sorting on More Than One Column
    • Sorting Numbered Lists
    • Sorting by Rows
  • Lesson 6: Filtering Data
    • Understanding Filtering
    • Applying and Using a Filter
    • Clearing a Filter
    • Creating Compound Filters
    • Multiple Value Filters
    • Creating Custom Filters
    • Using Wildcards
  • Lesson 7: Grouping and Outlining
    • Understanding Grouping and Outlining
    • Creating an Automatic Outline
    • Working With an Outline
    • Creating a Manual Group
    • Grouping by Columns
  • Lesson 8: 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
  • Lesson 9: 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
  • Lesson 10: 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
    • Creating Custom Rules
    • The Conditional Formatting Rules Manager
    • Managing Rules
  • Lesson 11: 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
  • Lesson 12: 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
  • Lesson 13: 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
    • Formatting With WordArt
    • Changing WordArt Fill
    • Changing WordArt Effects
  • Lesson 14: 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
    • Understanding Slicers
    • Creating Slicers
    • Inserting a Timeline Filter
  • Lesson 15: PivotTable Features
    • Using Compound Fields
    • Counting in a PivotTable
    • Formatting PivotTable 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
  • Lesson 16: PivotCharts
    • Inserting a PivotChart
    • Defining the PivotChart Structure
    • Changing the PivotChart Type
    • Using the PivotChart Filter Field Buttons
    • Moving PivotCharts to Chart Sheets
  • Appendix A: 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
  • Appendix B: Advanced Filters
    • Understanding Advanced Filtering
    • Using an Advanced Filter
    • Extracting Records With Advanced Filter
    • Using Formulas in Criteria
    • Understanding Database Functions
    • Using Database Functions
    • Using DSUM
    • Using the DMIN Function
    • Using the DMAX Function
    • Using the DCOUNT Function
  • Appendix C: The Quick Analysis Tools
    • Understanding Quick Analysis
    • Quick Formatting
    • Quick Charting
    • Quick Totals
    • Quick Sparklines
    • Quick Tables

FAQs

  • How do I enrol in this online training?

    You can enrol in this training on our website and make an online payment using any of the following option

    • Visa Credit or Debit Card
    • MasterCard
    • American Express
    • Diners Club
    • PayPal

    Once payment is received, you will automatically receive a payment receipt and access information via email.

Buy Now

Our Most Popular Training Courses

BApps2

Excel 2016 - Level 3

$380

Microsoft

  • Duration 1 Day
View Course
Everything you need to become a PRINCE2 Practitioner, all in one place.
Course Name Exam Access/Duration Price (Incl Tax)  
Excel 2016 - Level 3 1 Day
$380
course info
BApps3

Excel 2016 - Level 1

$380

Microsoft

  • Duration 1 Day
View Course
Everything you need to become a PRINCE2 Practitioner, all in one place.
Course Name Exam Access/Duration Price (Incl Tax)  
Excel 2016 - Level 1 1 Day
$380
course info
BApps6

Excel 2019 - Level 2

$380

Microsoft

  • Duration 1 Day
View Course
Everything you need to become a PRINCE2 Practitioner, all in one place.
Course Name Exam Access/Duration Price (Incl Tax)  
Excel 2019 - Level 2 1 Day
$380
course info
PI1

Outlook 2016 - Level 2

$380

Microsoft

  • Duration 1 Day
View Course
Everything you need to become a PRINCE2 Practitioner, all in one place.
Course Name Exam Access/Duration Price (Incl Tax)  
Outlook 2016 - Level 2 1 Day
$380
course info
PI2

PowerPoint 2016 - Level 2

$380

Microsoft

  • Duration 1 Day
View Course
Everything you need to become a PRINCE2 Practitioner, all in one place.
Course Name Exam Access/Duration Price (Incl Tax)  
PowerPoint 2016 - Level 2 1 Day
$380
course info