New Posts
Home / Computer 4 Beginners / Microsoft Excel Data Analysis Tools

Microsoft Excel Data Analysis Tools

Learning Microsoft Excel Data Analysis Tools.

This article illustrates the Microsoft Excel Data Analysis Tools. Those tools provide the ability to analyze data in a powerful skill and that helps you make better decisions.

Microsoft Excel Data Analysis Tools

  • Using Goal Seek.
  • Using Solver.
  • Using Scenario Manager.
  • Using data tables.

Using Goal Seek.

In the lesson of Microsoft Excel Data Analysis tools first, we will learn Goal Seek.

The goal means purpose or task.

Seek means find or search.

If you are using excel formulas and it gives you answers sheets rather not have you can turn off features off which is called Goal Seek.

As the definition tells that Goal means Purpose or task and Seek means to find or to know.

Here is the example:

An Institute has 50 Students and each student pays monthly fee 1000 and the total amount will be 50000.

T.STUDENTS        50
FEE                       1000
T.AMOUNT        50000

Now here is the query If Institute wants to earn per month 200000, so, how many students will Institute get.

Select the Data tab.

Locate the Forecast Tools group.

Click the What-If Analysis command.

A list of three options appears.

1 Using Goal Seek

2 Using Goal Seek
Ok.

3 Using Goal Seek

Using Solver.

In the previous lesson, we have seen goal seek tool where we changed the result of formula by changing the formula components one of the cells contributed.

A most sophisticated version of  the goal seek is called solver and untimely very sophisticated we have worksheet here namely ABC and you see there is total.

The total is what’s happening in many rows.

File

Options

4 Using Solver

5 Using Solver

Suppose: We have to use Gift offer. Offer is used only 2000/-, but we do not know which item we should buy and what quantity will be for solving this problem we have use solver command.

1.       First of all, type following data.

2.       Noted: Quantity and Cost should be multiple than the Total amount should also be the auto sum.

6 Using Solver

3.        Now keep the mouse over the D6 Cell address as you see in above image.

4.       Data

5.       Solver

7 Using Solver

8 Using Solver

Using Scenario Manager.

Definition of Scenarios here in this situation.

I want to give here Offer on Discount on different events/Exhibition.

9 Using Scenario Manager

1 select Dis column.

2 Data

2 What-If Analysis

4 Scenarios  Manager

10 Using Scenario Manager

11 Using Scenario Manager

12 Using Scenario Manager

13 Using Scenario Manager

14 Using Scenario Manager

15 Using Scenario Manager

Add for Diwali.

16 Using Scenario Manager

17 Using Scenario Manager

18 Using Scenario Manager

19 Using Scenario Manager

20 Using Scenario Manager

21 Using Scenario Manager

To see the summary click on Summary button.

Summary.

22 Using Scenario Manager

23 Using Scenario Manager

Using data tables.

Now I tell you how to Data Table in excel.

The formula tells, what net amount will be after deduction of Discount.

24 Using data tables

25 Using data tables

26 Using data tables

The result will be shown now as below image.

27 Using data tables

28 Using data tables

29 Using data tables

30 Using data tables

1.       Data

2.       What-If Analysis

3.       Data Table.

4.       Now you can check any quantity and also Price Row

5.        Example:

6.       In Price, I type and In Quantity 25 then hit enter from the keyboard.

31 Using data tables

 

 

 

 

 

 

About imran

My Name Mohammad Ramzan Bhutto. Live in District Jacobabad, Sindh, Pakistan. I have started blogging on Information Technology, my selected areas are Online Business, Digital Marketing, Make Money Online.

Check Also

Access Database relationships

How to use Microsoft Access Database Relationships.

How to use Microsoft Access Database Relationships. In this article, we will learn Access Database …