PivotTableData

Sales Rep Region Month Sale Magazine
Bucknell, Peter West 1-Jan $ 500 Business Times
Smith, Michael South 1-Feb $ 900 IT Weekly
Anderson, Rachel South 1-Feb $ 700 Value Driven Journal
Fuller, Brad North 1-Feb $ 375 People, Places, and Machines
Cushman, David North 1-Feb $ 450 Dollars and Sense
Nell, Janet East 1-Jan $ 550 Value Driven Journal
Peaslee, Garrett East 1-Feb $ 800 People, Places, and Machines
Drew, Nancy West 1-Jan $ 700 Dollars and Sense
Nell, Janet East 1-Feb $ 600 Business Times
Smith, Michael South 1-Feb $ 1,250 IT Weekly
Anderson, Rachel South 1-Feb $ 1,400 Business Times
Cushman, David North 1-Jan $ 1,000 IT Weekly
Bucknell, Peter West 1-Feb $ 600 Dollars and Sense
Peaslee, Garrett East 1-Jan $ 1,000 Value Driven Journal
Fuller, Brad North 1-Feb $ 1,200 People, Places, and Machines
Drew, Nancy West 1-Feb $ 580 Dollars and Sense
Smith, Michael South 1-Jan $ 400 Value Driven Journal
Nell, Janet East 1-Jan $ 720 People, Places, and Machines
Fuller, Brad North 1-Jan $ 925 Dollars and Sense
Bucknell, Peter West 1-Jan $ 1,100 Business Times
Cushman, David North 1-Jan $ 1,200 Business Times
Peaslee, Garrett East 1-Jan $ 250 Value Driven Journal
Drew, Nancy West 1-Jan $ 700 People, Places, and Machines
Anderson, Rachel South 1-Jan $ 750 Dollars and Sense
Cushman, David North 1-Jan $ 1,100 People, Places, and Machines
Bucknell, Peter West 1-Feb $ 1,200 Dollars and Sense
Fuller, Brad North 1-Feb $ 250 Value Driven Journal
Nell, Janet East 1-Feb $ 700 People, Places, and Machines
Bucknell, Peter West 1-Feb $ 750 Dollars and Sense
Smith, Michael South 1-Jan $ 1,000 Business Times
Fuller, Brad North 1-Feb $ 1,200 Business Times
Drew, Nancy West 1-Jan $ 580 Value Driven Journal
Nell, Janet East 1-Feb $ 400 People, Places, and Machines
Cushman, David North 1-Feb $ 720 Dollars and Sense
Peaslee, Garrett West 1-Feb $ 925 People, Places, and Machines
Smith, Michael South 1-Jan $ 700 Dollars and Sense
Peaslee, Garrett East 1-Feb $ 600 Business Times
Peaslee, Garrett East 1-Jan $ 1,250 IT Weekly
Nell, Janet East 1-Feb $ 1,400 Business Times
Drew, Nancy West 1-Feb $ 1,000 IT Weekly
Peaslee, Garrett East 1-Jan $ 600 Dollars and Sense
Anderson, Rachel South 1-Jan $ 500 Value Driven Journal
Anderson, Rachel South 1-Jan $ 900 Business Times
Bucknell, Peter West 1-Jan $ 700 IT Weekly
Anderson, Rachel South 1-Jan $ 375 Value Driven Journal
Smith, Michael South 1-Jan $ 450 People, Places, and Machines
Fuller, Brad North 1-Jan $ 550 Dollars and Sense
Cushman, David North 1-Jan $ 800 Value Driven Journal
Bucknell, Peter North 1-Mar $ 250 Business Times
Smith, Michael East 1-Apr $ 700 IT Weekly
Anderson, Rachel East 1-Jan $ 750 Value Driven Journal
Fuller, Brad West 1-Mar $ 1,100 People, Places, and Machines
Cushman, David East 1-Apr $ 1,200 Dollars and Sense
Nell, Janet South 1-Mar $ 250 Value Driven Journal
Peaslee, Garrett South 1-Apr $ 700 People, Places, and Machines
Drew, Nancy North 1-Jan $ 750 Dollars and Sense
Nell, Janet West 1-Mar $ 1,000 Business Times
Smith, Michael East 1-Apr $ 1,200 IT Weekly
Anderson, Rachel North 1-Mar $ 580 Business Times
Cushman, David West 1-Apr $ 400 IT Weekly
Bucknell, Peter South 1-Jan $ 720 Dollars and Sense
Peaslee, Garrett East 1-Mar $ 925 Value Driven Journal
Fuller, Brad North 1-Apr $ 700 People, Places, and Machines
Drew, Nancy West 1-Mar $ 600 Dollars and Sense
Smith, Michael North 1-Apr $ 1,250 Value Driven Journal
Nell, Janet East 1-Jan $ 1,400 People, Places, and Machines
Fuller, Brad West 1-Mar $ 1,000 Dollars and Sense
Bucknell, Peter South 1-Mar $ 250 Business Times
Cushman, David North 1-Apr $ 700 Business Times
Peaslee, Garrett West 1-Jan $ 750 Value Driven Journal
Drew, Nancy North 1-Mar $ 1,100 People, Places, and Machines
Anderson, Rachel East 1-Apr $ 1,200 Dollars and Sense
Cushman, David West 1-Mar $ 250 People, Places, and Machines
Bucknell, Peter South 1-Apr $ 700 Dollars and Sense
Fuller, Brad North 1-Jan $ 750 Value Driven Journal
Nell, Janet West 1-Mar $ 1,000 People, Places, and Machines
Bucknell, Peter East 1-Apr $ 1,200 Dollars and Sense
Smith, Michael North 1-Mar $ 580 Business Times
Fuller, Brad North 1-Apr $ 400 Business Times
Drew, Nancy East 1-Jan $ 720 Value Driven Journal
Nell, Janet East 1-Mar $ 925 People, Places, and Machines
Cushman, David West 1-Apr $ 700 Dollars and Sense
Peaslee, Garrett East 1-Mar $ 600 People, Places, and Machines
Smith, Michael South 1-Apr $ 1,250 Dollars and Sense
Peaslee, Garrett South 1-Jan $ 1,400 Business Times
Peaslee, Garrett North 1-Mar $ 1,000 IT Weekly
Nell, Janet West 1-Mar $ 720 Business Times
Drew, Nancy East 1-Apr $ 925 IT Weekly
Peaslee, Garrett North 1-Jan $ 700 Dollars and Sense
Anderson, Rachel West 1-Mar $ 600 Value Driven Journal
Anderson, Rachel South 1-Apr $ 1,250 Business Times
Bucknell, Peter East 1-Mar $ 1,400 IT Weekly
Anderson, Rachel North 1-Apr $ 720 Value Driven Journal
Smith, Michael West 1-Jan $ 925 People, Places, and Machines
Fuller, Brad North 1-Mar $ 700 Dollars and Sense
Cushman, David East 1-Apr $ 600 Value Driven Journal

