Advanced Excel

BY
LUCY WANGARI
 LESSON 1:
Introduction to Pivot Tables and
Charts
Data models by building relational data
Data Model is used for building a model where data from various sources can be combined by creating relationships among the data
sources. A Data Model integrates the tables, enabling extensive analysis using PivotTables, Power Pivot, and Power View.
A Data Model is created automatically when you import two or more tables simultaneously from a database. The existing database
relationships between those tables is used to create the Data Model in Excel.
Step 1 − Open a new blank Workbook in Excel.
Step 2 − Click on the DATA tab.
Step 3 − In the Get External Data group, click on the option From Access. The Select Data Source dialog box opens.
Step 4 − Select Access Database file.
Step 5 − The Select Table window, displaying all the tables found in the database, appears.
Step 6 − Tables in a database are similar to the tables in Excel. Check the ‘Enable selection of multiple tables’ box, and select all the
tables. Then click OK.
Data models by building relational data
Step 7 − The Import Data window appears. Select the PivotTable Report option. This option imports the tables into Excel and prepares
a PivotTable for analyzing the imported tables. Notice that the checkbox at the bottom of the window - ‘Add this data to the Data
Model’ is selected and disabled.
Step 8 − Click Ok and the data is imported, and a PivotTable is created using the imported tables.

You have imported the data into Excel and the Data Model is created automatically. Now, you can explore data in the five tables,
which have relationships defined among them.
Pivot Tables
When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your
worksheets more manageable by summarizing your data and allowing you to manipulate it in different ways.
Using pivot tables to answer questions
Consider the example below. Let's say we wanted to answer the question What is the amount sold for each product group? Answering it
could be time consuming and difficult; each product group appears on multiple rows, and we would need to total all of their different orders
individually. We could use the Subtotal command to help find the total for each , but we would still have a lot of data to work with.
Pivot Tables cont..
Fortunately, a PivotTable can instantly calculate and summarize the data in a way that will make it much easier to read. When we're done, the
PivotTable will look something like this:

Once you've created a PivotTable, you can use it to answer different questions by rearranging or pivoting the data.
Create a Pivot tables
1. Select the table or cells (including column headers) you want to include in your PivotTable.
2. From the Insert tab, click the PivotTable command.
3. The Create PivotTable dialog box will appear. Choose your settings, then click OK.
4. A blank PivotTable and Field List will appear on a new worksheet.
Create a Pivot tables cont..
5. Once you create a PivotTable, you'll need to decide which fields to add. Each field is simply a column header from the source data. In the
PivotTable Field List, check the box for each field you want to add. In our example, we want to know the total amount sold for each product
group, so we'll check the product group and Revenue fields.
6. The selected fields will be added to one of the four areas below. Alternatively, you can drag and drop fields directly into the desired area.
7. The PivotTable will calculate and summarize the selected fields

Just like with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter command on the Home tab. You can also
apply any type of number formatting you want. However, be aware that some types of formatting may disappear when you modify the
PivotTable.
If you change any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the
PivotTable and then go to Analyze >Refresh.
Pivoting Data
One of the best things about PivotTables is that they can quickly pivot or reorganize your data, allowing you to examine your
worksheet in several ways. Pivoting data can help you answer different questions and even experiment with your data to discover
new trends and patterns.
To add columns: So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you'll need
to add a field to the Columns area.
1. Drag a field from the Field List into the Columns area. In our example, we'll use the Year field.
2. The PivotTable will include multiple columns.
Pivoting Data Cont..
To change a row or column:
Changing a row or column can give you a completely different perspective on your data. All you have to do is remove the field in
question, then replace it with another.
1. Drag the field you want to remove out of its current area. You can also uncheck the appropriate box in the Field List. In this
example, we've removed the Month and product group fields.
2. Drag a new field into the desired area.
3. The PivotTable will adjust or pivot show the new data.
Pivot charts
Pivot Charts are like regular charts, except they display data from a PivotTable. Just like regular charts, you'll be able to select a chart type,
layout, and style that will best represent the data.
Create a PivotChart
1. Select any cell in your PivotTable.
2. From the Insert tab, click the PivotChart command.
3. The Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK and the PivotChart will appear.

