Pivot tables in Excel are a convenient way to summarize, arrange, group, explore, and analyze data. They help to easily explore data from diverse angles and get useful insights from it. Pivot tables in Excel is a common practice that data experts deploy, but there are many tips and tricks that can make the whole process more convenient. In this blog, we are presenting the top 14 Excel pivot table tips and tricks you should know about.
1.Natural Language Pivot Table
Natural language can help to create pivot tables automatically and address business-related queries. For example, if you have to search for a particular customer that how many products are sold to him in a specific time frame, then it might seem a time-consuming task. But it can easily be done with natural language. To do that, go to the "Home" tab and click the "Analyze Data" option. In the search bar, write like "products sold to customer XYZ in 2019". You will see that Excel will present the exact results you are looking for. Furthermore, you can click "Insert PivotTable", which creates a separate pivot table sheet for you containing all the corresponding details.
2.Click Values for Details
When you have created a pivot table, then you can easily click on the values given in it to know more details. For example, in the above pivot table that we created to know about the number of products sold to customer XYZ, if you click on the product value, then you will see that it will take you to a new Excel sheet that will present more details about the products sold to XYZ. This way, a simple click in the pivot table can open up more details.
3.Creating Pivot Tables from Multiple Data Tables
If you have multiple data tables that have few common fields, then you can use them to create pivot tables. To understand this, let's consider an example where you have one table about customer details that include columns, such as customer ID, name, phone, address, etc. Similarly, you have another table about products sold that include columns, such as customer ID, order ID, products sold, data, etc.
To create a pivot table, you first have to link the two tables. To do that, go to the "Data" tab and click the "Relationships" option. Now click "New" and it will open the prompt. From there, under the "Table" section, select the customer details table, while under the "Related Table" section, select the products sold table. Next, in the "Column (Foreign) section, pick the column from the customer details table that matches the column in the products sold table. In our case, it is "Customer ID". So, select "Customer ID" in both "Column (Foreign)" and "Related Column (Primary)" sections. Once done, click "OK" and then click "Close".
Now you have created the relationship among the two data tables and it's time to add the Pivot Table. To do that, go to the "Insert" tab and click the "PivotTable" dropdown menu and choose "From Data Model". It is because when you have set up a relationship between two data tables, it has created the data model. Next, click "New Worksheet" and then click "OK". It will take you to the new sheet where you can now link entries from both data tables and set up the pivot table. For example, from the right side, you can drag the "Name" column from the customer details table to the "Rows" section and drag "Products sold" from the products sold table" to the "Values" section. This way, it will create a pivot table showing the number of products sold to customers by name.
4.Change Field Layout and Sorting Options
Pivot table field layout contains all the data columns that you can use to create different pivot tables. But the list in the field section can be very large and might require a lot of scrolling. You can address this issue very easily with the field layout option. From the right side in the "PivotTable Fields" section, click on the "Settings" icon located at the top right corner, and there you will see different field layouts you can choose from. Similarly, if you want to sort your fields alphabetically, then you can also do it easily. Click on the "Settings" icon and then click "Sort A to Z".
5.Calculated Fields
You can also add additional calculated fields into your pivot table. For example, you have a pivot table that shows revenue and cost for each customer, but you also want to add a profit section. One way is to add the profit table manually, but the Excel pivot table has a more advanced way to do it. Go to the "PivotTable Analyze" tab and from the drop-down options of "Fields, Items, & Sets", click "Calculated Field". Now name your field as "Profit" and add the formula "Revenue - Cost". Afterward, click on "Add" and then click "OK". This way, you have created the new field in the existing pivot table.
6.Sort Values in Pivot Table
The pivot table has a lot to offer and one of them is sorting values for enhanced data analysis. For example, let's assume you have a pivot table that shows the profit of products sold per customer and you want to see which customer gave the most profit. You can easily sort that by right-clicking any entry in the profit column and then clicking "Sort" > "Sort Largest to Smallest".
7.Show Values Differently
Let's assume you have a pivot table showing the profit of products sold per customer. Now, traditionally, the profit column will be showing value in the local currency. But you can change the way you want to see values. For example, you can turn the profit column to % of the column total so that it can represent the percentage of profit each customer made. To do that, right-click any entry in the profit column, click "Show Values As", and then click "% of Column Total". Similarly, there are many other options available there that you can use to show values differently.
8.Summarize Values
The pivot table also allows you to summarize the values instead of just summing them. For example, if you want to count the orders by a customer, drag the "Order ID" column to the "Values" section. You will notice that the pivot table will sum the values, which isn't right because order ID is just a unique number. So, you can change it to count, by right-clicking any entry in the order ID column, clicking "Summarize Values By", and then clicking "Count". This way, you will see that it now shows the order counts. You can choose other options from the "Summarize Values By" section to customize your values as per your needs.
9.Slicer to Filter Data
You can filter your pivot table data easily with a slicer. To do that, go to the "PivotTable Analyze" tab and click "Insert Slicer" under the "Filter" section. It will ask which columns you want to add the slicers. To understand the use of slicers properly, let's assume you have a pivot table showing the profit of products sold per customer (the same example we used in the above tips). Add slicers to the "Customer Name" column and "Product" column. Once done, click "OK". It will show you slicers for the two columns that you can use to filter data. This way, if you click on the entries given in those slicers, the pivot table will filter out the results accordingly.
10.Timeline to Filter Data
You can also add timelines in the pivot table to filter the data based on date. To do that, go to the "PivotTable Analyze" tab and click "Insert Timeline" under the "Filter" section. It will show you the "Date" option if your data tables contained a "Date" column. So, tick the checkbox and click "OK". You will see a timeline filter option in which you can adjust the time to filter the pivot table entries accordingly.
11.Pivot Charts
As the name implies, pivot charts are meant to facilitate you in visualizing your pivot table graphically. To do that, go to "PivotTable Analyze" and click "PivotChart". There you will see different types of graphical charts that you can pick from. So, choose the one you want and visualize your pivot table in a graphical way. You can also use pivot charts in combination with the timeline.
12.Pivot Table Layouts
You are not just stuck with the standard layout of the pivot table, as there are different layouts that you can pick and customize your pivot tables. For example, you will see that the pivot table shows sub total and grand total when you have a table related to revenue type values. If you don't want sub and grand totals, click on any entry in the table, go to the "Design" tab from the top menu bar, click "Subtotals", and then click "Do Not Show Subtotals". Similarly, click "Grand Totals" and then click "Off for Rows and Columns". Similarly, you can also change the layouts of the pivot table. To do that, click on "Report Layout" and chose one of the layout designs showing there. If you want to set a particular layout as default whenever you launch pivot table, then go to "Files" > "Options" > "Data" > "Edit Default Layout". There you can customize the layout settings as you want.
13.Grouping
Instead of having separate rows in the pivot table, you can group them for enhanced analysis. To do that, select the rows you want to group, right-click, and then click "Group". This way, those rows will be grouped together and you can name that group as you want.
14.Customize Row Labels
You can customize row labels and positions in the pivot table easily. For example, if you want a row to take the top position no matter the kind of soring you have performed, you can right-click that row, then go to the "Move" option and pick the position you want to assign. Similarly, if you want to rename a row, instead of going back to the data table and doing there, you can select that row and change the name from the formula bar easily.
Wrapping Up
Pivot tables in Excel are one handy way to get the statistics and data analysis. The above-mentioned 14 tips present some convenient ways to use the pivot table, but there might be more useful ones for you that you can learn by testing and exploring pivot tables in Excel.
Sun IT Solutions is one of Toronto's leading IT Companies. We offer leading IT solutions including Managed IT Services, Managed Security Services, Cloud Services, Business continuity and disaster recovery (BCDR), Cyber Security Training and Dark Web Monitoring, IT Support Services, IT Consulting, IT Outsourcing Services and . We have been Toronto's leading managed IT service and IT support team for over 10 years and counting