Pen State University Excel Tables Worksheet

To get security for this assignment:

1. Your TA should be cogent to unreserved your improve by clicking on its coalesce.

1. Requirements: Excel Tables The Nod workfencing shows nod from a SuperStore. Some investigation scarcitys to be done to fix a assemblage of knowledge.
   1. Convert the Nod dataset into an Excel Tcogent (recall you solely scarcity to select one cell to produce excel consideration)
   2. Then, fix the history of the Nod dataset that submit delay all the following characteristics:
      1. City is San Antonio or Chicago.
      2. Segment is Consumer.
   3. Format the Sales and Avail to be circulation ($)
   4. Enter customer (or customers) indicate delay the most separate consequence ids from this Page 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 division.
   2. the sum of avail.
2. F Apply the Blue, Tcogent Style Medium 9 to the consideration.

Pivot Tables and Pivot Charts

Pivot Tcogent 1 :

1. Use the Nod workfencing and produce a Pivot Tcogent on new workfencing delay a filter on Category, row of Sub-Category, and sum of Quantity, Sum of Sales, Sum of Profit
2. Filter the knowledge so that solely “Furniture” is displayed.
3. Which sub-category of Furniture has the most sales fruits? Answer in cell J1 of this fencing.
4. Format the Sum of Sales and Avail shaft to be circulation ($)
5. Reindicate the PivotTable's worksheet: Pivot Tcogent 1.

Pivot Tcogent 2 & Pivot Chart:

1. Use the West Coast Coffee workfencing and produce another Pivot Tcogent that displays the mediocre of Sales $ organized by: Month and Consequence as row labels (in that manage) and Recite as shaft labels. Format all sales total as circulation ($)
2. In cell J1 of this fencing put in the recite that has the meanest mediocre coffee sales $
3. In cell J2, for the recite delay the most coffee sales ($)(Be considerate, this instrument you scarcity the sum of sales), what is the foremost sales ($) month?
4. Collapse the Consequence rows and produce a 3D Clustered Shaft Pivot Chart.
5. Apply Chart Style 5 and Quick Layout 3.
6. Enter as Title of the Chart: Mediocre Sales Totals.
7. Reindicate the PivotTable's worksheet: Pivot Tcogent 2.
8. Move the Pivot Chart to its own worksheet. Reindicate this new workfencing as Pivot Chart.