Confirming Pages

Introduction If you routinely track large amounts of information, such as customer mailing lists, phone lists, product inventories, sales transactions, and so on, you can use the extensive list manage- ment capabilities of Excel to make your job easier.

In this plug-in you will learn how to create a list in a workbook, sort the list based on one or more fields, locate important records by using filters, organize and analyze entries by using subtotals, and create summary information by using pivot tables and pivot charts. The lists that you create will be compatible with Microsoft Access, and if you are not already familiar with Access, the techniques that you learn here will give you a head start on learn-ing several database commands and terms. Plug-In T6, “Basic Skills and Tools Using Access,” will provide detail on many of the Access database commands and terms.

This plug-in covers the following five topics:

1. Building lists. 2. Creating conditional formatting. 3. Using Autofilter to find records. 4. Analyzing a list with the Subtotals command. 5. Using the PivotTable feature.

1. Describe how to create and sort a list in Excel.

2. Explain why you would use conditional formatting in Excel.

3. Describe the use of the Excel AutoFilter feature.

4. Explain how to use the Excel Subtotal command.

5. Describe the use of the Excel PivotTable feature.

LEARNING OUTCOMES

Problem Solving Using Excel 2019 T3 P L U G - I N

Plug-In T3 * T3-1

baL6732X_pluginT03_001-016.indd T3-1 12/21/16 07:44 AM

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-2 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-2 12/21/16 07:44 AM

Building Lists A list is a collection of rows and columns of consistently formatted data adhering to some- what stricter rules than an ordinary worksheet. To build a list that works with all of Excel’s list management commands, you need to follow a few guidelines.

When you create a list, keep the following in mind:

■ Maintain a fixed number of columns (or categories) of information; you can alter the number of rows as you add, delete, or rearrange records to keep your list up to date.

■ Use each column to hold the same type of information. ■ Don’t leave blank rows or columns in the list area; you can leave blank cells, if necessary. ■ Make your list the only information in the worksheet so that Excel can more easily

recognize the data as a list. ■ Maintain your data’s integrity by entering identical information consistently. For

example, don’t enter an expense category as Ad in one row, Adv in another, and Advertising in a third if all belong to the same classification.

To create a list in Excel:

1. Open a new workbook or a new sheet in an existing workbook. 2. Create a column heading for each field in the list, format the headings in bold type, and

adjust their alignment. 3. Format the cells below the column headings for the data that you plan to use. This can

include number formats (such as currency or date), alignment, or any other formats. 4. Add new records (your data) below the column headings, taking care to be consistent in

your use of words and titles so that you can organize related records into groups later. Enter as many rows as you need, making sure that there are no empty rows in your list, not even between the column headings and the first record. See Figure T3.1 for a sample list.

FIGURE T3.1

An Excel List

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-3

baL6732X_pluginT03_001-016.indd T3-3 12/21/16 07:44 AM

SORTING ROWS AND COLUMNS Once your records are organized into a list, you can use several commands on the Data menu to rearrange and analyze the data. The Sort command allows you to arrange the records in a different order based on the values in one or more columns. You can sort records in ascending or descending order or in a custom order, such as by days of the week, months of the year, or job title.

To sort a list based on one column:

1. Select the SortData worksheet from the T3 Tech PlugIn Data File.xlsx workbook located in the T3 data files folder.

