Business Data Analysis Using Excel

categories: Technology, Science & Productivity

MS Office

Analytics

MS Excel

SDG 9: Industry, Innovation and Infrastructure

By the end of this course, you will be able to

Understand the basics of Excel and how to use it to manage and analyze data. Learn about advanced Excel functions such as INDIRECT, SUBSTITUTE, IF, AND, OR, COUNTIF, SUMIF, VLOOKUP, INDEX, and MATCH.
Understand the principles of conditional formatting and shading in Excel, as well as the use of pivot tables and pivot charts. • Analyze data, use techniques to find patterns and trends in data, and design executive reports based on specific criteria.
Use advanced functions and skills learned in Excel to solve data analysis problems and tasks in a real-world business setting. Designs effective reports and graphs that contribute to decision-making.
Recognize everything related to macros, and you can build your own analysis system using Excel.

Course details

3 h 21 m/49 lessons
Last updated: 4/3/2019
4 Quiz
1 learning resources
Course completion certificate

Course Content

Free lessons

Understanding How IF Function Works And Nested IF

4 Minutes

Using OR, AND Functions With IF Function.

4 Minutes

Quizzes

Introduction To VLOOKUP Function.

4 Minutes

Text To Columns

2 Minutes

1. Business Data Analysis Using Excel

Defined Names

6 Minutes

learning resources

Name Manager - Create from Selection.

3 Minutes

Using INDIRECT Function to Extract Items from two-way lookup Tables

5 Minutes

Using SUBSTITUTE Function To Handle The Name Errors With INDIRECT Function.

2 Minutes

Relative, Absolute And Mixed Cell Reference.

5 Minutes

Example: Mixed Cell Reference

1 Minutes

Basic Function

6 Minutes

How The Excel Understand The Logical Test

5 Minutes

Understanding How IF Function Works And Nested IF

4 Minutes

10.

Example: Nested IF Function

2 Minutes

11.

Using OR, AND Functions With IF Function.

4 Minutes

Quizzes

12.

Example: AND,OR Together.

3 Minutes

13.

Build The First Report With COUNTIF Function With Chart.

5 Minutes

14.

Build The Second Report With SUMIF Function With Chart

3 Minutes

15.

Using SUMIFS Function To Build A Report Depend On Two Criteria.

5 Minutes

16.

Enhancement The Chart To Read It Effectively

2 Minutes

17.

Example: Extract The Report In New Sheet Using SUMIFS Function.

7 Minutes

18.

Introduction To VLOOKUP Function.

4 Minutes

19.

Problem Solving For VLOOKUP Using COUNTA Function.

4 Minutes

20.

Problem Solving For VLOOKUP Using COLUMNS Function.

2 Minutes

21.

Problem Solving for VLOOKUP using IF Error Function to Select from 2 Tables

3 Minutes

22.

Problem Solving For VLOOKUP Without Zero.

3 Minutes

Quizzes

23.

Using INDEX & MATCH Functions To Extract Items From Two-Way Lookup Tables.

5 Minutes

24.

Use Multiple Functions To Compare Two Lists Part #1

5 Minutes

25.

Use Multiple Functions To Compare Two Lists Part #2

2 Minutes

26.

Using the Search Function With the IF Function to Find Any Data

2 Minutes

27.

Problem Solving For Left Function Using Search Function.

2 Minutes

28.

Text To COLUMN And CONCATENATE Function

3 Minutes

29.

Text To Columns

2 Minutes

30.

Flash Fill

1 Minutes

31.

Conditional Formatting Techniques

11 Minutes

32.

Highlight All Rows Based On One Criteria.

4 Minutes

33.

Highlight All Rows Based On Two Criteria

4 Minutes

Quizzes

34.

Pivot Tables And Pivot Chart In Depth

4 Minutes

35.

Changing The Method Of Aggregation In A Pivot Table

2 Minutes

36.

Top 10 Analysis With Excel Pivot Tables

2 Minutes

37.

Show Value as Percentages in Excel Pivot Tables

2 Minutes

38.

PivotTable For Yearly Percentage Change Calculations.

3 Minutes

39.

How Is The Advanced Filter Works?

4 Minutes

40.

Using Logical Test To Extract Advanced Report From Advanced Filter.

5 Minutes

41.

Using ISNUMBER & SEARCH Function in Advanced Filter.

2 Minutes

42.

Find Top Five Total Number

2 Minutes

43.

Find Less Five Total Number

2 Minutes

44.

Using Wildcards In Advanced Filter

2 Minutes

45.

All About The Macros

6 Minutes

46.

How To Run The Macros

2 Minutes

47.

Record Macro For Advanced Filter

4 Minutes

48.

Step By Step To Build Your System Analysis Part#1

4 Minutes

49.

Step By Step To Build Your System Analysis Part#2.

5 Minutes

Quizzes

About this course

Microsoft Excel is one of the most popular spreadsheet programs of all time. This course will allow you to master Excel and gain data analysis and management skills. We will start by teaching you the basics of Excel and help you develop skills in managing names and creating complex formulas using functions such as INDIRECT and SUBSTITUTE. We will also delve into how to use IF functions AND and OR to make informed decisions based on data. In addition, you will learn how to design complex reports using Excel arithmetic functions such as COUNTIF and SUMIF, and you will understand how to analyze data using VLOOKUP, INDEX, and MATCH. Additionally, we'll demonstrate conditional formatting techniques and how to shade data effectively. By the end of this course, you will gain the skills to build your own analysis system and this will allow you to make the most of data and make accurate business decisions. Join us and prepare for a unique journey into the world of data analysis using Excel!