Try using filters or slicers to narrow down the data in your PivotChart. To view different subsets of information, change the columns or rows
in your PivotTable.
Formulas in pivot tables and pivot charts
By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to
use, execute the following steps.
1. Click any cell inside the Sum of Amount column.
2. Right click and click on Value Field Settings.
3. Choose the type of calculation you want to use. For example, click Count.
4. Click OK to get the output.
LESSON 2:
Power Query
Importing data from databases
Create a pivot table with an external data Source
Being able to analyze all the data can help you make better business decisions. But sometimes it’s hard to know where to start,
especially when you have a lot of data that is stored outside of Excel, like in a Microsoft Access or Microsoft SQL Server database, or in
an Online Analytical Processing (OLAP) cube file. In that case, you’ll connect to the external data source, and then create a PivotTable
to summarize, analyze, explore, and present that data.
Here’s how to create a PivotTable by using an existing external data connection
1. Click any cell on the worksheet.
2. Click Insert > PivotTable. In the Create PivotTable dialog box, click From External Data Source.
4. Click Choose Connection.
5. On the Connections tab, in the Show box, keep All Connections selected, or pick the connection category that has the data source
you want to connect to.
Importing data from databases
To reuse or share an existing connection, use a connection from Connections in this Workbook.
1. In the list of connections, select the connection you want, and then click Open.
2. Under Choose where you want the PivotTable report to be placed, pick a location. To place the PivotTable in a new worksheet starting at
cell A1, choose New Worksheet
To place the PivotTable in the active worksheet, choose Existing Worksheet, and then in the Location box, enter the cell where you want the
PivotTable to start.
3. Click OK.
Excel adds an empty PivotTable and shows the Field List so that you can show the fields you want and rearrange them to create your own
layout.
4. In the field list section, check the box next to a field name to place the field in a default area of the areas section of the Field List. Typically,
nonnumeric fields are added to the Rows area, numeric fields are added to the Values area, and date and time fields are added to the
Columns area. You can move fields to a different area as needed.

Tip: You can also right-click a field name, and then select Add to Report Filter, Add to Column Labels, Add to Row Labels, or Add to Values to
place the field in that area of the areas section, or drag a field from the field section to an area in the areas section. Use the Field List to
further design the layout and format of a PivotTable by right-clicking the fields in the areas section, and then selecting the area you want, or
by dragging the fields between the areas in the areas section.
Connect to a new external data source
To create a new external data connection to SQL Server and import data into Excel as a table or PivotTable, do the following:
1. Click Data > From Other Sources.
2. Click the connection you want.
• Click From SQL Server to create a connection to a SQL Server table.
• Click From Analysis Services to create a connection to a SQL Server Analysis cube.
3. In the Data Connection Wizard, complete the steps to establish the connection.
• On page 1, enter the database server and specify how you want to log on to the server.
• On page 2, enter the database, table, or query that contains the data you want.
• On page 3, enter the connection file you want to create.
To create a new connection to an Access database and import data into Excel as a table or PivotTable, do the following:
1. Click Data > From Access.

2. In the Select Data Source dialog box, locate the database you want to connect to, and click Open.
3. In the Select Table dialog box, select the table you want and then click OK.
If there are multiple tables, check the Enable selection of multiple tables box so you can check the boxes of the tables you want,
and then click OK.
In the Import Data dialog box, select how you want to view the data in your workbook and where you want to put it, and then
click OK.
The tables are automatically added to the Data Model, and the Access database is added to your workbook connections.
Power query
Power Query is an application for transforming and preparing data. With Power Query you can get data from sources using a
graphical interface and apply transformations using a Power Query Editor. Using Power Query, a business intelligence tool offered
by Microsoft Excel, you can import data from any number of sources, clean it, transform it, then reshape it according to your
needs. In this way, you can set up a query only once, re-use it later by simply refreshing.

Phases of Power Query

1. Connect
In this phase, users connect to the data source(s) from which they want to extract data. Power Query supports many data
sources, including databases, files, web pages, and more. Users can also specify any required authentication or authorization
details during this phase.

2. Transform
Once the data is loaded into Power Query, users can use various data transformation tools to clean, reshape, and transform the
data to meet their specific needs. Common data transformation tasks include removing duplicates, filtering data, merging data,
splitting columns, and pivoting data.

3. Combine
Power Query also allows users to combine data from multiple sources using various techniques. Users can merge tables, append,
or join data using a common key. This phase is beneficial for integrating data from different sources into a single, unified view.

4. Load
Finally, in the Load phase, users specify where to load the transformed data. They can load the data into an Excel worksheet or a
Power BI report or create a connection to the data source so that the data is automatically refreshed whenever the source data
changes.
Power query

Locate the data in your computer and import

