1. Rename a worksheet. EX 8882 Observe mode
Renaming a worksheet can help identify the type of data it contains.
In the Cells group on the Home tab, click the Format button.
Click Rename Sheet.
The text in the sheet tab is selected.
Type Sales in the sheet tab.
Press ENTER.
The sheet tab is renamed Sales.
2. Use Goal Seek. EX 9015 Observe modesce
Using Goal Seek lets you determine the target value of a cell, such as the net
Income, based on changes in another (Changing) cell, such as the revenue.
Click cell B20.
You will use Goal Seek to set a target value for cell B20, the net Income. Note that
the cell currently contains a formula, a requirement when using the Goal Seek
feature.
Click the Data tab on the ribbon.
In the Forecast group, click the What-If Analysis button.
The What-If Analysis menu opens.
Click Goal Seek.
The Goal Seek dialog box opens. The first argument you will enter is the Set cell,
which is the cell for which you are setting a target value. Because cell B20 is
selected in the worksheet, the default entry in the Set cell text box is cell B20. You
will accept this entry.
Click the To value text box. Type 1000 in the text box, and press TAB.
You must enter a value here. Your target is a net income of $1,000.
Click cell B5.
Goal Seek will calculate what the value in cell B5, revenue, must be in order to
achieve a net income of $1,000.00 in cell B20.
Click OK.
The Goal Seek Status dialog box opens, stating that Goal Seek has found a solution.
Excel returns the solution in the worksheet.
Click OK.
The dialog box closes. The value in cell B5 is now $224,330, the value that is
necessary to achieve the $1,000 Net Income in cell B20. Note that the values in cells
B7 and B20 have also changed; these cells contain formulas that are affected by the
value in cell B5.
3. Create a one-variable data table. EX 9349 Observe mode
See how changing one variable affects formula results by building a data table. The
table calculates and displays all the results at the same time in the same place.
Click cell D5.
When creating a data table, you must enter the cell reference for the input cell in the
upper-left corner of the data table.
Type =B4 in cell D5.
Press ENTER.
The variable input for the data table will be the interest rate referenced in cell B4.
Click cell G5.
, The top row of a data table contains the formulas that are affected when the input
cell changes. The first two formulas are already entered; you will enter the third
formula here.
Type =B9 in cell G5.
Press ENTER.
Excel copies the value and underlying formula of cell B9 to cell G5. The data table
will return results for the Monthly Payment, Total Interest Paid, and Total Loan Cost
values in columns E, F, and G, referencing the formulas in cells B7, B8, and B9
respectively.
Click and drag to select range D5:G13.
This is the range you will use for your data table.
Click the Data tab on the ribbon.
You will create the data table itself using Excel's What-If Analysis tool.
In the Forecast group, click the What-If Analysis button.
The What-If Analysis menu opens.
Click Data Table.
The Data Table dialog box opens with the insertion point in the Row input cell text
box.
Click the Column input cell text box, and type B4 in the text box.
Excel enters the cell reference for cell B4, the interest rate, as the input in the text
box.
Click OK.
Excel populates the data table with result values in columns E, F, and G for each of
the interest rate inputs in column D.
4. Name a table. EX 9353 Observe mode
Assign names to tables to identify their content and better organize your worksheet.
Increase speed and accuracy by using table names rather than cell references in
formulas, procedures, queries, and more.
Click cell A4.
The table is selected and the Table Tools Design tab appears on the ribbon.
Click the Table Tools Design tab.
In the Properties group, click the Table Name text box, and type Clients and press
ENTER.
Excel renames the table Clients. If you click the Name Box arrow on the formula bar,
the table name will appear on the list of available range and table names.
5. Apply a table style. EX 9359 Observe mode
If you do not like the color and formatting of your current Excel table style, you can
easily change it. Choosing a new style from the Table Styles gallery to coordinate
with other areas of the worksheet can add professionalism to your work.
Click cell A5.
The Table Tools Design tab appears on the Ribbon.
Click the Table Tools Design tab.
In the Table Styles group, click the More button.
Excel displays a gallery of table styles.
In the gallery, in the Medium section, click Green, Table Style Medium 7, the last
option in the first row of the section.
Excel applies the new table style to the table.
Renaming a worksheet can help identify the type of data it contains.
In the Cells group on the Home tab, click the Format button.
Click Rename Sheet.
The text in the sheet tab is selected.
Type Sales in the sheet tab.
Press ENTER.
The sheet tab is renamed Sales.
2. Use Goal Seek. EX 9015 Observe modesce
Using Goal Seek lets you determine the target value of a cell, such as the net
Income, based on changes in another (Changing) cell, such as the revenue.
Click cell B20.
You will use Goal Seek to set a target value for cell B20, the net Income. Note that
the cell currently contains a formula, a requirement when using the Goal Seek
feature.
Click the Data tab on the ribbon.
In the Forecast group, click the What-If Analysis button.
The What-If Analysis menu opens.
Click Goal Seek.
The Goal Seek dialog box opens. The first argument you will enter is the Set cell,
which is the cell for which you are setting a target value. Because cell B20 is
selected in the worksheet, the default entry in the Set cell text box is cell B20. You
will accept this entry.
Click the To value text box. Type 1000 in the text box, and press TAB.
You must enter a value here. Your target is a net income of $1,000.
Click cell B5.
Goal Seek will calculate what the value in cell B5, revenue, must be in order to
achieve a net income of $1,000.00 in cell B20.
Click OK.
The Goal Seek Status dialog box opens, stating that Goal Seek has found a solution.
Excel returns the solution in the worksheet.
Click OK.
The dialog box closes. The value in cell B5 is now $224,330, the value that is
necessary to achieve the $1,000 Net Income in cell B20. Note that the values in cells
B7 and B20 have also changed; these cells contain formulas that are affected by the
value in cell B5.
3. Create a one-variable data table. EX 9349 Observe mode
See how changing one variable affects formula results by building a data table. The
table calculates and displays all the results at the same time in the same place.
Click cell D5.
When creating a data table, you must enter the cell reference for the input cell in the
upper-left corner of the data table.
Type =B4 in cell D5.
Press ENTER.
The variable input for the data table will be the interest rate referenced in cell B4.
Click cell G5.
, The top row of a data table contains the formulas that are affected when the input
cell changes. The first two formulas are already entered; you will enter the third
formula here.
Type =B9 in cell G5.
Press ENTER.
Excel copies the value and underlying formula of cell B9 to cell G5. The data table
will return results for the Monthly Payment, Total Interest Paid, and Total Loan Cost
values in columns E, F, and G, referencing the formulas in cells B7, B8, and B9
respectively.
Click and drag to select range D5:G13.
This is the range you will use for your data table.
Click the Data tab on the ribbon.
You will create the data table itself using Excel's What-If Analysis tool.
In the Forecast group, click the What-If Analysis button.
The What-If Analysis menu opens.
Click Data Table.
The Data Table dialog box opens with the insertion point in the Row input cell text
box.
Click the Column input cell text box, and type B4 in the text box.
Excel enters the cell reference for cell B4, the interest rate, as the input in the text
box.
Click OK.
Excel populates the data table with result values in columns E, F, and G for each of
the interest rate inputs in column D.
4. Name a table. EX 9353 Observe mode
Assign names to tables to identify their content and better organize your worksheet.
Increase speed and accuracy by using table names rather than cell references in
formulas, procedures, queries, and more.
Click cell A4.
The table is selected and the Table Tools Design tab appears on the ribbon.
Click the Table Tools Design tab.
In the Properties group, click the Table Name text box, and type Clients and press
ENTER.
Excel renames the table Clients. If you click the Name Box arrow on the formula bar,
the table name will appear on the list of available range and table names.
5. Apply a table style. EX 9359 Observe mode
If you do not like the color and formatting of your current Excel table style, you can
easily change it. Choosing a new style from the Table Styles gallery to coordinate
with other areas of the worksheet can add professionalism to your work.
Click cell A5.
The Table Tools Design tab appears on the Ribbon.
Click the Table Tools Design tab.
In the Table Styles group, click the More button.
Excel displays a gallery of table styles.
In the gallery, in the Medium section, click Green, Table Style Medium 7, the last
option in the first row of the section.
Excel applies the new table style to the table.
