Exercise Pivot Table with Chart PDF

Preview

Summary

Pivot tables introduction...

Description

PIVOT TABLE Exercise 8: Pivot Table with “Chart” Pie What is a Pivot Table? A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns.

Why is it called a pivot table? A Pivot Table lets you rotate the data summary and look at it from different Angles or perspectives. A Pivot Table allows you to move the fields easily, nest fields within each other, and even create new groups of items. That's why an Excel Pivot Table is called 'Pivot' Table.

……………………………………………………………………………………………………………………... 

Download the Starter/Data File Named Exe 8 CBS-Culture and Complete the following:



Using the data in the Range of CBS-Culture Worksheet A2:F25.

Step 1: Select “Click and Shade In” A1:F25, click on the Insert Tab and Select Pivot Table as below:

Step 2: Click on the Radio Button “New Worksheet” and OK. Step 3: This Sheet or Menu on your screen will Appears, below:



Create a Pivot Table on a New Worksheet, Named or Called CBS-Pivot.

Step 4: Click anywhere on This Menu Below:

And, Step 5: on the Right Hand Side you will see This, below:



Step 6: Move the “Name of Plant” and “Indoor or Outdoor or Both” fields to the Rows area and; “Cost Per Plant” to the Values area of the table: (Click and Drag Them “One by One” and Release your Mouse Rows and Value ‘Boxes’…”. Shown Below:



Step 7: Sort on the “Name of Plant” in descending order (Click on the “Down Arrow” and Select Z-A)



Step 8: Format the “Cost Per Plant” to currency with 2 decimals. (Select the Column “Sum of Cost Per Plant” Click on Home Tab and Go to Number Currency, 2 Decimal Places)



Step 9: Use the Report “Outline Layout” to enhance the appearance. (Click anywhere on your Pivot Table, Move Top Right to Pivot Table Tools and Click on Design Tab) below:



Step 10: Once you in the Design Tab, Select Format Layout. There you will see Various Layouts, I chose Outline Layout Form and your NEW LAYOUT will Look Like This, below:



Step 11: Add Conditional Formatting to the “Indoor or Outdoor or Both” and Highlight the Text that Contain “Outdoor Only” Using Green Fill with dark Green Text (This is Straight forward…Home Tab_Conditional Formatting and select “Text that Contain”)



Step 12: On the Filter Arrow of the Subheadings (Down Arrow of Subheading Indoor or Outdoor or Both, Only Select Outdoor “Tick Box”), as Below:



Step 13: Insert a Pie Chart (3D) and Rename the Total Heading/Title to “CBS-OUTDOOR” (Select From A3:C18, then Click on Insert Tab, Select Pie (Down Arrow) 3D, like This:



Step 14: Rename your Pie Heading/Title (Which has Total Written it) Rename to CBSOUTDOOR

Step 15: Your output should look similar to the output in the figure below:



Save Exe8_CBS-Culture as: yourSurname_YourStudentNumber_PIVOT-PIE_YourGroup.



Submit/Upload to: Activity Submission Tab_Excel Exercise Submission 8: Pivot Table...