Easy step by step tutorial

Whatever table you have, create pivot tables in Excel It can be very useful and you don’t need to be an expert to learn how to handle them. It may be a bit confusing at first to understand what they are for or what their function is but we will explain it step by step.

What are pivot tables?

Pivot tables are Excel tables, as the name implies, that help us in case we have large amounts of data or information. What it allows us is to filter exactly what we need and not see all the fields in the table at all times and add them automatically.

They can be difficult to understand but it allows us to know how long a single person has worked, how many sales a product has had, how many calories a customer has burned. Its main purpose is summarize and analyze data from the entire table and of all the fields automatically without you having to do it manually. Each one can add their information, in the case of a collaborative work or Excel, and you can transform it into a dynamic table to have a result every week, every month.

It allows you to use it both in the workplace (how many minutes has someone worked, how many visits has a topic had, how many packages have they sent) or in the personal sphere (how many calories have you burned in a day, how many books have you read, spending on purchases , expenses of a shared flat …)

Uses pivot tables

As we say, there are multiple uses for pivot tables And we will tell you about some common uses that we can think of if you want to put into practice the knowledge learned in this tutorial:

  • Expenses of a shared flat
  • Books read by each student in class
  • Minutes dedicated to a job or job
  • Calories burned by you and your friends / partner
  • Expenses made on purchases and what
  • Packages sent or received
  • Visits of a blog article or news
  • Products sold of each type
  • Absences or class attendance
  • Etc

Create pivot tables

To create pivot tables in Excel the first thing you will have to do is have a conventional book in the Microsoft program. You will have to have all fields organized and filled to be able to do the final analysis. Once you have the table, select it with the computer cursor.

Excel Pivot Table

Please note that there can be no empty row or column in the data so that the new item is created successfully. If you drop something, it won’t work out. You can also select only the cells that interest you to create them and you don’t need to do the pivot table of all the information it allows to do it from a single part.

Excel dynamic tables

Selecting what interests you, go to the menu at the top and tap on “Insert”. Different options will open, but choose the first one: Pivot table. A pop-up window will now appear where it will allow you choose “table or range” and where you can decide where to put the pivot table report: in a new spreadsheet or in an existing spreadsheet. If you want it to be done on a new sheet, tap on this option. If you want it to be in the existing spreadsheet, select the cells where you want to place this summary, in a blank space in the table.

Create pivot tables

When you have already created the pivot table report you will see a menu on the right where you can choose the different fields you want to display. Filters, columns, rows and values ​​will appear. You can tap on the different filters so that you change the result and the values ​​you see on the screen and thus get an exact summary of what you want.

Excel dynamic table filters

Pivot Table Fields

From the right side of the menu we will access Pivot table fields. From here we can select or deselect which fields we can add to the report of all available but we can also drag them from one section to another to modify the aspects of the table.

That is, you can convert a row into a filter to be able to change the person’s data, for example. Different options will automatically appear in rows But you can pass them to filters if you want that the data of a person does not appear at that moment and that you can filter by user, by author or whatever the tag you have added to that column of your table. You can change it as many times as you want to go consulting the values.

Update currently

You can have a table update automatically without the need to continuously press “update” when you open the Excel workbook.

  • Tap on any part of the pivot table
  • You will see that the settings and options appear in the menu
  • Tap on the “analyze” tab that you will see at the top
  • Open the options, they will open in a popup window
  • Go to the “Data” tab in the top menu
  • Check the box “Update data when opening the file”
  • Confirm with “Accept”
  • Automatically the created fields will be modified

Automatically update data in pivot table

Update manually

To update manually when you choose, just tap anywhere on the pivot table to bring up the tools at the top. Press “Analyze” and you will see an “Update” button. Tap on it and the fields will have the latest new data you added.

Update all

Leave a Reply

Your email address will not be published. Required fields are marked *