Microsoft Excel 2013 Advanced. Online Excel Training Course

Master Advanced Excel 2013 Features. Become A Expert And Learn To Use Excel Like A Pro With This Advanced Excel Training

What you'll learn

To take your basic Excel skills to the next level

To gain a solid understanding of the more powerful features and functions in Excel

To be able to use Microsoft Excel in a commercial environment

Requirements

Microsoft Excel 2013, Windows 8, 7, Vista or XP

Description

Learn the expert features and functions in Microsoft Excel 2013, alongside our expert author, to help you get you beyond the basics and have advanced-level proficiency within the Excel 2013 suite Whilst in this course we’ll cover the basics of Excel, including SUM, MIN, MAX and other conditional statements, we’ll quickly move onto more advanced-level topics including: IF statements VLOOKUP and HLOOKUP Sparklines Pivot Tables and Charts Macro Operations The Ability to Master Excel By the completion of this computer based advanced Excel 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. With the ability to handle advanced tasks in Excel, you’ll be able to get more power out of your worksheets and be able to dramatically shorten your task times and increase your efficiency in the process. Content and Overview Suitable for beginners with Microsoft Excel (as we’ll cover the basics early on) but also for those who wish to cover the advanced topics in Excel. Through the course of 128 lectures and 10+ hours of content, you’ll learn all of the basic and advanced items of Excel, which will enable you to use Excel expertly within in a personal or commercial environment. Complete with working files and code samples, you’ll be able to follow along with the author throughout the lessons, and will receive a verifiable certificate of completion upon finishing the course.

Overview

Section 1: Getting Started

Lecture 1 Working Files - Download These First

Lecture 2 0101 Will I Be Able To Keep Up?

Lecture 3 0102 Using The Included Files

Lecture 4 0103 New For 2013: Its All In The Cloud

Lecture 5 0104 The Very Clever Flash Fill

Section 2: Using Mathematical Functions

Lecture 6 0201 The Basic SUM, COUNT, MIN, And MAX

Lecture 7 0202 AVERAGE, MODE, MEAN, And MEDIAN

Lecture 8 0203 SUMIF For Selective Adding Up

Lecture 9 0204 COUNTIF For Selective Counting

Lecture 10 0205 AVERAGEIF For The Mean Of Selected Cells

Lecture 11 0206 Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF

Lecture 12 0207 Area And Volume Calculations

Section 3: IF Functionality

Lecture 13 0301 IF Syntax And Uses

Lecture 14 0302 Nesting The IF Statement

Lecture 15 0303 Use Of The AND Operator Within An IF

Lecture 16 0304 Use Of The OR Operator Within An IF

Lecture 17 0305 The NOT Operator Within AND And OR Statements

Lecture 18 0306 Display Cell Formulas In Another Cell

Section 4: Performing Data Lookups

Lecture 19 0401 VLOOKUP: Syntax And Usage

Lecture 20 0402 VLOOKUP In Live Action

Lecture 21 0403 HLOOKUP: Variation On A Theme

Lecture 22 0404 Using A Near Match In The Lookup

Lecture 23 0405 Dealing With Missing Data In A Lookup

Lecture 24 0406 Managing The Lookup Table

Lecture 25 0407 Lookups Nested Within Lookups

Section 5: Sparklines

Lecture 26 0501 Creating A Sparkline

Lecture 27 0502 Altering The Design Of Sparklines

Lecture 28 0503 Dealing With Empty Cells

Lecture 29 0504 Comparing Sparklines Within A Sparkline Group

Lecture 30 0505 Removing Sparklines From A Worksheet

Section 6: Further Mathematical Functions

Lecture 31 0601 Working With Time In Excel

Lecture 32 0602 Calculations Using Time

Lecture 33 0603 Useful Time And Date Functions

Lecture 34 0604 Rounding Decimal Places

Lecture 35 0605 MOD And INT Functions And Uses

Lecture 36 0606 Generate And Use A Random Number

Lecture 37 0607 Loan And Investment Calculations

Lecture 38 0608 Loan Calculation Elements And Functions

Section 7: Outlining

Lecture 39 0701 Create An Outline Automatically

Lecture 40 0702 Adding An Outline Manually

Lecture 41 0703 Editing And Removing Outlining

Section 8: Scenarios

Lecture 42 0801 Setting Up A Set Of Scenarios

Lecture 43 0802 Displaying And Editing The Different Scenarios

Lecture 44 0803 How To Work Out Which Scenario You Are Displaying

Lecture 45 0804 Merging And Deleting Scenarios

Lecture 46 0805 Producing A Summary Of Scenarios

Section 9: Custom Views

Lecture 47 0901 Custom Views Explained

Lecture 48 0902 Use Of Outlining To Help Setup Custom Views

Lecture 49 0903 Editing And Deleting Custom Views

Lecture 50 0904 Add Quick Access To Custom Views

Section 10: Functions For Manipulating Text

Lecture 51 1001 LEFT And RIGHT: Text Manipulation

Lecture 52 1002 LEN And TRIM: String Extractions

Lecture 53 1003 FIND And MID: Text Functions Working Together

Lecture 54 1004 CONCATENATE: Building Strings From Multiple Cells

