Have you ever wondered how to make a Pivot Table and Macro in Excel? These are key tools that Microsoft Excel provides us with that significantly increase our productivity by making tasks much simpler and faster.
The purpose of the pivot table is to better accommodate the data that we have included in a spreadsheet. Additionally, it allows us to filter a large amount of information, temporarily hiding what we do not want to see at that moment.
The purpose of a macro is to allow us to automate the tasks we do constantly. It is a set of instructions stored in an Excel file that can be executed whenever necessary.
Below we will show you step by step how to create and use them in your daily life.
PivotTable in Excel
Step by Step to Create a Pivot Table
- Click on any cell in the data table that you want to consider in the new pivot table .
- Now select the PivotTable command found in the Tables group on the Insert tab.
- The Create PivotTable dialog box will appear. If necessary, you can adjust the data range to be considered in the pivot table.
- In this same dialog box you can choose whether you want to place the pivot table in a new Excel sheet or in an existing one. Click the OK button and the new pivot table will be created.
A blank table will appear and, on the right side, a panel to configure the pivot table by adding the fields we need. The table is modified automatically , something that with a normal table we would have to do manually, that is, the pivot table is linked to the original, so that, if we add or modify the data, we can update our pivot table so that the changes are reflected.
Customize the Pivot Table
- You can activate and deactivate fields
- Set data as rows or columns manually
- In the case of numerical data, dragging them to Values will allow us to perform common operations such as adding all the data in a list.
By playing with these elements, from a single table we will obtain different combinations depending on the response we want to satisfy with the information we see on the screen.
20% EXTRA DISCOUNT
Get started today and get fully certified in Power BI with our course
- 100% online at your own pace
- practical exercises
- Lifetime Access
- certified endorsed
Apply the coupon [DARE] and get an extra 20% discount for only 100 students. Click the button and join!
Grouping Data
If we have too much data in our table, we can evaluate whether some of it is more useful combined. If so, we can use the data grouping option provided by pivot tables.
You can do this by right-clicking on a cell in the column you want to group, and you will see two options: Group and Ungroup. Keep in mind that to group the data , they must be equivalent , such as those related to time periods .
If the result is not as expected, we can always go to Ungroup and get the opposite result depending on the configuration we give it.
FREE DOWNLOAD
Let us give you a hand! We have designed a Pivot Tables guide that will make you stand out in your environment. If you want to know more, download our free Ebook on Pivot Tables in Excel and enjoy learning with all the information we have for you. Master the full potential of Pivot Tables in Excel in a practical and simple way.
Segmenting Data
Since we have a large amount of data in our pivot table, it will be easier for us to filter that information using segmentations .
To do this, go to Analyze and then Insert a data segmentation, select the fields where you require these segmentations or filters to better control a large amount of data.
Dynamic Charts
While pivot tables help us understand, analyze, filter, segment and display information in a much more digestible and organized way, the truth is that graphics give it a special touch to visually display the data.
To generate dynamic charts, we only need to access Analyze > Dynamic Chart . All available chart models will appear, organized by category, such as column, bar, pie, radial, among others.
Dynamic charts are handled in the same way as tables, using the panel located on the right-hand side, which, if not visible, can be retrieved from Show > Field List . By modifying values, columns and rows we change the elements that appear in the chart.
Becoming an expert using Excel IS possible. We invite you to join our: Complete Excel Specialization , where you will learn to master this tool like an expert, making your work simpler and more productive.
Courses that may interest you
Do you want to learn more about financial tools? Check out our Advanced Dashboard with Power BI, Business Analysis with Excel and Python, and Python from Zero to Expert courses.
