Don’t Get Stuck in a Pivot Table Rut

Pivot tables are a quick way to summarize your data, and they have another advantage too. After you build the pivot table, it’s easy to change the pivot table layout, to get a different type of summary. Watch the two videos below, to see a few examples.

Try Different Pivot Layouts

Pivot tables help you organize and understand your data. They make it easy to summarize tons of information quickly, and you can change the layout to see different types of summaries.

So don’t get stuck in a rut with your Excel pivot tables, by using the same layout, over and over again. Try new things!

After you create a new pivot table, try out different layouts to see which one works best. With just a few clicks, you can tweak the layout of your pivot table to showcase your data in a whole new light.

  • Tip: Make a copy of the original pivot table worksheet, so you can go back to that version later, if you want to!

To help you get started, here are two videos that show examples of pivot tables in action. Try something different today!

Video: Create Quick Pivot Table and Change Layout

In the video below, you’ll see the steps to create a quick pivot table with Excel’s Recommended Pivot Tables feature.

Then, make simple changes to the layout, to get a different view of your data.

Video Timeline

  • 0:00 Introduction
  • 3:04 Create an Instant Pivot Table
  • 1:26 Pivot Table Field List
  • 2:12 Add and Remove Fields
  • 2:51 Add a Column Field
  • 3:31 Add Another Value Field
  • 4:11 Move the Value Fields
  • 4:55 Reposition the Value Fields
  • 5:15 Change Your Pivot Table
  • 5:30 Get the Sample File

Video: Arrange Multiple Value Fields

If you have 2 or more fields in the Values area of the pivot table, Excel automatically creates a new field in the layout, called “Values”.

Watch this short video, to see how use the Values button, to move the data fields in an Excel pivot table. With these steps, you can change the pivot table layout from vertical to horizontal, or change from horizontal to vertical.

In the screen shot below, the pivot table is in Outline Form layout, and the Value fields have been moved to the Rows area.

pivot table Value fields in Rows area

Video Timeline

  • 00:00 Introduction
  • 00:21 Move Values to Row Labels
  • 00:44 Change to Compact Layout
  • 01:01 Move Values to Top
  • 01:10 Move Values to Column Labels

Get the Sample Files

You can get the sample files for these videos, and more about pivot table layout changes on my Contextures website.

____________________

Don’t Get Stuck in a Pivot Table Rut

Don't Get Stuck in a Pivot Table Rut

____________________

Excel GetPivotData Formula Love It or Turn It Off

Do you love the Excel GetPivotData formula, that appears automatically, when you click on a pivot table value? Or do you want to know how to turn that annoying setting off? In the two videos below, see how to turn GetPivotData off or on, and see an enhanced formula, that pulls data from a specific pivot table in your workbook.

Continue reading “Excel GetPivotData Formula Love It or Turn It Off”

Move Pivot Table Value Fields – Vertical Layout

When you create an Excel pivot table, and add values, the value fields are automatically arranged in a horizontal layout, with the headings across the top of the pivot table. To create a narrower, taller pivot table, you can move the Values, so they’re listed vertically. Watch the short video below, to see the steps.

Continue reading “Move Pivot Table Value Fields – Vertical Layout”

How to Select Specific Part of an Excel Pivot Table

If you’re working with an Excel pivot table, you might want to select a specific section, such as the subtotals, so you can apply formatting. The short video below has a few pivot table selection tips.

Video: Select Specific Parts of Pivot Table

Do you want to select one or two subtotal lines in a pivot table, and change those to a different font or fill colour? Perhaps you want to make one sales region’s totals stand out from the others, in a monthly pivot report.

In this short video, I show how to select specific sections of an Excel Pivot Table, by using the Selection Arrow feature.

And, if the Selection Arrow doesn’t appear on your computer, you’ll see how to turn that feature on, with just a couple of clicks.

Video Timeline

  • 0:00 Introduction
  • 0:15 Pivot Table Selection Arrow
  • 0:47 Select Subtotal Rows
  • 1:06 Select Labels and Values
  • 1:59 Change Enable Selection Setting

More Pivot Table Formatting Tips

Here are a few links to my Contextures site, where you can get more pivot table formatting tips and videos.

Number Formatting: There’s a special way to format numbers in a pivot table, if you want that formatting to stick. If you format pivot table number the same way as normal worksheet cells, the number formatting might disappear, when you refresh the pivot table.

Conditional Formatting: Pivot Table conditional formatting needs an extra step, after you set it up. If you miss that step, new data might not show the conditional formatting!

Pivot Table Styles: An efficient way to format pivot tables is to make changes to the built-in pivot table styles, or create your own custom pivot table styles.

Get the Sample File

You can get the Excel pivot table selection sample file, the written steps, and more pivot table selection tips on my Contextures site.

The Microsoft Excel workbook is in xlsx format, and does not contain any macros.

___________________________________

How to Select Specific Part of Pivot Table

How to Select Specific Part of an Excel Pivot Table

___________________________________

Add Clickable Hyperlinks-Excel Pivot Table-Macro Code

Excel pivot tables don’t show hyperlinks, but there’s a way around that problem! In the video below, you’ll see how to create fake clickable hyperlinks in a pivot table, by using a few lines of Excel VBA code, and a bit of formatting.

Continue reading “Add Clickable Hyperlinks-Excel Pivot Table-Macro Code”

Pivot Table Not Showing All Data Problem-Layout Fix

Pivot tables are great for summarizing data, but do you ever notice that there are missing items in a pivot table?

For example, you know there are product records in the source data table, but one product isn’t showing up in the pivot table. How can you troubleshoot and fix the problem to show all data?

Pivot Table Not Showing All Data
Pivot Table Not Showing All Data

Continue reading “Pivot Table Not Showing All Data Problem-Layout Fix”

Excel Pivot Table Custom Subtotal Limitations

In an Excel pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. You can also add extra subtotals, if needed, by creating custom subtotals for a pivot field. There are some limitations though, which you can see in the video and notes below. Continue reading “Excel Pivot Table Custom Subtotal Limitations”

Troubleshoot Pivot Table Problems – Pop-up Info Message

If Excel error messages appear when you try to refresh a pivot table, there are macros on my Contextures site that can help you troubleshoot those problems. I’ve just added another macro on that page, to show a pop-up message with details, if a specific pivot table is behaving badly.

Continue reading “Troubleshoot Pivot Table Problems – Pop-up Info Message”