How to Make Dynamic & Customized Pivot Tables in Excel  (FREE Training Video Included) 

How to Make Dynamic & Customized Pivot Tables in Excel

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.

Pivot Tables: Your New Favourite Excel Tool & How to Use It

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!

Everything You Need to Know About Creating & Customizing Pivot Tables in Excel

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?

What Does a Pivot Table Do?

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:

• Row Labels
• Values (This is usually something you can quantify like dollars or units, but you can also count data that is not in a numeric format)
• Column Labels
• Report Filters

How to Prepare Your Data for a Pivot Table

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:

• Organize the data into rows and columns.
• Make sure there are no blank rows or blank columns – blank cells are not an issue.
• To use Pivot Table features effectively, you should have similar data in each column.
• To help Excel, unique column headings should be formatted differently than the data. This helps Excel to automatically recognize your data site rather than having to highlight what could be hundreds or thousands of rows. Formatting differences are quick and easy to implement and could include something as simple as making the column headings bold or centered.
• Essentially, you want to create an island of data. So, if there are title rows or other introductory parts of a data source worksheet, make sure there is at least one blank row separating that from the data that you want to analyze with the Pivot Table.

Step-by-Step Instructions for Creating 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.

• To get started making a Pivot Table, your cursor simply needs to be in one of the cells of the data source worksheet.
• Next, choose the Insert Tab. From here you’ll have a couple of options.
• First, there is the Recommended Pivot Table button. This is something you might want to explore once you’ve created some of your own Pivot Tables. This feature saves frequently used Pivot Tables and it will include some helpful suggestions for quickly streamlining and automating the creation of customized Pivot Tables.
• However, to get the hang of the process yourself, your other option is to select the Pivot Table button and create your Pivot Table from scratch.
• When you select the Pivot Table button, the Create Pivot Table dialogue box will automatically pop up. This is where you can choose the data source you want to use to create your Pivot Table.
• Keep in mind that if your data is formatted properly, Excel will be able to recognize what your data source is. To verify that Excel has highlighted all of the data you want to be included in the Pivot Table, look at the data source worksheet for the marquee or dotted line that surrounds all the highlighted data. Has Excel highlighted the correct range of data you want to analyze? If so, you’re good to go.
• Next, select the New Worksheet option to create your Pivot Table in a new Excel worksheet. The new worksheet will populate automatically after you click OK.
• Now you’re ready to build the structure of your Pivot Table.
• On the right-hand side of the worksheet, you will see your Pivot Table Field List. As you navigate this pane and choose different fields to include, you will see that the Pivot Table will begin building itself on the left-hand side of the worksheet.
• To start creating your Pivot Table, start by choosing the fields you want to be included in the Pivot Table. Different fields might include data like ‘Regions’, ‘Locations’, ‘Sales Representatives’, ‘Quarterly Sales Figures’, ‘Total Sales Figures’, and more.
• Remember, you choose fields based on what you want the Pivot Table to do. For instance, if you wanted to see a summary of the total sales data for different regions, you would select ‘Regions’ and ‘Total Sales’ as the primary fields in your Pivot Table.
• As you add fields, the Pivot Table will arrange itself to summarize data correctly. For instance, if you select ‘Regions’ and ‘Total Sales’ as your fields, the Pivot Table will automatically put the ‘Regions’ field in the Rows quadrant while the ‘Total Sales’ field will automatically move into the Values quadrant since the data has a numeric value.
• It’s that simple. With just a few clicks, you’ve already taken source data and transformed it into a useful and streamlined Pivot Table.

Formatting Options for 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.

