![\"How](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Pivot-Tables-In-Excel.png\")
<\/p>\n<\/p>\n
Typical MS Excel spreadsheet data appears in form of a table which consists of multiple columns and rows. Such tables can have millions of data cells, finding any significant meaning in them can be a Sisyphean task. If your daily job requires you to analyze and summarize key business metrics from huge data sets, information overload is inevitable. <\/span><\/p>\n How do you work around that? The most effective way is to use Excel<\/strong>\u00a0<\/span>Pivot Tables<\/b> – a summarizing tool that can greatly simplify the process of refining your data and display results in a succinct and clear way.<\/span><\/p>\n Often overlooked, <\/span>Pivot Tables<\/span><\/a> are a powerful tool used to help you recognize patterns in spreadsheet data and extract their significance in the<\/span> form of a summarized table. Although most people think of<\/span> them as complicated and time-consuming, they\u2019re actually quite user-friendly. Everything is done by simple <\/span>drag and drop<\/span><\/i> maneuvers. The best part – no need for complicated formulas. <\/span><\/p>\n Besides MS Excel, Pivot Tables can be created in many other spreadsheet applications like LibreOffice and Google Docs. If you\u2019re more comfortable with web-based applications and prefer to perform your data analysis online, make sure to check out our guide on <\/span>how to work with Pivot Tables in Google Sheets<\/span><\/a>.<\/span><\/p>\n In order to perform complex Excel data analysis, you\u2019ll have to master various Pivot Table functionalities. We\u2019ve already shown you <\/span>how to create Excel Pivot Tables<\/span><\/a>. Now we\u2019ll show you how to exploit the basic features of Excel Pivot Tables and how to customize them in order to compile meaningful reports. <\/span><\/p>\n First thing\u2019s first – before we start explaining how to make the most out of Pivot Tables let\u2019s take a moment to get to know their structure. Every Excel Pivot Table has a specific design layout and fields.<\/span><\/p>\n Fields <\/b>are located<\/span> in the field list, they\u2019re basically all the column headers presented in the table (non-numerical values).<\/span><\/p>\n On the other hand, the Pivot Table <\/span>Layout<\/b>\u00a0is determined by four area options:<\/span><\/p>\n Note<\/b>: <\/span>We\u2019ve prepared a spreadsheet example (see image) that contains raw, unsorted sales data from a fictional company. <\/span><\/i><\/p>\n Pivot Tables are ideal<\/span> when you need to answer a specific question. For our example, let\u2019s say that our manager asked us this question: <\/span><\/p>\n \u201cWho is our top performing sales representative?\u201d<\/i><\/strong><\/p>\n How do we find this out? <\/span><\/p>\n We need to add and rearrange the spreadsheet data in order to find the salesmen that got the highest sales numbers. To accomplish this, we need to add corresponding fields to the appropriate layout areas:<\/span><\/p>\n Here’s how the table should look like: <\/span><\/p>\n Tip 1<\/b>: <\/span>Depending on which field is placed in which area, different outcomes will be obtained. For instance, to show the product quantity that each salesman sold, drag the Quantity field to the Value area.<\/span><\/i><\/p>\n Tip 2<\/b>: <\/span>To show sale values as a % of the total sales instead of amount just right click on a sum value within the Pivot Table and set <\/span><\/i>Show Values As>% of Grand Total<\/b>.<\/span><\/p>\n Now, for our example, we went with a small sample and we can clearly see that Joe is the top performing salesman, while Ralph<\/span> has<\/span> the lowest scores. <\/span><\/p>\n If you\u2019re working with a huge spreadsheet, it won’t be this straightforward. You\u2019ll need to sort it out a bit. This can be done with a few clicks:<\/span><\/p>\n Tip 3<\/b>: <\/span>If you want to know what data makes up a certain value, you just need to double-click on the desired value cell. A brand new sheet will open containing all the data that forms the specific value.<\/span><\/i><\/p>\n So what else can we do with our Pivot Table? Well, now that we sorted the data it might be a good idea to change our table appearance. If you don\u2019t like the preloaded design you can choose the one that appeals to you the most: <\/span><\/p>\n On top of that, we are going to format the data in the Pivot Table. After all, it should be clear that the values we\u2019re summing up are $ amounts and not just plain numbers. Here’s how to make the change: <\/span><\/p>\n That\u2019s more like it. Now\u00a0our Excel Pivot Table is concise, well organized, and we can present the findings to our manager.<\/span><\/p>\n Let\u2019s say that our manager was satisfied to learn that Joe is our top salesman. However, he needs more information and asks us an additional question: <\/span><\/p>\n \u201cWhich salesman had the most success in Kansas?\u201d <\/i><\/strong><\/p>\n To find this out, we need to fine-tune the result by adding the State field to the Filters area:<\/span><\/p>\n We can conclude that out of four salesmen that sold products in Kansas, Harry was the most successful one. Same rules apply to any other report you need to generate: just apply the appropriate filters to the Pivot Table. <\/span><\/p>\n Tip 4<\/b>: <\/span>You can filter Pivot Table data manually by clicking on the Row or Column Labels <\/span><\/i>arrow<\/b>. For instance, if we click on the Column Labels arrow and uncheck Mary and Ralph their values will be excluded from the table. <\/span><\/i><\/p>\n Next, let\u2019s say our manager informs us that he is exhausted of looking at all these numbers and tables every single day and asks us to answer him this:<\/span><\/p>\n \u201cIs there a way<\/i> to graphically represent the best performing salesman in the state of California?\u201d \u00a0<\/i>\u00a0\u00a0<\/strong><\/p>\n Of course, there is. It\u2019s always a good idea to turn those dull, countless rows and columns of numbers into an attention-grabbing visual report. A <\/span>Pivot Chart <\/b>allows us to do exactly that. <\/span><\/p>\n Actually, it\u2019s quite easy to enhance your report with a Pivot Chart, here\u2019s how:<\/span><\/p>\n Obviously, Bill is the best performing salesman in the state of California with the combined sales amount of $23,935. <\/span><\/p>\n Tip 5<\/b>: <\/span>Compile eye-catching reports in order to convey information to your superior or a potential customer in a more fun and colorful way. <\/span><\/i><\/p>\n If we want to go that extra mile and dazzle our superiors, we can use a <\/span>Slicer <\/b>and add a note of interactivity to our report. Slicers basically provide us with buttons that allow us to filter Pivot Table data. In addition to filtering, slicers also help us understand exactly what we\u2019re seeing in a filtered Pivot Table or Chart. <\/span><\/p>\n Here’s how to Add a Slicer :<\/span><\/p>\n Although Bill was the best performing salesman in California, it was Harry who generated the highest income when it comes to the sale of product C. <\/span><\/p>\n Tip 6<\/b>: <\/span>If the plain Slicer is not a good fit for your report, go to <\/span><\/i>Slicer>Options>Slicer Styles<\/b> and select the style you prefer. <\/span><\/i><\/p>\n These are only<\/span> some of the basic things you can do to start working with Pivot Tables in Microsoft Excel. There are far too many to list and we can go on for days about each of them. Depending on your needs, this guide might be<\/span> all you need. \u00a0Nevertheless, if you want to explore the full potential<\/span> of<\/span> \u00a0Pivot Tables, you might want to get a book that specializes in the subject.<\/span><\/p>\n Are you using Pivot Tables to build your business reports? Feel free to share your favorite Pivot Table feature in the comment section below. <\/span><\/p>\n Also, if you\u2019d like to read articles like this on a weekly basis make sure to bookmark our page and follow our social media profiles on<\/span> Facebook<\/span><\/a>, <\/span>Twitter<\/span><\/a>, <\/span>Google+<\/span><\/a>. New post every Monday! <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"Pivot Tables – The Basics<\/strong><\/h2>\n
Pivot Table Design<\/strong><\/h3>\n
\n
![\"Pivot](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Pivot-Table-Design-1024x501.png\")
<\/p>\nCustomizing Excel Pivot Tables<\/strong><\/h2>\n
![\"Unsorted](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Unsorted-Excel-Data.png\")
<\/p>\nRearranging Data<\/strong><\/h3>\n
\n
![\"Top](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Total-Sales-Pivot-Table.png\")
<\/p>\nSorting Data<\/span><\/h3>\n
\n
![\"Sorted](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Sorted-Pivot-Table.png\")
<\/p>\nFormatting Table and Data<\/strong><\/h3>\n
\n
![\"Formatted](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Pivot-Table-Formatting.png\")
<\/p>\n\n
![\"Formatted](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Table-Data-Formatting.png\")
<\/p>\nFiltering Data<\/strong><\/h3>\n
\n
![\"Pivot](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Filtered-Pivot-Table.png\")
<\/p>\nPivot Charts & Slicers<\/strong><\/h2>\n
\n
![\"Best](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Pivot-Table-Chart.png\")
<\/p>\n\n
![\"Top](\"https:\/\/www.investintech.com\/resources\/blog\/wp-content\/uploads\/2018\/01\/Pivot-Table-Slicer.png\")
<\/p>\nConclusion<\/strong><\/h2>\n