What If Analysis In Excel, And How Goal Seek,Data Table and Scenario Manager Work?

Before starting know about what if analysis in excel and how goal seek, data table and scenario manager work?   We must have the knowledge of what does what if analysis means. In this tutorial I will teach you what is what if analysis and how its work. I hope that will you really enjoy this.

What is what if analysis?

It is the process of changing the value which you want to put in you data then you see how changes will affect the outcome formula on the spreadsheet.

How many tools are available in excel to perform what if analysis?

There are three types of different tools in excel to perform in what if analysis:-
1) Goal Seek
2) Data Table
3) Scenario Manager

1) Goal seek – Goal seek is the part of what if analysis. It is the ability to calculate backward to obtain an input data that he given output data. Below image shows that how to use goal seek and its work.

Step 2

Click on ok button and you see Mahesh Mishra total number will increase 136 to 150 and his Math Number will increase 14 to 28.Below image shows that when we press ok button see change the data.

2) Data Table – It also a part of what if analysis. It is the range of the cell that shows the changing of one variable or two variables put in your formula affect the result. See how to use data table and its work.

First select the range then go to data table option, In Row Input Cell click the principle amount and Column Input Cell click the rate or time period cell and press ok. Below image shows that how its work?
Step 1

Step2

 Image 2 Bank Loan analysis in Data Table

The Simple Interest formula is Principle *Rate*Time/100. Always remember when you start the data table first select the range and follow the above given description.

3) Scenario Manager- It is a set of value that Microsoft excel saves can substitute automatically in cells or excel spreadsheet. In simple suppose you create our three month budget sheet in three different sheet and you want to see all data in your master worksheet, In that case you use scenario manager. Below image shows that How to use Scenario Manager?

Step 1- Below image shows that a person whose budget sheet before is Promotion.

Step 2- Below image shows that a person whose budget sheet after is Promotion.

Step 3- Below image shows that a person whose budget sheet when he earn salary or extra income and his increase expense.

Final Step- Final step mean that you create our three month budget sheet in three different sheet and you want to see all data in your master worksheet, in below image you see the whole date in your master worksheet.

Through the video you can better understand to given all description about this tutorial.

In this article you have studied about  what if analysis in excel and how goal seek, data table and scenario manager work?. If you like this tutorial doesn’t forget to like us on Facebook and also follow me on twitter. And if you find anything missing than let me know through your commenting.

People Also Read : How to add input message, error alert, invalid data in data validation?
People Also Read : How to load Analysis Toolpak in Excel 2010?

Continue Reading →

Basic Consolidate in Excel by Using Position

Before starting to know about basic consolidate in excel by using position, we must have the knowledge of what does consolidate means, In this tutorial you will go to learn consolidate, its work and when we use consolidate in excel. I hope that will you really enjoy this.

What is consolidate in excel?

If you want to combine your data from several worksheet into a single worksheet, in this situation you use consolidate option. In simple consolidate combine your data multiple into one.

How many ways to consolidate data?

There are two ways to consolidate your data by category or by Position, But you will learn about by position in this tutorial.

Consolidate by position- when the each range of the data in the list format and there are no blanks row and column within the list. Make sure all data separate on your worksheet, don’t put the range on the data where you think to select the consolidate, be sure each range of the data in same layout.
Select the range of the data which you want to consolidate on master worksheet, then go to consolidate option and click on it below image shows that how to use consolidate option.

                              Click on consolidate option, consolidate window front on your screen, you see reference option select the range of the data and click on add option, same process on second sheet, Go to second sheet and select the range of the data in reference option and click on add option and when you go to third sheet where you put your data, On third sheet check the mark of top row, left column and create link to source data and click ok. Below image show that the explanation to given above description in image step to step.
Step 1

  Step 2

 Step 3

Through the video you can better understand to given all description about this tutorial.

If you have found any mistake or have any doubt related to above basic consolidate in excel by using position tutorial then comment below.


People Also Interested : How to make a pie chart in excel?  
People Also Interested : Basic consolidate in excel by using category and label.

Continue Reading →

How to add input message, error alert, invalid data in data validation?