2. Click any cell in the Sales Rep column; you want to use this column as the basis for sorting the list.

3. Click the Data tab. 4. Click the Ascending button to specify the order to sort by (A to Z, lowest to highest,

earliest date to latest). Your screen will look similar to Figure T3.2.

FIGURE T3.2

A Sorted List

SORTING MORE THAN ONE COLUMN If you have records in your list that have identical entries in the column you are sorting, you can specify additional sorting criteria to further organize your list.

To sort a list based on two or three columns:

1. Click any cell in the Sales Rep column. 2. Click the Data tab, and then click the Sort button. The Sort dialog box opens. 3. Click the Column list arrow, and then select the Sales Rep in the Sort by drop-down list.

Click the Order list arrow and specify A to Z order for that column. 4. Click the Add Level button, then click Magazine in the Then by drop-down list. Specify

A to Z order for the second sort. 5. Click the Add Level button, then click Sale for the sort. Specify Smallest to Largest order

for the third sort. The Sort dialog box should look like Figure T3.3 when you are done. 6. Click OK to run the sort.

Figure T3.4 shows how the sort looks based on the options you selected above.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-4 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-4 12/21/16 07:44 AM

FIGURE T3.3

Sort Dialog Box with Multiple Records

FIGURE T3.4

Data Sort Using More Than One Column

CREATING YOUR OWN CUSTOM SORT ORDER Excel allows you to create custom sort orders so that you can rearrange lists that do not follow predictable alphanumerical or chronological patterns. For example, you can create a custom sort order for the regions of the country (West, North, East, South). When you define a cus- tom sort order, it appears in the Options dialog box and is available to all the workbooks on your computer.

To create a custom sort order:

1. Click the File tab, scroll down to the Options button. A box will open, choose Advanced. Scroll down the dialog box that opens. Under the General category, click the box Edit Custom Lists.

2. Click the line NEW LIST under the Custom Lists section, and the text pointer appears in the List entries: list box. This is where you will type the items in your custom list.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-5

baL6732X_pluginT03_001-016.indd T3-5 12/21/16 07:44 AM

3. Type North, East, South, West, and then click Add. You can either separate each value with a comma or type each one on a separate line. The new custom order appears in the Custom Lists list box, as shown in Figure T3.5

4. Click OK to close the Custom Lists dialog box, and again for the Excel Options box.

FIGURE T3.5

Creating a Custom Sort

To use a custom sort order:

1. Click any cell in your list. 2. Click the Home tab. Then under the Editing group, click Sort & Filter, and then click

Custom Sort. 3. Under Column, in the Sort by drop-down box, select the Region field. 4. Under Order, select Custom List. 5. In the Custom Lists dialog box, select North, East, South, West, as shown in

Figure T3.6. 6. Click OK to run the sort. Your list appears sorted with the custom criteria you specified.

FIGURE T3.6

Sort Options Dialog Box

Creating Conditional Formatting Excel gives you the ability to add conditional formatting—formatting that automatically adjusts depending on the contents of cells—to your worksheet. This means you can highlight important trends in your data, such as the rise in a stock price, a missed milestone, or a sudden

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-6 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-6 12/21/16 07:44 AM

spurt in your college expenses, based on conditions you set in advance using the Conditional Formatting dialog box. With this feature, an out-of-the-ordinary number jumps out at anyone who routinely uses the worksheet.

For example, if a stock in a Gain/Loss column rises by more than 20 percent, you want to display numbers in bold type on a light blue background. In addition, if a stock in the Gain/ Loss column falls by more than 20 percent, you want to display the number in bold type on a solid red background. This is when you want to use conditional formatting.

To create such a conditional format:

1. If the workbook T3 Tech PlugIn Data File.xlsx is closed, open it.

2. Select the worksheet Conditional Formatting. 3. Select the column Sale. (Note: Each cell can maintain its own, unique conditional

formatting, so that you can set up several different conditions.) 4. Click the Home tab. 5. In the Styles section, click the Conditional Formatting button, and then point to

Highlight Cell Rules and click Between. . . . 6. In the first text box, type the number 1000. 7. In the second text box, type the number 1200. 8. In the third text box, use the drop-down arrow to select Green Fill with Dark Green

Text. Figure T3.7 displays the settings for this example. 9. Click OK. If any numbers fall into the ranges you specified, the formatting you specified

will be applied. 10. Now you need to add another rule to supply different criteria. Click the Conditional

Formatting button, and then point to Highlight Cell Rules and click Greater Than. . . . 11. Type 1250 in the first box and select Red Fill with Dark Red Text using the drop-

down arrow from the second box. 12. Click OK. 13. If any numbers fall into the ranges you identified, the formatting you specified will be

applied. Figure T3.8 shows the conditional formatting you entered for this example.

FIGURE T3.7

Conditional Formatting Dialog Box

Using AutoFilter to Find Records When you want to hide all the records (rows) in your list except those that meet certain crite- ria, you can use the AutoFilter command on the Filter submenu of the Data menu. The Auto- Filter command places a drop-down list at the top of each column in your list (in the heading row). To display a particular group of records, select the criteria that you want in one or more of the drop-down lists. For example, to display the sales history for all employees that had $1,000 orders in January, you could select January in the Month column drop-down list and $1,000 in the Sale drop-down list.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-7

