Pivot Tables: The Ultimate Guide

Data visualizationReporting and data analysis

Pivot tables are powerful tools for data analysis and visualization. They allow you to summarize, filter, sort, and present large amounts of data in a simple and interactive way. But what is a pivot table exactly? How does it work? And why should you use it for your business reporting?

In this article, we will answer all these questions and more. We will explain pivot tables’ basics, structure, and benefits. We will also show you examples of using pivot tables for different scenarios and purposes. By the end of this article, you will clearly understand what pivot tables are and how to use them effectively.

What is a pivot table?

A pivot table is a data analysis tool that transforms raw data into a summarized one. This technique helps to create powerful visualizations and derive new knowledge from the data. Despite seeming to be a simple tool, it can serve as a part of embedded BI solutions and help communicate insights within the company and beyond.

Depending on your needs and preferences, you can use either an Excel pivot table or a web pivot table component for data analysis and visualization.

An Excel pivot table is a tool that is created and used in Microsoft Excel. It is more suitable for desktop users who want to work with local or offline data sources and have more control over the pivot table settings and options.

A web pivot table component is a built-in library that you can add to your web application or website. It lets users analyze and visualize data in an interactive way on the fly. The pivot libraries can group data from a variety of data sources, from simple CSV files to complex OLAP cubes. Such web components may have different features and options, depending on the tool or library that is used to create them, such as drill-through, drag-and-drop support, color themes, localization support, charts, and more.

Structure & functionality

The structure is self-explanatory and intuitive: a pivot table is composed of three main areas to work with: rows, columns, and cells with aggregated values.

Think of each field of your data as a dimension. In the context of a pivot table, dimensions are presented as hierarchies. You can put them either into rows or columns.

Numerical hierarchies are interpreted as measures and can be put into cells. You can apply aggregation to them by using aggregation functions such as sum, average, maximum, minimum, and others.

Each cell contains an aggregated value at the intersection of a row and a column.

Each hierarchy contains members – values that belong to a field. They can be sorted or filtered by specified criteria.

Pivot Table Functions

1. Aggregation

   Summarize the data using aggregation functions – there are 13 of them available in WebDataRocks. See them yourself:

   

    

2. Sorting

   Arrange the data right on the grid according to your business logic so it becomes easier to comprehend its meaning. For example, members can be sorted alphabetically/reversed alphabetically, aggregated values – in ascending/descending order.

   

    

3. Filtering: Focus on the Important Data

   We are guided by a ‘less is more’ principle. That’s why we recommend refining the data from irrelevant or redundant subsets of data. A pivot table offers a filtering for the records to concentrate only on the important information.

   Three types of filters are at your service: based on values, member names and a special one – report filter.

   

    

4. Slicing & Dicing

   Do you want to take a look at your data from different perspectives? Change the slice dynamically by using a drag & drop feature. You spend a very little time to get a completely new view of your data. No need to ask the IT specialist to restructure your report – just drag & drop the necessary fields. This feature makes WebDataRocks a completely self-service tool.

   

    

   See how slicing & dicing your data is easy?

Ways to look at the data

A modern pivot table should be flexible in all the senses. You may want to see a general picture from a bird’s eye view, or you may want to know details about a particular aspect of your data.

For this reason, hierarchies can be placed one after another or be in a multi-level (parent-child) form. Use operations like expand & collapse to the former and drilling down & up to the latter. These operations help you get an almost microscopic view of your data.

Benefits

Whether you need an ad-hoc report or a recurring one, a pre-made functionality of a pivot table enables you to concentrate on the task itself rather than spending time thinking about how to do it technically.

How to create a pivot table report

Now that you have a better sense of what the pivot table is, let’s get your hands dirty with practice.

At this stage, the most important thing is to ask yourself a question: “What do I want to achieve with this report?”.

Let’s create a quarterly sales report to gain insights about the most successful regions of sales.

Step 1: Add data to the table by selecting the fields as rows and columns. Here we’ve created a multi-level hierarchy to diversify our report.

Step 2: Choose the values you want to measure and apply the aggregation to them (e.g. the sum).

Step 3: Sort and filter the data.
Now that you see how our table became filled with meaningful data, filter it by selecting the top 5 best-selling countries.

Step 4: Finally, save your report locally in a JSON format by clicking the Save tab. Later, you can load it into the component and go back to editing it.
Moreover, you can export it to the most convenient format for you: PDF, Excel or HTML.

Now you have a web-based report that you can share with colleagues and friends.

The benefits of using a pivot table for business

Considering the ubiquity of e-business and increasing volumes of data, taking advantage of existing online tools for analytics is essential for those who want to make their business flourish. Web pivot tables represent a perfect solution for handling this task. In contrast to their offline spreadsheet alternatives such as Excel, web-based reporting solutions definitely stand out due to:

• Customization options – you can make the component totally fit into the design of your application by changing its visual interface and functionality.
• Cross-browser compatibility & web responsiveness – you can run a pivot table and get access to reports from any device.
• Interactivity – you can change the data slice, aggregations, filters, and sortings on the fly.
• Integration options – whether you have a cloud-based application or a locally deployed one, you can embed a web-based pivot table in any project and provide your clients or employees with constant access to analytics.
• Sharing reports online – a built-in exporting functionality allows sharing results of the analysis between your colleagues.

Moreover, in WebDataRocks security of users’ data is our priority. We don’t collect or store your data on the server-side. Data is processed exclusively on the client-side and is stored only on your local or remote server.

Coming soon

Later, in a series of articles dedicated to challenges in various industries, we’ll take a closer look at real-world applications and learn how to solve analytical problems using WebDataRocks. Best practices will help you understand what is happening in your business, follow trends and make data-driven decisions significantly faster.
Stay tuned!

Useful links & advanced features

• Download a free WebDataRocks package
• Data types in JSON – learn how to set types and create multi-level hierarchies in the data source
• Changing predefined skins (themes)
• Conditional formatting – learn how to highlight important data
• CodePen demo with sales analytics

What else to read

• Tutorial: How to create a dashboard with WebDataRocks and AnyChart
• Data visualization with WebDataRocks & Chart.js: create a dashboard in 5 min
• 8 reasons to try Flexmonster after WebDataRocks
• How is Laravel Revolutionizing the Way Enterprises Develop Applications?