Essential Guide to Mastering Excel Pivot Tables and Reports

FI 389 Pivot Table A pivot table is a data summarization tool found in Microsoft Excel. PivotTables, as the name suggests, can be quickly rearrange by changing the orientation of the pivot table fields- to give you a different view of the table. In this tutorial, we will learn how to start with a basic data source and create PivotTable reports that answer common questions about our data. These techniques can be used for just about any type of source data. The following example shows reports you could use to compare your salespeople: Example 1: Comparing your salespeople Start with source data that contains information about your salespeople. Here is some of the source data records used for the reports in this section. Congratulations, you landed your first job as the Lead Sales Analyst at Nickie’s Sports Inc. On your first day at work your boss, Sheldon (whose constant demands, it turns out, was the reason the last analysts resigned) approaches you and requests that you generate a report from data he has added to your shared drive. His request is presented in Email 1 below:

Email 1 1. What is the sum of order amount for each salesperson? 2. What is the average order amount for each salesperson? 3. What is the minimum order amount for each salesperson? 4. What is the maximum order amount for each salesperson? 5. What is the standard deviation order amount for each salesperson? Answer to Email 1 We can use Pivot Table to solve this problem. To set up your PivotTable: 1. Click a cell in the source data. 2. On the Insert menu, click Pivot Table 3. Follow the instructions in steps of the wizard.

4. Clicking OK will result in the following: To answer questions (a) through (e) on EMAIL1 we follow the instructions below: EMAIL1 a) What is the sum of order amount for each salesperson?  We drag the Salesperson field into the Row area, and then drag the Sales field into the Values area.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

We then answer question (b) by dragging the sales field into the value area again as shown below Next use the mouse to click any cell on the “Sum of Sales2” column. The n right click the mouse and see the following output:

Repeat the same process to answer questions (c) through (e) of Email1. Our final output should look like this: Aaah, all in a day’s work. And the good part is, it’s only 12:20 PM3:03 PM. An early lunch is definitely in order. Chinese? Thai? Chick- fi… Before you could finish the thought, here comes a popup of a new email from Sheldon.

Email 2 a) What is the sum of order amount for salespeople in a specific country? b) What is the average order amount for salespeople in a specific country? c) What is the minimum order amount for salespeople in a specific country? d) What is the maximum order amount for salespeople in a specific country? e) What is the standard deviation order amount for salespeople in a specific country? Answer to Email 2 a) What are the order amounts for salespeople in a specific country? We drag the Country field and Salesperson field to the Row Label area. We also drag the Sales field to Value area as shown below. Alternatively we can drag the Country field into the Page area. You can then switch between "pages" to view the data for each country. We repeat the same approach in Email 1 for Email2 (b) through (e) to obtain the following final output.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Easy enough! Where were we? Yes, Lunch… *DING* Yes, you guessed it, another email from Sheldon of course!

EMAIL 3 a) How are salespeople ranked by order amounts? b) Also, draw a chart to show the order amount of each salesperson. c) Who are the top 10 salespeople? d) Who are the top 2 salespeople e) Who are the salespeople with sales between $20,000 and $70,000? f) Which salespeople have sales greater than $50,000? Answer to Email 3 a) How are salespeople ranked by order amounts? To sort the salespeople, click the Salesperson field arrow as shown below, click More Sort Options , and then click Descending , and then select Sum of Sales and then choose OK. The final output is below: To show the sales of each salesperson on a chart, we click on any cell on the table and then click on Analyze and then click on PivotChart.

Email 3 (c) through (f) To view the top items in a field, click the “Row Labels” field, click Value Filters, and then click Top 10. Under Top 10 Filter , click On , click Top in the Show box, and then enter 2 and choose OK .

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Certainly, this has to be all for the day. *Ding*…

EMAIL 4 a) How did salespeople perform in a specific quarter? b) How did salespeople perform in a specific month? c) How did salespeople perform in a specific month of the year? d) How did salespeople perform in a specific quarter of the year? Answer to Email 4 Drag the Salesperson and Date fields into the Row area. Note that Excel automatically tries to group the data. You can change the grouping by selecting Group Field under the Analyze tab on the ribbon. Make sure that only Quarters is selected. We obtain the following output.

Maybe I need to stop sending off these files so quickly! Sheldon is a real…. *DING*

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

EMAIL 5 a) How much should each salesperson receive in bonus given that the company has a bonus package for salespeople as follows: Sales Bonus Sales >= 4,000 0.15*Profit Sales < 4,000 0.10*Profit a) What bonus amount should each salesperson receive? All salespeople receive a 10% bonus, but you'd like to award a 15% bonus if they exceed $4,000 in orders per quarter. To create a formula that uses PivotTable data, you can use a calculated field. Click the report, click PivotTable on the PivotTable toolbar, point to Calculations, then Fields, Items, & Sets , and then click Calculated Field . In the Name box , type Bonus Amount . In the Formula box, enter the formula you want. If you want to use data from an existing field in your formula, click the field in the Fields box, and then click Insert Field .

… *DING*

EMAIL 6 a) What’s the percentage of profits by sales for each sale person (format to 2 dps) ? b) What’s the percentage of profit by total sales for each sale person (format to 2 dps) ? c) What’s the rank of each sale person based on his profits (smallest to largest) ? d) What was the dollar amount in profits for each country in each quarter? e) What is grand total of sales both by quarter and by country? Create the following table Create the Profits to sales column by going to Pivot Table Options – Fields Items and Sets – Calculated Fields For the Profits to Total and rank: Insert profits into the table two more times by dragging the profits label into the values box on the lower right hand corner of the pivot table options. Insides the table right click on the profits column and select “Show Values as” This will give you the option to select

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

“% of Column Total” and “Rank from Smallest to Largest”. Alternatively, you can click the arrow next to the column name in the values box, “select value field settings” – select the “Show Values as” Tab then from the drop down menu select the calculation you want. e) Dollar Amount of Sales The first step is to create the following table, using the following options. Create filters such that we can break down the sales by year and by product. Next, add the year and product filters

To calculate the grand total of sales by both country and quarter make sure that you have grand totals on for both columns and rows. To do this, click the pivot table so the “Pivot Table Tools” tab is activated in the top ribbon. Then under design, go to the Grand Totals tab on the left side of the bar. Once you click this you will be able to select grand totals for columns and rows. Examine the percentage that these sales in each quarter and country To show the values inside of the table in terms of percentage of total sales click the arrow to the right of Sum of Profits in the Values window. Click “value field settings” then go to “Show Values As”. In the drop down tab select % of Grand Total.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

M2-Unit1-Pivot Tables-Graphs

Related Documents