baL6732X_pluginT03_001-016.indd T3-7 12/21/16 07:44 AM

To use the AutoFilter command to find records:

1. If the workbook T3 Tech PlugIn Data File.xlsx is closed, open it. 2. Select the worksheet AutoFilter. 3. Click any cell in the list. 4. Click the Data tab, and then click the Filter button in the Sort & Filter section. Each

column head now displays a list arrow. 5. Click the list arrow next to the Region heading. A list box that contains filter options

appears, as shown in Figure T3.9.

FIGURE T3.8

Conditional Formatting

FIGURE T3.9

AutoFilter Options

Under Home Ribbon, Click Sort & Filer, Filter

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-8 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-8 12/21/16 07:44 AM

If a column in your list contains one or more blank cells, you will also see (Blanks) and (NonBlanks) options at the bottom of the list. The (Blanks) option displays only the records containing an empty cell (blank field) in the filter column, so that you can locate any missing items quickly. The (NonBlanks) option displays the opposite—all records that have an entry—in the filter column.

6. Select only East to use for this filter (you will have to uncheck the other entries). Excel hides the entries that don’t match the criterion you specify and highlights the active filter arrow. Figure T3.10 shows the results of using East as the criterion in the Region column.

7. You can use more than one filter arrow to further narrow your list, which is useful if your list is many records long. To continue working with AutoFilter but also redisplay all your records, click the list arrow next to Region and check Show All. Excel displays all your records again.

8. To remove the AutoFilter drop-down lists, unselect the AutoFilter command on the Filter submenu.

FIGURE T3.10

Specific Autofilter

CREATING A CUSTOM AUTOFILTER When you want to display a numeric range of data or customize a column filter in other ways, choose Custom Filter . . . from the Number Filters option to display the Custom AutoFilter dialog box. The dialog box contains two relational list boxes and two value list boxes that you can use to build a custom range for the filter. For example, you could display all sales greater than $1,000 or all sales between $500 and $800.

To create a custom AutoFilter: 1. Click any cell in the list. 2. Click the Data tab and then click the Filter button.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-9

baL6732X_pluginT03_001-016.indd T3-9 12/21/16 07:44 AM

3. Click the list arrow next to the heading Sale and select Number Filters, and then click on Custom Filter. . . . The Custom AutoFilter dialog box opens.

4. Click the first list box and select is greater than or equal to and then click the value list box and select $500.

5. Click the And radio button to indicate that the records must meet both criteria, and then specify is less than or equal to in the second list box and select $800 in the second value list box. Figure T3.11 shows the Custom AutoFilter dialog box with two range criteria specified.

6. Click OK to apply the custom AutoFilter. The records selected by the filter are displayed in your worksheet.

Analyzing a List with the Subtotals Command The Subtotals command in the Outline section of the Data menu helps you organize and analyze a list by displaying records in groups and inserting summary information, such as subtotals, averages, maximum values, or minimum values.

The Subtotals command can also display a grand total at the top or bottom of your list, letting you quickly add up columns of numbers. As a bonus, Subtotals displays your list in Outline view so that you can expand or shrink each section in the list simply by clicking.

To add subtotals to a list:

1. If the workbook T3 Tech PlugIn Data File.xlsx is closed, open it. 2. Select the worksheet Subtotals. 3. Arrange the list so that the records for each group are located together.

To do this, sort the list by Sales Rep. 4. Click the Data tab, and then click the Subtotal button in the Outline

section. Excel opens the Subtotal dialog box and selects the list. 5. In the At each change in: list box, choose Sales Rep. Each time this

value changes, Excel inserts a row and computes a subtotal for the numeric fields in this group of records.

6. In the Use function: list box, choose Sum. 7. In the Add subtotal to: list box, choose Sale, which is the column to

use in the subtotal calculation Figure T3.12 shows the settings for this example.

8. Click OK to add the subtotals to the list. You will see a screen similar to the one in Figure T3.13, complete with subtotals, outlining, and a grand total.

When you use the Subtotals command in Excel to create outlines, you can examine different parts of a list by clicking buttons in the left margin. Click the numbers at the top of the left margin to choose how many levels of data you want to see. Click the plus or minus button to expand or collapse specific subgroups of data.

You can choose the Subtotals command as often as necessary to modify your groupings or calculations. When you are finished using the Subtotals command, click Remove All in the Subtotal dialog box.

FIGURE T3.11

Custom AutoFilter

FIGURE T3.12

Subtotal Settings

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-10 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-10 12/21/16 07:44 AM

Using the PivotTable Feature A powerful built-in data analysis feature in Excel is PivotTable. The PivotTable feature ana- lyzes, summarizes, and manipulates data in large lists, databases, worksheets, or other col- lections. Pivot tables are so named because fields can be moved within the table to create different types of summary lists, providing a “pivot.” The PivotTable feature offers flexible and intuitive analysis of data.

Although the data that appear in pivot tables look like any other worksheet data, the data in the data area of the PivotTable feature cannot be directly entered or changed. The PivotTable feature is linked to the source data; the output in the cells of the table are read-only data. The formatting (number, alignment, font, etc.) can be changed, and a variety of computational options are available, such as SUM, AVERAGE, MIN, and MAX.

PIVOTTABLE TERMINOLOGY Some notable PivotTable terms are:

■ Row field—Row fields have a row orientation in a PivotTable report and are displayed as row labels. These appear in the ROW area of a PivotTable report layout.

■ Column field—Column fields have a column orientation in a PivotTable report and are displayed as column labels. These appear in the COLUMN area of a PivotTable report layout.

■ Data field—Data fields from a list or table contain summary data in a PivotTable report, such as numeric data (e.g., statistics, sales amounts). These are summarized in the DATA area of a PivotTable report layout.

■ Page field—Page fields filter out the data for other items and display one page at a time in a PivotTable report.

FIGURE T3.13

Subtotals, Outline, and Grand Total

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-11

baL6732X_pluginT03_001-016.indd T3-11 12/21/16 07:44 AM

BUILDING A PIVOT TABLE The PivotTable Wizard steps through the process of creating a pivot table, allowing a visual breakdown of the data in the Excel list or database. When the wizard steps are complete, a diagram, such as Figure T3.14, with the labels PAGE, COLUMN, ROW, and DATA appears. The next step is to drag the field buttons onto the PivotTable grid. This step tells Excel about the data needed to be analyzed with the table.

To use the PivotTable feature:

1. If the workbook T3 Tech PlugIn Data File.xlsx is closed, open it. 2. Select the worksheet PivotTableData. Click any cell in the list. Now the active cell is

within the list, and Excel knows to use the data in the Excel list to create a pivot table. 3. Click the Insert tab, and then click the PivotTable button in the Tables group, and click

on PivotTable. 4. The Create PivotTable dialog box opens. In the Select a table or range box, make sure

you see $A$1:$E$97. 5. Click OK. Your spreadsheet will now look like Figure T3.14. 6. Using the PivotTable Field List, drag the Month button to the Report Filter area. This

field operates like the row and column fields but provides a third dimension to the data. It allows another variable to be added to the pivot table without necessarily viewing all its values at the same time.

7. Drag the Region button to the COLUMN area. The column field is another variable used for comparison.

FIGURE T3.14

PivotTable Grid, PivotTable Toolbar, and PivotTable Field List

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-12 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-12 12/21/16 07:44 AM

8. Drag the Magazine button to the ROW area. A row field in a pivot table is a variable that takes on different values.

9. Drag the Sale button to the DATA area. The data field is the variable that the PivotTable report summarizes. Your pivot table should now look like Figure T3.15.

FIGURE T3.15

PivotTable Output with Data, PivotTable Toolbar, and PivotTable Field List

MODIFYING A PIVOT TABLE VIEW A pivot table can be modified at any time. For example, examining the sales for a particular region would mean that the Region field would need to be changed. Use the drop-down list to the right of the field name. Select a region and click OK. The grand total dollar amounts by region are at the bottom of each item; they have been recalculated according to the selected region(s).

This report can be used in various ways to analyze the data. For instance, click the Clear button in the PivotTable report, as seen in Figure T3.16. Then arrange the fields like this:

1. Magazine in the Report Filter area. 2. Month in the COLUMN area. 3. Sales Rep in the ROW area. 4. Sale in the DATA area.

The completed PivotTable dialog box should look like the one in Figure T3.17. The pivot table now illustrates the sales by month for each salesperson, along with the total amount of sales for each sales representative.

BUILDING A PIVOT CHART A pivot chart is a column chart (by default) that is based on the data in a pivot table. The chart type can be changed, if desired. To build a pivot chart:

1. Click the Insert tab, and then choose the PivotChart button in the Charts group. 2. Select the Stacked Column chart and click OK.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-13

baL6732X_pluginT03_001-016.indd T3-13 12/21/16 07:44 AM

FIGURE T3.16

Clearing the PivotTable Fields

FIGURE T3.17

Rearranged PivotTable Data

3. The pivot chart should look like Figure T3.18. 4. The chart appears in your PivotTable worksheet. If you like, click the Move Chart button

on the Analyze tab and select New sheet from the Move Chart dialog box.

Note: Whatever changes are selected in the PivotChart feature are also made to the pivot table, as the two features are linked dynamically.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-14 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-14 12/21/16 07:44 AM

FIGURE T3.18

PivotChart Output

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

Plug-In T3 * T3-15

baL6732X_pluginT03_001-016.indd T3-15 12/21/16 07:44 AM

P L U G - I N S U M M A R Y

If you routinely track large amounts of information, you can use several Excel tools for problem solving. A list is a table of data stored in a worksheet, organized into columns of fields and rows of records. Excel gives you the ability to add conditional formatting—formatting that automati-

cally adjusts depending on the contents of cells—to your worksheet. The AutoFilter command places a drop-down list at the top of each column in your list (in the heading row). The Subtotals command on the Data menu helps you organize and analyze a list by displaying records in groups and insert- ing summary information, such as subtotals, averages, maximum values, or minimum values. The PivotTable feature enables you to analyze, summarize, and manipulate data in large lists, databases, worksheets, or other collections.

M A K I N G B U S I N E S S D E C I S I O N S

1. Production Errors Established in 2000, t-shirts.com has rapidly become the place to find, order, and save on T-shirts. One huge selling factor is that the company manufactures its own T-shirts. However, the quality manager for the production plant, Kasey Harnish, has noticed an unacceptable number of defec- tive T-shirts being produced. You have been hired to assist Kasey in understanding where the problems are concentrated. He suggests using Excel’s PivotTable feature to perform an analysis and has provided you with a data file, T3 TshirtProduction Data File.xlsx. The following is a brief definition of the information within the data file:

a. Batch: A unique number that identifies each batch or group of products produced.

b. Product: A unique number that identifies each product.

c. Machine: A unique number that identifies each machine on which products are produced.

d. Employee: A unique number that identifies each employee producing products.

e. Batch Size: The number of products produced in a given batch.

f. Num Defect: The number of defective products produced in a given batch.

2. Coffee Trends College chums Hannah Baltzan and Tyler Phillips are working on opening a third espresso drive- through stand in Highlands Ranch, Colorado, called Brewed Awakening. Their original drive- through stand, Jitters, and their second espresso stand, Bean Scene, have done well in their current locations in Englewood, Colorado, five miles away. Since Hannah and Tyler want to start with low overhead, they need assistance analyzing the data from the past year on the different types of coffee and amounts that they sold from both stands. Hannah and Tyler would like a rec- ommendation of the four top sellers to start offering when Brewed Awakening opens. They have provided you with the data file T3 Jitters Coffee Data File.xlsx for you to perform the analysis that will support your recommendation.

3. Filtering SecureIT Data SecureIT, Inc., is a small computer security contractor that provides computer security analy- sis, design, and software implementation for commercial clients. Almost all of SecureIT’s work requires access to classified material or confidential company documents.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Confirming Pages

T3-16 * Plug-In T3

baL6732X_pluginT03_001-016.indd T3-16 12/21/16 07:44 AM

Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security work.

Although most of the personnel information for SecureIT resides in database systems, a basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management capabili- ties to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3 Employee Data File.xlsx, to assist SecureIT with producing several work-sheet summaries. Here is what is needed:

a. One worksheet that is sorted by last name and hire date.

b. One worksheet that uses a custom sort by department in this order: Marketing, Human Resources, Management, and Engineering.

c. One worksheet that uses a filter to display only those employees in the Engineering depart- ment with a clearance of Top Secret (TS).

d. One worksheet that uses a custom filter to display only those employees born between 1960 and 1969 (inclusive).

e. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.

4. Filtering RedRocks Consulting Contributions

RedRocks Consulting is a large computer consulting firm in Denver, Colorado. Don McCubbrey, the CEO and founder of the firm, is well-known for his philanthropic efforts. He believes that many of his employees also contribute to nonprofit organizations and wants to reward them for their efforts while encouraging others to contribute to charities. He started a program in which RedRocks Consulting matches 50 percent of each donation an employee makes to the charity of his or her choice. The only guidelines are that the charity must be a nonprofit organization and the firm’s donation per employee may not exceed $500 a year.

Don has started an Excel file, T3 Red Rocks Data File.xlsx, to record the firm’s donations. Included in this file are the dates the request for a donation was submitted, the employee’s name and ID number, the name of the charity, the dollar amount contributed by the firm, and the date the contri-bution was sent. Don wants you to help him create several worksheets with the following criteria: 1. One worksheet that sorts the list alphabetically by organization and then by employee’s

last name.

2. One worksheet that totals the contribution made per employee for the month of December.

3. One worksheet that sorts the list by donation value by lowest amount to highest amount.

Copyright 2022 © McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.

Excellent/Good Fair/Poor Demonstrates knowledge of the

PivotTable wizard by accurately

placing the field buttons onto the

PivotTable grid. The PivotTable

represents the information presented

in Figure T3.17 with the magazine in

the page area, the month in the

column area, the sales rep field in the

row area, and the sale field in the data

area.

Demonstrates limited

knowledge of the PivotTable

wizard through inaccurately

placing the field buttons onto

the PivotTable grid. The

PivotTable has the information

presented in Figure T3.17,

however the information is not

in the same order.

10 10

The monthly totals and grand totals

exactly match the totals in Figure

T3.17.

The monthly totals and grand

totals are different from the

totals in Figure T3.17. 5 5

The Sales Rep field list is in

alphabetical order (A to Z).

The Sales Rep field list is not

in alphabetical order (A to Z). 5 5

The monthly sales total columns are

listed in order starting with January.

The monthly sales total

columns are not listed in order

and do not start in January. 5 5

25 25

Possible Points Actual Points

The PivotTable chart is in column

format as illustrated in Figure T3.18.

The PivotTable chart is not in

column format. 8 8

The sales rep columns are listed in

alphabetical order (A to Z).

The sales rep columns are not

listed in alphabetical order (A

to Z). 7 7

The chart legend is located on the

right side of the chart.

The chart legend is not located

on the right side of the chart. 5 5

20 20

Possible Points Actual Points

The pivot table and chart have been

properly constructed from the provided

data.

The pivot table and chart have

not been properly constructed

from the provided data. 10 10

The pivot table and chart clearly

identify the top four selling coffee

products.

The pivot table does not

clearly identify the top four

selling coffee products. 10 10

PivotTable

BUSN 420 Minor Project Grading Rubric (100 points)

Comments:

Using Pivot Tables to Solve a Problem

PivotTable Chart

Comments:

Possible Points Actual Points

The student has made a clear

recommendation that identifies the top

four selling coffee products.

The student has not made a

clear recommendation that

identifies the top four selling

coffee products.

5 5

25 25

Possible Points Actual Points

The student has provided robust

examples of the use of Pivot Tables.

The student has provided

minor examples of the use of

Pivot Tables. 10 10

The student has supported his or her

text with at least one peer-reviewed

scholarly journal references.

The student has not supported

his or her text with at least one

peer-reviewed scholarly

journal references.

5 5

The student has kept close to the 300-

500 word count requirement.

The student has strayed

significantly from the 300-500

word count requirement. 5 5

Spelling, punctuation, grammar, and

APA formatting are stellar.

Spelling, punctuation,

grammar, and APA formatting

are poor. 10 10

30 30

100 100Final Score:

Overall Comments:

Comments:

PivotTable Applications

Comments:

Late deductions (e.g., -10):

Sheet1

Key
Jitters = North
Bean Scene = South
Product Month Store Revenue
Regular January North 4320
Regular February North 4500
Regular March North 3528
Regular April North 3960
Regular May North 3996
Regular June North 3960
Regular July North 3960
Regular August North 4230
Regular September North 4230
Regular October North 4275
Regular November North 4365
Regular December North 4455
Regular January South 3960
Regular February South 4230
Regular March South 4230
Regular April South 4275
Regular May South 4365
Regular June South 4455
Regular July South 4128
Regular August South 4176
Regular September South 3936
Regular October South 4090
Regular November South 3936
Regular December South 4350
Decaf January North 4350
Decaf February North 4350
Decaf March North 4350
Decaf April North 3610
Decaf May North 3640
Decaf June North 3610
Decaf July North 2210
Decaf August North 2430
Decaf September North 2430
Decaf October North 2460
Decaf November North 2540
Decaf December North 2310
Decaf January South 2620
Decaf February South 2450
Decaf March South 2430
Decaf April South 2480
Decaf May South 2540
Decaf June South 2610
Decaf July South 2340
Decaf August South 2380
Decaf September South 2540
Decaf October South 2310
Decaf November South 2400
Decaf December South 2320
French Roast January North 2290
French Roast February North 5290
French Roast March North 4320
French Roast April North 4500
French Roast May North 3528
French Roast June North 3960
French Roast July North 3996
French Roast August North 3960
French Roast September North 3960
French Roast October North 4230
French Roast November North 4230
French Roast December North 4275
French Roast January South 4365
French Roast February South 4455
French Roast March South 3960
French Roast April South 4230
French Roast May South 4230
French Roast June South 4275
French Roast July South 4365
French Roast August South 4455
French Roast September South 4128
French Roast October South 4176
French Roast November South 3936
French Roast December South 4090
Vanilla January North 3936
Vanilla February North 4350
Vanilla March North 4350
Vanilla April North 4350
Vanilla May North 4350
Vanilla June North 3610
Vanilla July North 3640
Vanilla August North 3610
Vanilla September North 2210
Vanilla October North 2430
Vanilla November North 2430
Vanilla December North 2460
Vanilla January South 2540
Vanilla February South 2310
Vanilla March South 2620
Vanilla April South 2450
Vanilla May South 2430
Vanilla June South 2480
Vanilla July South 2540
Vanilla August South 2610
Vanilla September South 2340
Vanilla October South 2380
Vanilla November South 2540
Vanilla December South 2310
Irish Cream January North 2400
Irish Cream February North 2320
Irish Cream March North 2290
Irish Cream April North 5290
Irish Cream May North 4350
Irish Cream June North 4350
Irish Cream July North 4350
Irish Cream August North 4350
Irish Cream September North 3610
Irish Cream October North 3640
Irish Cream November North 3610
Irish Cream December North 2210
Irish Cream January South 2430
Irish Cream February South 2430
Irish Cream March South 2460
Irish Cream April South 2540
Irish Cream May South 2310
Irish Cream June South 2620
Irish Cream July South 2450
Irish Cream August South 2430
Irish Cream September South 2480
Irish Cream October South 2540
Irish Cream November South 2610
Irish Cream December South 4350
Hazelnut January North 3610
Hazelnut February North 3640
Hazelnut March North 3610
Hazelnut April North 2210
Hazelnut May North 2430
Hazelnut June North 2430
Hazelnut July North 2460
Hazelnut August North 2540
Hazelnut September North 2310
Hazelnut October North 2620
Hazelnut November North 2450
Hazelnut December North 2430
Hazelnut January South 2480
Hazelnut February South 2540
Hazelnut March South 2610
Hazelnut April South 2340
Hazelnut May South 2380
Hazelnut June South 2540
Hazelnut July South 2310
Hazelnut August South 2400
Hazelnut September South 2320
Hazelnut October South 2290
Hazelnut November South 5290
Hazelnut December South 4320