You all heard about data validation in excel. In this tutorial you will learn about How to add input message, error alert, invalid data in data validation?. It is a most important topic in Microsoft excel. Data validation is a feature of Microsoft excel. You can use to define restriction on what type of data can or should be entered in a cell. You can configure data validation to prevent user from entering invalid data. You can also provide short message to define what input you can expect for the cell, and you give instruction to help users correct any errors.

FOR EXAMPLE
Select the range of the data which you want to use data validation and go to data tab and you see data tools group in this group you see data validation option click drop down list and select data validation. Below image shows that where is data validation option?

When you done step 2 which is given above image you see data validation window front of this screen and you see drop down list click on it and select whole number and second drop down list click on it and select between, you see minimum and maximum option select the minimum and maximum number which you want see for example you select minimum number 33 and maximum number 99 and click ok. Below image shows that the above given following description.

When you done the following instruction then go to data validation option and click on this option and select circle invalid data. Below image shows that what happen when we select the circle invalid data. you see Microsoft excel highlighted invalid data in circle.

How to remove invalid data circle?

When you see invalid data and you want to clear invalid circle, go to data validation and click on it you see clear validation circle option click on it. Below image shows that how to remove invalid data circle?

How to add input message on data validation in excel?

When other user use your selected range data and you want to help you can add input message to help those person. Go to data validation option and click data validation and go to Input message and mark the check box (show input message, when cell is selected) and fill title and input message and click ok. When other users select the cell he saw a message and follow description which you give. Below image shows that how to add input message on data validation in excel?

How to add error alert when users enter invalid data?

You also show an error when users enter invalid data. Go to data validation option and select the error alert and click on it and mark the check box (show error alert when user enter invalid data) and go to style drop down list and select anyone which you want, then go to title and add title (Warning message, invalid data) and add error message and click ok. See below image

How to remove data validation in Microsoft excel?

you want to remove data validation in your spreadsheet. Go to data validation and click on it, data validation window open on the screen you see clear all option below on this window click on it and press ok to remove data validation in Microsoft excel. See Image as given below

How to create a drop down list in Microsoft excel?

In Microsoft excel you create a drop down list in various way. If you want to create a drop down list through data validation option click the data validation option, in data validation window go to setting and click on allow drop down list and select list, click on source option and select the range of name which you want to add in drop down list and click ok. See below image

In this article you have studied that how to add input message, error alert, invalid data in data validation? If you like this tutorial don't forget like us on Facebook and also follow on twitter. And if you find any missing then let me know through your comment.

Continue Reading →

How to Load Analysis Toolpak in Excel 2010?

In this tutorial we will teach How to load analysis toolpak in excel 2010? in excel. First about you need to enable to the toolpak. Go to the File tab also known as backstage view and click on option tab as given in Image.

You see the excel window option go to the ADD INS tab and select the analysis toolpak given their, click the go button. you see image and follow step :-

you see a small window open in your screen its ADD INS window. You see the various option please select the option of analysis toolpak.

 How to find the analysis toolpak option? Go to the data tab and click on it you see Data analysis option on right of the screen click on it and you see data analysis option.

For example :- how to use Correlation option in analysis toolpak?
Correlation is a mathematically representation of the relationship between  two values. First select the range of data which you want to do correlation. Then go to data tab and press data analysis option right on the screen, click on it. You see data analysis window on screen , in window you see various option click on correlation option again you see correlation table, In input range column select the range of the data which you want to do correlation and mark the option in checkbox LABLE IN FIRST ROW then go to output option below in window click on it and select the range where you want to show correlation table. Below image shows that the following description

For other example moving average go to the data tab and press data analysis option right on the screen , click on it. you see Moving Average option again click on it and you see the Moving Average window, in which window you see input range click on it column and select the range which you want and uncheck the box LABLE IN FIRST ROW then go to the output option click on it and select the column where you want to show moving average table, after mark the option of chart output and ok .Below image shows that the following description.

If you like this post don't forget like us on Facebook and also follow me on twitter.If you have found any mistake or have any doubt related to above How to load analysis toolpak in excel 2010? in excel tutorial then comment below.

Continue Reading →