Microsoft Excel 2010: Advanced Training

Course Description

A Verifiable Certificate of Completion is presented to all students who undertake this Excel course.

This Excel 2010 advanced training course, follows up from the Beginners Excel 2010 course by delving even deeper into the features and functions of this powerful spreadsheet software.

Microsoft Excel 2010 is much more than a quick way to add up numbers. In this online course, you will learn advanced features such as nesting IF statements, how to calculate the Mean of a group of numbers using AVERAGEIF, SUMIF and more. You will learn about performing lookups with HLOOKUP and VLOOKUP. This tutorial also covers Sparklines, and goes in-depth with Pivot Tables and Charts. Finally, you will learn how to create and record your own Macros.

This advanced Excel video course is not for beginners. You should have a firm grasp of the basics before taking this Advanced Excel training course. By the conclusion of this advanced computer software tutorial for Microsoft Excel 2010, you will have mastered the advanced features and functions of this software. Working files are included to allow you to follow along with the same Excel files the author trains you with.

Full details

What am I going to get from this course?

• Over 135 lectures and 8.5 hours of content!
• Learn Advanced Microsoft Excel 2010 from a professional trainer from your own desk.
• 136 lectures (8.5 hours of content) teaching you lookups, advanced IF statements, Macros, Sparklines, and much more!
• Suitable advanced Excel users. ideal for users who learn faster when shown.
• Visual training method, offering users increased retention and accelerated learning.
• Breaks even the most complex applications down into simplistic steps.

Curriculum

Section 1: Getting Started With This Excel Course
	Lecture 1	Working Files - Download These First	Text
	Lecture 2	How Advanced Does The Advanced Get? Preview	04:57
	Lecture 3	Using The Included Excel Files Preview	01:41
	Lecture 4	New In Excel 2010 - The Sparkline Preview	03:36
Section 2: The Excel IF Function
	Lecture 5	The Syntax Of IF Preview	06:02
	Lecture 6	Nesting The IF Statement In Excel Preview	06:45
	Lecture 7	Use The AND Operator To Reduce Quantity Of Nested IFs Preview	04:09
	Lecture 8	Use The OR Operator To Reduce Quantity Of Nested IFs Preview	03:17
	Lecture 9	The NOT Operator Within AND And OR Statements Preview	02:47
	Lecture 10	SUMIF For Selective Adding Up Preview	05:04
	Lecture 11	COUNTIF For Selective Counting Preview	03:52
	Lecture 12	AVERAGEIF For The Mean Of Cells That Meet Our Criteria Preview	02:34
	Lecture 13	Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions Preview	07:17
Section 3: Performing Lookup in Excel
	Lecture 14	VLOOKUP Explained Preview	03:43
	Lecture 15	Applied Examples for VLOOKUP Preview	10:03
	Lecture 16	HLOOKUP Explained Preview	03:01
	Lecture 17	HLOOKUP In Action Preview	06:37
	Lecture 18	Looking For A Near Match In A Lookup Preview	04:51
	Lecture 19	Checking For Missing Data In A Lookup Preview	05:45
	Lecture 20	Extending The Size Of A Lookup Table Preview	04:14
	Lecture 21	Nested LOOKUPs In Excel Preview	04:28
Section 4: Excel Data Functions
	Lecture 22	The MATCH Function Explained	08:14
	Lecture 23	The INDEX Function Syntax	05:06
	Lecture 24	How To Stop Nonexistent Row Or Column Lookups In INDEX	02:41
	Lecture 25	The CHOOSE Lookup Function	02:37
Section 5: Excel Math Functions
	Lecture 26	Working With TIME	05:55
	Lecture 27	Rounding To Fractional Values	03:35
	Lecture 28	MOD For Working Out Remainders	02:18
	Lecture 29	Generating A Random Number	02:33
	Lecture 30	Pick A List Item At Random	03:05
	Lecture 31	Calculating Loan Repayments Using PMT	02:55
	Lecture 32	Excel - Investment Calculations Using PMT	02:59
	Lecture 33	Working Out Depreciation	04:21
	Lecture 34	Working Out Different Parts Of A Loan Calculation	04:58