Power query
Under the navigator, select the tables you'll analyze then click edit/transform
Power query
Query Editor helps you transform data based on your needs

After performing all the operations on the editor, we will have to output it to our Excel sheet. To do this, click on the Close and
Load option on the Ribbon section of the Power Query Editor.
 LESSON 3:
Filters $ Slicers
Filters
Sometimes you may want focus on a certain section of your data. Filters can be used to narrow down the data in your PivotTable, so
you can view only the information you need.
To add a filter: In the example below, we'll filter out certain product group to determine how they are of impact to each Type.
1. Drag a field from the Field List to the Filters area. In this example, we'll use the product group field.
2. The filter will appear above the PivotTable. Click the drop-down arrow, then check the box next to Select Multiple Items
3. Uncheck the box next to any item you don't want to include in the PivotTable, then click OK.
4. The PivotTable will adjust to reflect the changes.
Slicers
Slicers make filtering data in PivotTables even easier. Slicers are basically just filters but are easier and faster to use, allowing you
to instantly pivot your data. If you frequently filter your PivotTables, you may want to consider using slicers instead of filters.
To add a slicer:
1. Select any cell in the PivotTable.
2. From the Analyze tab, click the Insert Slicer command.
3. A dialog box will appear.
Check the box next to the desired field, then click OK to choose the filter variable.
Working with slicers to filter data in a table or pivot table
4. The slicer will appear next to the PivotTable. Each selected item will be highlighted in blue
5. Just like filters, only selected items are used in the PivotTable. When you select or deselect an item, the PivotTable will instantly reflect
the change.

Try selecting different items to see how they affect the PivotTable. Press and hold the Ctrl key on your keyboard to select multiple items at
once.
You can also click the Filter icon in the top-right corner of the slicer to select all items at once.
 LESSON 4:
Dashboards and What-if Analysis
Dashboard
A dashboard is a way of displaying various types of visual data in one place. Usually, a dashboard is intended to convey
different, but related information in an easy-to-digest form.
Excel features to create dashboards
You can create a dashboard in Excel using various features that help you make data visualization
prominent, which is the main characteristic of any dashboard. You can show data in tables with
conditional formatting to highlight the good and bad results, you can summarize the data in
charts and PivotTables, Excel Table, Excel Camera ,Excel PivotTables and so on.

Interactive controls
You can make your dashboard elements interactive with easy to use controls such as scrollbars,
radio buttons, checkboxes and dynamic labels.

Excel power pivot tables and power pivot charts

Excel Power PivotTables and Power Pivot Charts are helpful to summarize data from multiple resources, by building a memory
optimized Data Model in the workbook. The Data Tables in the Data Model can run through several thousands of dynamic data
enabling summarization with less effort and time.
Excel Data Model
Excel Power PivotTable and Power PivotChart
Excel Power View Reports -Reports provide interactive data visualization of large data sets bringing out the power of Data
Model and interactive nature of dynamic Power View visualizations.
Key Performance Indicators (KPIs) -are integral part of many dashboards. You can create and manage KPIs in Excel.

