Learn how to create pivot tables in Excel – WAU
For the management of a company to be done efficiently, it is necessary to organize the information well, in addition to generating reports that facilitate its decision making. Therefore, we teach in this content how to create your own pivot table. Check out!
But, after all, what is a pivot table? It is a summary of your data, grouped in a chart that allows you to generate reports and explore trends based on your information.
Pivot tables are particularly useful if the Excel tables you use in your company have long rows or columns with the values needed to track sums and easily compare with each other.
In other words, they derive meaning from this seemingly endless confusion of numbers on the screen. And, more specifically, they allow you to group your data in different ways so that you can make useful conclusions more easily.
The “dynamic” part of these tables derives from the fact that it is possible to view the data in them from a different perspective. That is, you reorganize them in order to reveal useful information for your business from them.
Do you already know how to use Excel and want to know more about this feature? Follow us and learn with the step by step that we separate below!
What is the use of the Excel PivotTable?
The pivot table is made up of rows, columns and data fields and pages. These components can be moved and help you to expand, isolate, add and group specific data in real time.
Thus, this table allows you to visualize the differences in a very large set of information. Therefore, it is very useful to organize a large amount of data in the spreadsheets used to chart the marketing and sales metrics, for example, among others of your company.
Here are the main benefits of using a pivot table. Look at that!
It’s easy to use
A pivot table uses a user-specified data field and converts the header of each column into an easier-to-manage option. Columns containing data can be removed, added or moved easily.
Here, friendly and informative summaries can be created from long spreadsheets of raw data. They can be summarized in several ways, including frequencies and averages.
As you can see, an advantage of pivot tables is that they are easy to use. You can easily summarize the data by dragging the columns to different sections of the table. The columns can also be rearranged as you wish with a click of the mouse.
Simplifies data analysis
With the help of Excel pivot tables, it is possible to manipulate large amounts of data at once.
These tables allow you to obtain a large number of them and work in a way that you can see only a few data fields. This helps in data analysis, even in large quantities.
Data summary help
Another important benefit of pivot tables is that they help to summarize data quickly and easily.
The table helps to make a concise summary of thousands of rows and columns of unorganized data. With the help of these tables, you can summarize large amounts of information in a small space.
The data can be summarized in a simple format that is easily understood. You can organize the rows and columns of the data according to your needs, as well as label and sort them the way you want.
How to put together a financial control spreadsheet that organizes your budget
Gantt chart: learn what it is, its benefits and how to make yours
15 Excel formulas and shortcuts you need to know!
Allows you to create data patterns
Excel pivot tables help you create custom tables from large groups of data. Manipulating them in this way will help to find recurring patterns in them. This, in turn, will assist in accurate data prediction.
Enables the creation of reports quickly
One of the most relevant features of Excel’s pivot tables is the ability to create reports efficiently. This saves you the long, strenuous hours you need to spend to create reports manually. Besides that, the table also helps provide links to external sources in the report created.
Favors quick decision making
A pivot table can be considered a valuable Excel reporting tool, as it allows users to easily analyze data and make quick decisions. This serves as a huge advantage in the industrial or marketing world, where it is crucial to make accurate and quick decisions.
How to assemble a PivotTable in Excel?
If you’re still feeling a little confused about what the pivot tables actually do, don’t worry.
This is one of those features that is much easier to understand when we see it in action. That is why, here are the 6 steps you must follow to create a pivot table.
1. Enter your data in a range of rows and columns
All Excel pivot tables start with a basic table in which all of your data is stored. To create this table, just insert your values into a specific set of rows and columns. Use the top row or the topmost column to categorize your values by what they represent.
For example, to create an Excel table of blog post performance data in your Content Marketing strategy, you can have:
- a column that lists each “URL”;
- one that lists “post title” for each URL;
- one listing “views to date”;
- and so on.
2. Sort your data by a specific attribute
When you have all the data you want to insert into your Excel spreadsheet, you must sort it in some way, to make it easier to manage after turning it into a pivot table.
To sort them, click on the “data” tab on the top navigation bar and select the “sort” function. In the displayed window, you can choose to sort your data by any column and order.
To sort your Excel spreadsheet by “views to date”, for example, select the title of that column in “column” and then select whether you want to sort your posts from lowest to highest or highest to lowest.
3. Highlight your cells to create your pivot table
After entering the data in your Excel spreadsheet and sorting it as you wish, highlight the cells you would like to summarize in a pivot table. Click “insert” along the top navigation and select the “pivot table” icon.
You can also click anywhere on your spreadsheet, select “pivot table” and manually enter the range of cells you want to include in the pivot table.
This will open an options box, in which, in addition to defining your cell range, you can select whether or not to start that pivot table on a new spreadsheet, or keep it on the existing spreadsheet. If you open a new spreadsheet, you can browse and exit it at the bottom of the Excel workbook. Once chosen, click “OK”.
Alternatively, you can highlight your cells, select “recommended pivot tables”, to the right of the “pivot table” icon, and open a pivot table with predefined suggestions on how to organize each row and column.
4. Drag and drop the fields into the “page fields” area
After completing Step 1, Excel will create a blank pivot table for you. Your next step is drag and drop a field – labeled according to the column names in your spreadsheet – to the desired position.
This will determine by which unique identifier – blog post title, product name and so on – the pivot table will organize your data.
5. Drag and drop a field into the “row fields” and “data fields” areas
After establishing how you are going to organize your data, the next step is to add some values by dragging into the “row fields” and “data fields”.
Continuing with the blog data example, let’s say you want to summarize the views of blog posts by title. To do this, simply drag the “views” field.
6. Adjust your calculations
The sum of a given value will be calculated by default, but you can easily change it to something like average, maximum or minimum, depending on what you want to calculate.
On a Mac, you can do this by clicking the small “i” next to a value in the “values” area, selecting the desired option and clicking “OK”. After making the selection, your pivot table will be updated accordingly.
If you are using a PC, you will need to click on the small inverted triangle and select the items you want to appear in the table.
You have now learned the fundamentals of creating pivot tables in Excel. But, depending on what you need for your pivot table, some small steps may be missing.
For example, you may notice that the data in the pivot table is not sorted the way you would like. If so, the “sort” function Excel can help you. Alternatively, you may need to incorporate data from another source in your reports, in which case the VLOOKUP function (also called VLOOKUP) can be useful.
As a matter of fact, for you who are already interested in organizing your business data using Excel, how about getting to know our marketing and sales metrics spreadsheet as well?