Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
This Microsoft Excel Advanced Formulas and Functions training course from Infinite Skills takes you beyond the basics of Excel, and teaches you how to use the advanced formulas and functions in this spreadsheet program by Microsoft. This course is designed for users that already have a basic working knowledge of Excel.
You will begin by reviewing the basic operations, such as sum and count syntax, function processing order, and knowing the troubleshooting tools. You will explore extended formula usage with 3D referencing, naming cells and absolute referencing. Guy proceeds to instruct you on how to look up and reference type functions using VLookup and HLookup. Other features that are covered in this computer based training include; generating random numbers and values, locating data using text functions, and learning to use an array formula to count unique entries.
By the completion of this training course, you will be comfortable with using many of the advanced formulas and functions that this powerful spreadsheet software from Microsoft offers. Working files are included, allowing you to follow along with the author throughout the lessons.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Welcome|
Important - Excel - Working Files - Download These FirstPreview
0101 How Advanced Is Advanced?Preview
0102 Using The Excel Working FilesPreview
0103 When Is A Formula A Function?Preview
|Section 2: Getting The Basics Right|
0201 Excel - Sum And Count Syntax
0202 Accessing Unknown Function Syntax Within Excel
0203 Referencing Entire Rows/Columns
0204 Excel - Function Processing Order
0205 Convert Formulas To Values In A Split Second
|Section 3: Extend Your Formula Usage|
0301 Naming Cells To Improve Formula Appearance
0302 Excel - 3D Referencing
0303 Full Absolute Referencing
0304 Making Only Part Absolute Formula
|Section 4: If And If Type Functions|
0401 The If Logical Test
0402 Nested If Statements
0403 Using The And, Or, And Not Functions Within If
0404 CountIf, SumIf, And AverageIf
0405 Multiple Criteria With CountIfs, SumIfs, And AverageIfs
|Section 5: Lookup And Reference Type Functions|
0501 Looking Up Information With VLookup
0502 Excel - HLookup
0503 Nearest Match With VLookup
0504 Nested Lookup Functions
0505 Choose And Match Functions For Locating Data
0506 Index And Match Functions For Retrieving Data
|Section 6: Mathematical Functions|
0601 Varying Rounding Functions
0602 MRound, Ceiling, And Floor For Rounding Also
0603 Int And Mod For Integer And Remainder Values
0604 Generating Random Numbers And Values In Excel
0605 Password Generating Workbooks
0606 Display Different Number Types With Roman And Arabic
|Section 7: Statistics|
0701 The Middle Value With Median
0702 Ranking Data Without Sorting
0703 Large And Small For Retrieving Biggest And Smallest Values
0704 A Choice Of Count Functions
|Section 8: Text Functions|
0801 Extracting Data With Left, Right And Mid
0802 Locating Data With Find, Search, Exact
0803 Use Trim And Clean To Remove Unwanted Gaps
0804 Combine Data From Different Cells
0805 Changing The Case Of Text Using Proper, Upper, And Lower
0806 Replace And Substitute In Action
0807 Format Number Content Within Strings With Text
|Section 9: Date And Time Functions|
0901 Using Today And Now Functions
0902 How Does Excel Deal With Dates And Time
0903 What Day Of The Week Is It?
0904 Difference Between Network Days And Work Day
0905 DatedIf For Time Differences
0906 EDate And EOMonth
0907 DateValue And TimeValue Functions
|Section 10: Information And Referencing Functions|
1001 Extracting Information With Cell And Info Functions
1002 Using IsBlank, IsOdd, IsEven, IsText, And IsNumber
1003 IsErr, IsError, IfError, IsNA, And IfNA For Error Checking
1004 The New IsFormula Function And FormulaText
1005 Use Offset To Obtain Data From Related Cells
1006 InDirect And Its Uses
|Section 11: Array Formulas And Functions|
1101 What Is An Array Formula?
1102 Use An Array Formula To Count Unique Entries
1103 Flipping Rows Or Columns With Transpose
|Section 12: Conclusion|
1201 Excel - Advanced Formula Summary
Infinite Skills is a Canadian based company who offer a huge and ever growing range of high quality eLearning solutions that teach using studio quality narrated videos backed-up with practical hands-on examples and comprehensive working files. All courses are created by trained educators and experts in video based education.
The emphasis is on teaching real life skills that are essential for progressing in today's commercial environment.