How to create Pivot Tables without Excel
If you don’t already know, Excel is a very capable software that will let you make sense of large datasets in minutes. We’ll demonstrate that you can do the same with Apache’s OpenOffice Calc. OpenOffice is a free open source alternative to Microsoft’s Office that is just as handy without the cost. It can be especially useful for small businesses and startups who may not afford to shell out the licensing fees associated with commercial software for every machine in the company.
Why do you need a pivot table
A pivot table lets you reorganize and summarize selective rows and columns in a spreadsheet and visualize it from different perspectives. For example, a sales manager can use a pivot table to know which particular product sold best, by what sales rep in what market region, in a quarter. It would otherwise take a lot of time to look through every sale and it’s particulars. With a pivot table, she can quickly reorganize the data and create visual summaries to present to her team.
Pivot tables checklist
To be most efficient at creating pivot tables, you should check the following list of items to make sure your data is ready for this amazingly powerful tool.
- Organize the source data in a tabular format.
- Remove blank rows.
- Remove blank columns.
- Convert value fields to numbers not text.
- Convert date fields to date format not text.
- Date fields should not contain blanks.
- Rename column headers to user friendly names.
- Put the source data in the table for automatic updates.
Step by step how to
In the following sections, we’ll use some toy sales data to illustrate the power of pivot tables using Calc. The data-set contains 7 columns and 1000 rows on the sales data by region, retailer type, and customer.
The sales manager may want to find out the answers to the following questions before her next sales meetings:
- What were the total sales in the South region in 2016?
- Who are the top five retailers by sales?
- How did The Home Depot’s performance compare against other retailers in the South?
To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert option on the menu bar and click Pivot Table or click on the pivot table graphic on the main toolbar.
The Available Fields are created based on the back-end data used for the Pivot Table. The left section is where you place the fields, and according to where a field goes, your data is updated in the Pivot Table.
It’s a simple drag and drop mechanism, where you can simply drag a field and put it in one of the five areas. As soon as you do this, it will appear in the Pivot Table in the worksheet
Now let’s try and answer the questions your manager had using this Pivot Table.
Q1: What were the total sales in the South region?
Drag the Region field in the Rows area and the Revenue field in the Data Fields area. It would automatically update the Pivot Table in the worksheet. Note that as soon as you drop the Revenue field in the Data Fields area, it becomes Sum of Revenue. By default, Calc sums all the values for a given region and shows the total. If you want, you can change this to Count, Average, or other statistics metrics. In this case, the sum is what we needed.
Q2 What are the top five retailers by sales?
Drag the Customer field in the Row area and Revenue field in the data fields area. In case, there are any other fields in the area section and you want to remove it, simply select it and drag it out of it or use the delete key.
You’ll get a Pivot Table as shown below:
Note that by default, the items (in this case the customers) are sorted in an alphabetical order. To get the top five retailers, you can simply sort this list and use the top five customer names.
Q3: How did The Home Depot’s performance compare against other retailers in the South?
You can do a lot of analysis for this question, but here let’s just try and compare the sales. Drag the Region Field in the Rows area. Now drag the Customer field in the Rows area below the Region field. When you do this, Calc would understand that you want to categorize your data first by region and then by customers within the regions. Now drag the Revenue field in the Data Fields area and you’ll have the sales for each customer (as well as the overall region).
You can do some further sorting by clicking on the Region and/or Customer fields to select different combinations of the two.
Once you have the created a pivot table that answers your questions, the last bit would be to visualize your results. We’ll create a graph for Q3 above and to demonstrate this.
As we can see, graphs helps make pivot tables more palatable. This can be very helpful to help you make more sense of the underlying relationships in your data. It’s also a great tool to present your data findings to a more varied audience. While this post is a good introductory piece to pivot tables, there is a lot more advanced stuff that can be done with Calc. There are many good resources online to build on starting with https://wiki.openoffice.org/wiki/Documentation/Tutorials.
