Microsoft Excel 2010 Tutorials
Beginner to Advanced Bundle

BEGINNERS TO INTERMEDIATE CONTENTS

Getting Started
Overview Of Excel 2010
The New File Menu In Excel 2010
Exploring The Excel Workbook
What Will I Be Able To Do At The End Of This Course
Customizing The Quick Access Toolbar
Working With The Ribbons
Using The Files Included With This Course

Working With An Excel Worksheet
Creating A New Workbook
Saving A Workbook
Saving In Other Formats
Open A Existing Workbook
Navigating An Excel Workbook

Selecting And Entering Data
Entering Text And Numbers
Entering Numbers As Text
Inputting Dates And Times
Editing The Contents Of A Cell
Using Copy And Paste
The FILL Handle
Using Undo And Redo
Shortcuts For Selecting Cells
Inserting Cells, Columns And Rows
Deleting Cells, Columns And Rows
Changing Column Widths And Row Heights
Hiding And Unhiding A Column Or Row
Searching For Values In A Worksheet

Developing A Workbook
Formatting And Naming Of Worksheet Tabs
Moving Worksheets Within And Between Workbooks
Copying Worksheets Within Workbooks
Inserting And Deleting Worksheets
Viewing More Than One Workbook
Hiding And Unhiding Worksheets

Printing And Page Setup
Using The Print Area Command To Control What Excel Prints
Setting Page Margins And Page Orientation
Creating A Header And Footer
Using Page Break View
Setting Print Titles For Large Worksheets
Printing Gridlines Column Row Headings And Other Related Options
Enabling The New Page Layout View
Running The Spell Checker
Print Preview And Printing The Whole Or Part Of The Worksheet

Workbook Formatting
Modifying Typeface, Font Size And Colors
Merging Cells
Applying Number Formats
Aligning Cell Contents
Changing The Text Direction In A Cell
Adding Borders To Cells And Ranges
Applying A Date Format To A Cell Or Range
Use Of The Format Painter
Clearing Cell Contents And/Or Formatting

Carrying Out Calculations
Getting Started With Basic Math Formula Addition
Getting Started With Basic Math Formula Subtraction
Getting Started With Basic Math Formula Multiplication
Getting Started With Basic Math Formula Division
Getting Started With Basic Math Formula Operand
Order Of Operations with BODMAS
Introduction To Excel Formulas: SUM
Copying Formulas And Functions
Displaying Formulae On A Worksheet
Some Useful Functions For Counting
Some Useful Functions For Averages
Some Useful Functions For Highest And Lowest
Absolute Referencing Requirements
Formulas Across Worksheets
Functions Across Worksheets
3D Referencing
Making Use Of Named Cells And Ranges

Advanced Formatting
Enhancing Worksheets Using Themes
Working With Comments
Creating Your Own AutoFill List
Turning Your Data Into A Table For Formatting

Security
Using Protection On A Worksheet
Protecting Part Of A Worksheet To Facilitate Data Entry
Controlling Access To Cell Ranges By Password And Or User Accounts
Saving A Workbook With Password Protection

Using Templates
Creating A Workbook From A Template
Creating Your Own Custom Template

Graphics Objects
Inserting And Modifying Clipart
Using SmartArt Graphics
Adding Drawn Shapes To A Worksheet
Inserting An External Picture
Adding A Background Image To A Worksheet
Formatting Graphic Objects
Controlling Stacking Order And Alignment Of Multiple Graphics Objects
Grouping Graphic Objects
Accessing Formula Results In A Callout Bubble

Using Excel As A Database
Fixing Rows And Columns On Screen
Fixing Rows And Columns For Print
Sorting Data In A Worksheet
Filtering Data In A Worksheet
Removing Duplicates In An Excel Database
Adding And Using Subtotals In An Excel Database
Splitting One Column Of Data Into More Columns
Splitting The Screen To View Different Parts Of The Same Worksheet
Linking To Data On A Webpage

Using Charts In Excel
Creating A Chart The Long Way
Creating A Chart The Quick Way
Changing A Chart From A Sheet Object To A Sheet
Modifying Your Chart Type
Controlling Chart Style And Layout
Adding A Trend line To A Chart
Pie Charts In More Detail

More Advanced Formula
Adding An IF Function For Cell Comparisons
Using the IF Function To Suppress Excel Error Messages
Text Manipulation With Formulae - Lesson 1
Text Manipulation With Formulae - Lesson 2
Date Formula In Excel
DATEDIF - A Hidden But Useful Formula
SUMIF COUNTIF And AVERAGEIF
Use Of A Formula To Rank Scores

Conditional Formatting
Changing Cell Appearance Based On Its Value
Editing Conditional Formatting Rules On A Worksheet
Removing Conditional Formatting Rules
Highlighting Duplicate Values Using Conditional Formatting
Identifying The Top Or Bottom Percentage Of A Range
Use Of Data Bars Within Conditional Formatting
Use Of Colour Scales Within Conditional Formatting
Applying An Icon Set To Conditional Formatting Rules

Office Web Apps And Excel
What Is Office Web Apps?
Sign In To Or Sign Up For Your Own SkyDrive
Saving An Excel Workbook Into SkyDrive
Accessing And Editing An Excel Workbook Through SkyDrive
Telling Others Where Your Public Documents Are
Direct Linking Or Embedding Of SkyDrive Documents
Creating A Folder Structure Within SkyDrive
Deleting Files From SkyDrive

Credits And Beyond
About The Tutor
Microsoft Excel 2010 Training CD End

ADVANCED TRAINING CONTENTS

