M2-Unit1-Pivot Tables-Graphs

.pdf

School

University of Alabama *

*We aren’t endorsed by this school

Course

490

Subject

Business

Date

Apr 3, 2024

Type

pdf

Pages

18

Uploaded by danielw3lch

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.
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