Pivot Tables are both incredibly simple and increasingly complex as you learn to master them. They’re great at sorting data and making it easier to know , and even an entire Excel novice can find value in using them.
We’ll walk you thru getting started with Pivot Tables during a Microsoft Excel spreadsheet.
First, we’ll label the highest row in order that we will better organize our data once we apply the PivotTables during a later step.
Before we continue, this is often an honest opportunity to urge obviate any blank rows in your workbook. PivotTables work with blank cells, but they can’t quite understand the way to proceed with a blank row. To delete, just highlight the row, right-click, choose “Delete,” then “Shift cells up” to mix the 2 sections.
click ok and delete row
Click inside any cell within the data set. On the “Insert” tab, click the “PivotTable” button.
click empty cell
When the dialogue box appears, click “OK.” you’ll modify the settings within the Create PivotTable dialogue, but it’s usually unnecessary.
pivot dialogue ok button
We have tons of options here. the only of those is simply grouping our products by category, with a complete of all purchases at rock bottom . to try to to this, we’ll just click next to every box up the “PivotTable Fields” section.
click check boxes
To make changes to the PivotTable, just click any cell inside the dataset to open the “PivotTable Fields” sidebar again.
click any cell
YOU MIGHT ALSO LIKE
Once open, we’re getting to pack up the info a touch . In our example, we don’t need our Product ID to be a sum, so we’ll move that from the “Values” field at rock bottom to the “Filters” section instead. Just click and drag it into a replacement field and be happy to experiment here to seek out the format that works best for you.
move sum to filters
To view a selected Product ID, just click the arrow next to “All” within the heading.
view product id arrow
This dropdown may be a sortable menu that permits you to look at each Product ID on its own, or together with the other Product ID. to select one product, just click it then click “OK,’ or check the “Select Multiple Items” choice to choose quite one Product ID.
This is better, but still not ideal. Let’s try dragging Product ID to the “Rows” field instead.
We’re getting closer. Now the merchandise ID appears closer to the merchandise , making it a touch easier to know . But it’s still not perfect. rather than placing the merchandise ID below the merchandise , let’s drag Product ID above Item inside the “Rows” field.
move product id
This looks far more usable, but perhaps we would like a special view of the info . For that, we’re getting to move Category from the “Rows” field to the “Columns” field for a special look.
move category to column
We’re not selling tons of dinner rolls, so we’ve decided to discontinue them and take away the merchandise ID from our report. to try to to that, we’ll click the arrow next to “Row Labels” to open a dropdown menu.
From the list of options, uncheck “45” which is that the Product ID for dinner rolls. Unchecking this box and clicking “OK” will remove the merchandise from the report.