Office Productivity
Microsoft
Excel
Learn Advanced Formulas and Data Analysis in Microsoft Excel
0.0 (0 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
0 students enrolled

Learn Advanced Formulas and Data Analysis in Microsoft Excel

Microsoft Excel Intermediate to Advanced Level Tools
New
0.0 (0 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
0 students enrolled
Created by Abraham Eninla
Last updated 7/2020
English
English [Auto]
Preview this course
Learn Advanced Formulas and Data Analysis in Microsoft Excel
Current price: $83.99 Original price: $119.99 Discount: 30% off
5 hours left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 12.5 hours on-demand video
  • 48 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Working with SUM, COUNT, MAX and MIN Functions
  • Understand the POWER of SUMIF, SUMIFS, COUNTIF, COUNTIFS, VLOOKUP, INDEX & MATCH for Data and Financial Analysis
  • Understand the Power of Data Analysis using Microsoft Excel Master how to use PIVOT TABLE and PIVOT CHART to create Dynamic Reports and Charts
  • Working with Images (Pictures, Shapes, Screenshots and Smart Art objects) in Excel
  • Using SUB TOTAL tool for creating Subtotals Calculation for grouped Items
  • Working with Charts (Column, Pie, Bar, Line Chart and lot more. Editing Charts and working on Chart Designs
  • Understand how to handle Large Data, Manipulate, Summarized and Generate Reports
  • Understanding Project Evaluation Techniques using Financial Functions (PV, FV, NPV & IRR)
  • Creating Sensitivity Analysis using the What-If-Analysis Tools (Goal Seek, Data Table, Solver and Scenarios)
  • Working with Logical Functions (IF, AND & OR and NESTING the IF Functions)
  • Combining Periodic or Regional Report using Data Consolidation Tool
  • Validating and Ensuring data entry correctness using the Data Validation Tools and Rules
  • Creating a Simple Dashboard for Data Presentation
  • Creating Links to Worksheets and external files using the Hyperlink Tool
  • Working with Spark line Chart tool for In-Cell Data Analysis and Presentation
  • Introduction to Excel Macros and VBA Automation (Macros Recording, codes, User Form creation, VBA Controls and Objects)
  • Case Study: Creating Income & Balance Statements from Raw data & Consolidating yearly reports.
  • Working with Names and using names in Formulas
  • Working with ranges and creating Tables from range of data
  • Understanding data protection in Excel
  • Using the Collaboration tools (Comments & Workbook sharing)
  • Working with Financial Function - PMT, PPMT & IPMT
  • Working with DATE Functions (EOMONTH, TODAY, NOW, NETWORKDAYS, WORKDAY)
  • Working with DATABASE Functions (DSUM, DCOUNT, DMIN, DMAX & DAVERAGE)
  • Working with SORTING, FILTERING (AutoFilter, CustomFilter etc), and Conditional Formatting Tool.
  • Working with External Data (Learn how to input Text File and Data Table from Web into Excel)
  • Working with Excel In-built Data Entry Form
  • Performing Calculations and consolidating worksheets using 3D Formulas and Functions
Requirements
  • This Course was Created with Microsoft Excel 2016 version. However, participants with Versions 2010, 2013, 2016, 2019 and Office 365 can participate Conveniently.
  • A basic knowledge of Microsoft Excel is required as this course seek to upgrade learners from a beginner level to Advanced users.
  • Students more importantly needs the operational knowledge of a Computer System.
Description

The Microsoft Excel course exposes students to all available tools, commands, and Functions in the application. The training is carefully structured to take care of the learning needs of students, who are really yearning to know how to use Excel to carry out tasks in their workplaces. The course is also prepared to help regular users of the application, who want to upgrade their knowledge and upskill.

The Intermediate topics capture the most frequently used command for day to day tasks. The Advanced level is also to help students learn the most advanced formulas, functions, and Tools. The advanced Excel training course builds on the intermediate course and is designed specifically for spreadsheet users who are already proficient and looking to take their skills to an advanced level.

The advanced excel tutorial will help you start a career in the area of data and financial analysis especially in the following fields; investment banking, private equity, corporate development, and equity research. By watching the instructor build all the formulas and functions right on your screen, you can easily pause, replay, and repeat exercises until you have mastered them.

Who this course is for:
  • Accountants, Financial Analyst, Data Analyst, Human Resource Managers, Project Managers and Trainers
  • Any person who desires to know Microsoft Excel and use it for Day to Day office Task.
  • Regular Microsoft Excel users who seek an upgrade.
Course content
Expand all 45 lectures 12:44:28
+ – Module One - Microsoft Excel Intermediate Lectures
4 lectures 55:53
Working with Names in Excel (Cell and Range Names)
12:46
Working with Table in Excel (Create a Table of Data from Range)
14:20
How to Protect a Worksheet data & Workbook Structure
13:38
How to use the Excel Collaboration Tools (Comments & Workbook Sharing)
15:09

In this Quiz section, students would be tested on Module One topics.

Module One Quiz Section
3 questions
+ – Module Two - Microsoft Excel Charts & Formula Auditing Tools
4 lectures 46:49
Working with Excel Charts - Column and Pie Chart
16:57
Working with Charts - Bar and Line Charts
08:54
Working with Charts - Scatter Chart and Histogram
10:59
Auditing values and Formulas using Trace Precedent and Dependent Commands
09:59

In this section, students would be quizzed on the Module Two Topics.

Module Two Quiz Section
3 questions
+ – Module Three - How to use Excel Functions and References
4 lectures 51:57
Creating Datasets calculations using the Subtotal Tools
05:27
Working with Cell Referencing (with Practical example)
06:34
Understanding the Microsoft Excel Insert Function Tool
20:02
Practical Approach to SUM, MAX, MIN, AVERAGE, COUNT, LARGE & SMALL Functions
19:54

In this Quiz section, students would be tested basically on Excel Functions, Subtotals, and other topics.

Module Three Quiz Section
3 questions
+ – Module Four - Microsoft Excel Functions (Learning Various Functions)
4 lectures 01:04:17
Working with the SUMIF, COUNTIF and AVERAGEIF Functions - Data Analysis Tools
24:56
Understanding the VLOOKUP Function
08:08
Understanding VLOOKUP (further example), & INDEX and MATCH Functions
19:54
Understanding how to use the Excel Logical Functions (IF, AND & OR Functions)
11:19

This quiz is designed to help students ensure that they understand the basis of the Module Four lectures.

Microsoft Excel Function Class Quiz
4 questions
+ – Module Five - Microsoft Excel Functions - Financials, Date, Text & Database
4 lectures 01:11:03
Working with Financial Functions (PMT, PPMT & IPMT) - plus Amortization Schedule
12:03
Working with DATE Functions - for schedules and project management tasks
18:03
Understanding the Excel TEXT Functions
28:16
How to handle Database calculations and analysis using the DATABASE Functions
12:41

This quiz is designed to test student's understanding of Module Five lectures

Microsoft Excel Financial, Date, Text and Database functions Quiz Section
6 questions
+ – Module Six - Working with Data Sorting, Filtering, Formating Data & using Images
4 lectures 01:24:47
How to Sort Data in Excel (Custom Sort & other Sort Criteria)
12:52
How to Filter Dataset in Excel (Custom Filter, Advanced Filter & using Wildcards
28:52
How to Create Conditional Formatting Rules in Excel
29:21
Working with Images (Pictures, Screenshots, SmartArt & Shapes)
13:42

In this quiz section, students will be tested to ascertain their comprehension of module six lectures.

Module Six Quiz Section
3 questions
+ – Module Seven - Working with External Data and using Data Entry Tool
3 lectures 31:57
Importing External data from Text file and Web data into Excel Worksheet
14:29
Working with Excel Data Entry Form - How to use Data Form to enter data
08:23
Learn how to use 3D Formulas and Functions in Excel
09:05

Students will be tested here on the various lectures in the Module Seven

Module Seven Quiz section
3 questions
+ – Module Eight - Advanced Excel Data Consolidation, Analysis & Summary task
4 lectures 01:18:46
Advanced Data Consolidation using SUMIFS, COUNTIFS & AVERAGEIFS (Case Study)
28:49
Combining Periodic or Divisional Reports using the Data Consolidation Tool
12:20
Creating Executive Summary Worksheets using PIVOT Table and Chart
25:45
Working with Slicer and Timeline Tools in Excel
11:52

In this Quiz section, students will show their understanding of the Data Analysis and Consolidation Tools

Module Eight Quiz Section - Data Consolidation and Analysis Tasks
3 questions
+ – Module Nine - Data Visualization, Validation and Financial Analysis
4 lectures 01:09:07
Dashboard Creation (Introduction Level)
21:08
Financial Analysis using PV and FV Functions
10:55
Performing Project Evaluation using NPV and IRR Functions
12:14
Using the Data Validation Tool to set Data entry Rules
24:50

In this Quiz section, students will show their understanding of Module Nine Lectures.

Module Nine Quiz - Data Visualization, Analysis and Financial Analysis Test
3 questions