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 | Leave a 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

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