If you work in finance or accounting, you already know that most jobs require intermediate to advanced Excel skills. Some of the most common Excel functions in these roles are PivotTable and VLOOKUP
This article describes the basics of a PivotTable. Click here if you want to know more about VLOOKUP Also don’t forget to check out the VLOOKUP alternative, a function called INDEX SEARCH.
Create a pivot table in Excel
What is a pivot table? In simple terms, a PivotTable is one of the built-in functions that you can use to quickly create a PivotTable from a large dataset in Excel.
Imagine you have an online store that sells different mobile phone models with sales data as shown below. Download a sample spreadsheet.
After two months of doing business, you are wondering if you sold more product in the first or second month. You would also like to know if you have sold more Apple or Samsung products. Finally, you would like to know the total sales for each month.
A PivotTable is the perfect candidate for a quick summary without having to use any Excel formulas like quantity or amount. The answers to the above questions can be found in seconds if you learn to work with a pivot table.
– /
Here are the step-by-step instructions on how to create a pivot table.
STEP 1. Create a pivot table by clicking any of the cells in the data table, then go to the top tab in Excel and select Insert Pivot Table.
STEP 2 – A selection window will appear and it should automatically detect the full range of the table based on the cell you clicked on earlier. In this example, we are adding our pivot table to a new sheet to make it easier to see.
STEP 3 – Click on the blank pivot table created in the new sheet. You will notice that the PivotTable Fields will appear on the right side of your spreadsheet. Here you drag with your mouse to create a quick summary.
STEP 4. To find out the number of mobile phones sold each month, drag “Month of Sold” to the ROWS area and “Brand” to the VALUES area.
You will notice that the pivot table will automatically update to show the number of rows for each month, which indicates the number of mobile phone sales for each month.
If you drag a Model or Warehouse Location to VALUES instead of Brand, the same numbers are returned for each month as it simply refers to the total number of rows in each Sale Month. Looks like we sold more phones in January than in February.
STEP 5. To find out if more Apple or Samsung products have sold in your store, you can reuse the same PivotTable without having to create a new one.
To do this, you can deselect the selection that you no longer need (by dragging the data field from the area to anywhere in the spreadsheet).
Then replace it with Brand in the ROWS field.
The pivot table will immediately be updated to show the total number of rows grouped by brand (i.e., the total number of products sold by brand to date). You’ve actually sold more Apple products than Samsung.
STEP 5. Finally, to see how much you got from sales each month, we’ll reuse the same PivotTable.
Clear the Brand field and drag Month of Sale back to the LINE area. Since we specifically want to know the total sales, clear the VALUES area and drag and drop Sales Price as shown below.
Because the Sales Price column in the original dataset is numeric, the PivotTable automatically sums up the sales price instead of counting the number of sales price rows. Voila, you got $ 7,550 in January and $ 7,100 in February.
Try playing around and dragging the fields as shown below and see what the result of the pivot table is.
This is only a small part of what a PivotTable can do, but it will give you a good basic understanding to get started. Happy research!
Tips. If the PivotTable Fields pane to the right of the spreadsheet is not available, try hovering your mouse over the PivotTable, right-click and select Show Field List. This should bring him back to life. Enjoy!
–