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

Hide Pivot Table Errors

When there are errors in the pivot table source data, you might see errors in the pivot table Values area. In the screen shot below, a VLOOKUP formula in column E has returned an #N/A error, because the product wasn’t found in the lookup table. That also creates an error in column G – Total Sales.

pivothideerrors01

Continue reading

Share and Enjoy

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

Show Text in Pivot Table Values Area

Usually you can only show numbers in a pivot table values area, even if you add a text field there. In the screen shot below, the Max of Region ID is in the Values area. Instead of the numbers 1, 2 or 3, we’d like to see the name of the region – East, Central or West.

pivot table values show numbers only

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Data Fields | 1 Comment

Format Date Field Subtotals

When you show subtotals for a pivot table date field, the dates might not be formatted like the rest of the dates. We’ll take a look at why this happens, and how you can fix it.

For example, in the  screen shot below,

  • the date (in row 4) is formatted as d-mmm-yy
  • the subtotal date (in row 8) is formatted as mmm/yy

subtotaldateformat02

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Date Fields | Leave a comment

Protect Pivot Table Source Data

If you have a pivot table that contains sales data for several regions, can you send each manager a copy of the pivot table with their region selected in the Report Filter, and prevent them from seeing data for other regions?

The short answer is , “No!”

protectsourcedata01

Continue reading

Share and Enjoy

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

Calculated Field vs Calculated Item

After you create a pivot table, you can enhance the results by writing your own formulas, to create calculated fields and calculated items. e When you’re getting started with formulas, it might not be clear what the difference is. When should you use a calculated field and when should you create a calculated item?

calculateditemfield06

We’ll take a look at a pivot table that uses both types of formulas, and see where and how they work. You can also find more information on each type of formula on my Contextures website:

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Calculated Field | 5 Comments

Format a Pivot Table in Excel 2003 Classic Style

Last week, someone asked me how to make an Excel 2010 pivot table have the same look as the default settings in Excel 2003.

You might have forgotten what those pivot tables looked like, or maybe you never used that version of Excel. In the screen shot below, you can see the pivot table that I created, with Region and City in the Row area, Order Yr in the Column area, and Quantity in the Data area.

pivotclassic01

Continue reading

Share and Enjoy

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

Sort Items in Excel Pivot Table Report Filter

When you add new items to a pivot table, they might not appear in alphabetical order in the Report Filter drop down. This can make the items hard to find, if there is a long list.

In the drop down list below, Binder is a new product that was added to the source data. It appeared in the pivot table, after it was refreshed, but it’s at the bottom of the list, instead of the top.

pivotreportfiltersort01

Continue reading

Share and Enjoy

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

Stop a Pivot Table Refresh

After you add new records to a pivot table’s source data, you have to Refresh the pivot table, to see the new data. If you have several pivot tables based on the same data source, all of them will be refreshed, if you update any one of them.

To Refresh a pivot table, and all others based on the same source:

  • Right-click a cell in the pivot table
  • Click Refresh

stoprefresh03

NOTE: When you refresh the pivot table, the entire pivot table is affected. You can’t refresh only part of a pivot table, or just add the new data to the pivot cache.

Continue reading

Share and Enjoy

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

Accidentally Create Calculated Items

In a pivot table, you can create calculated fields and calculated items, by inserting following a few steps, to insert formulas. There are detailed instructions on my web site

But did you know that you can accidentally create a calculated item too, without going through all the usual steps?

Continue reading

Share and Enjoy

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