CIS100 CUNY Creating and Modifying a Pivot Table Report

1 Open the downloaded file go16_xl_ch07_grader_7g_hw.xlsx. Save the file as Lastname_Firstname_7G_Aquatics in your Excel Chapter 7 folder. 0
2 Click cell A2, and then insert a Recommended PivotTable by choosing the Blank PivotTable option in the lower left corner of the dialog box. Filter by the Month field. Use the Pool field as the row labels and the Item field as the column labels. Place the Amount field in the Values area. Close the PivotTable Fields list. Note, Mac users insert a PivotTable and then arrange the fields as indicated. 10
3 Format the values in the PivotTable using the Number category to display zero decimal places and the 1000 Separator. 5
4 Insert a slicer for the Pool field and for the Item field. Apply Slicer Style Light 4 and Slicer Style Light 6 to the slicers, respectively.

Note: The style names may be Light Yellow, Slicer Style Light 4 and Light Green, Slicer Style Light 6, respectively, depending on the version of Office used.

5 By using the two slicers, filter the data to show, for the Tropics Pool, the total revenue for Spa Fees and Swim Lessons. Recall that you can use CTRL to select multiple filters in a slicer. Move the two slicers to the right of the filtered PivotTable, and then make a copy of this worksheet. Name the copied worksheet Tropics Pool.

Note, Mac users, after creating and naming the sheet, on the PivotTable Analyze tab, click Filter Connections. Remove all check marks.

6 Display Sheet1 and clear the filters from the slicers and remove the slicers from the worksheet. Rename the sheet 2Q Revenue 5
7 Insert a PivotChart using the Stacked Column chart type. Move the chart to a new worksheet named 2Q Revenue Chart

Note, Mac users select the range A4:G10 in the PivotTable. On the Insert tab, click Recommended Charts, and then click Stacked Column to make the chart. Move the chart to a new worksheet named 2Q Revenue Chart

8 Apply the Layout 3 chart layout and Chart Style 9. Add centered data labels, and then hide all of the field buttons on the chart. As the Chart Title, type 2nd Quarter Aquatics Program Revenue and then insert a custom footer with the file name in the left section. 7
9 Add a new worksheet to the workbook, and then import the Access database e07G_Pool_Supplies.accdb into the Data Model as a PivotTable Report using both tables in the database. Rename the sheet Supplies PivotTable

Note, Mac users, import the data from the downloaded, comma-delimited text file Pool_Supplies.csv and then convert the data to a table with headers. Insert a PivotTable using the imported data. Rename the PivotTable sheet Supplies PivotTable

10 To create the PivotTable, place the Supplier field in the Columns area. Place the Item field in the Rows area, and the Quantity field in the Values area. Apply the Number format to the values in the PivotTable with zero decimals and the 1000 separator. 5
11 Insert a row above the PivotTable, if necessary, so that the PivotTable begins in cell A2. In cell A1, type Aquatics Program Purchases and then apply the Title cell style. 5
12 Click in the PivotTable, and then insert a 3-D Pie PivotChart. Move the chart to a new sheet with the name Aquatic Supplies Chart

Note, Mac users continue to next step.

13 Modify the chart so that only the Supplier field displays in the Axis (Category) area. Remove any fields that display in the Legend (Series) area.

Note, Mac users display the Field pane and remove Item field from the Row area. Move the Suppliers field to the Rows area. Select the range A3:B7 in the PivotTable. On the Insert tab, click Pie, and then click 3-D Pie to create a traditional chart. Move the chart to a new sheet with the name Aquatic Supplies Chart

14 Apply Style 7, remove the legend from the chart, and display only the Category Name and Percentage data labels positioned in the Center. Format the data labels by changing the font size to 12, the Font color to Black, Text 1, and then apply Bold. 6
15 Change the chart title to Aquatic Supply Purchases and then change the font size to 24. Hide all of the field buttons on the chart, and then insert a footer with the file name in the left section. 10
16 Hide the Aquatics Revenue sheet. Select all the sheets and insert a footer in the left section that includes the file name, and then center the worksheet Horizontally on the page. Change the Orientation to Landscape and scale the Width to fit to 1 page. 6
17 Display the properties. As the Tags/Keywords, type aquatic revenue, supplies and as the Subject, type your course name and section #. Under Related People, be sure that your name displays as Author. Save your workbook. 2
18 Save and close the file, and then submit for grading. 0

