In this advanced training course for Microsoft Excel 2013, expert author Guy Vaccaro takes you beyond the basics of Excel, and teaches you how to use the advanced features and functions in this spreadsheet program from Microsoft.
You will start with basic operations such as SUM, MIN, and MAX, as well as conditional mathematical functions. Guy proceeds to instruct you on using IF statements to control conditions. You will learn how to perform data …
In this advanced training course for Microsoft Excel 2013, expert author Guy Vaccaro takes you beyond the basics of Excel, and teaches you how to use the advanced features and functions in this spreadsheet program from Microsoft.
You will start with basic operations such as SUM, MIN, and MAX, as well as conditional mathematical functions. Guy proceeds to instruct you on using IF statements to control conditions. You will learn how to perform data lookups using VLOOKUP and HLOOKUP, and how to create Sparklines. Other features that are covered in this advanced Excel tutorial are; working with time, outlining, custom views, text manipulation, error checking, pivot tables and pivot charts. Guy also covers some basic Macro operations within Excel for optimizing your workflow.
By the completion of this computer based training video for Microsoft Excel 2013, you will be comfortable with many of the advanced features and functions that this powerful spreadsheet software from Microsoft has to offer. Working files are included, allowing you to follow along with the author throughout the lessons.
Getting Started
Will I Be Able To Keep Up? 00:03:57
Using The Included Files 00:01:17
New For 2013: Its All In The Cloud 00:04:03
The Very Clever Flash Fill 00:04:13
Using Mathematical Functions
The Basic SUM, COUNT, MIN, And MAX 00:03:40
AVERAGE, MODE, MEAN, And MEDIAN 00:04:04
SUMIF For Selective Adding Up 00:06:30
COUNTIF For Selective Counting 00:03:18
AVERAGEIF For The Mean Of Selected Cells 00:03:30
Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF 00:07:06
Area And Volume Calculations 00:04:09
IF Functionality
IF Syntax And Uses 00:07:55
Nesting The IF Statement 00:07:27
Use Of The AND Operator Within An IF 00:06:30
Use Of The OR Operator Within An IF 00:04:04
The NOT Operator Within AND And OR Statements 00:04:28
Display Cell Formulas In Another Cell 00:04:11
Performing Data Lookups
VLOOKUP: Syntax And Usage 00:04:52
VLOOKUP In Live Action 00:06:53
HLOOKUP: Variation On A Theme 00:04:42
Using A Near Match In The Lookup 00:03:44
Dealing With Missing Data In A Lookup 00:04:56
Managing The Lookup Table 00:05:03
Lookups Nested Within Lookups 00:06:29
Sparklines
Creating A Sparkline 00:04:59
Altering The Design Of Sparklines 00:04:26
Dealing With Empty Cells 00:02:25
Comparing Sparklines Within A Sparkline Group 00:04:19
Removing Sparklines From A Worksheet 00:02:14
Further Mathematical Functions
Working With Time In Excel 00:10:21
Calculations Using Time 00:03:59
Useful Time And Date Functions 00:04:45
Rounding Decimal Places 00:04:32
MOD And INT Functions And Uses 00:04:43
Generate And Use A Random Number 00:05:37
Loan And Investment Calculations 00:05:59
Loan Calculation Elements And Functions 00:04:04
Outlining
Create An Outline Automatically 00:03:47
Adding An Outline Manually 00:03:22
Editing And Removing Outlining 00:06:39
Scenarios
Setting Up A Set Of Scenarios 00:06:59
Displaying And Editing The Different Scenarios 00:03:12
How To Work Out Which Scenario You Are Displaying 00:04:06
Merging And Deleting Scenarios 00:03:42
Producing A Summary Of Scenarios 00:03:52
Custom Views
Custom Views Explained 00:03:03
Use Of Outlining To Help Setup Custom Views 00:03:57
Editing And Deleting Custom Views 00:04:25
Add Quick Access To Custom Views 00:03:31
Functions For Manipulating Text
LEFT And RIGHT: Text Manipulation 00:04:28
LEN And TRIM: String Extractions 00:06:35
FIND And MID: Text Functions Working Together 00:06:53
CONCATENATE: Building Strings From Multiple Cells 00:05:24
Changing Case Functions 00:04:03
REPLACE And SUBSTITUTE: Two More String Manipulation Functions 00:04:43
Use Of CHAR Function For More Obscure Characters 00:06:12
Formatting Numeric And Date Values Using TEXT 00:05:01
Keeping The Values Created By String Manipulation 00:03:22
Arrays
Arrays And Creating A New Array Formula 00:05:27
Array Formulas With IF Statements 00:04:57
Conditional Evaluation With No IFs 00:07:22
The Array-Only TRANSPOSE Function 00:06:16
Useful Data Functions
Using The MATCH Function 00:06:59
How The INDEX Function Works 00:05:02
Handling Out Of Range Index Requests 00:03:13
The CHOOSE Lookup Function 00:03:34
MATCH And INDEX Functions Working Together 00:05:05
Some Other Useful Functions
Introducing IS Functions 00:03:23
Error Checking Using ISERR, ISERROR, And IFERROR 00:06:29
OFFSET Function Syntax 00:03:55
OFFSET Function: Creating A Dynamic Named Range 00:04:53
INDIRECT Function To Build Dynamic Formulas 00:04:16
Dealing With INDIRECT Errors 00:03:25
The CELL Function And Determining File Or Sheet Names 00:07:39
Auditing And Troubleshooting Formulas
What Are Tracer Arrows 00:04:20
Adding And Removing Tracer Arrows 00:02:58
Auditing Tools: Error Checking And Tracing 00:06:26
Step-By-Step Formula Processing 00:03:41
Using The Watch Window In Troubleshooting 00:04:11
PivotTables
What Is A PivotTable? 00:04:13
The New Recommended PivotTable Route 00:04:28
Creating Your Own PivotTables 00:06:24
Changing The Formatting And Formulas In PivotTable Summaries 00:04:21
Creating Multiple PivotTables On The Same Dataset 00:05:03
Moving And Deleting PivotTables 00:03:52
Making Use Of The Report Filter Options 00:05:08
Sorting The PivotTable Columns 00:05:16
Refreshing A PivotTable 00:02:51
Drilling Down Behind The Pivot Numbers 00:02:44
Multiple Fields In Row, Column, Or Data Sections 00:05:19
Controlling Grand Totals And Subtotals 00:04:53
Dealing With Empty Cells And Other Additional Options 00:05:08
PivotTable Styles 00:05:05
Creating Your Own PivotTable Styles 00:04:35
Creating And Using Calculated Fields 00:03:50
Using The New Timeline Filter Option 00:04:36
Adding And Using The Data Slicer 00:05:31
Using Data From An SQL Server In A PivotTable 00:04:51
Managing The External Connection To SQL Server 00:02:44
PivotCharts
Creating A PivotChart 00:03:37
Changing The Fields Used In A PivotChart 00:04:37
Formatting The PivotChart 00:05:39
Changing The PivotChart Type 00:04:02
Filtering A PivotChart 00:04:02
Hiding The PivotChart Buttons 00:02:10
Moving And Deleting PivotCharts 00:04:41
Goal Seek And Solver
What-If Analysis Using Goal Seek 00:04:05
Activating The Solver Add-In 00:01:51
Using Solver To Complete A What-If 00:05:27
Adding Constraints To Solver 00:03:48
Macros
What Is A Macro 00:01:42
Creating And Running Your First Macro 00:04:52
Saving Workbooks With Macros 00:01:57
Macro Security Settings For Workbooks With Macros 00:03:51
The Personal Macro Workbook 00:04:27
Deleting Macros 00:03:32
Use Of Relative Or Absolute Referencing 00:03:42
Trigger A Macro With A Keyboard Shortcut 00:03:10
Formatting With A Macro 00:04:27
Switch Scenarios And Views With Macros 00:06:11
Use Of Worksheet Buttons To Trigger Macros 00:06:24
Customizing Form Buttons And Other Shape Triggers 00:05:59
Assigning Macros To Ribbon Icons 00:05:48
Create Your Own Ribbon 00:03:34
View And Edit Macro Code 00:07:11
Add An Are You Sure Box To Macros 00:08:09
Summary And Credits
What Have I Learned 00:03:25
About The Author 00:01:14