Enable Technology

Excel 

Excel 2016




Course Overview


This integrated two day Excel 2016 course assumes no prior knowledge and aims to give a comprehensive understanding of how Excel can be used

Duration: 2 day course



Course Content


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

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

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

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

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
Extracting With Flash Fill
More Complex Flash Fill Extractions
Extracting Dates and Numbers

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

Page Setup
Strategies for Printing Worksheets
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

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 Command
Using the More Borders Command
Drawing Borders
Drawing a Border Grid
Erasing Borders
Formatting the Drawing Pencil

Essential Functions
Key Worksheet Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
The CHOOSE Function
The LOOKUP Function
Using Counting Functions
The ROUND Function
Rounding Up and Rounding Down
Manipulative Functions
The MOD Function
The TODAY Function
The NOW Function
The DATE Function
The PMT Function

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

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

Number Formatting Techniques
Applying Alternate Currencies
Applying Alternate Date Formats
Formatting Clock Time
Formatting Calculated Time
Understanding Number Formatting
Understanding Format Codes
Creating Descriptive Custom Formats
Custom Formatting Large Numbers
Custom Formatting for Fractions
Padding Numbers Using Custom
Formatting
Aligning Numbers Using Custom
Formats
Customising the Display of Negative
Values

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

Goal Seeking
Understanding Goal Seeking
Using Goal Seek

The Quick Analysis Tools
Understanding Quick Analysis
Quick Formatting
Quick Charting
Quick Totals
Quick Sparklines
Quick Tables

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

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

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

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

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

Importing and Exporting
Understanding Data Importing
Importing From an Earlier Version
Understanding Text File Formats
Importing Tab Delimited Text
Importing Comma Delimited Text
Importing Space Delimited Text
Importing Access Data
Working With Connected Data
Unlinking Connections
Exporting to Microsoft Word
Exporting Data as Text
Inserting a Picture
Modifying an Inserted Picture

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

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

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

Data Consolidation
Understanding Data Consolidation
Consolidating With Identical Layouts
Creating a Linked Consolidation
Consolidating From Different Layouts
Consolidating Data Using the SUM
Function

Data Tables
Understanding Data Tables and
What-If Models
Using a Simple What-If Model
Creating a One-Variable Table
Using One-Variable Data Tables
Creating a Two-Variable Data Table

Scenarios
Understanding Scenarios
Creating a Default Scenario
Creating Scenarios
Using Names in Scenarios
Displaying Scenarios
Creating a Scenario Summary Report
Merging Scenarios

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

PivotCharts
Inserting a PivotChart
Defining the PivotChart Structure
Changing the PivotChart Type
Using the PivotChart Filter Field
Buttons
Moving PivotCharts to Chart Sheets

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

Validating Data
Understanding Data Validation
Creating a Number Range Validation
Testing a Validation
Creating an Input Message
Creating an Error Message
Creating a Drop Down List
Using Formulas as Validation Criteria
Circling Invalid Data
Removing Invalid Circles
Copying Validation Settings






Email us about this courses         Telephone on 01952 305730






Training Locations: Enable Technology run courses in Wolverhampton, Telford, Shrewsbury, Birmingham, Cannock, Walsall, Dudley, Sandwell, Bridgnorth, Lichfield, Burton-upon-Trent, Stafford, Redditch, Bromsgrove, Solihull, Tamworth, Worcester, Shropshire, Staffordshire, Worcestershire and across the West Midlands.




West Midlands Scheduled Courses: Excel VBA; MS Project, SSRS; SQL; Crystal Reports; HTML;Adobe Illustrator; ASP.NET; Dreamweaver; Flash; Photoshop; InDesign; Google Scripting; Visio; PHP; Microsoft Access; JQuery JavaScript; Web Development; Office;

Birmingham Scheduled Courses: SQL; Excel VBA; MS Access;Photoshop; Adobe Illustrator; InDesign; Dreamweaver; MS Project;Visio; Flash; SSRS;PHP; JavaScript JQuery; HTML CSS; Microsoft Office;

Wolverhampton Scheduled Courses: Microsoft Access; Excel VBAMS Project; Visio; Adobe Illustrator; Photoshop; Dreamweaver; InDesign; Flash; HTML CSS; JavaScript; SQL; Microsoft Office; Adobe

Telford Scheduled Courses: SQL; Excel VBA; MS Project; Visio; PowerPoint; Photoshop; Adobe InDesign; Illustrator; Dreamweaver; Web Design; Office;

Cannock Scheduled Courses: Excel VBA; PHP; Microsoft Access; Visio; MS Project; PowerPoint; Dreamweaver; Photoshop; InDesign; Adobe Illustrator; Flash; JavaScript; HTML CSS;

Walsall Scheduled Courses: Excel; Access; MS Project; Visio; Photoshop; InDesign; Adobe Illustrator; Dreamweaver; Flash; SQL; JavaScript; HTML CSS;

Dudley Scheduled Courses: Excel; MS Access; SQL; MS Project;Visio; Adobe Illustrator; InDesign; Photoshop; Dreamweaver; Flash; JavaScript; HTML CSS;

Sandwell Scheduled Courses: Access;Excel; Microsoft Project; Visio; PowerPoint; Adobe Illustrator; Photoshop; Adobe InDesign; Dreamweaver; Flash; JavaScript; HTML CSS; SQL;

Shropshire Scheduled Courses: Illustrator; InDesign; Adobe; Dreamweaver; Flash; Photoshop; Excel; Project; Visio; PowerPoint; Office; JavaScript; HTML; Access;

Staffordshire Scheduled Courses: Illustrator; InDesign; Photoshop; Dreamweaver; Flash; Adobe; Excel;