Your cart is currently empty!

Pivot Table is a feature which you can usually see in the Google Spreadsheet and Microsoft Excel. While alots of people can address it existence, seldom people can explain what it is and what it’s difference between the normal table. In this article, we are going to break it down and elaborate it in detail with illustration.
It is a good to start explaining what is Pivot Table by a normal Table.
Month | Sales Turnover (USD$) | Salesperson |
---|---|---|
Jan | 5,000 | Anna |
Feb | 2,000 | Anna |
Feb | 3,000 | Angela |
Mar | 1,000 | Ann |
You will not feel strange about the tabular data pattern which a normal table brings to you. In this Normal Sales Turnover Table, you can easily get 5 piece of information because there 5 records inside the table.
The information, i.e. the 5 records, are in atomic level which means that they are already deintegrated into smallest granularity that if you further break down the record, say breaking down from information per Record to information per Cell, you no longer can get the useful information because it will be meaningless if you only know the value “Anna” without knowing what Month and how much Sales Turnover she get. In summary, a Record is the atomic level of a normal Table.
This “Tree View” of a normal Table is good for recording due to the fact that each record can be input separately without affecting the meaning and usefulness of each other. For example, it is totally fine that there is missing information of March Sales Turnover from Joan if you are only interested in the February result of Anna.
However, it is not good for “Forest View” when you want to see the consolidated result of the Sales Turnover Report. How about if you want to know the Total Sales Turnover Amount from Anna? Of course you can do the calculation by yourself each time you want to know the Total amount. But what if there are 10 million records?
Besides, the normal Table only tells you what you can see , it does not tell you what you cannot see. For example, what if I want to know the Sales Turnover of Joan in Jan ? “No Value (or Null)” is also a valuable information that you want to know.
Now, let’s see how we turn the Normal Table into a Pivot Table.
Jan | Feb | Mar | Total by Salesperson | |
Angela | 3,000 | 3,000 | ||
Ann | 1,000 | 1,000 | ||
Anna | 5,000 | 2,000 | 7,000 | |
Total by Month | 5,000 | 5,000 | 1,000 |
In the Pivot Table , now not only you can see the consolidated total amount in terms of Month or Salesperson, but also can easily address the Cell with missing value.
It’s glad to know, but still not impressive enough.
Because in the previous example, it we only use 2 parameters (i.e. Month and Salesperson) to co-ordinate a value (i.e. Sales Turnover Amount). How about if you want to add an additional parameter , for example, Product ?
Month | Sales Turnover (USD$) | Salesperson | Product |
---|---|---|---|
Jan | 5,000 | Anna | Apple |
Feb | 2,000 | Anna | Banana |
Feb | 3,000 | Angela | Cherry |
Mar | 1,000 | Ann | Dragonfruit |
While there is no big different in terms of the look and feel of the table except the additional 1 column added in the very right of the Normal Table, you can compare it with the new Pivot Table which added the Product parameter in below:
Salesperson | Product | Jan | Feb | Mar |
Angela | Cherry | 3,000 | ||
Ann | Dragonfruit | 1,000 | ||
Anna | Apple | 5,000 | ||
Banana | 2,000 |
Same as the Normal Table, an additional column Product is added immediately after the column Salesperson. And most important is that the Value Anna is shown only 1 time instead of 2 times (in Normal Table) due to the fact that the Pivot Table helps to “consolidate” the data to make it minimal.
While in the previous Pivot Table example there is the Total Amount , I skipped this information because it is not the focus on the essense of the Pivot Table. Feel free add the Total Amount any time you want to create a Pivot Table via using Google Spreadsheet ,Microsoft Excel or any Tabular tools.
Now by referring to this Pivot Table , you can answer the question from your boss like:
What is the performance of each Salesperson so far ?
You can sense that the focus is on the Salesperson which the boss probably wants to decide the quarterly bonus based on the performance of each Salesperson.
How about if the boss wants to see the profitability of his/her business? He may probably ask:
What is the performance of each month since the beginning of the year?
Apple | Banana | Cherry | Dragonfruit | |
Month | Anna | Anna | Angela | Ann |
Jan | 5,000 | |||
Feb | 2,000 | 3,000 | ||
Mar | 1,000 |
Due to the fact that the focus of the question shifted from Salesperson to Month, we then put the parameter Month as the name of the row to present as the focus of the table. We name this action as pivot by Month. Compared with the previous Pivot Table which is pivoted by Salesperson, you can find that the name of the Salesperson Anna shows twice this time. Because as we want to consolidate the data of Month (i.e. pivot by Month), we can only sacrifice the neatness of the Salesperson.
The term “Pivot” means whenever you want to rotate any one of the axises from 3 axises (for example), you need to use 1 axis to rely on. The axis you relied on is called the Pivot, while the axis is referring to the columns (or parameters, or dimensions , whatever you name it) in a normal Table. While in the physical world there can only be a maximum of 3 axises , you can add as infinity number of axises as you wish in logical world.
Pivot Table is good for presentation and bad for atomic level recording. If you start recording by applying the data schema of the Pivot Table, you will realise that if you try to insert the value of the table like what “Sales Turnover Pivot Table (Salesperson , Product , Month) , pivot by Salesperson” Table did previously, eventually you will have some missing value in the cell of the row’s name. Besides, the Pivot Table can never serve the function of data processing in turns of filtering and sorting.
Even worse, the client , including your boss, would like to derive different views for different scenarios in different periods of time. If you directly record the data in Pivot Table format, you will soon realise that whenever the client wants to change the pivot of the Pivot Table, say based on Month instead of based on Product, your previous effort on typing in the value , will be in vain. Therefore:
Use Normal Table format to record
Use Pivot Table format to present
When you search in the internet, although there is many other theory with the same name , Bingo! Theory (remarks : the “!” does matter) is a terminology which is invented by Diamond Digital Marketing to explain the priority of a project go vertically or horizontally.
Bingo is a popular game of chance where players mark off numbers on cards as they are randomly drawn by a caller. The goal is to be the first to complete a specific pattern, such as a horizontal line , vertical line or diagonal, and shout “Bingo!” to win.
Let’s start the explanation by Figure 2. To gain the every time when you complete any of a Client which contains 4 Steps which costs you USD$5 per step, you will be rewarded by a Sales turnover by USD$30, which in turn you get a Bingo! and will bring you USD$10 (30 – (5 x 4) profit.
However, you quickly realise that it is really hard for you to employ a staff who is resilient and has cross-talent which can cater both the 4 Steps covering Business Development , Design, Production and Marketing.
To enjoy the economy of scale, you hired a Business Development Manager, a Designer, a Production Manager and a Marketer to cater 4 Steps separately and respectively. They work very hard and perform well in the very beginning, bringing you 4 times of Sales Turnover (i.e. 4 Bingo!) from Client A,B,C and D of USD$ (30 x 4) – (5 x 16) = USD$40 in total.
However, one day your Marketer requested to resign from his position, which you understand and let him go. Due to the fact that you need to have Bingo! only if you can do both Step 1 ,2,3 and 4 at the same time, as now the Marketer is quit which cannot delivery the Step 4, which means that the effort of Step 1,2 and 3 for the Client A,B,C,D will all be in vain, costing you in total USD5 * 12 = USD$70 lost in total.
In this example, you can see that if you do horizontally , you can deliver as minimum as 4 Steps for any of a Client which costs you USD$5 x 4 = USD$20 to get a Bingo! USD$30 Sales turnover.
However, if you go vertically, even you have done 12 Steps which cost you USD$ 5 * 12 = USD$70 , which means that even though you are more hard working than the minimum 4 Steps, you still cannot get your Bingo! reward.
This example exactly reflects the reality that the most hard working one is not necessarily the one who makes the most profit , only if you have learnt how to prioritise of your work.
In most cases a great success is brought by a cross-talent ability, while in reality the education system only focuses on producing expert with specific talent. Whether you go broad (horizontal) or deep (vertical) is a matter of preference which is no clear cut right or wrong. However, it is crucial that at least you know if you are encountering this Bingo! Theory in your daily choice.
In SaaS or traditional manufacturing industry , Minimum Viable Product (MVP) refers to a version of a product with just enough features to be usable by early customers who can then provide feedback for future product development. The MVP is a typical choice of go horizontal instead of go vertical.
A Business Process Management System (BPMS) is a platform (normally a SaaS) that manages and automates well-defined business processes. It creates an infrastructure that enhances company agility, making work faster and easier. BPM involves modeling, automation, execution, control, measurement, and optimization of business activity flows to support enterprise goals across systems, employees, customers, and partners. CRM , Booking System, Human Resource Management System , can all be regarded as part of the Business Process Management System.
As Murphy’s Law stated , “when something can happen, it will happen”. Whenever there is an inconsistent business process, there is a chance of risk, no matter how little the chance is , as long as you tried enough times, the risk will finally be suffered. As a enterprenuer who run a business, Risk is the synonym of Cost which we should avoid. To standize the Business Process of a business means whenever you provide a same quantity and quality of input, the output of that Business Process will always be the same. This is one of the reasons why a Business Process Management System should exist.
Imagine you are requested from your superior that you have to deliver a Weekly Sales Report to your superior from different platforms including Shopify and Woocommerce. Due to the fact that these 2 platforms are not synchronised and you have to copy and paste the data from the Sales Report from these 2 platforms and consolidate the 2 reports into one and deliver them to your superior. In fact this is a monotonous and trivial data process that in fact brings little value to the company. But it’s inevitable for you to carry out the Task.
Imagine how much time can be saved if there is a system which can be scheduled and automatically synchronise the data from different data sources and consolidate them into 1 report.
This is the reason why a Business Process Management System should exist in order to automate the work process and bring us better life.
Do you have the experience that when your company is using a Google (or Excel) Spreadsheet as a CRM which you are responsible to update the record of the clients based on some event triggers , including
While there may be 50 columns in one Google Spreadsheet for each record of a client, each Event may only require you to modify a particular column (i.e. not all columns).
However, there will be a very long learning curve to memorise the mapping between the 50 columns and the 3 Events.
Even worse, you will never know the sequence of filling in 10 columns for a particular event.
For example, you may never know you need to fill in the Birthday Field in the CRM before there is a Birthday Coupon shown in the drop down list of the Available Coupon list.
By using a Business Process Management (BPM) System, we can set the conditional logic inside the BPM so that the frontend interface of the system will always guide you what to do next, and hide any fields or buttons that are not suitable for your next move, which can significantly eliminate the time of learning the frontend interface.
Have you had the experience that a single Google Spreadsheet is acting as a Client CRM which is shared by 10 Salesperson and 4 departments?
Whenever one sales person is using the Spreadsheet , he may probably apply a record filter so that he only filters the records that are related to him.
Meanwhile, if another sales person wants to use the same Spreadsheet, he may need to deactivate the filters applied by the previous sales person , which means the Spreadsheet cannot be used at the same time by 2 people.
Even worse, if there are 50 columns in the same Spreadsheet. And you can imagine that not all columns are related to all columns.
For example , the Date of Birth field may not be relevant to the Warehouse department which is only responsible for inbound and outbound sales order.
When one of the staff mistakenly erases the value of a field, no one will have any idea who damages the Spreadsheet and makes the Hygiene Spreadsheet never be good.
In order to build a Business Process Management System, following stacks should be considered: