Question

Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments AL Y Clear Reapply - Connections Properties Edit Links Ę! Hy HH HHH He 4 Flash Fill E Data Validation Remove Duplicates - Consolidate Group 48 Ungroup Subtotal Get External Data Refresh All Stocks Geogra... Al Sort Filter Advanced Text to Columns What If Analysis Analysis Data Tools Analysis M9 . ху fx D E E F G H I J K L L M N 0 P Q R S T U V W - A B с 1 Expense Employee Category 2 $ 93.28 Donaldson Misc 3 $ 113.64 Miller Misc 4 $ 114.90 Hart Misc 5 $ 123.41 Abbott Misc 6 $ 124.64 Overton Misc 7 7 $ 285.00 Miller Airfare 8 $ 285.32 Abbott Meals 9 $ 304.12 Miller Meals 10 $ 324.08 Donaldson Meals 11 $ 327.95 Hart Airfare 12 $ 340.76 Hart Meals 13 $ 374.82 Overton Meals 14 $ 423.99 Donaldson Airfare 15 $ 450.00 Donaldson Registration 16 $ 595.00 Abbott Registration 17 $ 595.00 Miller Registration 18 $ 658.00 Overton Airfare 19 $ 750.20 Abbott Airfare 20 $ 775.00 Overton Registration 21 $ 840.25 Miller Hotel 22 $ 864.84 Hart Hotel 23 $ 995.00 Hart Registration 24 $1,051.14 Abbott Hotel 25 $1,204.78 Donaldson Hotel 26 $ 1,283.50 Overton Hotel 27 28 29 30 31 32 33 34 35 36 Subtotals Expenses +
EX16_XL_CH05_GRADER_CAP_AS - Travel Expenses 1.8

Project Description:

<Project Description>

Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel. Open the downloaded Excel file named exploring_e05_grader_a1_Expenses.xlsx. Save the workbook as exploring_e05_grader_a1_Expenses_LastFirst, replacing LastFirstwith your own name.	0
2	On the Subtotals worksheet, use the Sort dialog box to sort the data by Employee and further sort by Category, both in alphabetical order.	4
3	Use the Subtotals feature to insert subtotal rows by Employee to calculate the total expense by employee.	6
4	Collapse the Donaldson and Hart sections to show only their totals. Leave the other employees’ individual rows displayed.	5
5	Use the Expenses worksheet to create a blank PivotTable on a new worksheet named Summary. Name the PivotTable Categories.	8
6	Use the Category and Expense fields, enabling Excel to determine where the fields go in the PivotTable.	5
7	Modify the Values field to determine the average expense by category. Change the custom name to Average Expense.	4
8	Format the Values field with Accounting number type.	4
9	Type Category in cell A3 and change the Grand Totals layout option to On for Rows Only.	5
10	Apply Pivot Style Dark 2 and display banded rows. Note, depending upon the version of Office being used, the style name may be Light Blue, Pivot Style Dark 2.	5
11	Insert a slicer for the Employee field, change the slicer height to 2 inches and apply the Slicer Style Dark 5. Move the slicer below the PivotTable. Note, depending upon the version of Office being used, the style name may be Light Blue, Slicer Style Dark 5.	6
12	Use the Expenses worksheet to create another blank PivotTable on a sheet named Totals. Add the Employee to the Rows and add the Expense field to the Values area. Sort the PivotTable from largest to smallest expense.	10
13	Change the name for the Expenses column to Totals and format the field with Accounting number format.	6
14	Insert a calculated field to subtract 2659.72 from the Expense field. Format the field with the custom name Above or Below Average and apply Accounting number format to the field.	10
15	Set 12.29 (approximate) as the width for column B and column C, change the row height of row 3 to 30, and apply word wrap to cell C3.	4
16	Create a clustered column PivotChart from the PivotTable. Move the PivotChart to a new sheet named Chart. Hide all field buttons in the PivotChart, if necessary. Note, Mac users, select the range A3:C8 in the PivotTable. On the Insert tab, click Column, and then click Clustered Column. Right-click, and from the shortcut menu, click Move Chart.	10
17	Add a chart title above the chart and type Expenses by Employee. Change the chart style to Style 14. Note, Mac users, continue on to the next Step.	0
18	Apply 11 pt font size to the value axis and display vertical axis as Accounting with zero decimal places.	4
19	Create a footer on all worksheets with your name in the left section, the sheet name code in the center section, and the file name code in the right section.	4
20	Ensure that the worksheets are correctly named and placed in the following order in the workbook: Subtotals, Summary, Chart, Totals, Expenses. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.	0

Total Points

100

Answer 1

2. Select any cell with data, and go to Data and select Sort. The selection will automatically expand to entire data. Check My data has headers.