• For instance, let’s say your original data source had values presented with an accounting format. Your data will not show up in this format in the Pivot Table. This is actually a good thing because it means you can customize how you want to display your data. In short, your data does not need to be formatted in the same way it is formatted in your data source.
• To customize the formatting of your Pivot Table Values, it might seem easiest to just go to the Home Tab and use the standard formatting options available to you. However, these standard options won’t always stay with the Pivot Table as you continue making changes.
• Here’s a quick trick for formatting your Pivot Tables as you go. Move to one of the cells that you want to format. Then, right-click and select Number Format.
• Here, you can choose from a variety of formatting options to ensure the Pivot Table data is presented in the way you prefer.
• The great thing is you don’t have to do this step individually for each cell. Whatever formatting options you choose in one cell will then be applied to all the Values data in your Pivot Table.
• Here’s where the real value and power of using a Pivot Table comes in. Let’s say you want to add another field to your table that currently summarizes total sales figures by region. For example, let’s say you want to add the ‘Department’ field to present the breakdown of total sales figures not only by region but also by department.
• To do this, go back to the Pivot Table Fields List on the right-hand side of the worksheet and add the ‘Department’ field to your Pivot Table.
• This additional field will automatically drop into the Rows section of your Pivot Table. You’ll now see a Pivot Table that has a variety of subtotals summarizing the amount of total sales made by each department, in each region.
• Here, you can also test out the ability to turn the data on its side. Although the data might be presented exactly how you want it to be already, it’s good to know that you have the option to format it differently based on your preferences.
• On the right-hand side, go back to the Pivot Tables Fields List and go down to where the ‘Department’ field is. As you hover over the ‘Department’ field, you will see the four-point arrow appear. From here, you can drag the field to wherever you want it to appear on the Pivot Table. For instance, let’s say you drag and drop it into the Columns section. This will change the layout of your table but will still provide a summary of total sales data for each region, and the breakdown of subtotals for each department in each region.
• These are just some of the ways you can format your Pivot Tables for optimal data organization, comparison, and analysis. Think of all the ways you might be able to summarize and present data from the variety of source-data worksheets you use every day.

Easy Ways to Optimize Your Pivot Tables in Excel

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.

Adding Extra Data Fields to Pivot Tables

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 Refresh Button

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.

Easy Ways to Sort & Filter Pivot Table Data

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

• PIVOT TABLES ANALYZE TAB – Remember that when you’re using Pivot Tables you can use the Pivot Tables Analyze Tab to access important functions and features. This is where you will find the important Refresh button and a variety of additional options for building and customizing Pivot Tables.
• PIVOT TABLES DESIGN TAB – There are also lots of Pivot Table options under the Design Tab. This is where you can customize the way your data looks by changing colours, sizes, table formats, and more. For instance, you can choose from different Pivot Table styles and can explore everything from dramatic and bold options to more simple and basic styles.
• SUBTOTALS & GRAND TOTALS & REPORT LAYOUT TABS – You also have the option to change how subtotals are shown by clicking the small arrow under the Subtotals Tab. Additionally, you can choose how and when to include grand totals by clicking the small arrow on the Grand Totals Tab. Finally, you can choose the way you want your report laid out by choosing the small arrow under the Report Layout Tab.
• MULTIPLE PIVOT TABLES FROM ONE DATA SOURCE – It’s also good to know you can have more than one Pivot Table pulling from the same data source. Each Pivot Table is created as a separate worksheet, that you can easily rename and move within the workbook
• EXTRACTING CERTAIN ROWS FROM DATA SOURCE – This is one of our favourite tricks to use with Pivot Tables. Have you ever needed to extract only certain rows from your data source worksheet? You can do this by copying and pasting the worksheet and then manually sorting out and removing the columns you don’t need. However, this is much easier to do once you have a Pivot Table to work with.

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

• COLLAPSING GROUPS – Keep in mind that when your Pivot Table has multiple rows, data will be organized in groups.  For instance, in the table created in the video, you’ll see a minus sign beside the ‘Regions’ field. This button gives you the option to collapse groups and hide the location subtotals. You can do this individually for each group and if you want to uncollapse a group, simply go back and click on the plus sign to have the hidden details reappear.

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.

Ready to Take Excel to New Heights? We Can Help!

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 info@fuellednetworks.com, or visit our website at www.fuellednetworks.com to chat with a live agent and book an Excel consultation.