قالب وردپرس درنا توس
Home / Tips and Tricks / Using pivot tables to analyze Excel data

Using pivot tables to analyze Excel data

A Microsoft Excel logo on a gray background

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 understand, and even a complete beginner in Excel can find value using them.

We help you get started with pivot tables in a Microsoft Excel spreadsheet.

First, we label the top row so that we can organize our data better once we apply the pivot tables in a later step.

add headlines

Before we proceed, this is a good opportunity to delete blank rows in your workbook. Pivot tables work with empty cells, but they can’t quite understand how to proceed with an empty row. To delete, highlight the row, right-click, choose “Delete” and then “Move Cells Up” to combine the two sections.

click ok and delete row

Click in a cell in the dataset. On the “Insert” tab, click the “Pivot Table” button.

click on empty cell

Click “OK” when the dialog box appears. You can change the settings in the Create PivotTable dialog box, but this is usually not necessary.

run dialog ok button

We have many options here. The simplest of these is to group our products by category, with a total of all purchases at the bottom. To do this, we just click next to each box in the “Pivot Table Fields” section.

click on checkboxes

To make changes to the PivotTable, click a cell in the dataset to open the “PivotTable Fields” sidebar again.

click on a cell

Once opened, we’re going to do a little bit of cleaning up the data. In our example, we don’t need our product ID to be a sum, so we’ll move it from the ‘Values’ field at the bottom to the ‘Filters’ section. Just click and drag it to a new field and feel free to experiment here to find the format that works best for you.

move sum to filters

To view a specific product ID, click the arrow next to “All” in the header.

look at the arrow of the product ID

This drop-down list is a sortable menu that allows you to view each product ID individually or in combination with another product ID. To choose a product, click on it and then click “OK”, or check the “Select multiple items” option to choose more than one product ID.

click on multiples

This is better, but still not ideal. Let’s try dragging the product ID to the “Rows” field.

We’re getting closer. Now the product ID appears closer to the product making it a little easier to understand. But it still isn’t perfect. Instead of placing the product ID below the product, we drag the product ID above the item in the “Rows” field.

move product ID

This seems much more useful, but we may want to take a different view of the data. For that we are going to move Category from the “Rows” field to the “Columns” field for a different look.

move category to column

We don’t sell many dinner rolls, so we decided to discontinue them and remove the product ID from our report. To do that, we’ll click on the arrow next to ‘Row Labels’ to open a drop-down menu.

row arrow

Remove the check mark at “45” from the list of options. This is the product ID for dinner sandwiches. If you uncheck this box and click “OK”, the product will be removed from the report.

uncheck the box

As you can see, there are a number of options to play with. How you display your data is completely up to you, but with pivot tables, there’s really no shortage of options.

Source link