{"id":179115,"date":"2021-02-15T13:24:44","date_gmt":"2021-02-15T10:24:44","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-pivot-tables-to-analyze-excel-data\/"},"modified":"2021-02-15T13:24:44","modified_gmt":"2021-02-15T10:24:44","slug":"how-to-use-pivot-tables-to-analyze-excel-data","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-pivot-tables-to-analyze-excel-data\/","title":{"rendered":"#How to Use Pivot Tables to Analyze Excel Data"},"content":{"rendered":"

“#How to Use Pivot Tables to Analyze Excel Data”<\/strong><\/p>\n

\n\"A<\/p>\n

Pivot Tables are both incredibly simple and increasingly complex as you learn to master them. They\u2019re great at sorting data and making it easier to understand, and even a complete Excel novice can find value in using them.<\/p>\n

We\u2019ll walk you through getting started with Pivot Tables in a Microsoft Excel spreadsheet.<\/p>\n

First, we\u2019ll label the top row so that we can better organize our data once we app<\/a>ly the PivotTables in a later step.<\/p>\n

\"add<\/p>\n

Before we continue, this is a good opportunity to get rid of any blank rows in your workbook. PivotTables work with blank cells, but they can\u2019t quite understand how to proceed with a blank row. To delete, just highlight the row, right-click, choose \u201cDelete,\u201d then \u201cShift cells up\u201d to combine the two sections.<\/p>\n

\"click<\/p>\n

Click inside any cell in the data set. On the \u201cInsert\u201d tab, click the \u201cPivotTable\u201d button.<\/p>\n

\"click<\/p>\n

When the dialogue box appears, click \u201cOK.\u201d You can modify the settings within the Create PivotTable dialogue, but it\u2019s usually unnecessary.<\/p>\n

\"pivot<\/p>\n

We have a lot of options here. The simplest of these is just grouping our products by category, with a total of all purchases at the bottom. To do this, we\u2019ll just click next to each box in the \u201cPivotTable Fields\u201d section.<\/p>\n

\"click<\/p>\n

To make changes to the PivotTable, just click any cell inside the dataset to open the \u201cPivotTable Fields\u201d sidebar again.<\/p>\n

\"click<\/p>\n

Once open, we\u2019re going to clean up the data a bit. In our example, we don\u2019t need our Product ID to be a sum, so we\u2019ll move that from the \u201cValues\u201d field at the bottom to the \u201cFilters\u201d section instead. Just click and drag it into a new field and feel free to experiment here to find the format that works best for you.<\/p>\n

\"move<\/p>\n

To view a specific Product ID, just click the arrow next to \u201cAll\u201d in the heading.<\/p>\n

\"view<\/p>\n

This dropdown is a sortable menu that enables you to view each Product ID on its own, or in combination with any other Product ID. To pick one product, just click it and then click \u201cOK,\u2019 or check the \u201cSelect Multiple Items\u201d option to choose more than one Product ID.<\/p>\n

\"click<\/p>\n

This is better, but still not ideal. Let\u2019s try dragging Product ID to the \u201cRows\u201d field instead.<\/p>\n

\"\"<\/p>\n

We\u2019re getting closer. Now the Product ID appears closer to the product, making it a bit easier to understand. But it\u2019s still not perfect. Instead of placing the Product ID below the product, let\u2019s drag Product ID above Item inside the \u201cRows\u201d field.<\/p>\n

\"move<\/p>\n

This looks much more usable, but perhaps we want a different view of the data. For that, we\u2019re going to move Category from the \u201cRows\u201d field to the \u201cColumns\u201d field for a different look.<\/p>\n

\"move<\/p>\n

We\u2019re not selling a lot of dinner rolls, so we\u2019ve decided to discontinue them and remove the Product ID from our report. To do that, we\u2019ll click the arrow next to \u201cRow Labels\u201d to open a dropdown menu.<\/p>\n

\"row<\/p>\n

From the list of options, uncheck \u201c45\u201d which is the Product ID for dinner rolls. Unchecking this box and clicking \u201cOK\u201d will remove the product from the report.<\/p>\n

\"uncheck<\/p>\n

As you can see, there are a number of options to play with. How you display your data is really up to you, but with PivotTables, there\u2019s really no shortage of options.<\/p>\n<\/div>\n