How to Create a Simple Pivot Table in Excel

Also, we answer what is a pivot table?

If you are in the field of finance or accounting, you already know that most of the job opportunities require intermediate or advanced Excel skills. Some of the most common Excel functions in these roles arePivot TableandVLOOKUP.

This article will outline the basics of a pivot table. Go here if you want to learn more aboutVLOOKUP. Also, be sure to check out the alternative to VLOOKUP, a function calledINDEX-MATCH.

Create a Pivot Table in Excel

What is pivot table? Simply put, a pivot table is one of the built-in functions that you can use to quickly create a summary table based on a large set of data in Excel.

Imagine if you own an online shop that sells different models of mobile phones with sales data as shown below.Download sample spreadsheet.

After doing business for about two months, you are curious if you have sold more product in the first month or the second. You would also like to know whether you have sold more Apple products or Samsung products. Lastly, you would like to know the total sales received in each month.

The pivot table is the perfect candidate for getting a quick summary without needing to use any Excel formula, such as count or sum. The answers to the above questions can be produced in a matter of seconds once you know how to work with a pivot table.

Here are step-by-step instructions for creating a pivot table.

STEP 1– Create a pivot table by clicking in any of the cells within the data table, then go to the top tab in Excel and selectInsert->Pivot Table.

STEP 2– A selection window will appear and it should automatically determine the full range of the table based on the cell where you clicked earlier. For this example, we’re adding our pivot table to a new worksheet, so it’ll be easier to see.

STEP 3– Click on the blank pivot table created in the new sheet. You will notice aPivot Table Fieldswill appear on the right side of your spreadsheet. This is where you drag-and-drop to create the quick summary.

STEP 4– To know the number of mobile phone sold each month, dragMonth Soldto theROWSarea andBrandtoVALUESarea.

You will notice that thePivot Tablewill be automatically updated to show the number of rows for each month, which indicates number of mobile phone sales for each month.

If you dragModelorWarehouse LocationtoVALUESinstead ofBrand, it will produce the same numbers for each months as it is simply referring to the total count of rows in eachMonth Sold.Looks like we sold more phones inJANcompared toFEB.

STEP 5– To know whether more Apple or Samsung products were sold in your store, you can reuse the same pivot table without needing to create a new one.

To do this, you can clear the selection that you no longer need (by dragging the data field out of theAreaand dropping it anywhere on the spreadsheet).

Next replace it withBrandin theROWSbox.

The pivot table will be instantly be updated to show total number of rows, grouped byBrand(i.e. Total number of product sold byBrandto date). You actually sold moreAppleproduct compared toSamsung.

STEP 5– Lastly, to know how much you have received in sales in each of the months, we will be reusing the samePivot Table.

Clear out theBrandfield and dragMonth Soldback to theROWSarea. As we specifically want to know the total sales, clear theVALUESarea and drag inSale Priceas shown below.

As theSale Pricecolumn in the original dataset is in number format, the pivot table will automatically sum up theSale Price, instead of countingthe number ofSale Pricerows. Voila, you have received $7,550 inJANand $7,100 inFEB.

Try to play around and drag the fields as per below and see what is the outcome of the pivot table.

This is just scratching the surface of what pivot table can do, but it will give you a good basic understanding to start with. Happy exploring!

Tips: If thePivot Table Fieldspane on the right of the spreadsheet goes missing, try to hover your mouse over the pivot table, right click and chooseShow Field List. That should bring it back up. Enjoy!

Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications.Read Aseem’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

How to Create a Simple Pivot Table in Excel#

Create a Pivot Table in Excel#

How to Create a Simple Pivot Table in Excel

Create a Pivot Table in Excel