Microsoft pioneered an excellent tool to create, analyze, store and manipulate data. We know it by the name of Microsoft Excel and true to its name; it is one of the greatest spreadsheet application in the Microsoft Office suite.
Initially, it worked fine from evaluating simple expenses for analyzing complex data. Today, the latest version – the Microsoft Excel 2016 is an excellent option but also potent tool for data analysis for business insights and watching trends.
For making better rational decisions, you not only need to process data quickly but also effectively. But the rapid accumulation of data can become overwhelming and can leave you gasping. In no time, you may feel lost, and you have a burden of extensive data to compile.
The magic lies in this Excel tool with one of its features like Pivot Table coming to play. It helps you exploit and play with the data stored in the cells. Pivot tables will help you utilize its prowess of data analysis, exploration and summarization to present it in a manner which is easy to comprehend.
What is a Pivot Table?
As said earlier, a pivot table in MS Excel is a tool to summarize, explore and analyze massive scattered data. The summarization takes just a few clicks, and the results are there for your comprehension.
The pivot tables are flexible and can be modified or presented at your choice. You can customize and adjust the way you want, and for as many cells you want. It is ideal for calculating, evaluating and displaying information in tables and breakdowns to the scale you need.
Pivot charts can also be created based on pivot tables. These charts will automatically be updated when your pivot tables get updated. Many times it can unravel the hidden facts buried under your data.
The images shown below is a pivot table and a pivot chart –
How to create a Pivot Table?
- The data in the sheet should be in a tabular format, and no blank row or blank column should be left.
- Data Types of the data in the columns should be the same. You should not mix numbers, date, currency, and text in a single column
- If you alter data in your Pivot Table, your actual data will never be changed as Pivot Table works as a snapshot of the original data.
Creating Pivot table is very easy, you need to follow the below steps –
- Select the table of data or cells from your data table. The cells should include the headers. You can select the entire table or some cells from the table to create a pivot table.
Like in our example we want to see which salesman has how much order amount each month. So, we select our fields as shown –
This gives a PivotTable like this –
The selected and checked fields add to the Row, and if you want to see a specific field in the column as we did, you need to drag it to the Column area below.
The columns with the data for each row will be updated and refreshed according to the Rows.
Here we have deselected the three rows from the table, and we get the table without these rows when we click on OK.
We have unchecked January from the table, and we can see data for only February and March in the table –
Filter for Region has appeared here-
Now, suppose we want to see data for only East and North region, we can uncheck the South and West regions from the filter and click OK.
So, the data has been filtered for only East and North regions –
Upon clicking OK, it creates a Pivot Chart or a graphical representation of your Pivot Table. You can drag and drop the chart at any place you want on the sheet. You also can increase or decrease the size of the chart quickly.
In our example, we have unchecked the North, South and West regions. The graph is now showing us data according to East region only.
You can provide the Table name that you want to add as a new data source –
What is a Recommended Pivot Table?
Recommended pivot table option enables you with an automatic pivot table. This option provides a template for creating Pivot Table. You can select any one of the types, change source data or create a blank pivot table in the Recommended Pivot Table Dialogue Box.
Recommended Pivot Table is an excellent functionality for those who have insufficient knowledge about Pivot Tables. After you created the recommended Pivot Table, you can then manipulate the filters like the Pivot Table you create manually. You also can change different orientations.
How to remove a Pivot Table?
If you do not need the Pivot Table anymore, you can select the entire table and press Delete. Make sure the whole table is selected; otherwise you’ll get an error message – “Cannot change this part of a table Report.
Create your tables in a manner of how you want your data to be displayed. Utilize the PivotTables and make your process simple. Invest your time and practice using the pivot tables and its uses and various data types.