But what if you wanted to find the percentage these product sales contributed to all company sales, rather than just those products' sales totals? The table would automatically give you three totals at the bottom of each column - having added up each product's quarterly sales. Let's say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a pivot table. But that's not the only figure you can automatically produce. Pivot tables naturally show the totals of each row or column when you create them. Showing product sales as percentages of total sales. Using a pivot table, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 - and calculate their respective sums - in less than a minute. Manually sorting through them all could take a lifetime. Now, imagine your monthly sales worksheet has thousands and thousands of rows. You could then do the same for product 2, and product 3 until you have totals for all of them. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. Say you have a worksheet that contains monthly sales data for three different products - product 1, product 2, and product 3 - and you want to figure out which of the three has been bringing in the most bucks.
Comparing sales totals of different products. Here are seven hypothetical scenarios where a pivot table could be a solution: 1. They can be used to better understand, display, and analyze numerical data in detail - and can help identify and answer unanticipated questions surrounding it. The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. This is one of those technologies that are much easier to understand once you've seen it in action. Microsoft Excel Version 2003 Classic Pivot Table Display view.If you're still feeling a bit confused about what pivot tables actually do, don't worry. This means you don’t have to go to the data source consistently to add those extra few rows of data or new columns of data. From the Display tab in the dialog box, you will need to Un-tick or Tick as you like “ Classic Pivot Table layout” which enables you to drag and drop fields into the Column and Row items area.Ĭreating functions to incorporate dynamic data is something you would have done in the past but now in Microsoft Excel Pivot Tables, there is a feature in Microsoft Excel versions 2010, 20 which now has “ Format as Table” allowing your data to increase as you add more records – “rows of data” and add new fields – “columns of data”. Remember in Microsoft Excel versions 20 you will find it under the tab call Options and then again you are looking for a command button called Options. Look for the options command usually found to the left of the Ribbon. Mac’s also have the Format as Table feature.ĭo you still like your classic view in Pivot Tables in Microsoft Excel, or do you prefer the modern view but can’t get out of the classic view? In version 2013, 2016 you will find in the Microsoft Excel Ribbon that it is now called Analyze instead of Options. If you have decided to use Format as Table, the table name will display here. To start a Microsoft Excel Pivot Table ensure your cursor is inside the data set, so long as your data has no blank rows or blank columns you should have no trouble going to the Insert Tab and Selecting, Insert Pivot Table as it will automatically pick up the block of data. Whenever you can use “ Format as Table” for your data format this will have great benefits and save time. This is a definite NO NO in Microsoft Excel Pivot Tables. The correct format for your Microsoft Excel Pivot Table data is that the top header row must contain the field names and the consecutive rows below must contain the records of data.
When creating a Pivot Table your data must be laid out correctly otherwise you will get an error message pop up. Microsoft Excel’s Pivot tables are used to summarise and group data by sum, average or Count.
How To Manage Excel Pivot Table Data Source