Scary Pivot Table Training Giveaway

Halloween is a couple of days away, and you’ll see many frightening things that night. Keep an eye out for vampires, witches, ghosts and monsters!

Something else that scares some people is a pivot table. Even though it’s a beautiful thing, a pivot table can be scary if you’re not sure how to handle one.

  • Are you afraid to get started with pivot tables?
  • Have you learned the basics, but aren’t sure what to do next?
  • Do the advanced pivot table features make you wake up screaming in the night?

xtreme pivot table giveaway www.pivot-table.com

Continue reading

Share and Enjoy

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

Show Running Total in Excel Pivot Table

With a running total in a pivot table, you can see how amounts accumulate over a period of time, or through another field, such as products, or customers.

In the screen shot below, the Quantity field shows a running total across the Months column. In March, the Year To Date total for Corner Cabin is 2595.

Show Running Total in Excel Pivot Table http://www.pivot-table.com/

Continue reading

Share and Enjoy

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

Automatically Refresh a Pivot Table

It would be nice if a pivot table automatically updated, whenever its source data changes, but unfortunately, that doesn’t happen.

If you add new records, or delete records, or edit the existing data, the pivot table doesn’t show the revised data right away.

To keep the pivot table up-to-date, you have 3 choices:

  1. Manually refresh the pivot table
  2. Use programming to update it
  3. Change a pivot table setting, to get some automatic updates

Manually Refresh

A quick and easy way to refresh the pivot table after the data changes is to manually update it:

  • Right-click any cell in the pivot table, then click on Refresh.

pivotrefresh01

Refresh When File Opens

Another way to update is to set the pivot table to refresh when you open the file that it’s in. With this method, you’ll get partial automation, without having to add macros to the file. Any time you close then re-open the file, the pivot table will be refreshed.

To set this up:

  1. Right-click any cell in the pivot table
  2. Click PivotTable Options
  3. In the PivotTable Options window, click the Data tab
  4. In the PivotTable Data section, add a check mark to Refresh Data When Opening the File
  5. Click OK to close the dialog box.

pivotrefresh02

Refresh With Programming

The final way to update the pivot table is with programming. You can use Excel VBA to automatically update a pivot table when its worksheet is activated.

There are instructions on my Contextures website, for copying VBA code to your own files.

Put the following code on the worksheet module, if there is one pivot table on the sheet:

Private Sub Worksheet_Activate()
   Me.PivotTables(1).RefreshTable
End Sub

If there are multiple pivot tables on the sheet, with different source data, use this code, pasted onto the worksheet module:

Private Sub Worksheet_Activate()
   Dim pt As PivotTable
   For Each pt In Me.PivotTables
      pt.RefreshTable
   Next pt
End Sub

_______________________________

Share and Enjoy

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

Pivot Table Intro and Resources

Many years, and thousands of pivot tables ago, I wrote a couple of articles for Jon Peltier’s Excel charting website.

Jon has been updating his website, and asked me to write new versions of those articles. He has just published them, and you can follow the links below, to read them.

Continue reading

Share and Enjoy

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

Excel Meal Planner With Pivot Table

Now that fall is here, you might be back into a routine of weekly meal planning, and Excel is a great tool to use for that.

To help you get started, there is a weekly meal planner on my Contextures website, that you can download. Enter the meal and product information, based on your favourite meals.

Then, pick your meals for the upcoming week, and click a button  to create a weekly shopping list.

Continue reading

Share and Enjoy

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

Get Total Amount from Specific Pivot Table

If you have multiple copies of a pivot table in a workbook, on different sheets, you can use the GETPIVOTDATA function to pull the total amount from a specific pivot table. In this example, there are 3 copies of the pivot table in a workbook, each with a different layout and filter applied.

The sheets are named consistently, starting with “PT_”, and the pivot table location is the same on each sheet.

Continue reading

Share and Enjoy

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

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 | 1 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 | 6 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