UNIT 10: USING EXCEL PIVOT TABLES
How do you create a chart for a pivot table?
Click a cell in the data table (activates the PivotTable tools). Choose Analyze ---> select PivotChart.
To show numbers as percentages: When you right-click one of the numbers in the column, then you have a number of options. Instead of picking "Summarize Values By", choose "____________" (3 words) instead. After selecting that option, Excel will present you with a variety of ways to show percentages. Choose % of Grand Total.
Show Values As
They are visual buttons Used to apply filters to the data. Show tables with selective information.
Slicers
How do pivot tables resemble ordinary Excel tables?
Both have the filter (down) arrow Both can display totals.
How do you create a slicer?
Click inside the pivot table and choose the Analyze tab. In the Filter group, find the Insert Slicer icon. You will see a field dialog box indicating the various fields. You can select more than one slicer at a time by using ctrl-click to select others
First, insert the pivot table so that you see the display of the dialog box. Drag the necessary data into two places: the rows and into the values. Right-click one of the numbers in the rows. Choose the Group option In your pivot table, group the numbers in the left column, and then select the count calculation for the numbers in the right column.
Frequency Distribution
Pivot Tables: What does the plus box next to the Type fields do?
Indicate that there is more information below. To display that information click the plus button.
Format Numbers Should you select all of the numbers and then apply the accounting or comma formatting to the table?
No
Is converting data into an Excel table before using pivot tables required?
No
A data processing tool used to query, organize and summarize data or information between spreadsheets, tables or databases
Pivot Table
Unique dialogue box that helps you create your pivot table. It has an upper section and a lower section.
Pivot Table Fields
Pivot tables require lists. You need to make sure that the data is well formed. What does this mean?
Place the data at the edge of a spreadsheet Or surround the data with empty cells
To insert a pivot table:
Place your cursor inside the data table. Then go to the Insert table and click Pivot Table. A dialogue box will appear: Choose range and worksheet (new or existing)
A statistical function that calculates the total of a range of values when a specified condition is met
SUMIF Function
What can the filter arrow do?
Select one or several types to display. Sort data in ascending or descending order.
Pivot Tables and Frequency Distributions: To count the number of instances falling into intervals specified in the left column: Right-click a number in the right column Select _____________ (3 words) and then select ________. The count function will count the number of instances falling into the intervals specified in the left column.
Summarize Values By, Count
In preparing data for use with pivot tables (choose all true answers) The data should include totals. The data should be a list. The data should have headings. The data can be adjacent (side to side) with other data if there is no space in between.
The data should be a list. The data should have headings.
Pivot Charts: The chart has several items with down arrows. What do the down arrows represent?
They are filters.
How do you use average, count and max in a pivot table?
To change the individual fields, right-click on a number in each field and then select a different option for Summarize Values By for each one
You can easily add another column to your existing pivot table or make another one:
To clone your pivot table drag your mouse over the pivot table, and use ctrl-c to copy it. Then paste it somewhere else on the worksheet using ctrl-v. Then drag the desired field into the desired quadrant a second time.
Pivot Charts: You can click on ______ and choose what types you wish to display and Excel will change the columns displayed.
Type
How do you change the report layout of your pivot table (i.e. the display settings)?
Under the Design Tab, go to Report Layout in the Layout Group
How would you format your pivot table to have banded rows?
When you click an item inside the table, PivotTable Tools appear in the ribbon. Pick the Design tab. Under Style options, you can select Banded Rows
How can you display subtotals at the bottom of each group in your pivot table? (without using tabular form)
When you click an item inside the table, PivotTable Tools appear in the ribbon. Pick the Design tab. Under the Layout tab there is a Subtotals icon. When you click that icon, you can pick Show all Subtotals at Bottom of Group.
Format Numbers: Should you select one number and right-click it, then select the "Number Format" option from the menu?
Yes
When you click an item inside the table, PivotTable Tools appear in the ribbon. There are two tabs: _________ and ________.
analyze, design
Some users prefer using Excel tables because when data is added at the bottom of the table, that data is _____________ (2 words) in the pivot table
automatically included
What are other calculations you can do in a pivot table (other than finding the sum of some values)?
average, count, and max
Because pivot tables summarize data, there should be no totals at the __________ of the original list/table or elsewhere.
bottom
Headings represent ______________ (3 words).
categories of data
_________ in pivot tables are there because of a calculation with a condition or criteria
cells
In using pivot tables (chose all answers that are correct) the plus sign beside a field indicates that the detailed data is shown. click the minus sign to reduce the data shown. the down arrow beside a field label indicates that data can be filtered. click the plus sign to reduce the data shown.
click the minus sign to reduce the data shown. the down arrow beside a field label indicates that data can be filtered.
Pivot Table Fields: Notice the list of fields in the upper section. These field names come from the _______ titles in our data table.
column
Pivot Tables and Frequency Distributions: The ______ function will count the number of instances falling into the intervals specified in the left column.
count
Excel has built in ways to handle _______ fields.
date
Microsoft created a _________ (2 words) that is unlike any we have used so far. There is a place in that _______ for rows, columns, and values.
dialog box
What does the tabular form (one of the report layout options) do?
displays the totals at the bottom of each group
Do pivot tables make frequency distributions easier, harder, or take relatively the same amount of effort?
easier
Pivot Chart: Whenever you remove an unnecessary field label, the chart space __________.
expands
Dragging and dropping ________ into a pivot table facilitates rotational, or pivotal, structural changes
fields
Pivot Charts: You can delete selected columns using the ________, and Excel will delete them on the chart.
filter
Slicers sort data. display pivot charts. import data. filter data.
filter data.
Pivot Table Fields: To create a pivot table, you need to drag field names to the desired locations in the _____________ (2 words) in the bottom section of the dialog box.
four quadrants
The pivot tables work with lists of data (tables of data), and those lists have _____________
headings
The data used in pivot tables need not be financial data. As long as the record __________ or __________ something, users can use pivot tables to summarize and display information.
measures, counts
Pivot Tables: To show only summary information, click on the _______ button.
minus
Excel automatically groups dates into ________, _________, and _________.
months, quarters, years
You could use the SUMIF function _________ times
multiple
Pivot tables display data in many different ways: Do they change the underlying data!!!
no
It is easy to drag headings into the rows quadrant or the column quadrant. The data can ________ from a row to a column or vice versa
pivot
Pivot Charts: If you choose the ______ sign on the bottom right, Excel will display a column for every type
plus
Pivot tables (pick all of the correct answers) require well-formed data to work properly. cannot use data that is placed at the top or on the side of a spreadsheet. do not like white space around the cells containing the data. do like white space around the cells containing the data.
require well-formed data to work properly. do like white space around the cells containing the data.
When you click an item inside the pivot table, PivotTable Tools appear in the ________.
ribbon
Pivot Chart: Excel adds a number of field names to the chart that you can remove. _______-click a field name that you wish to remove You can choose ________ Field or one of the _______ Field Buttons options.
right, remove, hide
There is a place in the pivot table dialogue box for ________s, _________s, and _________s.
rows, columns, values
When you select and format one number in a pivot table, will all of the numbers in the data field have the same formatting? Or stay the same?
same formatting
Slicers are filters used to show ________ fields.
selected
If you are using transactional data and you have the option to include transaction dates, you should or should NOT include the dates?
should
To Produce Pivot Tables with Multiple Fields in a Quadrant: You need to drag the fields in a ________ sequence and place them in a ________ order.
specific
When Excel builds tables, what is the default format?
the compact format
If you would switch the Quarter and the Sales Person fields in the rows quadrant the quarter would be on the top row for each section and the sales people would be listed below the quarters. the sales people would be listed at the top row for each section and the quarters would be listed below them.
the quarter would be on the top row for each section and the sales people would be listed below the quarters.
In the Report Layout group (pick all of the correct answers) the compact form option places the totals at the bottom of the selected field. the outline form is "busier" than the tabular form. the outline form puts the totals at the bottom of each group. the tabular form puts the totals at the bottom of each group.
the tabular form puts the totals at the bottom of each group.
In using pivot tables with transaction data, it is best to use limited data sets because Excel cannot handle large data sets. the order of the fields dragged into the rows (or columns) quadrant is not important. the only useful calculation is the sum (or total) calculation. time is often an important element.
time is often an important element.
Transaction data always has a ________ dimension, a _______ value and refers to one or more objects (i.e., the reference data)
time, numerical
The compact format: It places the totals in the ___________ row for each type.
top
What kind of data is typically used with pivot tables?
transactional data
(T/F) Pivot tables require lists: You must organize data into rows with unique headings. If the data you have is in columns, you need to transform those columns into rows. There should be no empty rows and no empty columns.
true
(T/F) The pivot table is more dynamic than an ordinary Excel table.
true
(T/F) You can use pivot tables to do queries, do multiple types of calculations, to organize data, to summarize data, and to create charts.
true
(T/F): Pivot tables handle transaction data very well
true
(T/F): The underlying data used by the pivot table does not change, but Excel changes the ways Excel displays the data.
true
(T/F): There is a right way to do this and a wrong way to format numbers in pivot tables
true
Pivot Charts: (T/F): You can also select different kinds of charts: pie, line, bar, and area charts. Excel will notify you if certain chart types are inappropriate when using pivot tables.
true
The pivot table can pivot (rows and columns), summarize data, and answer queries
true
You can calculate a variety of results by the Show Values as option. using the Summarize Values by option. dragging the Cost field into the values quadrant. Dragging the Cost field into the filters quadrant.
using the Summarize Values by option. dragging the Cost field into the values quadrant.
When formatting numbers it is important to select all of the numbers before formatting them. we should click one number and then apply the Number formatting option.
we should click one number and then apply the Number formatting option.
In doing a frequency distribution using a pivot table you use the group option for the distribution count column. you use the group option for the grading scale column.
you use the group option for the distribution count column.