Section 6: Arrays In Excel
	Lecture 35	What Is An Array And An Array Formula	03:00
	Lecture 36	Creating And Using An Array Formula	03:29
	Lecture 37	Conditional Evaluation In An Array Formula	03:42
	Lecture 38	The Very Clever TRANSPOSE Array Function	03:32
Section 7: Excel Functions For Working With Text
	Lecture 39	LEN And TRIM Two Very Useful Text Functions	02:44
	Lecture 40	Using LEFT And RIGHT For String Extraction	03:36
	Lecture 41	FIND And MID Working Together To Extract Parts Of Strings	04:15
	Lecture 42	Build Strings From Multiple Cells	03:58
	Lecture 43	Excel - Changing The Case Of Text In Cells	02:56
	Lecture 44	REPLACE And SUBSTITUTE In Action	05:12
	Lecture 45	Formatting Numeric Values With A Text String Using TEXT	05:09
	Lecture 46	Extracting The Values From The Text Functions We Have Used	03:00
Section 8: Other Useful Excel Functions
	Lecture 47	Welcome To IS Functions	02:45
	Lecture 48	Error Checking With ISERR ISERROR ISNA And IFERROR	08:06
	Lecture 49	The OFFSET Formula Explained	03:17
	Lecture 50	Dynamic Named Ranges Using The OFFSET Function	05:48
	Lecture 51	Use The INDIRECT Function To Build Dynamic Formulas	05:59
	Lecture 52	Dealing With INDIRECT Errors	01:46
	Lecture 53	Use Formulas To Determine An Excel Filename And Or Sheet Name	05:43
Section 9: Sparklines
	Lecture 54	Creating A Sparkline In Excel	03:06
	Lecture 55	Change The Design Of Sparklines	03:38
	Lecture 56	Dealing with Empty Cells	01:57
	Lecture 57	Comparing One Sparkline To Another by Altering Vertical Scale	02:57
	Lecture 58	Removing Sparklines From A Sheet	01:35
Section 10: Outlining
	Lecture 59	Outlining Explained	02:35
	Lecture 60	Creating An Outline Automatically	02:34
	Lecture 61	Creating An Outline Manually	02:58
	Lecture 62	Manually Removing Data From An Outline	03:25
	Lecture 63	Removing The Outlining From A Worksheet	00:55
	Lecture 64	Adjusting A Grouping Created By Automatic Outlining	03:54
Section 11: Custom Views In Excel
	Lecture 65	Creating A Custom View Of A Worksheet	02:48
	Lecture 66	Changing From One Custom View To Another	01:34
	Lecture 67	Editing A Custom View	01:14
	Lecture 68	How To Delete A Custom View	01:20
Section 12: Scenario
	Lecture 69	Setting Up A Scenario And Entering Values	04:17
	Lecture 70	Display The Scenario Values	02:01
	Lecture 71	Editing The Values Of A Scenario	01:32
	Lecture 72	Deleting A Scenario	01:06
	Lecture 73	Merge Scenarios From Different Sheets	02:13
	Lecture 74	Getting A Summary Of All Scenarios	04:28
Section 13: Auditing And Troubleshooting Formulas
	Lecture 75	Description Of Tracer Arrows	01:00
	Lecture 76	Tracing Precedents And Dependents	02:56
	Lecture 77	Remove Tracer Arrows	01:54
	Lecture 78	Error Checking Using Auditing Tools	04:23
	Lecture 79	Step By Step Processing Of Formula To Help With Troubleshooting	02:55
	Lecture 80	Excel - Utilizing The Watch Window	03:48
