Excel Wrap-Up

• What tab is used to access macro functionality in Excel?• The Developer tab

• Know what a recorded macro is as well as its advantages and disadvantages.

• When using the subtotal feature in Excel, what is your first step?• Sort the data first by the field that you want to see subtotals by/for.

• Pivot Tables can help you see relationships in the data

• Prior to making a pivot table, you should create what sort of statement?• A By statement

• __________ and __________ by ____________ and _____________

Row ColumnData PointValue

Data PointValue

Select what you want to move (Values), right-click, select “Move Values to” and you want to “Move Values to Rows”

When moving values to rows, ALWAYS MOVE THE DATA POINTS (found before the “by” statement). The words after the “by” statement should not be moved because they form the basis of your rows and columns.

Goal Seek

Tells you what must happen to get a certain desired result.

Given that you have a 4% interest rate, a 30 year loan and that you are making one payment a month on a $150,00 house, what does the down payment need to be in order to make a $600 monthly payment?

SolverFinds the very

best answer given various constraints that you must work around or take into account.

The Solver does what?

Optimizes

What-If Analysis using Data Tables

A one variable data table lets you change one input and you can see the effect on several other variables.

A two variable lets you change two inputs and you can see the effect on one output variable.

What-If Analysis Using Scenario ManagerScenario Manager enables you to create as many scenarios

as you want, easily switching between the different scenarios to display the results of several what-if analyses

5) Return to the original Customer Data Sheet. Display all customer records. Next, go to the Production data worksheet. What follows are important definitions of the spreadsheets fields that you will need to keep in mind as you select the fields to include in your Pivot Tables.

Batch ID: a unique number used to identify each batch or group of products produced.

Product ID: a unique number that identifies each product. Machine ID: a unique number that identifies each machine

used in the production process. Employee ID: a unique number used to identify each

employee involved in production Batch Size: the number of products produced in each given

batch (group). Num Defective: the number of defective products produced

in a given batch.Note: carefully think as you build each Pivot Table. The numerical operation will vary from table to table (sum, average, count or some other operator will be used on a varying basis)

Excel Project #2: Case Problem #2