939 total views, 1 views today
Many Excel experts believe that pivot tables are the single most powerful tool in Excel.
Most of us use Excel to slice and dice our data. Excel is one of those products with some very powerful tools for creating pivot table that are easy to use. It turns out that by learning handful of these tools, you can save yourself hours of time and quickly whip up some revealing analysis.
Let’s look at few of the Advantages of Pivot Table:
- Representation of information is more organized
- It can compress large amount of data to a summary
- Keeps records and allows quick update
- It can link data to extended sources
I’ll start this Excel series off with an introduction to the almighty ‘Pivot Table’.
The pivot table is well-loved among data geeks for its ability to quickly summarize large datasets. Suppose you want to know the Sum of Salary for different departments from the Employee Table. For each Employee, you have one row of data with Emp_id, Name, Department, Date of Joining, Salary and Location.
A pivot table will allow us to quickly crunch thousands of rows of data into a neat little table of totals. The screenshots below illustrate this. In this example, you can find that the summary of Sum of Salary Details for different departments by Employee’s Location for a particular Manager; everything in a summarized manner, by using Pivot table function.
1. Creating Pivot Tables:
The creation of pivot table can be done by following the below steps:
- Highlight the data and select the ‘Insert’ tab → ‘Pivot Table’ → ‘Ok’ (leave the default options).
- In the ‘Pivot Table Field List’ (see screenshot below), drag & drop ‘Department’ into the ‘Row Labels’ box; then drag & drop ‘Location’ into the ‘Column Labels’ box.
- Lastly, drag & drop ‘Salary’ into ‘Values’. The default setting will set this to ‘Sum of Salary’.
2. Sort Alphabetically or Numerically:
If you wish to sort the pivot table alphabetically or numerically, follow the below provided steps:
- Right-click on the pivot table
- Select ‘Sort’
- Select ‘Smallest to Largest’ or ‘Largest to Smallest’
- If you want a ‘custom sort’, you can select ‘More Sort options’
- In this example, we are selecting ‘Smallest to Largest’
After sorting the sum of salaries from ‘Smallest to Largest’, the report will be displayed as shown below.
3. Group columns and rows:
Suppose, we have a pivot table with a long list of dates, which you want to group by month. We can do this in a snap with the ‘Grouping Feature’. Right-click on a random ‘Date of Joining’ on the pivot table and select ‘Group’ from the pop-up menu. Just highlight the rows you want to put into one group, select the ‘Group’ option and select ‘Month’ from the list. With this, it groups the dates by Months. You can even display the data in Quarters or Years. Once the group has been created, you can rename the column label as you’d like.
4. Filter the values, columns, or rows:
Here I am going to show you how you can add filters, and how you can hide the rows, etc. We can do the same using ‘filter’ option. These filters are very helpful for people to look at their data column-wise, name-wise, value-wise or row-wise as per your desired conditions.
For example, if you want to show only the rows with a Department whose Salary is less than $60,000, right-click on the pivot table → select ‘Value Filters’ → select ‘Less than’ → enter 60,000 in the box.
You can use many other ‘Filter’ options viz., Equals, does not Equal to, Greater than, Greater than or equal to, Less than or equal to, Between, Not Between, Top 10.
After adding the filter to get the values below/less than $5,00,000 the data will show in the report as shown below:
Brijesh is working as MIS Executive at EzDataMunch. He is responsible for maintaining and generating all the reports of organization. He is also involved in Marketing related activities such as E-mail Campaign, Lead Generation and Video Marketing for our organization.