Getting Started
How Advanced Does The Advanced Get?
Using The Files Included
About The Tutor
New In 2010 - The Sparkline

The IF Function
The Syntax Of IF
Nesting The IF Statement
Use AND Operator To Reduce Quantity Of Nested IFs
Use OR Operator To Reduce Quantity Of Nested IFs
The NOT Operator Within AND And OR Statements
SUMIF For Selective Adding Up
COUNTIF For Selective Counting
AVERAGEIF For Mean Of Cells That Meet Our Criteria
Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions

Performing Lookups
VLOOKUP Explained
Applied Examples For VLOOKUP
HLOOKUP Explained
HLOOKUP In Action
Looking For A Near Match In A Lookup
Checking For Missing Data In A Lookup
Extending The Size Of A Lookup Table
Nested LOOKUPs

Data Functions
The MATCH Function Explained
The INDEX Function Syntax
How To Stop Nonexistent Row Or Column Lookups In INDEX
The CHOOSE Lookup Function

Math Functions
Working With TIME In Excel
Rounding To Fractional Values
MOD For Working Out Remainders
Generating A Random Number
Pick A List Item At Random
Calculating Loan Repayments Using PMT
Investment Calculations Using PMT
Working Out Depreciation
Working Out Different Parts Of A Loan Calculation

Arrays
What Is An Array And An Array Formula
Creating And Using An Array Formula
Conditional Evaluation In An Array Formula
The Very Clever TRANSPOSE Array Function

Functions For Working With Text
LEN And TRIM Two Very Useful Text Functions
Using LEFT And RIGHT For String Extraction
FIND And MID Working Together To Extract Parts Of Strings
Build Strings From Multiple Cells
Changing The Case Of Text In Cells
REPLACE And SUBSTITUTE In Action
Formatting Numeric Values With A Text String Using TEXT
Extracting The Values From The Text Functions We Have Used

Other Useful Functions
Welcome To IS Functions
Error Checking With ISERR ISERROR ISNA And IFERROR
The OFFSET Formula Explained
Dynamic Named Ranges Using The OFFSET Function
Use The INDIRECT Function To Build Dynamic Formulas
Dealing With INDIRECT Errors
Use Formulas To Determine An Excel Filename And Or Sheet Name

Sparklines
Creating A Sparkline
Change The Design Of Sparklines
Dealing with Empty Cells
Comparing One Sparkline To Another by Altering Vertical Scale
Removing Sparklines From A Sheet

Outlining
Outlining Explained
Creating An Outline Automatically
Creating An Outline Manually
Manually Removing Data From An Outline
Removing The Outlining From A Worksheet
Adjusting A Grouping Created By Automatic Outlining

Custom Views
Creating A Custom View Of A Worksheet
Changing From One Custom View To Another
Editing A Custom View
How To Delete A Custom View

Scenario
Setting Up A Scenario And Entering Values
Display The Scenario Values
Editing The Values Of A Scenario
Deleting A Scenario
Merge Scenarios From Different Sheets
Getting A Summary Of All Scenarios

Auditing And Troubleshooting Formulas
Description Of Tracer Arrows
Tracing Precedents And Dependents
Remove Tracer Arrows
Error Checking Using Auditing Tools
Step By Step Processing Of Formula To Help With Troubleshooting
Utilizing The Watch Window

Pivot Tables
What Is A Pivot Table
Steps To Create A Pivot Table
Rearranging Fields In A Pivot Table
Changing The Math Of The Data Summary
Number Format Control Of The Summary Area
Creating A Second (Or More) Pivot Table On The Same Data
Moving A Pivot Table
Removing A Pivot Table
Making Use Of The Report Filter Option
Sorting A Pivot Tables Columns
Displaying Values As A Percentage
Refreshing A Pivot Table Manually Or Semi-Automatically
Drilling Down Behind The Pivot Table Summaries
Applying Pivot Table Styles
Creating Your Own Custom Pivot Table Style
Copying A Pivot Table Style Between Workbooks
Using More Than One Field In Row And Column Headings
Disabling And Enabling Grand And Sub Totals
Filtering Columns And Rows Within A Pivot Table
Dealing With Empty (NULL) Cells
Exploring The Additional Pivot Table Options
Introducing The Slicer Tool
Managing Your Slices
Formatting Your Slices
Connecting A Pivot Table To SQL Server
External Connection Refresh Rate And Password Saving

Pivot Charts
Creating A Pivot Chart
Altering Chart Types Formats And Layouts
Advanced Layout Control Of A Pivot Chart
Filtering A Pivot Chart
Hiding Pivot Chart Elements
Moving A Pivot Chart Between Sheets
Deleting A Pivot Chart (With Care)

Goal Seek And Solver
Using Goal Seek To Carry Out What If Analysis
Using SOLVER To Carry Out What if Analysis
Activating The SOLVER Add In
Add Constraints Into A SOLVER Problem
Alberts Cafe Solver Solution

Macros in Microsoft Excel 2010
What Is A Macro in Excel 2010
Creating Storing And Running Your First Macro
Using Relative Or Absolute Referencing During Recording
Saving Workbooks With Macros Issues
Opening Files Containing Macros
The PERSONAL Workbook
How To Delete Macros
Use A Macro For Formatting in MS Excel 2010 - Lesson 1
Use A Macro For Formatting in MS Excel 2010 - Lesson 2
Trigger A Macro With A Keyboard Shortcut
Using Form Buttons To Trigger Macros
Customizing The Form Buttons
Assigning Macros To Ribbon Icons
Create Your Own Ribbon
Remove Options From Ribbons
View And Edit Macro Code
Add A Confirmation Dialog Box To Macros