One of Microsoft Excel’s most potent—and intimidating—features is the pivot table. Powerful in that it may aid in the summarization and understanding of big data collection. It’s intimidating since pivot tables have a history of being difficult and you’re not exactly an Excel pro.
What is a Pivot Table in Excel?
To put it another way, pivot tables interpret the apparently infinite tangle of statistics on your screen. Additionally, it enables you to arrange your data in various ways so that you may more quickly come to insightful conclusions.
What is the use of Pivot Table in Excel?
The goal of pivot tables is to provide simple methods for quickly summarising vast volumes of data. They may be used to more clearly comprehend, present, and evaluate numerical data in depth as well as to find and respond to any unforeseen questions that may arise.
Here are seven fictitious situations where a pivot table may provide an answer:
- comparing the overall sales of various items.
- displaying product sales as a share of overall sales.
- merging redundant data.
- calculating the number of employees in various departments.
- Giving empty cells default values.
How to make a pivot table in excel? 5 Steps
To create a Pivot Table in your Excel Sheet, follow these 5 steps below:
Step 1) Enter the range of Rows and Column
Every pivot table in Excel begins with a fundamental Excel table, which contains all of your data. Simply insert your data into the designated rows and columns to build the table. To group your values according to what they stand for, use the top row or column.
A column listing each “Top Page,” a column listing each URL’s “Clicks,” a column showing each article’s “Impressions,” and so on may be included in an Excel table with blog post performance statistics, for instance. (In the stages that follow, we’ll use that example.)
Step 2) Sort your Excel for a particular attribute
Click the Data tab in the top navigation bar and choose the Sort icon from the options that appear. You may choose to arrange your data by any column and in any order in the window that opens.
Choose “Views to Date” as the column heading under “Column,” for instance, and then decide whether you want to sort your posts from smallest to largest or from largest to smallest.
You may successfully rearrange each row of your Excel sheet by the number of views each blog post has got by selecting OK in the Sort window’s bottom-right corner.
Step 3) Select the Cells for which the Pivot table needs to be created
After entering the data and sorting it in your Excel worksheet as you choose, select the cells you want to include in a pivot table by highlighting them.
Select the PivotTable icon by clicking Insert in the top menu. You may also manually input the range of cells you want to be included in the pivot table by clicking anywhere on your worksheet, choosing “PivotTable,” and doing so.
This will open an option box where you can set your cell range and decide whether to launch this pivot table in a new worksheet or retain it in the current worksheet in addition to choosing your cell range.
At the bottom of your Excel worksheet, you may navigate to and away from any newly opened sheets. After making a decision, click OK.
To open a pivot table with pre-set recommendations for how to arrange each row and column, you can also pick Recommended PivotTables to the right of the PivotTable icon while your cells are highlighted.
Step 4) In the Row Labelled area, Drag and Drop the field required
When Step 3 is finished, Excel will produce a blank pivot table for you. The next step is to drag and drop a field into the Row Labels section with a label that corresponds to the names of the columns in your spreadsheet.
By doing so, you may choose the distinctive identifier—blog post title, product name, etc.—by which the pivot table will arrange your data.
For illustration, suppose you wish to categorise a lot of blogging data according to article titles. Simply click and drag the “Top pages” box to the “Row Labels” section to do that.
Step 5) Drag and Drop the field in the Values area, then Calculate
The next step is to add some values by dragging a field into the Values box once you’ve decided how to organise your data.
Let’s use the blogging data example again and imagine you want to sum up views of blog posts by title. Simply drag the “Views” field into the Values box to do this.
By default, the total of a certain number will be computed, but you can simply alter this to anything else based on what you want to calculate, such as average, maximum, or minimum.
Pivot Table in Excel Shortcut Keys
The Insert PivotTable dialogue box will pop up once you pick any cell in your data collection and hit the shortcut keys ALT + N + V. From here, you can select where to place the new pivot table.
Use the shortcut keys ALT + D + S to rapidly change the data source for an existing pivot table. By doing so, the Change Data Source dialogue box will pop up, allowing you to choose a different data source for the pivot table.
Use the shortcut ALT + F5 to rapidly refresh all pivot tables in a spreadsheet. If you want to verify if a workbook with several pivot tables has been updated, doing so can save you a tonne of time.
You now know how to create a pivot table in Excel. With this knowledge, you may determine what you want from your pivot table and locate the answers you seek.
The data in your pivot table, for instance, might not be arranged in the way you’d prefer. Excel’s Sort tool can be useful in this situation. The VLOOKUP function, on the other hand, may be useful if you need to include data from another source in your reports.