Pivot tables are incredibly simple and increasingly complex as you learn to master them. They are excellent for ordering data and making it easy to understand, and even a complete beginner to Excel can find value in its use.
We'll guide you to get started with pivot tables in a Microsoft Excel spreadsheet.
First, we'll label the top row so we can better organize our data once we apply the pivot tables in a later step.
before continuing, this is a good possibility to get rid of blank rows from your workbook. Pivot tables work with blank cells, but they can't understand how to proceed with a blank row. To remove, just highlight the row, right click, choose “Remove”, subsequently “Shift cells up” to combine the two sections.
Click inside any cell in the dataset. In the tab “Insert”, Click the button “Dynamic table”.
When the dialog box appears, click on “To accept”. You can modify the settings within the Create PivotTable dialog box, but regularly it is not necessary.
We have many options here. The simplest of these is to simply group our products by category, with a total of all purchases at the bottom. To do this, we will simply click next to each box in the section “PivotTable Fields”.
To make changes to the pivot table, just click on any cell within the dataset to open the sidebar “PivotTable Fields” again.
Once opened, we will clean the data a bit. In our example, we don't need our product ID to be a sum, so we will move it from the field “Values” at the bottom of the section “Filters”. Just click and drag it to a new field and feel free to experiment here to find the format that best suits your needs..
To view a specific product ID, just click the arrow next to “Everyone” in the header.
This drop-down menu is a sortable menu that enables you to view each Product ID on its own or in combination with any other Product ID.. To select a product, just click on it and then click “To accept”, or check the option “choose multiple posts” to select more than one product ID.
This is better, but still not ideal. Instead, let's try dragging product ID to the field “Rows”.
We are getting closer. Now, Product ID appears closer to the product, which facilitates its understanding. But still not perfect. Instead of putting the Product ID under the product, let's drag the product ID on top of the post into the field “Rows”.
This seems much more useful, but maybe we want a different view of the data. For that, we are going to move Category of the field “Rows” to field “Columns” for a different look.
We are not selling many muffins, so we decided to discontinue them and remove the product ID from our report. To do that, we will click on the arrow next to “row labels” to open a dropdown menu.
From the list of alternatives, uncheck “45”, what is the product ID for the muffins. If you uncheck this box and click “To accept”, the product will be removed from the report.
As you can see, there are several options to play. How you display your data is truly up to you, but with pivot tables, there is truly no shortage of alternatives.
setTimeout(function(){
!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq = n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s) } (window, document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘335401813750447’);
fbq(‘track’, ‘PageView’);
},3000);
