What you'll learn:
- How to use Excel Functions to speed up activities
- How to use Excel Formulas to automate calculations
- How to use Pivot Tables to sort and filter big data
- How to Impress your boss with the use of advanced SUMIFS,COUNTIFS,VLOOKUP,HLOOKUP,LOOKUP,INDEX and MATCH and lots more
- How to create reports
- How to validate excel data and trace formulars
- How to nest the Right, Mid and Left functions to subset text strings in excel
- How to automate excel functions and formulas
- How to dynamically use cell locking
- How to create awesome charts and graphs in Excel
- How to create dashboards in Excel
- How to secure your dashboard in Excel
- How to clean dataset in excel
- Life-time access to professional trainer from prolearnit
- How to analyze and make optimal decisions among competing projects.
this advanced Microsoft excel course will teach you how to use advanced excel formulas and functions to solve business problems. This course is very detailed and it is designed for users who already have a basic knowledge of Microsoft excel. This course uses practical real-life datasets to explains what, why and when we use a particular excel function or formula as well as showing you how to use the function or formula. You will have access to the Excel spreadsheets used in this course so that you can follow along with the course instructor.
At the end of this course, you will have learned:
· How to validate your dataset.
· How to use the logical statements to query excel dataset.
· How to effectively use excel pivot tables to handle large dataset so as to provide answers to complex business problems.
· Dynamic Cell locking
· What, when, why and how to use Excel functions or formulas to automate task in excel.
· How to analyze and make optimal decisions among competing projects.
· How to clean big data set.
You will begin by revising the basic Microsoft Excel paste special options and operations, as well as the transpose option. You will also revise how to use excel to automatically fill series in a spreadsheet. You will explore and fully understand how and when to use the cell locking options to lock an entire column, row, or just a cell in your spreadsheet. You will also learn how to create and when to use named ranges to reference an entire row, column or spreadsheet in Excel. You will learn how to audit a formula, filter and use the subtotal Excel functions. You will be taught the difference between the subtotal function and the sum function; you will also learn why learn when and why to use the subtotal function in place of the sum function. You will explore the Excel Lookup, Vlookup and Hlookup functions indepth. You will learn the difference between the True and False Match in Excel Vlookup function and when to they are to be used. You will learn how to use the Excel index and match functions as well as how to nest them and query a large dataset. You will Explore everything you need to know about using excel functions and formulas to trim, deduplicate, substitute and clean big data for further processing. You will learn all you need to know and how to use the left, right and mid functions in excel. The find and length functions are extensively treated in this course. You will explore how to work with nesting the find, length, left, right and mid functions to query large datasets.
You will also learn how to format texts using excel functions, this course will show you how you can concatenate datasets and protect your dataset using data validation. You will become familiar with the Excel advanced logical countifs and sumifs functions to query datasets. You learn everything you need to know about how to use logical conditions like ifs and nested ifs to sort and query datasets. This Course also extensively explores the if and or/and functions to sort and query dataset.
This Course will teach you all you need to know about goal seek in other to carry out project evaluation and make optimal decisions.
This course Extends into teaching you how to activate Microsoft Excel Pivot Table. You will also learn how to use the Microsoft Excel pivot tables to also filter and sort datasets in excel, and we will teach you how they compare with the various excel functions and formulas already discussed. You will understand how to query your datasets in Microsoft Excel using the pivot table options, you will learn all you need to know about the various value options and how to sort dataset in pivot tables. You will explore the how to use the calculated fields options in pivot tables and how to slice datasets and use timelines in excel.
