Using Pivot Tables to Analyze Spreadsheets

1. Using Pivot Tables to Analyze Spreadsheets Spreadsheet Analysis Project

2. Project Goal The goal of the project is to answer questions about the data then present it in a typed format with accompanying charts. There is no word count you must meet, but your report should have the following.

3. Report Makeup A cover page with your name, the project name, date, and class. An introduction explaining what you are trying to find out. The point is if someone not in this class picked up the paper, would they know what it was for.

4. Finding the answers with pivot tables Getting started with the spreadsheet.

5. Download the data The data used here is from the practice assignment – the project data is nearly identical, but has MUCH more of it.

6. Create a Table Find the very bottom right item and click it. Use Ctrl-Shift-Home to select the entire table. On the Home Tab choose format as Table. Be sure that My table has headers is checked.

7. Add any extended fields We need to know the extended revenue, that is how much each order was. (Items x Cost per ) = Total Next to make a new column for these two multiplied together. Click in the first column to the right of the data, and type Total The List automatically includes this column. Under the header type in the formula to multiple the Quantity by the price each. Excel automatically copies it down!

8. Isolate the questions What is it we want to know from the data?

9. The Questions… Identify: Total sales by year in dollars Highest Selling Item per year in Revenue Overall most selling item - quantity not revenue For Extra Credit What was the highest selling month of the highest selling year. Which month over all has the best sales, which has the worst.

10. The Chart Questions Then illustrate the following using charts Highest Revenue Year - All Item Sales Comparison - bar chart Highest Selling Item, year by year - column chart Sales Trend of most (quantity not revenue) sold item - line chart Division of all the items sold by the top distributor - pie chart.

11. Let's tackle the questions Step-by-step on answering the project questions. Remember this is different data, so the process will remain the same by the answers will be different!

12. Creating a Pivot Table Create the first pivot chart Click on the table Click Design Click Summarize with Pivot Table Click OK on create Pivot Table

13. The Pivot Table Window The Pivot Table List holds all the columns of Data Bottom left is where we will drop the labels we are interested in. Value: Used for counting and totaling information, usually where you will put the dollar amount or volumes. Column and Row Labels:Drop the various factors of your questions here, you can stack them to “drill down” to more specific answers. You CAN NOT mess up the data here, so feel free to experiment.

14. Question 1: Total sales by year in dollars Click on TOTAL and drag it down to Values Click on Year and drag it down to Labels That’s is it! You answer is here – Let’s change the name of the sheet and save this pivot table for our report. Double click the sheet name and rename it Question 1

15. Question 2: Highest Selling Item per year in Revenue Start with making another pivot table The question involves four parts What items were sold How much did they bring in sales What was it each year Which one was highest First let’s find out what was sold and for how much, then we can calculate which was the highest. Drag down TOTAL to Values Year to Labels THEN Product under Year This will break it down by year this way. Next Which Sold for the most?

16. Filtering and Sorting There are three ways to filter or sort. Click the Drop down menu and select which you want to see. Select More Sort Options and sort by other fields, such as revenue This works well, as you can use descending to show the highest ones on top! Still lists all the others though, so may be confusing. Or use Value Filter, and Choose Top 10, then change it to Top What ever you want of something! That’s it, we did Question 2, now change the sheet label to Question 2, and make another Pivot Table for the next question!

17. Question 3: Overall most selling item - quantity not revenue This question is based around how MANY of an item sold, NOT how much they sold for. We are looking for the fast mover here. Quantity Purchased under Value Product Name under Labels Use either a More Sort option or the Top 10 Filter to find the highest one

18. Question 4 & 5 – Extra Credit For Extra Credit What was the highest selling month of the highest selling year. Involves TOTAL, Year and Month – then sort or Top 10 to find the highest Which month over all has the best sales, which has the worst. Two Questions, one chart – uses revenue, and month but NOT Year, this is looking for a seasonal trend. Sort by Sort Options to put in order, then pick the top and the bottom months!

19. Pivot Charts Creating Pivot Charts to Illustrate the Tables

20. Chart 1:Highest Revenue Year - All Item Sales Comparison - bar chart Start off with creating the pivot table to illustrate the items sold each year. Use TOTAL under Values Use Year under Label Use Product under Label and Year Determine the highest selling year, then FILTER out the other years. Now let’s make a chart! Click in the Pivot Table Choose Options – Pivot Chart Select the Chart you need – this time it is a bar chart

21. Let’s move this chart to a new Sheet Click on the Table Click Design On the far right there is Move Chart – Choose New Sheet, you can label it now if you want.

22. Chart Changes Change the Title from Total to something more meaningful, like Sales Comparison for 2006 Re-label the sheet tab to Chart 1 We’ve done the first chart!

23. Let's make another Chart 2 - Highest Selling Item, year by year - column chart

24. Chart 2 - Highest Selling Item, year by year - column chart The factors this time are: Total Year Product Let’s start with the Pivot Table Total under Sum Year under Labels Product under Label Use the Value Filter on Product to Only show the Top 1 Now let’s begin the chart Click on the pivot table Click Options Click Pivot Chart Choose a Column Chart this time Change the title and resize it and you are done!

25. The Completed Chart

26. Chart 3: Sales Trend of most (quantity not revenue) sold item - line chart Step 1 – Create the Pivot Table, we will use: Quantity Purchased Product Year (so we can show it over years) Month (so we can see more of a trend) Let’s setup the Pivot Table Quantity under Values Product, then Year, then Month under Row Label Use a Value Filter on Products – Top 10 reducing to top 1 for Products Insert a Line Chart Next

27. Complete the Chart Change the title Move and Resize the Chart

28. Chart 4 – LAST ONE!Division of all the items sold by the top customer - pie chart. This one has several parts First is to collect the items sold to EACH customer Second is to determine who the BEST customer was, and filter out everyone else. We will use TOTAL, Customers, and Products Total under Values Customers under Rows Products under Customers in Rows Use the More Sort Option to Bring the best customer to the top. Determine who the best customer was, then filter everyone else out

29. Report Filter Change the customer label To a Report Filter instead

30. Create a Pie Chart Click on the Pivot Table Click Options Click Pivot Chart and choose a PIE Chart Move the Chart to its own Page Click the New Chart Click Design – Move Options Change to Own Sheet Now Format the Chart Click the Chart Click Design Under Chart Layouts, pick the first one. Change the title, and you are done! You can change the fonts and colors if you wish to.

31. The Completed Chart!

32. Now put your results in to words Using Microsoft Word create a short report explaining your findings in a Question and Answer Format. Be sure to include a introduction stating what this is for, something like“Following is the results of the sales analysis of the 2004-2007 Sales Figures. The questions presented are listed below with the findings and charts.” Then List each question, and include a type answer. Don’t just paste in the table. For the Charts copy and paste your chart and put the question above each one. Points will be awarded for professional looking format, type it like you are setting this up for your boss. Send your report AND the spreadsheet in for grading.