Excel XP Pivot Table Reports -Exercises

Excel XP Pivot Table Reports - Exercises Exercise 1: Pivot tables reports: What they are No exercise!

Exercise 2: Creating source data. 1. Create a new Workbook 2. Type the Headings Name, No of days, Leave type, Month 3. Use a Data Form to the 5 records shown right 4. Save the file as Leave data

Name No of Leave Month days type Amy Jon Amy Jon Amy

2 3 15 3 10

AL AL AL FL SL

May June May June June

Exercise 3: Create a summarising Pivot Table Report 1. Create a Pivot table based on your data and on the same sheet, with Name as the row heading, Leave type as the column heading and No of days as the data item 2. Save the file as Leave 3. Open the file Language info 4. Using the data create a Pivot table on the same sheet with row heading Native Language, column heading Language learning and data as Name 5. Rename the sheet as Language details 6. With the same data create another Pivot table on a new sheet (do not base on the previous Pivot table) with row headings Native Country and Native Language, column headings Working Country and Language learning and data as Name 7. Rename the sheet as Country and Language 8. With the same data create another Pivot table on a new sheet (do not base on the previous Pivot table) with row heading Native language and column heading Native Country, pages Language learning and Working country and data as name 9. Rename the sheet as Native pages 10. Save Language Info

Excel XP Pivot Table Reports - Exercises

Exercise 4: Adjusting Pivot table fields 1. Change to the Country and Language sheet. 2. Remove the Native Language and Language learning fields from the Table 3. Change back to the Leave file 4. Create a new Pivot table below the existing one,(don’t base on existing Pivot table) with row name, column Month and data No of Days and Leave type 5. Remove the Leave type field from the table 6. Change back to the Language Info file and the Country and Language sheet. 7. Add Native Language as a row field into the Table to the left of Native country 8. Add Language learning as a column field to the right of Working Country

Exercise 5: Select relevant information from pivot tables 1. Change to view Leave and view only Annual Leave (AL) 2. Close and save the file 3. Using Language info and the Language details sheet remove the Native Language English from view in the Pivot Table. 4. Change to the Country and Language sheet 5. Remove the Native Country China and India from view 6. Change back to view all languages in both Native Country and Native Language 7. View the details for the Native Country Canada only 8. Change back to view all Native Country 9. View the Language learning Cantonese only 10. Change back to view all Language Learning 11. Change to the Native pages spreadsheet 12. View information for Working country China and Language Learning Cantonese 13. View all information for Cantonese 14. View all the information

2

Excel XP Pivot Table Reports - Exercises

Exercise 6: Pivot Table Report Options 1. Change to the Country and Language sheet 2. Change the name for the pivot table to Country and Language 3. Change to the Language details sheet 4. Change the Pivot table name to Language and remove the grand totals from the rows and columns 5. Change to the Native pages sheet and rename the Pivot table as Native 6. From the data on the Language details sheet create on a new sheet a new Pivot table, without Grand Totals and autoformatting, with the name Unformatted and with name as a row heading. Choose your own column, page and data fields. Note that the column widths are not changed 7. Rename the sheet Unformatted 8. Save the file Language Info

Exercise 7: Delete a PivotTable report 1. Open the Leave file and delete the bottom Pivot Table 2. Change to the file Language Info and the sheet Unformatted 3. Delete the Pivot table.

Exercise 8: Edit the source area used by the pivot table 1. Change to the Language details sheet and add 3 new people to the end of the data 2. Change the data source for each of the 3 Pivot tables in this Workbook to include the 3 new rows.

Exercise 9: Creating a Pivot table based on a Pivot table 1. Create a Pivot table on the Native sheet –below the previous one - based on the Pivot Table Native but with row heading Language learning and Native Language; column headings Native Country and Working country; data Name 2. Name the table Second Pivot

Exercise 10: Update or refresh data shown in the pivot table 1. Insert the new (rows) shown below into the data on the Language Info sheet after row 13.

2. Refresh the Pivot tables on Country and Language and Language details and note the changes (look especially for Esperanto) 3. Refresh the top Pivot table on Native pages and note that both tables change 4. On the Language Info sheet insert a new record

3

Excel XP Pivot Table Reports - Exercises 5. Make all the headings in the Pivot table on the same sheet bold and change the column widths 6. Refresh the data 7. Remove the Preserve formatting option and refresh the table again (note loss of formatting) 8. Put the Preserve formatting option on again. 9. Save and close all files

