Create a PivotTable

Basic Description

Excel Pivot Tables are tables, that are produced by Excel, to summaries large amounts of data in a spreadsheet. A further feature of pivot tables is the ability to quickly show the data that makes up any part of the table. For the time being, don’t worry about how pivot tables are produced. This section of the tutorial simply aims to answer the question “What is a Pivot Table in Excel?”. We will learn how to create the tables in the next few slides of this tutorial.

 

Pivot Table Creation

We will first create a very simple pivot table, which shows the total sales for each of the eight sales product in the below spreadsheet.

Date Time Product Name Customer ID Customer Name Product Price
8/1/2014 0:00 MS Excel 1386 Mahmoad 154
8/1/2014 0:00 MS Excel 1400 Md. Liton 180
8/1/2014 0:00 MS Power Point 1109 Md.Atiqur 128
8/1/2014 0:00 MS Excel 1163 Md. Hafiz 65
8/1/2014 0:00 MS One Note 565 Rajib Roy 235
8/1/2014 0:00 MS Excel 1394 Rakibul 47
8/1/2014 0:00 MS Excel 1392 Aashique Rahman 25
8/1/2014 0:00 MS Excel 1080 Md. Moshiyel 132
8/1/2014 0:00 MS Excel 1183 Md. Khairul 98
8/1/2014 0:00 MS Excel 1150 Jahidul 123
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

 

1. Select any cell within the data range or select the entire data range to be used in your Pivot Table.

Note: If you select a single cell in the data range, Excel will automatically identify, and select the whole data range for your Pivot Table. In order for Excel to successfully do this, the following must be satisfied:

– Each column in the data range has a unique header
– The data does not contain any blank rows

2. Click on the Pivot Table button, which is generally located on the left, within the ‘Insert’ tab

Pivot Table insert tab

3. You will be presented with the window entitled ‘Create PivotTable’ (shown on the below). Make sure that the selected range is the range that you want to use for your Pivot Table. There is also an option asking where you want the Pivot Table to be placed. If you are not sure, select the option ‘New worksheet’. Click OK

pivot table 2

4. You will now be presented with an empty Pivot Table, and a ‘Pivot Table Field List’, which contains several data fields. Note that these are the headers from your initial data spreadsheet.

Within this ‘Pivot Table Field List’:

  • Drag the ‘Product Name.’ field into the area of the ‘Pivot Table Field List’ marked ‘Row Labels’.
  • Drag the ‘Product Price’ field into the area of the ‘Pivot Table Field List’ marked ‘Σ Values’.
  • Check: Make sure that the value in the ‘Σ Values’ section of the pivot table reads “Sum of Amount” and not “Count of Amount”

Pivot table fields

5. Your Pivot Table will be populated with the total sales for each product name, as shown in below.

pivot table

If you want the sales to be displayed as a currency, this is done by formatting the cells containing these values.

To do this:

  • Select the column containing the cells to be formatted.
  • Right click on this column with the mouse and select the option Format Cells …
  • A window will pop up. Ensure the Number tab of this window is selected
  • From the list of data types, select Currency, and then, from the options that appear on the right, select the currency type and the number of decimal places that you want to display.
  • Click OK

Your final Pivot Table will be as shown on the below table:

Product Name   Sum of Product Price
MS Excel $                   6,158,443
MS Info Path $                       248,418
MS Lync $                       260,202
MS One Note $                       684,452
MS Outlook $                       284,548
MS Power Point $                    1,441,725
MS Publisher $                       395,435
MS Word $                       397,127
Grand Total $                   9,870,350

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment