What is a Pivot Table?

Pivot Table

If you're a beginner or an intermediate Excel user, you may have definitely heard about Pivot Tables in Excel. This tool is one of the incredibly important part of any analyst's Excel toolkit, and appears frequently in Excel-based job interviews like the Uber Analytics interview. But what is a Pivot Table, and how it helps Excel Users analyze data more effectively? In this article, we'll go through the basics of Pivot Tables, explaining what they are and how to use them in daily practice. Simply defined, a Pivot Table is a tool built into Excel that allows one to summarize large quantities of data in a quick and easy way. Given an input table with tens, hundreds, or even thousands of rows, Pivot Tables allows one to extract answers to a series of basic questions about your data with the minimal effort. That may all seem a bit abstract, so let's dive into a real-world example, so as to get an idea about what a Pivot Table really is.

Defining the problem

Have a look at the below spreadsheet, that contains an individual list of SnackWorld's sales to customers sorted by date and item type. The below example only has twelve rows, but it is important to note that what we are about to discuss, also applies to much bigger spreadsheets — our sheet could just as easily contain hundreds or thousands of rows, making it much more difficult to analyze them via conventional means.
Let's say that a Snack World analyst is tasked with summing up the total dollar sales per individual customer. The task is urgent, as the Snack World CEO wants to see the data for an upcoming presentation that is to start in five minutes. How can the analyst complete the task in a quick and easy way? Here are some options from techniques and formulas we have learned till now:

She can add each customer's sales up by hand:

But we know that this approach is inefficient, time-consuming, prone to human error, and infeasible if the sheet in question contains hundreds or thousands of rows.

She could apply a SUMIF formula:

A SUMIF formula would help the analyst sum up sales customer wise. But it would take a lot of time to write the formula, and our analyst might miss a customer or two if the list is particularly large. Neither of the above options will work efficiently for our analyst. Enter the world of Pivot Tables. This tool will allow her to quickly sum up sales by customer with minimal effort. In the following Blog, we will learn:
  • Examples of Pivot Table and
  • Its Potential Uses: In Both Commercial and Non-Commercial Applications

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Websete : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198

Comments

Post a Comment

Popular posts from this blog

What is Pivot Table Example?

Image
So, how does using a pivot table to perform large scale enterprise business calculations look like? Check out the below example, in which we have used the Excel tool named Pivot Table to complete the task mentioned in the earlier blog i.e. Summing sales in dollars by customer. We were able to prepare the report in a matter of few seconds based on the input data tab. Take Note of a couple of things: it has captured the data we wanted (the sum of the Total Price column, segmented by customer); it has included a comprehensive list of customers on our input sheet; and it is ready for flexible adjustments and modification (see the Pivot Table Field List dialogue on the right-hand side of the screen). We have solved our analyst's problem. Potential uses of Pivot Tables At higher levels, Pivot Tables are more effectively used to summarize the data. Beyond that, they are infinitely flexible, and applicable to a huge variety of situations. Here are some of the more example scenarios
Read more

What are the various Formatting options available in Excel?

Image
We can have a look at the various formatting options available in Excel by selecting a cell that contains a number and from thereon heading towards the Number formatting drop-down on the Home ribbon tab: Following are the most used formatting options that are available in Excel: Number. Excel would format your input as a generic number with commas in-between the thousands digits. Currency. Excel would format your input with a currency sign in front of it and two trailing digits after the decimal point. Accounting. Similar as the Currency format, but currency sign will align to the left-hand side of the cell. Date. Excel would format the given input as a date. Note that dates in the Excel are displayed as numerical quantities. Time. Excel would format the given input as a date or time. Times are also displayed as numerical quantities in the Excel. Percentage. Excel would format the given input as a Percentage. Like, an input of 0.76 will be read as 76%. It is important to not
Read more

How to do Sorting or Alphabetizing in a custom order?

Image
The traditional sorting methods that we've been using so far present a small problem: what happens if we want to order our list chronologically month wise? Our month names have been entered as text strings rather than dates, so alphabetization will not work: if we sort from A to Z, February will come first, then January, followed by March. And if we sort the other way, March will come first, then January, followed by February. How can we get Excel to sort chronologically month wise without alphabetizing? The answer is simple: with the use of a Custom list. Try selecting your data table and opening the Sort dialogue once again: In the Column section, we will select Month, and in the Order section, we will select Custom List. When we do that, a new dialogue opens: This screen allows us to specify our own custom order of columns by typing them into the List entries section like so: Once you have done this, hit OK to confirm our custom sort and then OK again to perform the s
Read more