 Excel 2016 - Level 2

# 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.

#### Chapter 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

#### Chapter 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

#### Chapter 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

#### Chapter 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

#### Chapter 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

#### Chapter 6: Filtering Data

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

#### Chapter 7: Grouping and Outlining

• Understanding Grouping and Outlining
• Creating an Automatic Outline
• Working With an Outline
• Creating a Manual Group
• Grouping by Columns

#### Chapter 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

#### Chapter 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

#### Chapter 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

#### Chapter 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

#### Chapter 12: Chart Elements

• Understanding Chart Elements
• Repositioning the Legend
• Showing Data Labels
• Showing Gridlines
• Formatting the Chart Area

#### Chapter 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

#### Chapter 14: PivotTables

• Understanding PivotTables
• Recommended PivotTables
• 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

#### Chapter 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

#### Chapter 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
• Editing a Complex Formula
• Copying Nested Functions
• Switching to Manual Recalculation
• Pasting Values From Formulas
• Documenting Formulas

• 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

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

#### Key Participants Benefit

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 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

blah is false