Lecture 55 1005 Changing Case Functions

Lecture 56 1006 REPLACE And SUBSTITUTE: Two More String Manipulation Functions

Lecture 57 1007 Use Of CHAR Function For More Obscure Characters

Lecture 58 1008 Formatting Numeric And Date Values Using TEXT

Lecture 59 1009 Keeping The Values Created By String Manipulation

Section 11: Arrays

Lecture 60 1101 Arrays And Creating A New Array Formula

Lecture 61 1102 Array Formulas With IF Statements

Lecture 62 1103 Conditional Evaluation With No IFs

Lecture 63 1104 The Array-Only TRANSPOSE Function

Section 12: Useful Data Functions

Lecture 64 1201 Using The MATCH Function

Lecture 65 1202 How The INDEX Function Works

Lecture 66 1203 Handling Out Of Range Index Requests

Lecture 67 1204 The CHOOSE Lookup Function

Lecture 68 1205 MATCH And INDEX Functions Working Together

Section 13: Some Other Useful Functions

Lecture 69 1301 Introducing IS Functions

Lecture 70 1302 Error Checking Using ISERR, ISERROR, And IFERROR

Lecture 71 1303 OFFSET Function Syntax

Lecture 72 1304 OFFSET Function: Creating A Dynamic Named Range

Lecture 73 1305 INDIRECT Function To Build Dynamic Formulas

Lecture 74 1306 Dealing With INDIRECT Errors

Lecture 75 1307 The CELL Function And Determining File Or Sheet Names

Section 14: Auditing And Troubleshooting Formulas

Lecture 76 1401 What Are Tracer Arrows

Lecture 77 1402 Adding And Removing Tracer Arrows

Lecture 78 1403 Auditing Tools: Error Checking And Tracing

Lecture 79 1404 Step-By-Step Formula Processing

Lecture 80 1405 Using The Watch Window In Troubleshooting

Section 15: PivotTables

Lecture 81 1501 What Is A PivotTable?

Lecture 82 1502 The New Recommended PivotTable Route

Lecture 83 1503 Creating Your Own PivotTables

Lecture 84 1504 Changing The Formatting And Formulas In PivotTable Summaries

Lecture 85 1505 Creating Multiple PivotTables On The Same Dataset

Lecture 86 1506 Moving And Deleting PivotTables

Lecture 87 1507 Making Use Of The Report Filter Options

Lecture 88 1508 Sorting The PivotTable Columns

Lecture 89 1509 Refreshing A PivotTable

Lecture 90 1510 Drilling Down Behind The Pivot Numbers

Lecture 91 1511 Multiple Fields In Row, Column, Or Data Sections

Lecture 92 1512 Controlling Grand Totals And Subtotals

Lecture 93 1513 Dealing With Empty Cells And Other Additional Options

Lecture 94 1514 PivotTable Styles

Lecture 95 1515 Creating Your Own PivotTable Styles

Lecture 96 1516 Creating And Using Calculated Fields

Lecture 97 1517 Using The New Timeline Filter Option

Lecture 98 1518 Adding And Using The Data Slicer

Lecture 99 1519 Using Data From An SQL Server In A PivotTable

Lecture 100 1520 Managing The External Connection To SQL Server

Section 16: PivotCharts

Lecture 101 1601 Creating A PivotChart

Lecture 102 1602 Changing The Fields Used In A PivotChart

Lecture 103 1603 Formatting The PivotChart

Lecture 104 1604 Changing The PivotChart Type

Lecture 105 1605 Filtering A PivotChart

Lecture 106 1606 Hiding The PivotChart Buttons

Lecture 107 1607 Moving And Deleting PivotCharts

Section 17: Goal Seek And Solver

Lecture 108 1701 What-If Analysis Using Goal Seek

Lecture 109 1702 Activating The Solver Add-In

Lecture 110 1703 Using Solver To Complete A What-If

Lecture 111 1704 Adding Constraints To Solver

Section 18: Macros

Lecture 112 1801 What Is A Macro

Lecture 113 1802 Creating And Running Your First Macro

Lecture 114 1803 Saving Workbooks With Macros

Lecture 115 1804 Macro Security Settings For Workbooks With Macros

Lecture 116 1805 The Personal Macro Workbook

Lecture 117 1806 Deleting Macros

Lecture 118 1807 Use Of Relative Or Absolute Referencing

Lecture 119 1808 Trigger A Macro With A Keyboard Shortcut

Lecture 120 1809 Formatting With A Macro

Lecture 121 1810 Switch Scenarios And Views With Macros

Lecture 122 1811 Use Of Worksheet Buttons To Trigger Macros

Lecture 123 1812 Customizing Form Buttons And Other Shape Triggers

Lecture 124 1813 Assigning Macros To Ribbon Icons

Lecture 125 1814 Create Your Own Ribbon

Lecture 126 1815 View And Edit Macro Code

Lecture 127 1816 Add An Are You Sure Box To Macros

Section 19: Summary And Credits

Lecture 128 1901 Course Summary: What Have We Learned In This Course

Users with a basic knowledge of Excel,Users who have completed our Beginners / Intermediate Excel title