PivotPower Premium Update 201407

If you’ve bought a copy of my PivotPower Premium add-in, I sent out an update email last week, so you can download the latest version.

There are a few new features, that I hope you will find useful. A big thank you to my lovely customers, who suggested these new features!

coverribbon_20140703

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Excel add-in | 1 Comment

Hide Zero Items in Pivot Table

If you create a calculated item, extra items might appear in the pivot table, with zero amounts in some rows. In the example shown below, a calculated item was created in the Category field. Because of that, all the cities are listed under each region, instead of just the region in which they are located.

Los Angeles and San Diego are not in the East, so their sales in that region show up as zero. It makes the pivot table look messy and confusing!

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Calculated Item | Leave a comment

Show Fiscal Year to Date Totals

Last week, you saw how to calculate the fiscal year and month, by adding formulas in a pivot table’s source data.

The formulas referred to a named cell – FYStart – where you enter the start month of the fiscal year.

pivotfiscalyear01

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Functions | 1 Comment

Show Fiscal Year and Month Totals

In a pivot table, you can show totals for each calendar year or month:

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot fields | 4 Comments

Link to Pivot Table Creates Absolute Reference

Last week, I did a Pivot Table presentation, and someone asked why you get an absolute reference, if you try to link to a pivot table cell. For example, in the screen shot below, I typed an equal sign in cell E4, then clicked on cell C4, which has the quantity for the Bars category.

That created a GETPIVOTDATA formula, instead of a simple reference to cell C4.

getpivotdata07

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot tables | 4 Comments

Use Sample Pivot Table VBA Code

In some of the pivot table articles that I post here, there is sample code that you can use in your own files. Sometimes I show the code sample here, and you can copy and paste it into your workbooks. Other times, I give a link to a file that you can download, and copy the code from that.

If you’re not an Excel programming expert, here are a few tips for copying the Excel VBA programming code to your workbook.

copysamplecode05

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table programming | 1 Comment

Group By Periods in Excel Pivot Table

If you’re working with dates in a pivot table, it’s easy to group them by years, months or days – those are options in the Group By dialog box.

pivot table group by dialog box

However, sometimes you might need a different type of date grouping. In this example, we’d like to group the sales data into 4-week periods, to match our company’s sales calendar. Keep reading to see the steps to set that up, and make sure the period starts on the correct weekday.

Also, you can read more about pivot table grouping on my Contextures website.

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table grouping | 2 Comments

Compare Years in Pivot Chart

A pivot table is a quick and effective way to summarize data, and you can also create a pivot chart, to show a visual summary. And, if you’re summarizing the data by date, you’ll usually need to group the date field, to get a chart that’s easy to read.

In this example, the source data contains records from the service department at a manufacturing company. The pivot table counts the number of work orders that were completed each day. We’ll build a chart that compares last year’s monthly counts to this year’s.

pivotchartyears01b

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Pivot Chart | 1 Comment

Better Format for Pivot Table Headings

When you create a pivot table, Excel applies a default pivot table style. If there are two or more fields in the Row Labels area, you might see dividing lines, below the item headings.

pivotitemheadingformat03

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table formatting | Leave a comment

Hide Arrows in Pivot Table Headings

After you set up a pivot table, you might like to prevent people from selecting items in one or more of the heading drop downs.

pivothidearrows02

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table filters | Leave a comment