Lesson 3: PIVOT TABLES AND PIVOT CHART Flashcards
A PivotTable is a powerful tool for exploring and _______
analyzing information
A PivotTable helps you _________
organize and manipulate the raw data in your spreadsheet
blank rows in a source list or database can
limit the usefulness of your PivotTable
We can use aggregate functions such as
SUM, MINIMUM, MAXIMUM, AVERAGE, etc.
To create an Excel PivotTable:
- Select the range of data
- On the Insert Ribbon, Tables group, click the PivotTable button
- When the Create PivotTable dialogue box appears, choose the table or range option, so the PivotTable will be based on the Excel table or range you selected.
- Once you select your data source, you can then choose to locate your PivotTable in an Existing Worksheet or a New Worksheet.
- Click OK to create your empty PivotTable ready for population by chosen fields.
PivotTables and Charts enable us to
summarize and analyze large datasets.
To change the Summary Calculation you need to:
- Click any cell inside the Sum of Amount column.
- Right click and click on Value Field Settings.
- Choose the type of calculation you want to use. For example, click Count.
- Click OK
Procedures to apply a style to the PivotTable.
- Select the Design tab of the Ribbon
- Select the small arrow in the PivotTable Styles area to see the PivotTable Style gallery.
- Select an option to apply the style. Shown here is the New Pivot Table Style dialog box.
To insert Pivot Chart from data:
- Place your cursor somewhere in the data you want to analyze.
- Select the Insert tab from the Ribbon
- Select PivotChart.
- Excel automatically provides a range of cells based on your selection. You can change the table or range if desired.
- Select a location for the PivotChart. You can have Excel create a new worksheet or select one of the existing sheets.
- Select OK.
The what if tool tells you the ____________
impact of making a change, whereas the Goal Seek tool tells you the underlying factors that must change to achieve a desired result.
USING THE GOAL SEEK SCENARIO TOOL:
- Open an Excel table.
- Click Scenarios, and select Goal Seek.
- In the Scenario Analysis: Goal Seek dialog box, select the column that contains the target value from the list.
- Specify the value that you want to achieve. If the column goal contains continuous numeric values, you can also specify a desired increase or decrease in the value. For example, you might choose Sales as the column and specify that the target is an increase of 120%.
Or, you can specify the goal as a range of values, by typing a lower and upper limit. - Specify the column that contains the values you will change. In other words, pick the column that will be manipulated to produce the desired result.
- Optionally, click Choose columns to be used for analysis, and select columns that contain useful information. Deselect columns that will not contribute to the analysis.
- Specify whether you want to make predictions for the entire table, or for only the selected row.
- If you selected the Entire table option, the tool adds the predictions to the source table in two new columns.
- If you selected the option On this row, the results of analysis are output to the dialog box for review. The dialog box stays open so that you can continue trying out different values and goals.
3 things the tools does when creating a goal seeking scenario
- Creates a data mining structure that stores key facts about the data in your table
- Creates a logistic regression mining model based on the data.
- Creates a prediction for each value that you specify.
What-If scenario tool analyzes _____
patterns in existing data, and then enables you to evaluate the effect that changes in one column would have on the value of a different column.