You can leave a response, or trackback from your own site.


25 Responses to “CIS100 CUNY Creating and Modifying a Pivot Table Report”

  1. Hundreds of 1000’s of people are playing poker online. When you perform free POKER ONLINE you can practice as frequently as you want for as lengthy as you want. 1 great site that you can go to is Poker Stars.

  2. Anh Dufrene says:

    Best Private Proxy Retail store – – Order Private Proxies and Receive 50 OFF in addition to Free of charge Proxies!

  3. Online poker is generally faster than real life poker. My boy buddy received me 1 hundred of each colour which are blue, white, red, green and black. Get into these pots, but know when to fold your hand.

  4. The main goal of the game is to get a pair of jacks and to get the possible winning hand for the highest payout. Now, if you’re obtaining as thrilled as I am by all this, just allow me tell you real quick what I know.

  5. Private proxies and also lowest prices: 50 price cut, free proxies plus special offers – only on

  6. 467784 272745An extremely fascinating go via, I may not concur entirely, nevertheless you do make some genuinely legitimate factors. 137659

  7. Using casino games like roulette, slots, video poker, and blackjack. Even though it’s annoying to some, you got to like my spouse for being enthusiastic about something.

  8. Pat Mazza says:

    wow, awesome post.Really looking forward to read more. Keep writing.

  9. Major thanks for the blog post.Thanks Again. Cool.

  10. Leo Giddens says:

    All he cares about is stretching his income, aside from income from work or other business activities. I’ve talked about spirits and Angels alot recently. This card game is about drinking, and it is fairly fun.

  11. Charlyn Brey says:

    This is not to say that experience is not acquired from previous play. There are so many concerns that it may consider time to figure out exactly where or how to even get heading. Fun is waiting for you following all.

  12. We admire poker players who produced themselves to the top, winning every match they get in. You can also discover valuable poker playing tips and discover out all the rules of the video games.

  13. CICD says:

    44616 906528Today, I went to the beach with my children. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She placed the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is totally off topic but I had to tell someone! 643775

  14. Do not try this in Vegas unless you have actually mastered human cloning. He then walked the room and saw other games of varying stakes. When you predicted this right you get two times your bet.

  15. I might not wait to get house and order my own set of these. Numerous gamers will discard of chips that they no longer utilize, contribute them, or offer them. There are different bets you can make and there are different chances for these bets.

  16. granny sex says:

    Sеx iѕ fоr Yоung Pеоplе

  17. 641506 108839An attention-grabbing discussion is worth comment. I believe which you really should write much more on this matter, it wont be a taboo subject nevertheless usually persons are not sufficient to talk on such topics. To the next. Cheers 299286

  18. 681188 895006The when I just read a weblog, Im hoping that this doesnt disappoint me approximately this one. Get real, Yes, it was my method to read, but When i thought youd have something interesting to state. All I hear can be a number of whining about something which you could fix need to you werent too busy trying to find attention. 20932

  19. My Blog says:

    77226 994286Delighted for you to discovered this site write-up, My group is shopping more often than not regarding this. This can be at this moment definitely what I are already seeking and I own book-marked this specific web site online far too, Ill often be keep returning soon enough to look at on your unique blog post. 827998

  20. casino bonus says:

    482975 544710This web-site can be a walk-through rather than the information you wished about it and didnt know who ought to. Glimpse here, and you will surely discover it. 767138

  21. 240698 549497Sounds like some thing a lot of baby boomers really should study. The feelings of neglect are there in several levels when a single is over the hill. 449475

  22. Charlesuriny says:

    aliciadevoucoux Les Episode De City Hunter Gratuitement toyota corolla 2010 blue book precio

  23. Destockage G Ant Casino Application Casino Max Fleuriste Geant Casino Jas De Bouffan Ma Carte Casino Fr

  24. Online Slot Games Uk Offline Casino Games Software Pressing Geant Casino Pontarlier

  25. 768669 731263I saw your post awhile back and saved it to my computer. Only recently have I got a chance to checking it and have to let you know good function. 769828

Leave a Reply

Powered by WordPress | Designed by: buy backlinks | Thanks to webdesign berlin, House Plans and voucher codes