BUSN 420 Management Information Systems

Minor Project

The following exercises will introduce you to Microsoft Excel Pivot Tables. Having a good

understanding of Pivot Tables will give you an advantage in the business world. When your supervisor

asks you to analyze a large pile of data to solve a problem, you will be able to use a Pivot Table to slice

and dice data to gain information and create usable business intelligence to solve a real-world problem.

Please read the information provided and complete each exercise. Once you have completed all of the

exercises below, submit your Excel and Word documents using the assignment link in Canvas.

Please see the grading rubric at the end of these instructions for more information.

• First, read the attached Tech Plug-In T3: Problem Solving Using Excel document

• Then, review the “Using the PivotTable Feature” section on pages T3-10 through T3-14

• Create Figure T3.17 “Rearranged PivotTable Data” on page T3-13 and Figure T3.18 “PivotChart

Output” on page T3-14

SOLVE A PROBLEM USING A PIVOT TABLE

• Refer to the “Making Business Decision” section of the Tech Plug-In T3: Problem Solving Using

Excel document

• Complete Exercise 2: Coffee Trends

• Develop a PivotTable and PivotTable chart illustrating the top-selling coffee products

• Indicate the top 4 selling coffee products

PIVOT TABLE APPLICATIONS

How have you in the past, or could you in the future, use pivot tables in a business or ministry setting?

Considering the power of Pivot Tables to analyze data, what ethical issues might arise from the

improper usage of this tool to analyze business or ministry data. How should a Christian Worldview

inform these ethical dilemmas?

Please be brief and keep your response to around 300-500 words. Support your response with at

least one peer-reviewed scholarly journal reference.

All 4 sections of the Minor Project should be uploaded in Canvas, including:

1. A Pivot Table illustrating the monthly magazine sales by the sales representative as illustrated in Figure

T3.17 of the assignment instructions.

2. A Pivot Table Chart illustrating the monthly magazine sales by the sales representative as illustrated in

Figure T3.18 of the assignment instructions.

3. A Pivot Table and Chart illustrating the top 4 selling coffee products.

4. A 300 to 500-word essay describing the use of pivot tables and charts in a business or ministry setting.

The following rubric will be used to grade your submissions:

*

BUSN 420 Minor Project Grading Rubric (100 points)

PivotTable Possible

Points

Actual

Points Excellent/Good Fair/Poor

Demonstrates knowledge of the

PivotTable wizard by

accurately placing the field

buttons onto the PivotTable

grid. The PivotTable represents

the information presented in

Figure T3.17 with the magazine

in the page area, the month in

the column area, the sales rep

field in the row area, and the

sale field in the data area.

Demonstrates limited

knowledge of the

PivotTable wizard

through inaccurately

placing the field buttons

onto the PivotTable grid.

The PivotTable has the

information presented in

Figure T3.17; however,

the information is not in

the same order.

10

10

The monthly totals and grand

totals exactly match the totals in

Figure T3.17.

The monthly totals and

grand totals are different

from the totals in Figure

T3.17.

5

5

The Sales Rep field list is in

alphabetical order (A to Z).

The Sales Rep field list is

not in alphabetical order

(A to Z).

5 5

The monthly sales total

columns are listed in order

starting with January.

The monthly sales total

columns are not listed in

order and do not start in

January.

5

5

Comments: 25 25

PivotTable Chart Possible

Points

Actual

Points

The PivotTable chart is in

column format, as illustrated in

Figure T3.18.

The PivotTable chart is

not in column format. 8 8

The sales rep columns are listed

in alphabetical order (A to Z).

The sales rep columns

are not listed in

alphabetical order (A to

Z).

7

7

The chart legend is located on

the right side of the chart.

The chart legend is not

located on the right side

of the chart.

5

5

Comments: 20 20

Using Pivot Tables to Solve a Problem Possible

Points

Actual

Points

The pivot table and chart have

been properly constructed from

the provided data.

The pivot table and chart

have not been properly

constructed from the

provided data.

10

10

The pivot table and chart

clearly identify the top four

selling coffee products.

The pivot table does not

clearly identify the top

four selling coffee

products.

10

10

The student has made a clear

recommendation that identifies

the top four selling coffee

products.

The student has not made

a clear recommendation

that identifies the top

four selling coffee

products.

5

5

Comments: 25 25

PivotTable Applications Possible

Points

Actual

Points

The student has provided robust

examples of the use of Pivot

Tables.

The student has provided

minor examples of the

use of Pivot Tables.

10

10

The student has supported his

or her text with at least one

peer-reviewed scholarly journal

reference.

The student has not

supported his or her text

with at least one peer-

reviewed scholarly

journal reference.

5

5

The student has kept close to

the 300-500 word count

requirement.

The student has strayed

significantly from the

300-500 word count

requirement.

5

5

Spelling, punctuation, grammar,

and APA formatting are stellar.

Spelling, punctuation,

grammar, and APA

formatting are poor.

10

10

Comments: 30 30

Late deductions (e.g., -10):

Final Score: 100 100

Overall Comments:

*

Get help from top-rated tutors in any subject.

Efficiently complete your homework and academic assignments by getting help from the experts at homeworkarchive.com