How To Create Pivot Table In Excel For Mac

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. 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.

Learn how to summarize, sort, and analyze your data in using one of the most overlooked features in Microsoft Excel for Mac 2016: pivot tables.

Not only that, but they also allow you to quickly change how your data is summarized with almost no effort at all. This lesson will show you how to create a simple PivotTable in Excel to summarize a set of daily sales data for a team of several sales people. This lesson shows you how to create a PivotTable in Excel 2016 (both Mac and Windows versions), Excel 2013, Excel 2010 and Excel 2011 (Mac). What you'll learn in this lesson In this tutorial, you'll learn how to: • Set up your data in Excel so it is in a format that you can use for a PivotTable. • Create a PivotTable with that data • Change the PivotTable report to reflect different views on the same data. 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.

The first few rows are shown below: In fact, this spreadsheet extends down for 688 rows of sales data, for all of January and February (). Antivirus for apple mac. 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. 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. Getting started with PivotTables - make sure your data is ready There are some important rules you need to follow if you want to create a PivotTable from your data: • Your data should be organized in columns with headings. These headings will be used when you create the PivotTable, and things will get very confusing without headings. • Make sure there are no empty columns or rows in your data. Excel is good at sensing the start and end of a data table by looking for empty rows and columns.

If it finds an empty row or column, it assumes your data stops at that point. • 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). This automatically selects the whole table. If any data remains unselected, you need to check for empty columns or rows within the data table.

• Note that empty cells within your table are OK. What isn't OK is a whole row or a whole column of empty cells. • Consistent data in all cells. • If you have a date column, make sure all the values in that column are dates (or blank). • If you have a quantity column, make sure all the values are numbers (or blank) and not words. At this point, if everything is looking OK, you're ready to move on to the next step.

Create a blank PivotTable To start your PivotTable, follow these steps: • Click on a cell in the data table. Any cell will do, provided your data meets the rules outlined above. In fact, at this point it's all or nothing - select the whole table or just one cell in the table.

Don't select a few cells, because Excel may think you are trying to create a PivotTable from just those cells. • Click on the Insert menu and click the PivotTable button: • ​This button looks like this in Excel 2007 and 2010 for Windows: • The button looks like this in Excel 2013 and 2016 (both Windows and Mac versions of Excel 2016). Note the new feature to see Recommended PivotTables.

This is the fastest way to create a PivotTable. However, we'll choose the PivotTable button for now so you can learn how to build a PivotTable from scratch: • If you're using Excel 2011 for Mac, you can choose PivotTable. From the Data menu (although we think Excel 2016 for Mac is well worth the upgrade.

It's faster, and is almost identical to Excel 2016 for Windows). • The following dialog box will appear. This is the same on all versions of Excel from Excel 2007 right through to Excel 2016: • Note that the Table/Range value will automatically reflect the data in your table (you can click in the field to change the Table/Range value if Excel guessed wrong).