By default, Excel will suggest a New Worksheet, which I think is the best choice unless you already know you want it on an existing worksheet. Alternatively, you can choose an external data source such as a database (this is an advanced topic and is outside the scope of this lesson).Īlso notice that you can choose where the new PivotTable should go. You can click the icon on the right side of this field to change the Table/Range value if Excel guessed wrong. Note that the Table/Range value will automatically reflect the data in your table. You'll use this to specify where the data for your pivot table lives (in an Excel table or range, or an external data source, such as a database) and where the pivot table you are creating will be placed.
LEARN HOW TO USE PIVOT TABLES IN EXCEL HOW TO
In this lesson, we'll choose the PivotTable button so you can learn how to build a PivotTable from scratch: This is the fastest way to create a PivotTable, although you may still need to modify it to match your requirements. Note the feature to see Recommended PivotTables. Don't select a few cells, because Excel may think you are trying to create a PivotTable from just those cells.Ĭlick on the Insert menu and click the PivotTable button, as shown below. In fact, at this point it's all or nothing - select the whole table or just one cell in the table. Any cell will do, provided your data meets the rules outlined above. To start your PivotTable, follow these steps:Ĭlick on a cell in the data table. If you have a quantity column, make sure all the values are numbers (or blank) and not words.Īt this point, if everything is looking OK, you're ready to move on to the next step.
![learn how to use pivot tables in excel learn how to use pivot tables in excel](https://www.excel-easy.com/smi/data-analysis/pivot-tables-2.png)
If you have a date column, make sure all the values in that column are dates (or blank).What isn't OK is a whole row or a whole column of empty cells. Note that empty cells within your table are OK.If any data remains unselected, you need to check for empty columns or rows within the data table. This automatically selects the whole table. A quick way to check if your data is ready to be used in a PivotTableis to click a single cell anywhere in the data table, then press SHIFT+* (or CTRL+SHIFT+8).If it finds an empty row or column, it assumes your data stops at that point. Excel is good at sensing the start and end of a data table by looking for empty rows and columns. Make sure there are no empty columns or rows in your data. These headings will be used when you create the PivotTable, and things will get very confusing without headings.
![learn how to use pivot tables in excel learn how to use pivot tables in excel](https://1.bp.blogspot.com/-vR_ed2otWWI/X28gpcxLSoI/AAAAAAAAANg/iTnhIj6ZTgMBV9QXRGk07zAt5eADjnM9gCLcBGAsYHQ/s569/pivot_in_excel_1.jpg)
Your data should be organized in columns with headings. There are some important rules you need to follow if you want to create a PivotTable from your data: Getting started with PivotTables - make sure your data is ready That's where PivotTables are by far the best solution - you'll be able to convert this data in under a minute, and be able to get different summaries with a few clicks of the mouse. So while you might look at the data in the table above and think "I could summarize that quickly by hand or with a few clever formulas", the likelihood is that it would all get too much - and would certainly take too long to do by hand.
![learn how to use pivot tables in excel learn how to use pivot tables in excel](https://i.pinimg.com/originals/ec/f7/4b/ecf74b024a8010653039ba5236d55564.png)
LEARN HOW TO USE PIVOT TABLES IN EXCEL DOWNLOAD
In fact, this spreadsheet extends down for 688 rows of sales data, for all of January and February ( you can download a copy of the spreadsheet here). The data we'll work with in this example is an Excel table that has two months of daily sales data for a team of four sales people, broken down by product.
![learn how to use pivot tables in excel learn how to use pivot tables in excel](https://deskbright-media.s3.amazonaws.com/static/cms/images/articles/excel/what-is-a-pivot-table/image1.jpg)
If you are finding yourself writing lots of formulas to summarize data in Excel (using functions such as SUMIF and COUNTIF) then PivotTables can save you a lot of time and work and give you insights into your data that are otherwise too hard to discover. Excel's PivotTable feature is an incredibly powerful tool that makes it easy to tabulate and summarize data in your spreadsheets, particularly if your data changes a lot.