Change Pivot Table to Outline Layout With VBA

When you create a new pivot table in Excel, it uses the Compact Layout, by default. That layout has all the row fields in a single column, with each field slightly indented from the previous field. You can read more about the Compact Layout on my Contextures website.

NOTE: If you have my PivotPower Premium add-in installed, you can select a layout in your Preference Settings. That makes it easy to apply all your favourite settings to a new pivot table, or any existing pivot tables.

Continue reading “Change Pivot Table to Outline Layout With VBA”

Saving Source Data with Pivot Table File

When you create a pivot table in your workbook, the source data records are saved in a special memory area – a pivot cache.  Then, when you close the file, Excel can save the source data in this pivot cache, or clear that memory.

Keep reading, to see how you can change this setting, and learn the advantages and disadvantages of either choice.

Note: If you want to re-create the source data, see: Re-create the Source Data

Save Source Data Setting for pivot table https://www.pivot-table.com/

Continue reading “Saving Source Data with Pivot Table File”

Pivot Table From Data on Multiple Sheets

A frequent question about pivot tables is how to create one from data on different worksheets, or even in different file. Last week, I updated my page on this topic, which shows several ways you can accomplish this goal.

  • Multiple Consolidation Ranges
  • Microsoft Query
  • Power Query

Continue reading “Pivot Table From Data on Multiple Sheets”

Give Your Excel Pivot Table a Makeover

When you create a pivot table, do you just slap it together, and hope for the best, or do you spend time experimenting with different calculations, layouts and field arrangements?

Yes, it’s easy to fall into a rut, and create the same type of pivot table, each time that you build one. That makes it quick and easy to get the job done, but you might not be presenting the data in the best way possible.

It’s time to give your pivot table a makeover, so it looks better, and its data is easier to read and understand.

Continue reading “Give Your Excel Pivot Table a Makeover”

Create Calculated Field With a Count

In addition to using fields from the source data, you can create calculated fields in a pivot table, to add your own formulas. For example, add a field that multiplies the total sales by 3%, to show a Bonus amount.

You can learn the basics of Calculated Fields on my Contextures website.

pivotcalculatedfield00

Continue reading “Create Calculated Field With a Count”

Grouping Dates Add Extra Items in Pivot Table Filter

Last week, someone asked me how they could get rid of the extra items that appear in the filter, when you group a date field. Maybe you’ve seen them too – they start with a less than or greater than symbol, and show the first and last dates in your pivot table data.

You can see an example in the screen shot below.

Continue reading “Grouping Dates Add Extra Items in Pivot Table Filter”

Create a Calculated Field in a Pivot Table

One of the top pivot table fears in our survey was “How do you insert a calculated field?” And no wonder it was near the top of the list – pivot table formulas can be very confusing!

So, let’s take a look at calculated fields, and its close companion, the calculated item.

Continue reading “Create a Calculated Field in a Pivot Table”

Pivot Table Training Giveaway Winners

Thanks to everyone who entered the Pivot Table Training giveaway, for a chance to win an amazing online course from John Michaloudis, at My Excel Online Training.

You shared your biggest pivot table fears, and you can see the voting result below. In the upcoming weeks, I’ll cover some of these topics, and all of them are covered in comprehensive John’s online pivot table course.

The winners are listed below, and each person has been sent an email, with instructions for claiming their prize. Winners will have 24 hours to respond, by adding a comment in this blog post, and if they don’t claim the prize, another random name will be selected.

Continue reading “Pivot Table Training Giveaway Winners”

Scary Pivot Table Giveaway Week 2

We’re having a pivot table training giveaway, and there are only a few days left to enter – the deadline is Sunday, November 9th, 2014, at 11:59 PM (Eastern Time)

If you enter, you could win one of the 3 Xtreme Pivot Table Courses ($199 value), thanks to John Michaloudis, from My Excel Online Training. This amazing course has:

  • more than 200 easy-to-follow videos — beginner, intermediate and advanced level
  • practice workbooks
  • finance business cases
  • 12 months of personal support.
  • work through the lessons at your own pace
  • track your progress.

xtreme pivot table giveaway www.pivot-table.com

Continue reading “Scary Pivot Table Giveaway Week 2”