Pen State University Excel Tables Worksheet

To get credit for this assignment:

1. Your TA should be able to open your file by clicking on its link.

1. Requirements:Excel TablesThe Orders worksheet shows orders from a SuperStore. Some research needs tobe done to locate a group of information.
   1. Convert the Orders dataset into an Excel Table (remember you only need to selectone cell to create excel table)
   2. Then, locate the records of the Orders dataset that comply with all thefollowing characteristics:
      1. City is San Antonio or Chicago.
      2. Segment is Consumer.
   3. Format the Sales and Profit to be currency ($)
   4. Enter customer (or customers) name with the most distinct product ids from thisPage 1 of 2

filtered set into cell A306

1. E Add a Total row and use the properties of the Total Row to calculate:
   1. the sum of quantity.
   2. the sum of profit.
2. F Apply the Blue, Table Style Medium 9 to the table.

Pivot Tables and Pivot Charts

Pivot Table 1 :

1. Use the Orders worksheet and create a Pivot Table on new worksheet with afilter on Category, row of Sub-Category, and sum of Quantity, Sum of Sales,Sum of Profit
2. Filter the information so that only “Furniture” is displayed.
3. Which sub-category of Furniture has the most sales revenue? Answer in cell J1 ofthis sheet.
4. Format the Sum of Sales and Profit column to be currency ($)
5. Rename the PivotTable’s worksheet: Pivot Table 1.

Pivot Table 2 & Pivot Chart:

1. Use the West Coast Coffee worksheet and create another Pivot Table thatdisplays the average of Sales $ organized by: Month and Product as row labels(in that order) and State as column labels. Format all sales numbers as currency($)
2. In cell J1 of this sheet put in the state that has the lowest average coffee sales $
3. In cell J2, for the state with the most coffee sales ($)(Be careful, this means youneed the sum of sales), what is the highest sales ($) month?
4. Collapse the Product rows and create a 3D Clustered Column Pivot Chart.
5. Apply Chart Style 5 and Quick Layout 3.
6. Enter as Title of the Chart: Average Sales Totals.
7. Rename the PivotTable’s worksheet: Pivot Table 2.
8. Move the Pivot Chart to its own worksheet. Rename this new worksheet asPivot Chart.