Exercise 11: Using simple Functions 1. 2. 3. 4. 5. 6. 7.

Open the Expenses file and view the Qtr1 sheet In the Pivot table change the Sum of Amount field to be Average of amount Open the file Durian and view the December sheet In the Pivot Table change the Sum of Number to Average of number Add Number as a new data field (You now have sum of number and average of number) Change the new Sum of Number to StDevP of Number Save both Durian and Expenses

Exercise 12: Number format 1. In Expenses on the Qtr 1 sheet change Average of amount so the numbers show to 2 decimal places and with a $ sign in front 2. In Durian on the December sheet change Average of number so the numbers show to 3 decimal places 3. Change StDevP so the numbers show to 2 decimal places

Exercise 13: Sorting Data 1. In Durian move pm to the left of am 2. Sort the Names into descending order 3. Show the top 2 items only 4. Switch the Top ten Autoshow off

Exercise 14: Calculating fields and items 1. Change to the Expenses file and the Qtr 1 sheet 2. In the Pivot table create a calculated field called Forecast which multiplies the expenses by 4 to create a forecast yearly amount. (grand total = $11,340.00 3. Let’s say that Accommodation alone carries 15% VAT. Calculate a new Item called accom inc VAT which multiplies the Accommodation item by 1.15. Why can’t you? 4. Remove the Average of Amount data field and try the previous exercise again. (Total 3,675.56) 5. Change to the Durian file and the December sheet 6. Create a new Pivot table on a new sheet as the data with Name as row, Where as column, Number as data and Date as page 4

Excel XP Pivot Table Reports - Exercises 7. Name the new sheet When 8. We have discovered that Pooh Bear was being “conservative with the truth” and halved his actual intake of Durian when he reported the amount eaten. Create a new item called Real Pooh which doubles the amount eaten by Pooh 9. Save the Durian and Expenses file

Exercise 15: Changing and Deleting calculated fields/ items 1. Change to the Expenses file and the Qtr 1 sheet 2. Inflation is rising! Change the formula for Forecast so that it multiplies by 4.5 to allow leeway. 3. Remove the calculated field Forecast 4. VAT has been increased to 17.5%! Change the formula for accom inc VAT 5. Change to the Durian file 6. Remove the item Real Pooh

Exercise 16: Relating data 1. Change to Expenses 2. Change Sum of amount so that each value shows as a percentage of the column total 3. In the Durian file and December sheet change average of number to sum of number 4. Change sum of number so that it show the difference of each value from Donald Duck’ values. 5. Change average of number so that it shows the running total in time of day

Exercise 17: Hiding page items 1. Change to the Language Info file and the Native page sheet 2. Hide all the pages except Cantonese and Mandarin 3. Change to the Durian file and When sheet 4. Hide all dates except the 1 –3 December

Exercise 18: Subtotals 1. Change to the December sheet on Durian 2. Remove all data fields and add Sum of number 3. Show the Min, Max and Sum subtotals for Where 4. Show the Min subtotal for Name 5. Remove each subtotal

5

Excel XP Pivot Table Reports - Exercises

Exercise 19: Printing Pivot Table Reports 1. View a Print Preview of the Pivot table in Expenses on Qtr1 2. Open the Language Info file and change to the Country and Language sheet 3. Show all languages in Language learning 4. Print Preview the table – note the repetition of International (page 4) 5. Change so that headings will not be repeated 6. Print Preview the table – note International is not repeated 7. Print the table so that each major item is on a new page 8. Close and save all files

Exercise 20: Creating a “consolidating!” pivot table report (using multiple ranges) 1. Open the file Stationery note the three sheets with data 2. Consolidate onto sheet Total the data from January, February and March 3. Change the Count of value to Sum of Value 4. Close and save Stationery

Exercise 21: Using Autoformats 1. Use the AutoFormats to format two Pivot Tables

Exercise 22: Creating and printing a pivot chart 1. Open the Durian file and change to sheet December 2. Create a Pivot Chart on a new sheet based on the data on this sheet with category Name and Date Data Sum of Number and page Where 3. Change to Expenses and the sheet Qtr 1 4. Create a Pivot Chart on a new sheet based on the data on Qtr 1 with category Reason data Amount and Forecast and Page Name

6