Oct 11, 2017 Pivot Charts are connected to pivot tables and provide a visualization of the data in the pivot table. Since the two objects are connected, any changes made to the pivot table will be reflected in the pivot chart. This includes filters. When a filter is applied to the pivot table, the pivot chart will also be filtered. There are some truly nifty features in a pivot table. Like double-clicking a sum to create a table displaying its source data. The interface is great too – friendly drag and drop. Even if your table set-up isn’t right you can probably drag a few things here and there to fix it. You can base a pivot table on an Excel list or some other data.

When you maintain an Excel sheet, I am sure you do not feed in data just for the sake of storage. Most of the times you intend to analyze and organize that data, right? Now, MS Excel has got a beautiful feature called Pivot Table that lets you summarize large tables in the form of a report. Then, without much manual effort and time you can analyze your data. Let us see how.

Here’s a section of sample data sheet that I maintained during a sports event at my office. It holds registration fee for different games, collected from different floors of the office building, and by different people.

I would like to get the grand total, meaning the total for each floor, for each event and the total money collected by each SPOC. The solution is to create a pivot table. A sample for the above data is shown below.

Creating a Pivot Table

The minimum effort it takes to generate such a table should not make you undermine the immense potential of this tool.

The tutorial is based on MS Excel 2013. The steps would be more or less same on other versions as well.

Step 1: Select the data/cells on which you would like to create a pivot table.

Step 2: Navigate to Insert tab and click on the tool that reads PivotTable, towards the extreme left of the ribbon.

Step 3: The Create Pivot Table window will show up. It would show the selected data range. If you wish you can change it here or choose an external data source. Besides, you have an option to create the table on the same sheet or a new one (prefer a new one to avoid errors).

Step 4: This will activate the Pivot Table panel on the right. Select the fields on which you want to base your analysis.

Step 5: Now, you will start getting a feel of what Pivot Tables are. Try and choose different combinations with different row and column labels to get a detailed understanding.

Here, check out another report against the selected fields and row/column labels respectively. Try and compare the images (left and right).

Let me tell you frankly that you would understand this feature in detail only by usage and practice. Reading tutorial just once isn’t enough.

Few Handy Tips

• Choose your row and column labels carefully because they decide how the analyzed data will be arranged.
• You can move fields between row and column labels through drag and drop.
• For a field, the context menu shows you an option called Value Field Setting that allows you to choose the nature of the report or field data.
• You may apply filter to the result in order to get a customized report.

Conclusion

This tutorial is meant to set forward the basic technique to create a pivot table and few rules and standards that can get you started. There’s a lot more to it though. You will learn the ropes only by creating a lot of them and playing around with the options.

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.

Read Next

Top 8 Ways to Fix Windows 10 Network Connection Issues

Are you struggling to keep your

Pivot Table On Mac Excel 2016

In this Power Pivot and Power Query tutorial from Simon Sez IT. Excel expert, Deb Ashby teaches you how to get started with the Power Pivot and Power Query tools in Excel

In this Advanced Microsoft Excel tutorial, she looks at the following:

• PivotTables – a quick reminder 😉
• Importing data into Excel from multiple sources
• Cleaning data using power Query
• Creating a Power Pivot table
• Creating relationships using Power Pivot
• Using Measure and KPIs in Power Pivot
• Displaying data in a Pivot Chart

Manual Pivot Table On Mac Excel 2008 Freeze Pane

Download the Excel file used by Debs and follow along.

Other Excel classes you might like:

Take a look at more recorded live classes from Simon Sez IT.

Pivot Table In Mac Excel

We hope you enjoyed this video. For more, free, Simon Sez IT Excel tutorials, take a look at our Excel Resources.

Similar Posts