Learn How to Use Pivot Tables in Excel – Our Top 5 Posts

PivotTables are one of Excel’s most useful features. If you’re a little unfamiliar with them, you’re in for a treat! PivotTables are powerful tools for calculating, summarizing, and analyzing data. They allow you to spot similarities, patterns, and trends – especially useful for anyone who works with large amounts of data on a regular basis. Once you know the basics of how to use pivot tables in Excel, so many new (and efficient!) doors are opened.

If you haven’t used the PivotTable feature before, you get plenty of tutorials and fun, hands-on practice with an Excel University Campus Pass. 

Since we work with PivotTables all the time, we wanted to give them a special shout out and highlight the posts that Excel enthusiasts have appreciated the most!

Without further ado, these are our Top 5 most popular PivotTable posts. 

1. Edit PivotTable Values

Did you know that you can edit the value in a PivotTable? Chances are, you’ve tried to do just that and received an error message. If that’s a problem you run into on a regular basis, you’re about to learn how to fix it. 

In this post, we use a quarterly bonus scenario to demonstrate how someone would export payroll data, store it in a table, and then create a PivotTable to provide the totals. Then, you’ll learn how to add a Calculated Item and have it compute a value to populate the “quarterly bonus” field in our example. 

Once you learn how to edit the values, you’ll open up even more possibilities with PivotTables!

2. Impossible PivotTables 1 – Calculated Fields

Are you familiar with Power Pivot? If not, you’re missing out! This post shows how to use Power Pivot to create PivotTables that are either impossible or require workarounds with regular PivotTables.

Essentially, Power Pivot combines the formula-based report’s mathematical power with the PivotTable capability. It enables you to create PivotTable reports without the need for the previously mentioned workarounds. As a consequence, you’ll have a clear, dependable report that’s simple to edit and maintain in the long run. 

To learn even more, check out the rest of the series – Impossible PivotTables 2 and Impossible PivotTables 3 – or sign up for a free webinar!

3. Show PivotTable Months without Data

Have you ever needed to build a PivotTable report that groups by month? If so, you’ve probably noticed that the PivotTable only shows the months that have data in the source. The PivotTable will summarize the data that is currently available, and if no transactions are available for a certain month, then the month won’t be displayed. 

A popular workaround is to insert empty data rows for each desired month into the data source. But this isn’t necessary, and there’s a much more efficient way to go about fixing the problem!

In this post, we go over a simple option that will make Excel show all months, including those with no underlying data transactions.

4. PivotTable Calendar

Excel has plenty of date-related features and functions, and in this post, we demonstrate how to create a graphical calendar using PivotTables. 

We break the process down into several easy-to-follow steps. First, you’ll learn how to create a date table that keeps track of the dates for the periods you’d like to see in your calendar. Next comes making the PivotTable, then formatting it, and then you’re on your way to creating a responsive graphical calendar! Even if you don’t use them on a daily basis, the tools that allow us to create one can be used in a variety of other circumstances.

5. How to Build a PivotTable with the Data Model

PivotTables are a fantastic feature of Excel, but they do have limitations. Luckily, this post addresses those limitations so you can keep geting the most out of Excel.

When you utilize a data model instead of a single Excel table, many of the traditional constraints are removed. If you want to learn how to create a PivotTable utilizing the data model (and find out what the data model is), this is the post for you!

We’re always finding cool new ways to use PivotTables in Excel. If you know of any other useful PivotTable tips (or want to share your favorite post), leave us a comment!