Slice and Dice your Excel Data like a Magician

Excel spreadsheets are handy tools for tracking and analyzing data. However, they do have some limitations. If you build a spreadsheet in a certain layout, it will often take a fair amount of time and work to provide alternate views of the data.

The spreadsheet below tracks product sales by day for a Fruit stand. We’ll see how a Pivot table can give us lots of different insights into this data.

Creating the Pivot Table from Existing Data

Go to the Insert tab and click the Pivot Table button.

The Create Pivot Table window will open.

You can pull  in data from an external source if you want, but typically you will just use the data in the sheet you are on.

You can also put the pivot table in the existing sheet or in a new one. It’s a little bit cleaner to put the pivot table in its own tab.

Click the OK button.

Your Pivot Table will open in a new tab. There will be no data displayed, but all of the fields in your original tab will be displayed in the Pivot Table Field List on the right side of the screen. You can arrange these fields in numerous useful combinations.

Drag the fields into their buckets at the bottom of the Pivot Table Field List.

Your Pivot Table’s data will be displayed based on your settings. The image below shows where the fields appear on the actual table.

The values in your table may need to have a particular number formatting. This data is money, so I’m going to set the format to Currency.

Click the Sum of Sales field in the Values box.

A menu will appear.

Select Value Field Settings from the menu.

The Value Field Settings menu will open.

You can change the Values to other formulas (count, average, etc.) if you need to.

Click the Number Format button.

The Format Cells menu will open.

Select Currency from the Category  list and click the OK button.

You will see the pivot table

Filtering and Sorting Your Data

The data in all of the fields in your new Pivot Table can be filtered and sorted to display or hide any data. Use the dropdown buttons on any field to pull up the Filter options.

You can sort the values of a field A to Z or Z to A or use more advanced options. Let’s see the Filtering options.

You will see all of the values for the selected field. You can check and uncheck any combination of values to show or hide those values. The field below contains all of the products in listed. I only want to see results for Apples and Oranges.

Check the values that you want to see and click the OK button.

Now you will only see those values (Apples and Oranges) displayed. You will also see the Filter icon displayed on the dropdown button.

Rearranging (Pivoting) Your data

You can completely rearrange the layout of the data on report by dragging the fields around the areas of the Pivot Table Fields List in the lower right corner of the screen. I currently have the report displaying Products down the rows and Days across the columns. I want the switch it so that the Days go down the rows and the Products go across the columns.

Drag the Day field into the Row Labels box and drag the Products field into the Column Labels field and click the Updated button.

Now you can see the report updated to the new layout.

Nesting Fields of Data

You can “nest” one field inside of another field in a pivot table. If you drag more than one field into one of the report areas the lower field will be grouped inside of the higher one.

Let’s see what it looks like if we move the Origin field above the Products field in the Column Labels area.

Drag Origin over and above Products and click the OK button.

You will see the values in the Origin field (Imported and Local) with the Imported or Local products grouped below.

You can drag the Products field ABOVE the Origin field to reverse the grouping.

Now you will see any Imported and Local data grouped inside of each Product.

This technique works the same way on the Row Labels.

Pulling in New Data

If you go back to the original worksheet where your source data is contained, you can make changes to the underlying data. You can have these changes reflected on your Pivot Table very easily.

Go back to the Pivot Table and right click in one of the cells.

An option menu will open.

Select Refresh.

The data in your pivot table will update to reflect any changes made in the underlying source data.

See the Video Tutorial for Using Excel Pivot Tables

The following video shows how to create and manipulate a Pivot Table in Microsoft Excel.

 

[tubepress]

Did you Like this Article?

Sign up for free updates from VitaminCM.com?

}); function mce_success_cb(resp){ mce_jQuery('#mce-success-response').hide(); mce_jQuery('#mce-error-response').hide(); if (resp.result=="success"){ mce_jQuery('#mce-'+resp.result+'-response').show(); mce_jQuery('#mce-'+resp.result+'-response').html(resp.msg); mce_jQuery('#mc-embedded-subscribe-form').each(function(){ this.reset(); }); } else { var index = -1; var msg; try { var parts = resp.msg.split(' - ',2); if (parts[1]==undefined){ msg = resp.msg; } else { i = parseInt(parts[0]); if (i.toString() == parts[0]){ index = parts[0]; msg = parts[1]; } else { index = -1; msg = resp.msg; } } } catch(e){ index = -1; msg = resp.msg; } try{ if (index== -1){ mce_jQuery('#mce-'+resp.result+'-response').show(); mce_jQuery('#mce-'+resp.result+'-response').html(msg); } else { err_id = 'mce_tmp_error_msg'; html = '