Introduction to Filtering, Pivot Tables, and Charts

Introduction to Filtering, Pivot Tables, and Charts >> Introduction to Data Analysis Using Excel

1. Download the spreadsheet “Store Sales 2009.xlsx” below.  Use the data in this spreadsheet for the remainder of the assignment.
Store Sales 2009.xlsxThe “Compiled Information” tab was created to pull in the data from the other tabs using formulas.  The formula in the “Shipping Cost” column was entered incorrectly.  Fix the formula in cell D3, then re-copy the correct formula down to the remaining rows.  What was the problem with the original formula?

• The formula did not reference the correct tab.
• The formula should use a locked reference for the table_array parameter.
• The formula should use a locked reference for the lookup_value parameter.
• The formula should be written with the HLOOKUP function.  

2. On the “Compiled Information” tab, a VLOOKUP formula has been pre-entered into cell E3.  This formula was written correctly, and it uses references to the numbers in cells E1 through G1 to determine the correct index_number parameter.  Fill in cells F1 and G1 with the correct index numbers, then copy the formula in cell E3 down to all the rows in columns E, F, and G.  What number did you enter into cell G1?

6

3. Now that the data is updated, create a pivot table that contains all of the data on the “Compiled Information” tab.  Using the PivotTable Fields side bar, drag the “Unique ID” field to the VALUES area and summarize the information by Count.  What is the Count of Unique ID?

2153

4. Update the pivot table to show “Order Date” grouped by month.  Drag the “Sales” field into the correct area to show the Sum of Sales for each of the months listed.  Which month had the lowest sales? (Your answer should be the name of the month.)
Hint: Remember that you can sort the data once it is in the pivot table!

MAY

5. Continuing from the previous question, update the pivottable to show the Average of Sales by month. Which month had the highest average? (Your answer should be the name of the month.) 

December

6. Which Customer Segment contained the single order with the highest shipping cost?

• Consumer
• Corporate
• Home Office
• Small Business  

7.In the Corporate Customer Segment, what was the mostcommon order quantity?

123

8. In all segments, how many orders were placed in one quarter of the year but shipped in the next quarter (For example, an order in which Order Date = Qtr1 2009 and Ship Date = Qtr2 2009)?

678.34

9. Create a line chart that shows the average of “Sales” for each customer segment by month, where each customer segment is represented by a line on the chart.  Which of the following line charts shows the average sales data?

• The range on y-axis is 0-50 with scale of 5 units
• The range on y-axis is 0-100 with scale of 10 units
• The range on y-axis is 0-200000 with scale of 20000 units
• The range on y-axis is 0-6000 with scale of 1000 units

10. The line chart shown follows a zig-zag pattern with decreasing peaks left-rightLine charts are useful for looking for trends in data and gaining quick insights.  The chart above shows total Sales in 2009 by month in the Consumer segment.  Which of the following could be inferred from the chart?  Select all that apply.

• Month to month sales numbers are relatively volatile (>10% difference in sales compared to previous month).
• In 2009, March and April were the months with the highest total sales.
• In general, total sales numbers continued to rise
• throughout 2009.
• In 2009, June was the month with the lowest total sales.