Create a PivotTable
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
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
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”
5. Your Pivot Table will be populated with the total sales for each product name, as shown in below.
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|