Excel is the trusted spreadsheet solution used by professionals to track, organize, and analyze critical business data. Excel Pivot Tables is a streamlined and dynamic way to summarize and present different data fields. Want to start using and making the most out of Excel Pivot Tables? We’ve got you covered with this detailed training guide.
When it comes to storing, tracking, organizing, and analyzing data, Microsoft Excel is the trusted spreadsheet solution for the majority of clients we work with. Excel is a familiar and powerful solution that helps professionals teams manage and master data of any kind. Our team of IT professionals works with businesses and organizations in every industry and across the board, Excel is a trusted favourite.
From our experience, however, many professionals are just getting by and are only using the bare minimum that Excel has to offer. Excel is designed with a variety of built-in tools and features that can help streamline processes and optimize the way data is stored, organized, and analyzed. We love helping clients tap into these features in order to save time and effort with Microsoft Excel.
One of the most strategic built-in tools is known as Excel Pivot Tables. A Pivot Table is an interactive Excel table that strategically summarizes, organizes, and compares large amounts of data from an existing Excel worksheet. Pivot Tables make it simple for professionals to quickly slice and dice data and examine it for similarities, differences, and important patterns.
We decided it was time to put together a training guide that will help professionals get comfortable with the key steps involved in creating, formatting, and customizing Pivot Tables in Excel. We’ve even included a FREE training video to help you master each and every feature we outline in this guide. Ready to become a Pivot Table data master? Let’s get started!
In our following instructional guide, we include a variety of tips and tricks that will help you customize and optimize every Pivot Table you create in Excel. But before we get into the optimizing tips, let’s go over the basics. What is a Pivot Table and how do you create one in Excel?
A Pivot Table organizes and presents information from an existing data source worksheet. Each section of your Pivot Table represents a different field or category of information. You assign different types of data to each section of the Pivot Table based on how you want the data organized and presented.
To create a Pivot Table, you simply drag & drop source data into 1 of 4 areas on the Pivot Table. While every Pivot Table doesn’t need to use all four sections, they include:
Before you create your Pivot Table, it’s important to make sure your source data is set up properly. If you already have a data source that you are successfully storing and filtering, then your data should be ready for creating Pivot Tables.
Here are the main guidelines you want to follow to ensure your data is ready to be put into a Pivot Table:
Now that you understand what a Pivot Table is and how to prepare your data before creating one, let’s go over the steps involved in bringing your Pivot Table to life. Here are some step-by-step instructions for creating Pivot Tables in Excel.
Now that we’ve gone over the basic instructions for creating them, let’s dive into some of the different ways you can format and customize your Pivot Tables in Excel. Depending on how you want the Pivot Table to present your data, there are a variety of things to consider when it comes to formatting choices.
Now that we’ve covered the basics, let’s go over some of the more detailed tips and tricks you can use to customize and optimize your Pivot Tables from top to bottom. This final section will include important tools you can use and some general things to remember to ensure your Pivot Tables are always functioning at optimal capacity.
We’ve already talked about adding a third field to an existing Pivot Table, but it’s important to know that you insert even more fields to add a bit more complexity to your Pivot Tables. For instance, let’s take the Pivot Table we were discussing above that presents total sales data organized and broken down by region and department. You can also add an additional field like ‘Location.’
Usually, the ‘Location’ field will drop in right below the region field where it belongs, but always keep in mind that you can change the way fields are organized and presented in your Pivot Table based upon the hierarchy you have within your data. The great thing is that in just a few minutes you are able to create an attractive table that summarizes total sales data by region, department, and location. Without Pivot Tables, sorting and filtering the data manually this way would have taken hours.
The most important thing to remember when creating Pivot Tables is that changes made to source data do not reflect automatically in the Pivot Tables you have created for that data. The problem here is that you could have a great looking Pivot Table that presents and breaks down data nicely, but if the source data has been changed, your Pivot Table values may not be up-to-date. This can create miscommunications and miscalculations that you want to avoid at all costs. You never want to be making decisions based on incomplete or outdated data.
That’s why if you remember one thing from this training, it’s that you must remember to refresh your Pivot Tables. Here’s how to do it. In the Pivot Table Analyze Tab, you’ll see an option for Refresh. Keep in mind that there is also a short-cut you can use to refresh (Alt + F5). By clicking Refresh or using the keyboard shortcut, your Pivot Table data will update to include the most recent changes made in the source-data worksheet.
Keep in mind that if you’re working with more than one Pivot Table, you also have the option of choosing Refresh All simply by selecting the small arrow under the Refresh button. Above all, just make sure that pressing the Refresh button is instituted as a best practice when working with Pivot Tables so you prevent creating tables or making any decisions based on outdated or incorrect data.
Also, it’s good to know that if you have a data source that is continually changing because you’re often adding rows or values to it, you can use the Change Data Source button. This allows you to change the range that Excel is using for the data source to make sure all your relevant and up-to-date data is included in the Pivot Table.
Now, let’s look at some of the options you have for filtering and sorting data in your Pivot Table. For each Pivot Table Field, there is a drop-down menu. For instance, in your ‘Region’ Field, let’s say you only want to see the data for certain regions. Simply click the drop-down menu and select only the regions you want to see reflected in the Pivot Table. Then click OK and the Pivot Table will present only the regional data you want to see. You can use the same drop-down menu to clear the filter and restore all the original data in your Pivot Table.
Keep in mind that you can also sort data in your Columns. Let’s say your Pivot Table has sorted data by region and then by location within those regions. But now, you want to sort it by the numerical values. To do this, simply go to the cell you want to sort, and then right-click, choose Sort, and then select how you want to sort the numerical data (like from lowest to highest, for instance). Then your data will be sorted based on those values within each group, rather than alphabetically by region and location.
Finally, let’s talk about the Report Filter. From here you can make additional changes to how your data is presented and organized in the Pivot Table. You can remove certain fields altogether or you can change the position of certain fields depending on how you want the data presented. You can even move certain fields directly into the Report Filter on the left-hand side so it can be used to strategically filter how you see data presented in the Pivot Table.
As you continue working with Pivot Tables, know that filtering options are also available using slicers and timelines, meaning you can create custom calculations and even build Pivot Charts.
Important Pivot Table Tools to Remember
Let’s take the hypothetical table we’ve been talking about. Let’s say you want to see where the data for a particular location comes from. Simply move to one of the values for that location and double-click. This will open an entirely new worksheet that will list all the records that contributed to the values for that location. It’s really that simple.
Keep in mind though that these extracted rows are not linked back to the original data source so they will not reflect any changes that haven’t been refreshed into the Pivot Table. However, if you remember to refresh, this is an incredibly easy way to quickly bring important source data to attention whenever you need it by extracting it directly into another worksheet or even an entirely new workbook if necessary
You can also collapse all groups at once. To do so, highlight a group cell, go up to the Pivot Table Analyze Tab, and select the Collapse button on the left-hand side of Group Selection. This will collapse all the groups at once. Collapsing groups gives you a different way to summarize and view the data, while still maintaining the structure of your Pivot Table and the ability to expand and present additional data, subtotals, and breakdowns as needed.
Use this detailed instruction guide and training video has helped you come to understand the different ways you might make use of Excel Pivot Tables in your day-to-day operations. There’s no denying the power and flexibility of Excel Pivot Tables. Think of all the ways your team might save time and effort by using the built-in tools and customization features that Excel has to offer.
We’ve really only scratched the surface when it comes to Excel optimizing tools and time-savers. That’s why we recommend reaching out for additional guidance and support from a team of Microsoft experts. The right team of professionals can help you make sure you’re getting the very most out of Excel and every other Microsoft solution you use. When in doubt, call in the pros!
Ready to work smarter, not harder with Excel? Give us a call anytime at (613) 828-1280, drop us a line at email@example.com, or visit our website at www.fuellednetworks.com to chat with a live agent and book an Excel consultation.
Published On: 13th February 2021 by Ernie Sherman.