Copy Pivot Table Source Number Formatting

When you add numeric fields to a pivot table, Excel applies General format, in most cases. It doesn’t matter if you’ve spent hours applying fancy custom formatting to the numbers in the source data – all of that is ignored.

After the numeric data has been added to the pivot table, you can manually change the formatting of each field, in the Field Setting window. It’s not too painful in a small pivot table, but can take a while if there are several Values fields.

numberformatall02

Continue reading

Share and Enjoy

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

5 Annoying Pivot Table Problems

As much as I love Excel pivot tables, there are a few annoying problems that you’ll run into, while working with them. I made a list of my top 5 annoyances, and you might have other problems to add to the list.

5 Annoying Pivot Table Problems http://www.pivot-table.com/

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Excel Pivot Tables | 11 Comments

Remove Sum Of in Pivot Table Headings

When you add fields to the Values area, they are set as either Sum or Count, and the field is renamed. For example, Quantity becomes Sum of Quantity, or Count of Quantity. Instead of short field names, you might end up with long ones, which can make the columns wider than necessary.

Instead of using these default names, you can change the field names to something shorter, or more descriptive, such as Qty Sold. Watch the video below, to see the steps, or follow the written instructions.

Remove "Sum Of" in #Excel pivot table headings; avoid error msg http://www.pivot-table.com/

Continue reading

Share and Enjoy

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

Compare Top and Bottom Products

An Excel pivot table is a great way to summarize a large amount of data, and with a Top 10 filter, you can show the top products, or the worst selling ones

In this video, a pivot table summarizes the products sales over a two year period. With a Value Filter, you can quickly show the top products, and see their total sales.

pivottop10filter01c Continue reading

Share and Enjoy

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

Pivot Table Tips on Contextures

Thank you for following my pivot table tips, here on the Excel Pivot Tables Blog. I appreciate it!

There are many more tips and tutorials on my Contextures website, and I’ve just finished setting up a Pivot Table index, so you can find everything.

pivot table tutorial index

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Excel Pivot Tables | Leave a comment

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