As any dashboard is based on the specific intent of what the audience is most interested about, dashboard components and
dashboard layout varies from case to case.
Initial Preparation
The first step in building a dashboard is the initial preparation. Take some time in understanding
the following ;
• Why do you need the dashboard?, Is this dashboard for a specific task, like showing the status of a project, or does it need
to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will
guide you in the design.
• What purpose the dashboard will serve?− Your dashboard should highlight only the data that adds value. You should
understand the data that is required. Anything outside that is unnecessary.
• What is the source of data? − You should understand from where the data comes. It can just be an Excel worksheet or it
can be through data connections to your Excel workbook from various dynamic data sources.
• Who is the audience for the dashboard? − Is this for a manager, an executive, a stakeholder, an external vendor or general
audience? Understand their requirements and preferences such as how much time do they have to look at the dashboard,
the level of detail they expect and how they would like to digest the information. For example, while choosing a chart type,
knowing the audience helps you to decide on whether you have to show relations between values or you have to draw a
specific comparison.
• Does the dashboard need to be static or dynamic? − Can the dashboard be updated periodically, say, weekly or monthly,
or does it require to get updated to continuously streamline the data changes that happen at the backend? This choice will
change the way you build the dashboard.
• Does the dashboard need to be just a display or is it to be interactive? − Can the dashboard have read-only access or do
you have to provide interactive controls / features that enable certain people to explore the data as required? This choice
also will change the way you build the dashboard.
Once you have answers to these questions, decide on what Excel features you need and you don’t need. This is because your
goal and expertise is to produce an effective dashboard that suits the purpose.
Next, identify the components of the dashboard. These can be text, tables, charts, interactive controls, etc. Decide on the
dashboard layout with these components.
Copy your Excel dashboard on a PowerPoint slide. Draw boxes for each component to get a sense of the layout and add quick
sketches of the components that you want to include. You can also do this on a piece of paper. Get approval for this copy from
the management and/or the key audience before you start working on the actual dashboard. This will save time on rework.
However, it is quite possible that you might have to tweak in some changes to the dashboard as the dashboard gets into usage
and you receive feedback. But, the approved dashboard mockup is a real good starting for your work.
Organize the Data Source for the Excel Dashboard
Before building the dashboard in Excel, you need to organize the data source. In Excel, this is possible in various ways;
• If the data is just an Excel table, establish a link to your workbook from the workbook where the data will get updated.
• If the data is from multiple Excel tables, or if it is from various data sources, it is a good option to build the Data Model in
your workbook.
You can either import the data into the workbook periodically or establish data connections so as to refresh the data as when
it gets updated, based on whether the dashboard has to be static or dynamic.
Set Up the Excel Dashboard Workbook
Once you have organized the data, you need to structure your workbook. Insert two to three worksheets in the workbook −
one worksheet for your dashboard and one or two worksheets for the data (data or Pivot Table/Pivot Charts or Power View
Reports, which you can hide). This will help you to organize and maintain your Excel workbook.
Prepare the Data for the Excel Dashboard
Based on the choices you have, i.e. the answers you have for the questions in the Initial
Preparation step, prepare the data for the Excel dashboard. The data can be any of the following ;
• Results from data analysis
• Results from data exploration
• Data resulting from computations on the input data
• Data summarization from PivotTables or PowerPivot Tables
Select the Dashboard Components
You have learnt about the various Excel features that you can use in a dashboard. Based on your requirements for the dashboard at
hand, select any of the following Excel features for the dashboard components.
• Tables
• Sparklines
• Conditional Formatting.
• Charts
• Slicers
• Interactive Controls
• PivotTables
• Pivot Charts
• PowerPivot Tables
• PowerPivot Charts
• Power View Reports
• KPIs
Selecting the dashboard components will help you to align to your approved dashboard copy layout.
Identify the static and dynamic components and the components that are to be grouped for Slicers, if any.
Identify Parts of the Dashboard for Highlighting
Identify those parts of the dashboard that require immediate attention, such % Complete or the Current Status. You can use
bigger font and a striking font and font color for these. Decide on how much color you want to incorporate in your dashboard.
This decision can be based on the audience for the dashboard. If the dashboard is for executives and/or managers,
choose colors that impact the visualization of the results being displayed. You can add a dashboard background color to make
the dashboard components pop. You can use the same color code for similar charts or related results. You can use conditional
formatting too.
A careful selection of the parts of the dashboard for highlighting makes it effective.
Build the Dashboard
This is the crucial and final step in creating your Excel dashboard. This step involves assembling your dashboard components
that you can efficiently and effectively do by using Excel camera.
Once the dashboard components are assembled, give it a final touch;
• Give a title to the dashboard.
• Incorporate a timestamp.
• Include the copyright information, if required.
Using Excel Camera
Excel camera helps you in capturing snapshots from your worksheets and place them in a different worksheet. For example, you can
capture a table with conditional formatting on a worksheet and place it on your dashboard. Whenever the data gets updated, the
dashboard will get refreshed to display the changed data.
You can include the Excel camera as part of your Quick Access Bar as follows;
1. Right-click on the small arrow on the Quick Access Toolbar.
2. Click on More Commands in the Customize Quick Access Toolbar list. Excel Options dialog box appears.
3. Click on Quick Access Toolbar.
4. Select All Commands under Choose commands from.
5. Click on Camera in the commands list.
6. Click on the Add » button. Camera appears in the right side list. Click the OK button. The camera icon appears on the Quick Access
Toolbar in your workbook.
You can use Excel camera as follows;
• Select the range of cells to be captured.
• Click on camera on the Quick Access Toolbar. The range of cells appear with a dashed line border.
• Click on the worksheet where you want to place the captured region. It can be your dashboard sheet.
• Click at a position where you want to place it.
The captured region appears at that point.
Whenever you make changes to the original data, the changes will get reflected in the dashboard.
Date and Time Stamp on Excel Dashboard
You can incorporate a Date or Date and Time Stamp on your dashboard to display when the data was last update. You can do
this using the Excel functions TODAY () and NOW ().
To incorporate a Date Stamp, enter =TODAY () in the cell where you want to place the Date Stamp on your data worksheet.
This will display the current date whenever the workbook is updated.
• Ensure that the cell where you entered the TODAY () function is formatted to the Date format that you want to display.
• Capture the display with camera and place it on the dashboard. The date on the dashboard will reflect the date when the
workbook was last updated.

