How do I calculate a pivot table?

Answer 1

Creating a Pivot Table is much easier to construct than people tend to believe. It is very helpful when you have a large amount of data and you need to see the information and numbers in different formats.

Let’s assume we have an e-commerce vendor who has customers in cities throughout the country and may want to know her revenue or quantity sold by city or item purchased. To begin the Pivot Table calculation, open up a new worksheet, on the first tab import the data or enter the data manually. You must have a title for each of the columns. A small sample of the data sheet cells may look as follows:

CUSTOMER CITY ITEM PURCHASED QUANTITY PRICE TOTAL

A Station City T-Shirts 25 15.00 375.00

B New City Gym Pants 5 22.00 110.00

C Station City Jackets 10 40.00 400.00

1)The above sample of the data sheet is where the pivot table will pull the information.

2) Put your cursor in the top left cell of the data sheet. Make sure you are capturing the column headers and drag the cursor down to the bottom right hand corner of the data, highlighting the whole table.

3) Go to the Excel ribbon above and click on the "Insert" tab.

4) Then click on "Pivot Table" on your left.

5) A function box will appear. You will see in the box the table range you highlighted.

6) For simplicity, click on "New Worksheet," and click "OK."

7) A new tab will appear at the bottom.

8) The new Pivot Table Field Box will allow you to sort the data based on the box you click.

9) If the vendor wants to know how revenue was received by city by item she will check off: City, Item Purchased, and Total. The pivot table will show the result by column and row in their respective cells as follows:

Row Labels Sum of TOTAL

Appleton 1,225

Jackets 400

T-Shirts 825

New City 1,989

Gym Pants 264

Jackets 1,200

T-Shirts 525

Newtown 400

Jackets 400

Newtown Square 2,205

Gym Pants 330

Jackets 600

T-Shirts 1,275

Station City 2,854

Gym Pants 704

Jackets 800

T-Shirts 1,350

Grand Total 8,673

10) If the vendor wants to know how the items were received by city by item she will check off: City, Item Purchased, and Total. The pivot table will show the result by column and row in their respective cells as follows:

Row Labels Sum of QUANTITY

Appleton 65

Jackets 10

T-Shirts 55

New City 77

Gym Pants 12

Jackets 30

T-Shirts 35

Newtown 10

Jackets 10

Newtown Square 115

Gym Pants 15

Jackets 15

T-Shirts 85

Station City 142

Gym Pants 32

Jackets 20

T-Shirts 90

Grand Total 409

I hope this was helpful. If you have any questions, please message me I will try to get back to you within 24 hours.