Pivot Tables & Charts - Excel 2016
PivotChart
-a graphical representation of data in a PivotTable -interactive-> changes made in associated PivotTable, immediately reflected here -DYNAMIC -need to refresh, delete & recreate
calculate field
-a user-defined field -does not exist in the original dataset -use basic arithmetic operations (no cell references or range names) -use built-in calculations
PowerPivot
-built-in add-in -allows large #s of rows of data from multiple data sources to be imported -creates a relationship b/w 2 or more related tables within a workbook -maintains connection b/w the tables
PivotTables are dynamic
-data can be rearranged -expanded or collapsed -grouping -row & column categories can be switched
data mining
-is a PROCESS -analyzes large volumes of data -identifies trends and patterns
subtotal
-to insert subtotal rows at each designated field change in a sorted range of data -calculated using summary functions -additional levels of these can be inserted
slicers
-used to filter data in a PivotTable -creates options on screen to help user sort through data (quick filtering) -used to focus your audience
How many variables does Goal Seek change with each time it is run?
1
How many scenarios can Scenario Manager manage?
32
Which of the following would be an effective calculated field labeled Retail Price to calculate a retail price that is 300% of wholesale price (for example, the wholesale price is $3 and retail price is $9) from a Wholesale Price field?
= Wholesale Price *3 with a Name of Retail Price in the Insert Calculated Field dialog box
Which PivotTable calculated fields in correctly constructed to calculate a 20%tip on a meal at a restaurant?
='Meal Cost'*.2
In the following pivot table question, both animal type and animal breed are in the same pivot table input box. The sums are available individually. These individual sums, combining both the restrictions of pet type and animal type, could also be found by using which Excel function on the table?
=sum()
Power Pivot
A built-in add-in program that enables users to create a PivotTable from multiple related tables
Embedded chart
A chart placed on a worksheet rather than on a separate chart sheet
embedded chart
A chart placed on a worksheet rather than on a separate chart sheet
embedded chart
A chart placed on a worksheet rather than on a separate chart sheet.
Dual-axis chart
A chart that uses the left side (or top) of the chart as one value axis (y-axis) and the right side (or bottom) of the chart as a second value axis (y-axis)
dual-axis chart
A chart that uses the left side (or top) of the chart as one value axis (y-axis) and the right side (or bottom) of the chart as a second value axis (y-axis)
Criteria filter
A filter option that allows you to specify various conditions in addition to those that are based on an equals criterion. 282
Chart
A graphical representation of numeric data in a worksheet.
chart
A graphical representation of numeric data in a worksheet.
PivotChart
A graphical representation of the data in a PivotTable.
PivotChart
A graphical representation of the data in a PivotTable. 298
Record
A group of related fields in an excel table. 258
What is an outline?
A hierarchical data structure created by the subtotal feature.
Outline
A hierarchical structure of data that you can group related data to summarize.
PivotTable
A highly configurable table that condenses large amounts of data. A PivotTable is used to analyze and display numerical data in detail and to answer unforeseen questions about data.
Axis
A line bordering the chart plot area used as a frame of reference for measurement.
axis
A line bordering the chart plot area used as a frame of reference for measurement.
DatePart Function
Access function that examines a date and returns a portion of the date.
To modify a PivotTable: select the name of the field you want to modify. On the PivotTable Tools, Analyze tab, in the ______ Field group, select ______ ______.
Active, Field settings
CTRL + Mouse Click
Add entry to slicer section
You would like to see both average sales and total sales for a category in a pivot table. Which of these would best accomplish both a Total and an Average column on the field Sales?
Add sales to the VALUES area and change its Value Field Settings to Average in the Summary value field by and then repeat allowing it to default to SUM.
A list of active and inactive Add-ins is in the ________.
Add-Ins Dialog box
A program that can be added to Excel to provide enhanced functionality.
Add-in
An ____ is a program in Excel which provides enhanced capabilities over the base installation.
Add-in
Ope pivot table wizard
Alt + D + P
Create pivot chart on the same worksheet
Alt + F1
Ungroup pivot table items
Alt + Shift + Left arrow
Group pivot table items
Alt + Shift + Right arrow
PivotTablereport (a.k.a. PivotTable)
An interactive table that: •Uses calculations to consolidate and summarize data. •Enables you to analyze data in a dataset. •Can easily and quickly be rearranged to analyze data from different viewpoints. •Looking at data from different perspectives helps identify trends and patterns among the variables that might not be obvious from looking at hundreds or thousands of rows of data yourself.
What settings should you select for a PivotTable if you want to apply a different color scheme and display different fill colors for main category rows and horizontal lines within the PivotTable
Banded Columns check box and a different PivotTable style
Format of a PivotTable
Basic formatting consist of primary row labels formatted in bold and subtotals are bold
A constraint that Solver enforces to reach the target value.
Binding constraint
PowerPivot
Built-in add-in Excel program that create a relationship between two tables
Filter button
Buttons that appear in each column of the header row and open a menu with options for sorting and filtering the table data. 266
A section within the PivotTable Fields List used to place a field that will display labels to organize summarized data vertically in a PivotTable.
COLUMNS area
To add new fields to your pivot table which are not there in the original source data, you use ___________ fields option. Hint: You have added sales tax column to the total amount using this feature.
Calculated
A user-defined field that performs a calculation based on other fields in a PivotTable.
Calculated Field
what field is a user defined field?
Calculated Field
What field is in the ROWS area of the PivotTable that built this PivotChart?
Category
_______ fields: fields that group the values in the PivotTable.
Category
ALT + J, Y, S
Change PT Style
ROUND Function
Changes a value by rounding it to a specific number of decimal places.
The ________ are the cells containing variables whose values change within the constraints until the objective cell reaches its optimum value.
Changing variable cell
A cell containing a variable whose value changes until Solver optimizes the value in the objective cell.
Changing variable cells
ALT + C
Clear slicer selection
You have created a Pivot Table and made some changes to values in the original dataset from which the PivotTable was created. How does this affect the PivotTable?
Click Refresh in the Data group on the Options tab to update the PivotTable. Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.
How do you split the sales data worksheet into panes?
Click a cell, then click the view tab, then click split.
How do you add fields to the filters area
Click and drag from your PivotTable.
How do you create a slicer?
Click on the Table Tools Design tab and click insert slicer.
How do you clear a slicer?
Click the clear filter button on the side of an active slicer.
How do you calculate subtotals?
Click the data tab on the ribbon, then click the subtotal button. You can then click the "at each change in" button to pick when you want a running subtotal.
Which option would clear a PivotTable filter on a State field?
Click the funnel icon to the right of the State filter and then select All and then click OK.
How do you crate a pivot Table.
Click the insert tab on any table and in the tables group, click the pivot table button.
ALT + A, H
Collapse entire field
The subtotal feature creates an outline, where categories can be ___ or ___.
Collapsed or expanded
On the far right, in the figure above, there is the PivotTable field list. The items listed there, such as "Day" and "Segment" are ____ from the original source table.
Columns
A limitation that imposes restrictions on Solver.
Constraint
What do you hit to make cell A1 the active cell?
Control home. 266
If Ted decides that he wants to work with nonnumeric data in his PivotTable, what function must he use to produce summaries?
Count
ALT + N, C
Create Pivot Chart
ALT + N, V
Create Pivot Table
RMBK, T, S
Create a filter rule for PT
DATE Function
Creates a date value for the date represented by the year, month and day arguments.
Hide pivot table item
Ctrl + -
Select entire pivot table
Ctrl A
What are PivotTables?
DYNAMIC
If solver is installed on your computer, it will display on the ____ tab.
Data
The process of analyzing large volumes of data to identify trends and patterns in the data
Data mining
____is the process of analyzing large amounts of data to identify trends and patterns in the data
Data mining
A collection of related tables that contain structured data used to create a database
Data model
If Solver is loaded, it will appear on this tab.
Data, Analyze
Sort Data in a PivotTable
Default is alphabetical by row label text
Discount Rate is a valid range name.
False
Displaying subtotals at the top or bottom of a section is controlled on the PivotTable styles gallery.
False
If you are interested in finding what products people buy when they buy coffee, you would employ data warehousing techniques on supermarket sales data
False
Pivot tables are updated automatically when the source data changes.
False
Goal Seek differs from one and two-variable data table analysis by ________.
Goal Seek uses the original worksheet data to change an input.
A process of joining related rows or columns of related data into a single entity so that groups can be collapsed or expanded
Grouping
Ted wants to be able to analyze date data in his PivotTable by month. What must he do with the date field in his PivotTable?
He must group the data by the date field
Ted wants to use his PivotTable to show the total value of the motorcycles for a series of motorcycle dealerships but he wants to filter the display to show only those dealers whose total stock of motorcycle is greater than $1 million. How would he accomplish this?
He would develop a PivotTable using the value filter.
How do you convert your excel table back into a range?
Hit the convert range button on the Table tools design tab. 276
How do you freeze panes?
Hit the view tab and click the freeze panes button. 263
How do you exclude values in a slicer table?
Hold the control key as you click the options you don't want.
Match each of the following tools to its best description of its function: I. Range name II. What-if analysis III. Substitution value IV. Step value V. Stop value A. Enables you to experiment with different variables to observe how they affect the outcome. B. Memorable name substitution for formulas C. The value that is changed in what-if analysis. D. Value increments when filling a series E. The ending value for the series
I=B , II=A , III=C , IV=D , V=E
Match each of the following terms to its best description: I. Scenario II. Scenario Manager III. Scenario Summary Report IV. Changing Cell V. Scenario Summary PivotTable A. Displays different scenarios with their input values and results well organized on a new worksheet B. Values that represent different possible situations C. Variable D. Tool that enables you to build different scenarios for comparison E. An optional output
I=B , II=D , III=A , IV=C , V=E
Match each of the following terms to its description: I. Substitution Value II. What-if analysis III. Changing variable cell IV. Range name V. Variable A. A cell containing a variable whose value changes until Solver optimizes the value in the objective cell B. Replaces the original value of a variable in a data table C. A value that you can change to see how that change affects other values D. A word or string of characters that represents one or more cells E. The process of changing variables to observe how changes affect calculated results
I=B , II=E , III=A , IV=D , V=C
Match each of the following terms to its best description: I. Answer Report II. Scenario Summary Report III. Scenario PivotTable IV. Goal Seek Dialog Box V. Optimization Model A. Tool to input the set cell, to value, and by changing cell B. Finds the highest, lowest or exact value for a particular result by adjusting variables C. Output from Solver which displays on a new worksheet. D. Organizes the results from a set of scenarios in a PivotTable for further analysis. E. Organized structured table of scenarios, their input values, and their respective results that displays on a new worksheet
I=C , II=E , III=D , IV=B , V=A
Match each of the following terms to its description: I. Binding constraint II. Changing variable cell III. Add-in IV. Constraint V. Solver A. Enhances functionality in Excel B. Manipulates variables based on constraints to find the optimal solution to a problem C. Limit placed on the Solver model D. Solver must enforce this to solve the model. E. Cell containing a variable whose value changes until Solver optimizes the value in the objective cell.
I=D , II=E , III=A , IV=C , V=B
Chart area
The entire chart and all its elements
chart area
The entire chart and all its elements
chart area
The entire chart and all its elements.
Category fields
The fields that group values in a PivotTable. 298
Header row
The first row of the table that is formatted differently and should be repeated for tables that continue beyond one page. 258
Header row
The first row of the table that is formatted differently and should be repeated for tables that continue beyond one page. 266
Primary sort field
The first sort field when you use the sort dialog box. 272
data mining
The process of analyzing large volumes of data to identify patterns and trends
Grouping
The process of joining rows or columns of related data together into a single entity so that groups can be collapsed or expanded for data analysis.
Sort
The process of rearranging the records in a table based on one or more fields. 258
Secondary sort field
The second field when you use the sort dialog box. 272
You have just created slicer for the State field in a PivotTable. Which of the following does not characterize the initial slicer?
The slicer buttons are set to filter out all records.
Pivot table
The tool used by most spreadsheet programs to create a summary table.
Which of the following is a true statement Carter can make to Ted about PivotTables?
To remove a field from a PivotTable, you click the field's check box in the PivotTable field list.
What tool is used to edit pre-existing range names?
Name Manager
Do Excels automatically update Pivot Tables?
No
Can you change data in a PivotTable?
No, you must edit the excel table and the refresh
A constraint that does not restrict the target value that Solver finds.
Nonbinding constraint
The limit of no more than a $50,000 down payment is an example of a(n) ________ when calculating the optimal combination of down payment and interest rate to purchase a new printing press.
Nonbonding constraint
A PivotChart is by definition linked with a PivotTable. If you modify one, the other also changes accordingly.
True
What is subtotaling data?
Presents summary information that helps to analyze the data.
Data mining
Process of analyzing large volumes of data, using advanced statistical techniques, and identifying trends and patterns in the data.
A section within the PivotTable Fields List used to place a field that will display labels to organize data horizontally in a PivotTable.
ROWS area
A worksheet contains a PivotTable placeholder and the PivotTable Fields List. No fields have been added to the Pivot Table yet. If you click the College Major field check box in the PivotTable Fields List, where does excel place this field?
ROWS area
A word or string of characters that represents one or more cells
Range name
You cannot change data directly in the PivotTable. Instead, you must edit the Excel table, and then ____, or update, the PivotTable to reflect the updated data.
Refresh
CTRL + ALT + F5
Refresh all Pivot Tables
An association created between two tables where both tables contain a common field of data
Relationship
RMBK, T, C
Remove a filter from PT
Two Types of Filters
Report and Group
To create a PivotChart: click anywhere in the PivotTable to show the PivotTable Tools on the ______. On the PivotTable Tools, ______ tab in the Tools group, select PivotChart _____. In the Insert Chart ______ _____, click the chart type and subtype you want.
Ribbon, Analyze, button, dialog box
The___function is the default summary statistic for PivotTables
SUM
Pivot summarizes data using what points?
SUM, COUNT, AVERAGE, MIN, MAX, PRODUCT, COUNT NUMBERS, STDEV,STDEVP, VAR & VARP
What is the best option for a field to build a relationship between the tables Sales Personnel and Orders with the following fields?
Sales Person ID
A set of values that represent a possible situation.
Scenario
Enables you to define and manage scenarios to compare how they affect results.
Scenario Manager
___ can define 32 different scenarios for comparison.
Scenario Manager
Which dialog box enables you to specify the result cells for a scenario summary report?
Scenario Summary
The what-if analysis tool best suited to evaluating multiple scenarios within a formula is____
Scenario manager
Which tools are best suited to calculate the impact of multiple interest rates on an auto loan?
Scenario manager, one-variable data table
A worksheet that contains scenario results.
Scenario summary report
How do you make a table?
Select a range of cells, click the insert tab on the ribbon, then click the Table button. 264
Which of the following is not a valid method to remove a field from a PivotTable?
Select the field to be removed in the PivotTable and press DELETE.
What does applying a banded row format do to a Pivot Table?
Shades odd and even rows differently
ALT + J, T, A
Show values as:
A window listing all unique items in a field so that the user can click button to filter data by that particular item or value
Slicer
A pivot table groups data into categories and then use functions to summarize data.
True
A relationship is an association between two tables where the tables contain a common field of data
True
The text or field name that appears as a header or title at the top of a slicer to identify the data in that field
Slicer caption
What filters data in a Pivot Table
Slicers
An add-in application that manipulates variables based on constraints to find the optimal solution to a problem.
Solver
This analysis tool has the ability to handle multiple adjustable cells while minimizing, maximizing, or meeting goals.
Solver
Which of the following is an Excel add-in?
Solver
Which of the following tools can incorporate constraints?
Solver
Which what-if analysis tool is the best option for complex calculations requiring constrained optimization?
Solver
How can you determine if the Solver add-in is active? (Check all that apply.)
Solver appears on Data tab of the Ribbon, Solver is an option in the Home tab of the Ribbon
RMBK, S
Sort PT cells
_______ allows you to organize the data in the PivotTable so it's easier to find the items you want to analyze.
Sorting
The data used to create a pivot table is referred to as?
Source data
Toggle pivot table field checkbox
Space
Student Last Name, Student First Name, College, Major, and GPA. Data are sorted by College, then by Major, and in setting within the Subtotal dialog box, and what would be a more appropriate setting?
Student Last Name (default field), College (correct field)
Replaces the original value of a variable in a data table.
Substitution value
A row that contains a t least one aggregate calculation, such as SUM or AVERAGE
Subtotal
___display the total values for each particular row or column in a PivotTable
Subtotals
By default, the PivotTable report uses the ____ function for numbers in the Values area.
Sum
You created a PivotTable to summarize commissions earned by employees in each department. What is the default summary statistic for the commissions field when you add it to the PivotTables
Sum
ALT + J, T, D
Summarize values by
To insert a PivotTable you would go to the INSERT tab on the ribbon and go to the ______ group
Tables
IsNull Function
Tests a field value or an expression for a null value; if the field value or expression is null, the result is true; otherwise, the result is false.
data label
Text that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell
Data label
Text that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.
data label
Text that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.
Creating a Calculated field uses...
basic arithmetic operations, but not cell references or range names, built-in calculations
To create range names for multiple cells at once, use the ____tool
create from selection in defined names group on formula tab
Subtotal feature
creates an outline, where categories can be collapsed or expanded. outline buttons are used to determine which values are displayed.
counts
crosstabs typically only list ___, but pivot tables can list other summary measures
slicers
introduced in 2010, lists of the distinct values of any variable that can be filtered
timeline feature
introduced in 2013, acts like a slicer but filters specifically based on date variables
Data Mining
is a PROCESS that...analyzes large volumes of data, uses advanced statistical techniques, Identifies trends and pattern
PivotChart
is a graphical representation of data in a PivotTable
Sort button
is in data group on table tools layout 271
Grouping is___
joining rows or columns of related data into a single entity for analysis
Filtering a Field
lets you focus on a subset of items in that field
banding
line by line alternates color
Slicer
small window containing one button for each unique item in a field so that you can filter the PivotTable quickly
editions
sort through these
Subtotals
temporary column sums, averages, and other calculations created with the Excel Subtotal feature
Slicer Caption
text that displays in the header at the top of a slicer window
Filtering
the process of displaying a subset of rows in an excel table or a structured range of data that meets the criteria you specify 276
compact, outline, tabular
three layouts available from the report layout list on the design ribbon
purpose of excel
to mine data
Change the Values Field
to modify the summary statistical function, column heading, or number format
what does a pivot table allow you to do?
to summarize, analyze, and explore large amounts of data
range of data
top section of the dialog box allows for specification of ___
Goal Seek is a what-if analysis tool.
true
Solver has the ability to generate an answer report.
true
Solver is an Excel Add-in.
true
TRUE
true or false: multiple variables can exist in the values field
FALSE
true or false: the values field can only show the sum of values in a category
Group and Ungroup Data
turn related data into an outline...you can expand/collapse them depending on your focus...needs to contain a formula
analyze and design
two ribbons contained in the super tab
numeric
typically, these variable types are placed in the values area
categorical
typically, these variable types are places in filters, rows, or columns
A valid character to separate words in a range name is____
underscore
Calculated Field
user defined field that gets its value based on performing calculations in other fields
An____is the changeable input value used in a what-if analysis
variable
absolute
what kind of references are for PivotTables?
data mining
what process do PivotTables support?
insert ribbon
where is the pivot table button located
Are Pivot Charts interactive?
yes, , meaning changes made in the associated PivotTable are immediately reflected in the PivotChart
True/False : You cannot change the data directly in the PivotTable!
True
True/False: If you make changes to the underlying data in the PivotTable, you must update the PivotTable
True
When you create a Pivot Table, you need to specify where to find the data for the PivotTable. Excel may help you to find the data range but you have the duty to confirm it.
True
You can create more than one slicer in a pivot table.
True
A data analysis tool that provides results based on changing two variables.
Two-variable data table
Which tool is most effective when comparing the impacts of various combinations of interest rates and down payments on a mortgage?
Two-variable data table
How do you sort from multiple columns?
Use the sort dialog box. It is found in the data tab.
A section within the Pivot Table Fields List used to place a field to display summary statistics, such as totals or averages in a PivotTable.
VALUES area
In the pivot table terminology, which fields display the numbers that are summarized in the pivot table, for example, amount?
Value
A value that you can change to see how that change affects other values.
Variable
The process of changing variables to observe how changes affect calculated results.
What-if analysis
____ enables you to experiment with different variables or assumptions so you can compare the related outcomes.
What-if analysis
What do outline buttons control?
Which summary statistics are displayed.
Outline buttons are used to determine what?
Which values are displayed.
You created an outline within a worksheet. What does the - button indicate to the left of a row heading?
You can click it to collapse the details of that category.
group
_______ rows or columns of related data into an outline -expand or collapse these depending on your focus -must contain a formula
Relationship
a connection between 2 tables where both tables contain a common field of data
outline
a hierarchical structure of data
Using the % of Grand Total Calculation Option will display the value as___
a percentage of the grand total
Subtotal
a row that contains one or more aggregate functions. such as sum or average, of values within each category within a dataset
Pivot Table values area
a section within the pivot table field list used to place a field to display summary statistics such as total or averages in a pivot table
Pivot table row area
a section within the pivot table field list used to place a filed that will display labels to organize data horizontally in a pivot table
Pivot table columns area
a section within the pivot table fields list used to display columns of summarized data for the selected filed that will display labels to organize summarized data vertically in a pivot table
Pivot table filter area
a section within the pivot table fields list used to place a field so that the user can then filter the data by the field.
Slicer
a small window containing one button for each unique item in a field so that you can filter the pivot table quickly
structured reference
a tag or table name that replaces a cell name
PivotTable Fields List
a task pane that displays the list of fields in a dataset and the areas to place those fields to create the layout that organizes the data into a PivotTable
Descending order
arranges text in reverse alphabetical order from Z to A, numbers from largest to smallest and dates from newest to oldest 271
relationship
association b/w 2 related tables containing a related field of data
Relationship (PowerPivot)
association between two related tables containing a related field of data
Sort field
field used to determine the order of records in the datasheet 258
PivotTable Timeline
filters data based on a particular date range
Group Filter
filters out data based on a row or column category
Group filter
filters out data based on a row or column category
group filter
filters out data based on a row or column category
Report filter
sets the overall conditions for aggregating data
discipline
shows all of the versions you can chose from
what is data mining
* a process *analyzes large volumes of data *identifies trends and patterns
A Pivot Table allows you to specify different columns of data to compare to each other, using functions such as count, sum, etc... So, for example, if I created the following pivot table from the data table, which answer is the correct setup to get the pivot table shown?
*Rows:* Pet Type *Columns:* Animal Type/Breed *Values:* Sum of Number
What does a Pivot Charts do?
- PivotChart is a graphical representation of data in a PivotTable. -You can create a PivotChart from a PivotTable or at the same time you create a PivotTable. -Creating and customizing PivotCharts is similar to the methods used on regular charts. -A PivotChart is interactive, meaning changes made in the associated PivotTable are immediately reflected in the PivotChart.
What does a PowerPivot do?
-Allows large numbers of rows of data from multiple data sources to be imported -Creates a relationship between two or more related tables within a workbook Maintains connections between the tables
Slicers
-Are graphical -Provide buttons that you can click for quick filtering -Indicate the current filtering state
PivotTable/Chart
-DYNAMIC -interactive data-mining feature -great summarizers of data -can't change data directly (refresh)
What do Pivot Tables do?
-Data can be rearranged -Details can be expanded or collapsed -Data can be organized and grouped differently -Row and column categories can be switched
Calculated Field
-Is a user-defined field -Does not exist in the original dataset
Parameter Query
A query in which the user interactively specifies one or more criteria values.
Which of the following is not an acceptable range name
A range name in excel can not start with a number and it should start with a letter only. So, 2018_Rate is not an acceptable range name. Range name must contain word or string of characters to one or more cells and not shortcuts.
Excel table
A range of related data that is managed independently from other data in the worksheet 259
Total row
A row inserted at the bottom of a table that provides a menu of functions for each column in the row. 266
Data series
A row or column of data represented by a line, set of columns, bars, or other chart type.
data series
A row or column of data represented by a line, set of columns, bars, or other chart type.
Subtotal row
A row that includes one or more aggregate functions, such as a sum or average, of values within each category within a dataset.
What is a scenario in Excel What-if analysis?
A set of values that represent a possible situation
Chart sheet
A sheet that contains only a chart.
chart sheet
A sheet that contains only a chart.
A one-variable data table can be built ____
A structured range that contains different values for one variable to compare how these values effect one or more calculated results. For example, you can use a one-variable data table to compare monthly payments on a mortgage. You can compare how different values of the interest rate affect the calculated results.
Relationship
A term that describes when two tables have a connection between two fields.
relationship
A term that describes when two tables have a connection between two fields.
Slicer
A tool that filters the entire PivotTable, restricting the sums, counts, averages, and so on, to only those fields selected in the slicer or filter field.
slicer
A tool that filters the entire PivotTable, restricting the sums, counts, averages, and so on, to only those fields selected in the slicer or filter field.
Power Pivot
A tool that offers Excel users the ability to generate PivotTables very quickly on very large data sets of tens or even hundreds of millions of rows.
Field name/column header
A unique label that describes the contents of the data in a column. 259
What is a slicer
A window listing all items in a field and enabling efficient filtering
You created a slicer on the field State. What items would you be most likely to see in the slicer?
AL, CA, GA, KY, MS, NC, OR, and VA
Given a list of candy bars in a field called Item and their prices in a field called Price on an empty PivotTable, how would you find out how many of each type of candy bar you have
Drag Item to both the VALUES and ROWS area
columns area
Drag fields here to add more vertical data to a PivotTable
Rows area
Drag fields here to display categories horizontally in a PivotTable
If you would like to show a subtotal of the wholesale price of a number of items of clothing on a PivotTable of data of both men and women. You have the following fields: Gender, Item and Price. Sum of Price is in the VALUES area. Gender is in the ROWs area. How do you get a subtotal of shirts and pants to display for each gender?
Drag the Item field beneath Gender in ROWS
Currently, the House Types field is in the ROW area, the Reals Estate Agent field is in the COLUMNS area, and Sum of List Prices is in the VALUES area. How can you modify the PivotTable to display the agent names as subcategories within the house types in the first column.
Drag the Real Estate Agent field from the Column Labels area, and then drop it below the House Types field in the Row Labels area.
Which of the following is not a solving method supported by Solver?
Duplex
When Making Charts, you should do all of the following if possible, except?
Ensure the chart has images and clip art relevant to it's topic to make it appear dynamic and interesting.
IIF Function
Evaluates a condition and returns one value if the condition is true and another value if the condition is false.
Quick Analysis gallery
Excel displays Recommended PivotTables based on the data
Pivot Table
Excel's interactive data mining feature, used to group and summarize data in a professional looking report
Create pivot chart on new worksheet
F11
What is the keyboard shortcut to paste range names?
F3
A section within the PivotTable Fields List used to place a field so that the user can then filter the data by that field.
FILTERS area
In a PivotTable Fields task pane, the___filters the data to display results based on particular conditions you set.
FILTERS area
Build a PivotChart in the PivotChart wizard on the PivotTable Tools Design tab
False
You can drag one field to the FILTERS area of the PivotTable Fields pane to change what values are displayed in the original source table.
False
You cannot change the label of a value
False
Known as the "upper section" of a PivotTable, lists the name of each field in the data source.
Fields Section
Value field
Fields that contain summary data in a pivot table. 298
You have created a PivotChart showing sales by quarter by sales rep. Before presenting it to management, you notice the name of rep who has since been fired. How do you remove this rep from the chart without deleting the data?
Filter the Sales Rep field in the PivotChart and deselect the employee's check box.
In pivot table terminology, which fields are used to select one or more items to display in the pivot table i.e. only show data you want. smaller portion of large data
Filters
True/False: Excel does automatically update PivotTables!
Flase
RMBK, F
Format PT cells
banded rows
Formatting added to alternating rows so that even and odd rows are different colors, making it simpler to distinguish records 266
First column
Formatting added to the leftmost column of the table 266
last column
Formatting added to the rightmost column of the table 266
How do you refresh a pivot table.
Go to the pivot table. Click the Pivot Table Tools Analyze tab. Click refresh.
A tool that identifies the necessary input value to obtain a desired goal.
Goal Seek
Doug would like to purchase a new automobile. He has budgeted for $600 per month. If the interest and number of payments are constant variables that cannot change, which analysis tool should Doug use to calculate the amount to spend on a car?
Goal Seek
This tool calculates the value required in a single cell to produce a desired result within a related cell.
Goal Seek
Split bar
Indicates a division between the panes. 277
ALT + J, T, S, L
Insert Slicer
ALT + J, Y, R
Insert banded rows
Which of the following is true with respect to this pivot table?
It is using Animal Type as a Legend (Series)
Ted wants to be able to use his PivotTable to combine items into groups. Which of the following is true?
Items that appear as column labels can be grouped
The four squares labeled FILTER, COLUMNS, ROWS, and VALUES. These four areas represent the ____ of the PivotTable.
Layout
Freezing
Lets you keep the headers visible as you work with the data in a large worksheet. 263
Slicer
Object used to filter the data in an excel table. 276
The cell that contains the formula-based value that you want to maximize, minimize, or set to a value in Solver.
Objective cell
How do you add a total row to a table?
On the ribbon, click the Table tools Design tab and click on the total row option in the Table Styles option.
How do you remove duplicate records from a table?
On the ribbon, click the table tools design tab and click remove duplicates. 270
Pane
One of the resizable areas generated when you want to view a large worksheet. 277
A data analysis tool that provides various results based on changing one variable.
One-variable data table
RMBK, G
Open Grouping dialog box
Finds the highest, lowest, or exact value for one particular result by adjusting values for selected variables.
Optimization model
A ___________ chart is a graphical representation of the data in a pivot table.
Pivot
A ______ allows you to add, remove, filter, and refresh data fields.
PivotChart
A graphical representation of aggregated data derived from a PivotTable
PivotChart
A PivotChart is similar to a regular chart, except it is based on the categories and structure of a
PivotTable
A task pane that displays the fields in a dataset and enables a user to specify what fields are used to create a layout to organize the data in columns, rows, values, and filters in a PivotTable
PivotTable Fields List
An interactive table that uses calculations to consolidate and summarize data from a data source into a separate table to enable a person to analyze the data in a dataset without altering the actual data.
PivotTable report
A set of formatting that controls bold, font colors, shading colors, and border lines
PivotTable style
A small window that starts with the first date and ends with the last date in the data source. It contains horizontal tiles that you can click to filter data by day, month, quarter, or year
PivotTable timeline
Modifying a PivotTable
add, remove, or rearrange fields to get a different perspective of the data
filters field
allows for separation of a pivot table by its categories
Report Filter
allows you to filter the PivotTable to display summarized data for one or more field items or all field items in the Report Filter area
what is a report filter
allows you to filter the PivotTable to display summarized data for one or more field items or all field items in the Report Filter area
report filter to a PivotTable
allows you to filter the PivotTable to display summarized data for one or more field items or all field items in the report filter area
Pivot chart
an interactive graphical representation of data in a pivot table
PivotTable
an interactive table that enables you to group and summarize either a range of data or an Excel table into a concise, tabular format for easier reporting and analysis
PivotTable
an interactive table used to group and summarize either a range of data or an Excel table into a concise, tabular format for reporting and analysis 298
super tab
appears when any cell inside the pivot table is selected
Ascending order
arranges text alphabetically from A to Z, numbers from smallest to largest and dates from oldest to newest 271
why people prefer to use slicer that a regular filter
because slicer display color-coded buttons to show which fields are being filtered , whereas using the filter arrows within the pivot table do not show what fields are currently being filtered
An ____ is a rule that Solver has to enforce to reach the objective value
binding constraint
location for results to be placed
bottom section of the dialog box allows users to select ___
A slicer can be formatted so the___display in one or many columns.
buttons
Subtotals (Subtotal Data)
calculated using summary functions
columns field
categories that go across the top of a pivot table
rows field
categories that go down the left side of a pivot table
if you make changes to the underlying data in a PivotTable...
click on PivotTable and refresh
Data Model
collection of related tables that contain structured data used to create a database
values field
contains data to be summarized
Outline Buttons
control which summary statistics are displayed
PivotTable Style
controls bold formatting, font colors, shading colors, and border lines
What process do PivotTables support?
data mining
A____is a collection of related tables forming a database
data model
A PivotTable time line can filter dates by all of these, except____
decade
Grand Totals (Subtotal Data)
displayed after each group
grand totals
displayed after each group
COLUMNS Area
displays columns of summarized data for the selected field(s)
VALUES Area
displays summary statistics, such as totals or averages, for the selected field
FILTERS Area
displays top-level filters above the PivotTable so that you can set the filters based on your own conditions
The difference between =vlookup() and =hlookup() is what?
each record in a vlookup is in a column, and in hlookup is in a row.
A PivotTable Calculated field starts with___
equal sign
pivot tables
excel tool that allows for the breakdown of data by category, used to display tables of counts
Pivot Tables
extends the capability of database functions by presenting the data in summary form, can display Pivot Charts, and can be saved as Web pages with full interactivity
A Scenario Manager Summary report displays the single best input to achieve the desired outcome.
false
A two-variable data table is the most effective tool to find the appropriate interest rate and purchase price to make a monthly payment exactly $500 in a car loan.
false
Solver is on the Formulas tab.
false
what does filtering a field allow you to do?
focus on the subset items in that field.
filter, rows, columns, values
four areas indicated in the pivot table pane
ROWS Area
groups data into categories in the first column based on the selected fields
Outline
hierarchal structure of data that you can group related data to summarize
Outline
hierarchical data structure created by the Subtotals feature
group data
if dataset contains a formula, excel can create an automatic outline
Subtotal Command is Used to...(Subtotal Data)
insert subtotal rows at each designated field change in a sorted range of data
PivotTable Report
interactive table that uses calculations to consolidate and summarize data from a data source into a separate table
Pivot Chart
interactive, graphical representation of the data in a PivotTable
Which of these characters is not an acceptable character to use in a range name?
must begin with a letter, or underscore, upper or underscore combinations, numbers, periods and underscores throughout the range name. A range name can not include spaces or special characters
Where does Excel create a Scenario Summary report?
new work sheet
An ____constraint does not restrict the target that Solver finds.
nonbinding constraint
categories
one column w/ duplicate data
crosstabs or contingency tables
other names for pivot tables
A hierarchical structure of data that you can group related data to summarize
outline
An___is a hierarchical structure used to group and summarize related data.
outline
A primary benefit of creating a PivotTable is that you can rearrange or___fields to view and summarize data from different perspectives.
pivot, turn
grouping and subtotaling data...
presents summary info that helps to analyze the data
Grouping and Subtotaling Data
presents summary information that helps to analyze the data
Data mining
process of analyzing data using advanced statistical techniques to identify trends and patterns in the data
Data Mining
process of analyzing large volumes of data, using advanced statistical techniques, and identifying trends and patterns in the data
Grouping
process of joining rows or columns of related data together into a single entity so that groups can be collapsed or expanded for data analysis
Table styles
provide a way to quickly format an entire table using a preset style definition 259
Solver does not support which of the following calculations on the target cell?
range
The___area is replaced with the AXIS(CATEGORY) in PivotChart task pane
rows
report filter
set the overall conditions for aggregating data
Report Filter
sets the overall conditions for aggregating data