You can incorporate a Date and Timestamp on your dashboard in a similar way with the function NOW ().
• Enter = NOW () in the cell where you want to place the Date and Time Stamp on your data worksheet.
• Ensure that you have the right format for Date and Time.
• Capture the display with camera and place it on the dashboard.
The date and time stamp will get incorporated on the dashboard and will reflect the date and time when the workbook was
last updated.
Test, Sample, and Enhance the Dashboard
• You need to test your dashboard to ensure that it displays the data accurately.
• Test it in various possible scenarios.
• Test for the precise updates (static or dynamic as is the case).
• Test the interactive controls, if any.
• Test the look and feel.
You might have to do some trial runs to ensure that your dashboard is as you wanted. The next step is to get the dashboard
evaluated by sample audience, especially those who approved your copy dashboard. As they will be using the dashboard, they
will undoubtedly have input as to its use and effectiveness. This feedback helps you to ensure that your dashboard is effective.
Do not hesitate to ask for feedback. Once you get the feedback, enhance it with required changes, if any. Your Excel dashboard
is ready to use.
Share the Dashboard
You need to make the Excel dashboard available to the intended audience. You can do it in several ways.
• Mail the Excel dashboard workbook (You have to hide the worksheets other than the dashboard worksheet. You can also
protect the workbook).
• Save the Excel dashboard workbook on a shared network drive.
• Share the dashboard online.
If the Excel dashboard is static you can mail it but if it is dynamic or has interactive controls, then it should have a connection
to the backend data and hence requires sharing online.
You can share an Excel dashboard online with any of the following options;
• Microsoft OneDrive.
With your Windows Live account, you will get access to OneDrive where you can post and share documents.
• New Microsoft Office Online.
• Microsoft SharePoint.
You can also save the Excel workbook file as an Acrobat Reader file (.pdf) and post it to web. But, once again this option is only
for the static dashboards.
Tips for Effective Excel Dashboards
To make an Excel dashboard effective, you need to do certain things and avoid some. Some tips for effective Excel dashboards
are as follows;
1. Keep it simple.
• A simple, easy to understand dashboard is much more effective than a jazzy dashboard. Remember that it is the data that
needs emphasis.
• According to Glenna Shaw, you need to strike a balance between making your dashboard attractive enough to hold your
audience’s interest, but not so stylized that it obscures the information displayed.
• Better avoid 3D effects, gradients, extra shapes and unnecessary gadgets.
• If you can achieve emphatic display with conditional formatting or Sparklines, prefer using Tables to Charts.
2. Use Excel Conditional Formatting.
• Use Excel Conditional Formatting that provides several options to automatically
• update based on the values in your table.
3. Select appropriate chart types.
• Remember that there is no general rule for using a chart type. Sometimes conventional chart types like column chart, bar
chart, doughnut chart, etc. convey the message emphatically more than the sophisticated charts that are coming into
• existence.
• You can use Excel Recommend Charts command to initially evaluate the suitable chart types.
• As you can change the chart type with a single Excel command − Change Chart
• Type, you can play around to visualize the display and choose the appropriate chart.
4. Use interactive controls.
• Use interactive controls such as scroll bars, option (radio) buttons and check boxes that help the user easily and effectively
visualize the different aspects of the data.
5. Use Excel Data Model for Big Data.
• If you have large data sets from various data sources, you better use the Excel
• Data Model that can handle thousands of rows of data with memory optimization and can group data tables with
relationships.
6. Choose appropriate colors.
• Be careful in choosing the colors. Use the colors judicially to provide sufficient impact, but not override their purpose.
Moreover, if the audience is likely to include color blind, restrain from using Red and Green colors. In such cases, though
traffic light symbols sound effective for the data being displayed, they are not suitable for the dashboard. Use a gray scale
instead.
7. Use Slicers.
• Slicers are more effective than dropdown lists as they have a visual impact.
• You can group charts, PivotTables, Pivot Charts to use a common Slicer.
8. Group the Excel dashboard components together.
• You can add visual interest to your dashboard by inserting a shape, such as a rectangle and placing your dashboard
components that can be grouped on top of that shape. For example, if you are using a common Slicer, you can group all
the dashboard components that share the Slicer.
9. Use Excel data hierarchies.
• If your data has innate data hierarchies, define them in the Data Model and use them to interactively drill up and drill
down the data.
10. Avoid crowded dashboard layout.
• Remember that display of more information than that is necessary will overwhelm the audience and beat the focus on the
actual purpose.
• Do not include any data or chart in your dashboard, if you can do away with it.
• This is an important checkpoint while testing your dashboard. Evaluate each dashboard component if is necessary and
sufficient.
• The dashboard components and the layout should support the single purpose of your dashboard.
There are several possible types of dashboards. There are no set of standards for dashboards, except for some do’s and don’ts.
You can understand the purpose and use your imagination to select the components and layout for your dashboard. However,
you need to be on the same page with the viewers of the dashboard and hence their preferences are to be taken care to make
it effective. A dashboard can get modified as the time proceeds based on the context and changing needs.
Example of a dashboard
What-if analysis
Excel includes many powerful tools to perform complex mathematical calculations, including what-if analysis. This feature can help
you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you will learn how to use a
what-if analysis tool called Goal Seek.
Goal Seek
Whenever you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the
opposite way: It lets you start with the desired result, and it calculates the input value that will give you that result. We'll use a few
examples to show how to use Goal Seek.
Let's say you're enrolled in a class. You currently have a grade of 64, and you need at least a 70 to pass the class. Luckily, you have one
final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final
assignment to pass the class. In the image below, you can see that the grades on the first five assignments. Even though we don't
know what the fifth grade will be, we can write a formula or function that calculates the final grade. In this case, each assignment is
weighted equally, so all we have to do is average all five grades by typing =AVERAGE(). Once we use Goal Seek, cell B9 will show us the
minimum grade we'll need to make on that assignment.
What-if analysis
Select the cell with the value you want to change. Whenever you use Goal Seek, you'll need to select a cell that already contains a
formula or function. In our example, we'll select cell B7 because it contains the formula =AVERAGE(B2:B6).
1. From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu.
2. A dialog box will appear with three fields.
• The first field, Set cell:, will contain the desired result. In our example, cell B9
• The second field, To value:, is the desired result. In our example, we'll enter 70 because we need to earn at least that to pass the
class.
• The third field, By changing cell:, is the cell where Goal Seek will place its answer. In our example, we'll select cell B7 because we
want to determine the grade we need to earn on the final assignment.
3. When you're done, click OK.
4. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
The result will appear in the specified cell. In our example, Goal Seek calculated that we will need to score at least a 92 on the final
assignment to earn a passing grade.
Other types of what-if analysis
For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. Instead
of starting from the desired result and working backward, like with Goal Seek, these options allow you to test multiple values
and see how the results change. Scenarios let you substitute values for multiple cells (up to 32) at the same time. You can
create as many scenarios as you want and then compare them without changing the values manually. In the example below,
we're using scenarios to compare different venues for an upcoming event.
Data tables allow you to take one or two variables in a formula and replace them with as many different values as you want,
then view the results in a table. This option is especially powerful because it shows multiple results at the same time, unlike
scenarios or Goal Seek.
Example; Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a
certain % for the lower price of $20. If you sell 60% for the highest price, cell C11 calculates a total profit of
60 * $50 + 40 * $20 = $3800.
But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each
different percentage is a different scenario. You can use the Scenario Manager to create these scenarios.

1. On the Data tab, in the Forecast group, click What-If Analysis.

2. Click Scenario Manager.
Other types of what-if analysis
The Scenario Manager dialog box appears.
3. Add a scenario by clicking on Add.

4. Type a name (60% highest), select cell B3 (% sold for the highest price) for the Changing cells and click on OK.
5. Enter the corresponding value 0.6 and click on OK again.
Other types of what-if analysis
6. Next, add 4 other scenarios (70%, 80%, 90% and 100%).
Finally, your Scenario Manager should be consistent with the picture below:

NB: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell B3 accordingly
for you to see the corresponding result on the sheet.
Scenario Summary
To easily compare the results of these scenarios, execute the following steps.
1. Click the Summary button in the Scenario Manager.
2. Next, select cell C11 (total profit) for the result cell and click on OK.