Now select Employee in sort by and order A to Z. Click on Add Level and Select Category in drop down “Then by”. Order for this would also be A to Z.

Click OK and data would get sorted.

File Home Insert Page Layout Formulas Data Review View Developer Help Search From Text/CSV Le From Web From Table/Range Recent Sources [Existing Connections AL ZA Z Y Queries & Connections B Properties Refresh All Edit Links Clear To Reapply Advanced Stocks Geography Sort Get Data Filter Text to Columns OG Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools B4 for Hart D N Sort ? X + Add Level X Delete Level ( Copy Level Options... My data has headers Column Order Sort On Cell Values Sort by Empoyee A to Z Then by Category Cell Values A to Z 1 2. 3 4 5 6 7 8 9 10 11 12 13 14 15 16 171 18 19 20 A B с C Expense Empoyee Category $93.28 Donalson Misc $113.64 Miller Misc $114.90 Hart Misc $123.41 Abbott Misc $124.64 Overton Misc $285.00 Miller Airfare $285.32 Abbott Meals $304.12 Miller Meals $324.08 Donalson Meals $327.95 Hart Airfare $340.76 Hart Meals $374.82 Overton Meals $423.99 Donalson Airfare $450.00 Donalson Registration $595.00 Abbott Registration $595.00 Miller Registration $658.00 Overton Airfare $750.20 Abbott Airfare $775.00 Overton Registration $840.25 Miller Hotel $864.84 Hart Hotel coas nn art Dictration Subtotals Expenses Employee OK Cancel 21 22 +

B4 foc Abbott 1 w N 4 5 6 7 8 9 10 11 12 А B с Expense Empoyee Category $750.20 Abbott Airfare $1,051.14 Abbott Hotel $285.32 Abbott Meals $123.41 Abbott Misc $595.00 Abbott Registration $423.99 Donalson Airfare $1,204.78 Donalson Hotel $324.08 Donalson Meals $93.28 Donalson Misc $450.00 Donalson Registration $327.95 Hart Airfare $864.84 Hart Hotel $340.76 Hart Meals $114.90 Hart Misc $995.00 Hart Registration $285.00 Miller Airfare $840.25 Miller Hotel $304.12 Miller Meals $113.64 Miller Misc $595.00 Miller Registration $658.00 Overton Airfare 01 202 sn vartan Uotal Subtotals ExpensesEmployee 13 14 15 16 17 18 19 20 21 22

3. Select entire data then go to Data tab. Under Outline group, click Subtotal. The Subtotal dialog box is displayed.

At each change of Employee, we wish to find total of expenses, so make selections as shown in screenshot below.

AutoSave Off Have Book1 - Excel Akanksha_Gupta08 А File Home Insert Page Layout Formulas Data Review View Developer Help O Search Share Comments Le From Text/CSV + H Recent Sources [à Existing Connections WA TIT 41 AC clear From Web Queries & Connections Properties Refresh All Edit Links Queries & Connections Get Data From Table/Range Group Ungroup Subtotal 智区 8 Stocks Geography - Clear Te Reapply Filter Advanced Sort & Filter 2.) Sort Text to Columns GO What-If Forecast Analysis Sheet Forecast Get & Transform Data Data Types Data Tools Outline A1 fi Expense Subtotal Quickly calculate rows of related data by inserting subtotals and totals. D E F H 1 к L M N O 1 2 3 4 5 6 7 8 9 10 11 12 13 14 A B Expense Empoyee Category $750.20 Abbott Airfare $1,051.14 Abbott Hotel $285.32 Abbott Meals $123.41 Abbott Misc $595.00 Abbott Registration $423.99 Donalson Airfare $1,204.78 Donalson Hotel $324.08 Donalson Meals $93.28 Donalson Misc $450.00 Donalson Registration $327.95 Hart Airfare $864.84 Hart Hotel $340.76 Hart Meals $114.90 Hart Misc $995.00 Hart Registration $285.00 Miller Airfare $840.25 Miller Hotel $304.12 Miller Meals $113.64 Miller Misc $595.00 Miller Registration $658.00 Overton Airfare 01 $ 292 50 Overton Uatal Subtotals ExpensesEmployee 15 16 17 18 19 20 21 22 # FO Average: 531.9448 Count: 78 Sum: 13298.62 B + 100% e х w I 3:40 AM 10/24/2020 26

Subtotal ? X ✓ At each change in: Empoyee Use function: Sum Add subtotal to: Expense Empoyee Category Replace current subtotals Page break between groups Summary below data Remove All OK Cancel

A1 11213 D E 1 2 3 4 5 6 7 0 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 A B Expense Empoyee Category $750.20 Abbott Airfare $1,051.14 Abbott Hotel $285.32 Abbott Meals $123.41 Abbott Misc $595.00 Abbott Registration $2,805.07 Abbott Total $423.99 Donalson Airfare $1,204.78 Donalson Hotel $324.08 Donalson Meals $93.28 Donalson Misc $450.00 Donalson Registration $2,496.13 Donalson Total $327.95 Hart Airfare $864.84 Hart Hotel $340.76 Hart Meals $114.90 Hart Misc $995.00 Hart Registration $2,643.45 Hart Total $285.00 Miller Airfare $840.25 Miller Hotel $304.12 Miller Meals $113.64 Miller Misc $595.00 Miller Registration $2,138.01 Miller Total $658.00 Overton Airfare $1,283.50 Overton Hotel $374.82 Overton Meals $124.64 Overton Misc $775.00 Overton Registration $3,215.96 Overton Total $13,298.62 Grand Total Subtotals Expenses Employee +

4. Click the “-“ sign next to Donaldson and Hart to collapse these sections and show only their totals.

A1 face 11213 D 1 Nm C Category Airfare Hotel Meals Misc Registration 4 5 I+ + А B Expense Empoyee $750.20 Abbott $1,051.14 Abbott $285.32 Abbott $123.41 Abbott $595.00 Abbott $2,805.07 Abbott Total $2,496.13 Donalson Total $2,643.45 Hart Total $285.00 Miller $840.25 Miller $304.12 Miller $113.64 Miller $595.00 Miller $2,138.01 Miller Total $658.00 Overton $1,283.50 Overton $374.82 Overton $124.64 Overton $775.00 Overton $3,215.96 Overton Total $13,298.62 Grand Total Airfare Hotel Meals Misc Registration 6 7 13 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 - Airfare Hotel Meals Misc Registration Subtotals Expenses Employee

5. Select Expenses worksheet and Go to Insert tab, and select PivotTable from the Tables group.

Verify the range of data in the PivotTable pop up and select radio button for new worksheet so that Pivot table gets created on a new worksheet. Name the pivot table Categories by changing name under Analyze, PivotTable Name

AutoSave Off H2 Book1 - Excel Akanksha Gupta08 A х File Home Insert Insert Page Layout Formulas Data Review View Developer Help O Search Share Comments y LC H H Get Add-ins 6? Ω 11 PivotTable Recommended Table Pivot Tables tu V Line Column Win/ Loss Illustrations Slicer Timeline Maps PivotChart Link Recommended Charts Comment Text My Add-ins Symbols 3D Map Tables Add-ins Charts Tours Sparklines Filters Links Comments Pivot Table fo Airfare Easily arrange and summarize complex data in a PivotTable. D E F G H H | J K L M N O P Q R S T UA FYI: You can double-click a value to see which detailed values make up the summarized total. Tell me more 6 Overton Misc $124.64 7 Miller Airfare $285.00 8 Abbott Meals $285.32 9 Miller Meals $304.12 10 Donalson Meals $324.08 11 Hart Airfare $327.95 12 Hart Meals $340.76 13 Overton Meals $374.82 14 Donalson Airfare $423.99 15 Donalson Registrati $450.00 16 Abbott Registrati $595.00 17 Miller Registrati $595.00 18 Overton Airfare $658.00 19 Abbott Airfare $750.20 20 Overton Registrati $775.00 21 Miller Hotel $840.25 22 Hart Hotel $864.84 Docictrati coas on Subtotals Expenses Employee 22 Mart (+ FO B + 100% e х w I 3:44 AM 10/24/2020 26

Create Pivot Table ? X Choose the data that you want to analyze Select a table or range Table/Range: Expenses!$A$1:$C$26| O Use an external data source Choose Connection... Connection name: Use this workbook's Data Model Choose where you want the PivotTable report to be placed New Worksheet Existing Worksheet Location: Choose whether you want to analyze multiple tables Add this data to the Data Model OK Cancel

AutoSave Off H Book1 - Excel PivotTable Tools Akanksha_Gupta08 A File Home Insert Page Layout Formulas Data Review View Developer Help Analyze Design Search Share Comments Active Field: Clear + Pivot Table Name Pivot Tablet Options E Insert Slicer Insert Timeline Filter Connections → Group Selection Ungroup 7 Group Field Group 07 Fields, Items, & Sets OLAP Tools Relationships - E Field List +/- Buttons A Field Headers Drill Drill Field Settings Down Up Active Field IT Select Move Pivot Table Refresh Change Data Source PivotChart Recommended Pivot Tables Pivot Table Filter Data Actions Calculations Tools Show A3 х f A B с D E F G H H J K L M N O 1 Pivot Table Fields 2 Choose fields to add to report: 3 4 Search PivotTable 1 5 6 7 To build a report, choose fields from the Pivottable Field List Employee Category Expenses 8 9 More Tables... 10 11 12 13 = Drag fields between areas below: 14 15 O Y Filters III Columns 16 17 18 19 Rows Σ Values 20 21 22 Subtotals Categories Expenses Defer Layout Update Update Ready + 100% i e х w I 3:48 AM 10/24/2020 26

File Home Insert Page Layout Formulas Data Review View Developer Help Analyze Design Active Field: → Group Selection Clear i PivotTable Name Categories Options Ungroup Insert Slicer 7 Insert Timeline Filter Connections Select Average Expenses Drill Drill Field Settings Down Up - Group Field Refresh Change Data Source Move Pivot Table E Pivot Table Active Field Group Filter Data Actions

6. On the new Categories worksheet, select checkbox for Category and Expenses under PivotTable Fields. Excel automatically places Category under Rows and Sum of Expenses under Values and generates the Pivot

AutoSave Off H Book1 - Excel PivotTable Tools Akanksha_Gupta08 A File Home Insert Page Layout Search Share Comments Active Field: Pivot Table Name Pivot Tablet Formulas Data Review View Developer Help Analyze Design → Group Selection Insert Slicer Clear + Ungroup Insert Timeline IT Select - Refresh Change Data Group Field Filter Connections Source Move Pivot Table Category Drill Drill Field Settings Down Up 07 Fields, Items, & Sets OLAP Tools Relationships E Field List +/- Buttons A Field Headers Options PivotChart Recommended Pivot Tables Pivot Table Active Field Group Filter Data Actions Calculations Tools Show АЗ X f Row Labels A B с D E F G H H 1 J K L M N 1 Pivot Table Fields Choose fields to add to report: Search 2 3 Row Labels Sum of Expenses 4 Airfare 2445.14 5 Hotel 5244.51 6 Meals 1629.1 7 Misc 569.87 8 Registration 3410 9 Grand Total 13298.62 10 Employee Category Expenses More Tables... 11 12 13 Drag fields between areas below: 14 15 Filters III Columns 16 17 18 19 Rows Category Σ Values 2 Sum of Expenses 20 21 22 Subtotals Categories Expenses Defer Layout Update Update D + 100% e х I w 3:51 AM 10/24/2020 Q 26

7. Click on Sum of Expenses under Values and select Value Field Settings

AutoSave Off H Book1 - Excel PivotTable Tools Akanksha_Gupta08 A File Home Insert Page Layout Search Share Comments Active Field: Pivot Table Name Pivot Tablet Formulas Data Review View Developer Help Analyze Design → Group Selection Insert Slicer Clear + Ungroup Insert Timeline IT Select - Refresh Change Data Group Field Filter Connections Source Move Pivot Table Category Drill Drill Field Settings Down Up 07 Fields, Items, & Sets OLAP Tools Relationships E Field List +/- Buttons A Field Headers Options PivotChart Recommended Pivot Tables Pivot Table Active Field Group Filter Data Actions Calculations Tools Show АЗ X f Row Labels A B с D E F G H H 1 J K L M N 1 PivotTable Fields Choose fields to add to report: Search 2 3 Row Labels Sum of Expenses 4 Airfare 2445.14 5 Hotel 5244.51 6 Meals 1629.1 7 Misc 569.87 8 Registration 3410 9 Grand Total 13298.62 10 Employee Category Expenses Move Up More Tables... 11 12 13 Drag fields between areas be 14 15 Y Filters Move Down Move to Beginning Move to End Y Move to Report Filter II Move to Row Labels Move to Column Labels Σ Move to Values X Remove Field 1 Value Field Settings... Sum of Expenses 16 17 18 19 Rows 20 Category 21 22 Subtotals Categories Expenses Defer Layout Update Update D + 100% e х I w 3:52 AM 10/24/2020 Q 26

In the Pop up change Sum to Average and give custom name as Average Expenses

Value Field Settings ? x Source Name: Expenses Custom Name: Average Expenses Summarize Values By Show Values As Summarize value field by Choose the type of calculation that you want to use to summarize data from the selected field Sum Count Average | Max Min Product Number Format OK Cancel

8. Again click Average Expenses under Values and select Value Field Settings. Select Tab “Show Values As” and then click Number Format button

Value Field Settings ? x Source Name: Expenses Custom Name: Average Expenses Summarize Values By Show Values As Show values as Base item: No Calculation Base field: Employee Categor Expenses Number Format OK Cancel

Select Accounting in Number format.

Format Cells ? X Number Sample $325.82 Category: General Number Currency Accounting Date Time Percentage Fraction Scientific Text Special Custom Decimal places: 2 Symbol: $ Accounting formats line up the currency symbols and decimal points in a column. OK Cancel