Unless you’re a Microsoft Excel power user, creating pivot tables in a spreadsheet can be a daunting task. It requires time and dedication to learn the functions and overcome the inevitable error messages. Once mastered, they’re incredibly valuable for anyone looking to glean information from their database. But Excel is not the only solution for business owners looking to slice and dice their database. Online database software applications are built on the foundation of a relational database that enables this functionality, yet lacks the intimidation factor that comes with Excel. If you’re unfamiliar with pivot tables, or if you are finding that Excel has a number of limitations now for analyzing your data set, then it may be time to look at online database software. But before we go there –> let’s better understand the purpose of a pivot table.
What is a pivot table?
A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. It allows grouping by any data field. Pivot tables are the perfect solution when you need to summarize and analyze large amounts of data. In just a few clicks, you have access to a whole new set of information. As already said, spreadsheets are one solution to create pivot tables, but the best tools don’t require to write complicated formulas or to start all over again every time you want to organize the data differently. A drag and drop option to move your fields around is the easiest way to go.
The challenge with Excel pivot tables
A snappy, efficient pivot table is worth its weight in gold. Getting there is, however, half the battle for many non-technical Excel users. The key is to not try and make a single report be all things to many people. Utilize function tables and pivot tables to summarize and filter data sets that each tell different stories, and create as many pivot tables as you need. Many Excel power users find that as their business becomes more complex, or the number of users increases, that managing reports driven off Excel is unmanageable due to lack of user permissions. Risk of human error escalates and report data accuracy can be called into question. It’s generally at this stage that businesses realize they need an online database software that can provide them with this level of security, with the database customization and analytical tools they need. And in many cases, enterprise-scale ERP or CRM applications are either not affordable, or perhaps just not a perfect fit.
When do you use a pivot table?
Pivot tables are most commonly used in situations where data needs to be aggregated, and sliced and diced for analysis. It’s particularly useful when you are looking to calculate and summarize data in order to make comparisons. Within Kohezion, we see our customers commonly use pivot tables to do the following:
- Run automatic calculations on summed or counted values
- Create %’s of totals
- Segment data by date, user or other variable and calculate totals
- Organize data into columns and rows with automatic calculations that would otherwise be impossible with Excel
Alternatives to Excel pivot tables
Building a pivot table in Kohezion’s online database software is not complicated, nor is it time-consuming. In fact, it can be done in minutes compared to the time it would take you to build it manually or in Excel. But most importantly, as with any enterprise SaaS software, it has the inherent user rights which ensure your database is only edited by those that should, ensuring the accuracy of your pivot tables and reports.
How to create a pivot table in Kohezion
- Create a database with Excel using Kohezion. Article: Create a database with Excel (3 min read)
- Build your pivot table
- Open the desired search in the search groups left sidebar.
- Click on the third icon (blue cube) to switch from a grid or calendar view to the pivot table view.
- Drag and drop the desired field(s) in the boxes to use them as columns and/or rows.
- Choose a type of aggregation view: table, bar chart, heatmap, table with heatmap, line chart, etc.
- Choose a type of aggregation function: count, sum, average, etc.
Pivot table examples
1. Pivot Table With Subtotal – Count Aggregation – Column Only
The sample database below is a sales tracking database, and contains just a few fields such as categories, profit and state. Utilizing the pivot table functionality, you can create a a pivot table to summarize all sales. Simply drag the category field as a column and use a table view paired with the sum function. This is a simple sum function, using only one column.
This took 20 seconds to build.
2. Pivot Table With Subtotal Bar Chart – Count Aggregation – Column And Row
To push the functionalities a bit further, columns and rows can be paired to rearrange data even further. Within this same database, a table with subtotal bar chart aggregation view can be paired with a simple count aggregation function. The category field can then used as a column and the State field used as a row.
Unlike Excel, which would return an error reference, this Kohezion pivot table isn’t generating errors when some fields are left empty.
Interested in trying to build a pivot table off your existing database? Give Kohezion a try and create a free account today! Simply upload your Excel file, and create a pivot table in minutes. If you have bigger or multiple Excel databases you’d like to migrate to Kohezion, get in touch. Our turnkey solutions are surprisingly affordable.