Goal Seek in Excel
Goal Seek is used to get a particular result when you're not too sure of the starting value. For
example, if the answer is 56, and the first number is 8, what is the second number? Is it 8
multiplied by 7, or 8 multiplied by 6? You can use Goal Seek to find out.
1. From the Excel menu bar, click on Data
2. Locate the Data Tools panel and the What if Analysis item. From the What if
Analysis menu, select Goal Seek
3. The following dialogue box appears:
Set cell: Which cell contains the Formula that you want Excel to use
To value: What answer are you looking for
By Changing Cell: is the part you're not sure of. Excel will be changing this part.
DATA TABLE
Data table is a range of cells that shows how changing one or two variables in
your formulas will affect the results of those formulas.
1. One-variable data tables Use a one-variable data table if you want to
see how different values of one variable in one or more formulas will
change the results of those formulas
2. Two-variable data tables Use a two-variable data table to see how
different values of two variables in one formula will change the results of
that formula.
3. Data table calculations Data tables are recalculated whenever a
worksheet is recalculated, even if they have not changed.
On the Data tab, in the Data Tools group, click What-If Analysis, and then
click Data Table.
If the data table is column-oriented, type the cell reference for the
input cell in the Column input cell box.
If the data table is row-oriented, type the cell reference for the
input cell in the Row input cell box.
Consolidate
To summarize and report results from data on separate worksheets,
you can consolidate the data from each separate worksheet into one worksheet
(or master worksheet). When you consolidate data in one worksheet, you can
more easily update and aggregate it on a regular or ad hoc basis.
There are two main ways to consolidate data:
Consolidate by position Use this method when the data from multiple
source areas is arranged in the same order and uses the same row and
column labels.
Consolidate by category Use this method when the data from multiple
source areas is arrange differently, but the same row and column labels
are used.
On the Data tab, in the Data Tools group, click Consolidate. You can also consolidate
data by using a formula or a PivotTable report.
A Pivot Table
The Pivot tables are used to summarize, analyze, explore and present your data. A Pivot table
is a way to extract data from a long list of information and present it in a more meaningful
and user friendly understandable format.
Here are the steps to create a Pivot table in Excel 2010.
Step 1: First of all, please make sure to select the data range for which you want to make the
pivot table.
Step 2: Insert the Pivot Table by going to the Insert tab and then clicking the Pivot Table
icon.
Step 3: Select the target cells where you want to place the pivot table. For starters, select the
New Worksheet option.
Step 4: The new worksheet will open and you will be able to see the pivot table that you just
created, you can now generate the report from this table and can perform various operations
on this table for better visualization and presentation of data. Just for example I calculated the
sum of all of the selected cells.
In the right side, you will see the Pivot Table Panel which contains many useful options to
work with the Pivot table.
Solver
Solver is an Excel add-in that can solve problems by enabling a Target cell to achieve
some goal.
This goal may be to minimse, maximise, or achieve some target value.
It solves the problem by adjusting a number of input cells according to a set of criteria
or constraints which are defined by the user.
The Solver Window
This section will explain the solver window and its use in defining the problem within solver.
Set Target Cell:
This is the Target cell which is the cell which you are trying to solve the problem for.
Equal To:
Section defines what we want to do with our Target Cell.
By Changing Cells:
Refers to the cells which will be modified by Solver to try and solve the problem.
Subject to the Constraints:
Constraints are the rules which define the limits of the possible solutions to the problem
You can Change or Delete Constraints if you make a mistake by selecting the appropriate
constraint and using the Change or Delete Buttons