Section 14: Pivot Tables In Excel
	Lecture 81	What Is A Pivot Table	04:19
	Lecture 82	Steps To Create A Pivot Table In Excel	06:56
	Lecture 83	Rearranging Fields In A Pivot Table	03:50
	Lecture 84	Changing The Math Of The Data Summary	03:03
	Lecture 85	Number Format Control Of The Summary Area	03:17
	Lecture 86	Creating A Second (Or More) Pivot Table On The Same Data	03:01
	Lecture 87	Moving A Pivot Table	03:05
	Lecture 88	Removing A Pivot Table	01:55
	Lecture 89	Making Use Of The Report Filter Option	05:47
	Lecture 90	Sorting A Pivot Tables Columns	03:30
	Lecture 91	Displaying Values As A Percentage	05:00
	Lecture 92	Refreshing A Pivot Table Manually Or Semi-Automatically	02:56
	Lecture 93	Drilling Down Behind The Pivot Table Summaries	04:06
	Lecture 94	Applying Pivot Table Styles	03:14
	Lecture 95	Creating Your Own Custom Pivot Table Style	03:39
	Lecture 96	Copying A Pivot Table Style Between Workbooks	02:13
	Lecture 97	Using More Than One Field In Row And Column Headings	05:01
	Lecture 98	Disabling And Enabling Grand And Sub Totals	05:53
	Lecture 99	Filtering Columns And Rows Within A Pivot Table	06:08
	Lecture 100	Dealing With Empty (NULL) Cells	02:25
	Lecture 101	Exploring The Additional Pivot Table Options	04:07
	Lecture 102	Introducing The Slicer Tool	03:54
	Lecture 103	Managing Your Slices	03:25
	Lecture 104	Formatting Your Slices	03:07
	Lecture 105	Connecting A Pivot Table To SQL Server	05:27
	Lecture 106	External Connection Refresh Rate And Password Saving	02:20
Section 15: Excel Pivot Charts
	Lecture 107	Creating A Pivot Chart	04:26
	Lecture 108	Altering Chart Types Formats And Layouts	03:10
	Lecture 109	Advanced Layout Control Of A Pivot Chart	04:25
	Lecture 110	Filtering A Pivot Chart	03:14
	Lecture 111	Hiding Pivot Chart Elements	02:24
	Lecture 112	Moving A Pivot Chart Between Sheets	02:57
	Lecture 113	Deleting A Pivot Chart (With Care)	02:40
Section 16: Goal Seek And Solver
	Lecture 114	Using Goal Seek To Carry Out What If Analysis	07:17
	Lecture 115	Using SOLVER To Carry Out What if Analysis	05:23
	Lecture 116	Excel - Activating The SOLVER Add In	02:13
	Lecture 117	Add Constraints Into A SOLVER Problem	05:25
	Lecture 118	Alberts Cafe Solver Solution	08:13
Section 17: Macros In Excel
	Lecture 119	What Is A Macro	02:35
	Lecture 120	Creating Storing And Running Your First Macro	05:29
	Lecture 121	Using Relative Or Absolute Referencing During Recording	03:53
	Lecture 122	Saving Workbooks With Macros Issues	02:49
	Lecture 123	Opening Files Containing Macros	04:11
	Lecture 124	The PERSONAL Workbook	01:21
	Lecture 125	How To Delete Macros	02:46
	Lecture 126	Use A Macro For Formatting	02:36
	Lecture 127	Trigger A Macro With A Keyboard Shortcut	03:16
	Lecture 128	Using Form Buttons To Trigger Macros	02:42
	Lecture 129	Customizing The Form Buttons In Excel	03:15
	Lecture 130	Assigning Macros To Ribbon Icons	03:17
	Lecture 131	Create Your Own Ribbon	03:17
	Lecture 132	Remove Options From Ribbons	02:00
	Lecture 133	View And Edit Macro Code	03:54
	Lecture 134	Add A Confirmation Dialog Box To Macros	04:05
	Lecture 135	How To Get The Excel Working Files	00:16