Find and Fix Pivot Table Source Data

alt text: Find and fix pivot table data source problems

After you create a pivot table, you might add or change records in the source data. Sometimes the pivot table does not update correctly, to show the new data. Or if you've inherited the pivot table file from someone else, you might not even be able to find the source data, in a large workbook.

To help with these problems, I've added a new page on my Contextures site, with tips for finding and fixing the source data for a pivot table.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Find and Fix Pivot Table Source Data

Hide Pivot Table Subtotals

Remove pivot table subtotals VBA

Sometimes the Excel macro recorder creates code that gets you off to a good start. Other times, it's not so helpful.

This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. One click, and the subtotals disappear, or reappear.

Let's get the code for that, which I'm sure will be equally clean and simple. Ha!

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Hide Pivot Table Subtotals

Combine Data on Two Worksheets

Combine tables with Power Query

Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I've done some work with Power Pivot, and was impressed by what could be done with that add-in. But, despite its capabilities, I haven't been using it for big projects, or client work.

Recently though, I've been testing Power Query, and the things that it can do are very exciting. I'm just getting started with this add-in, but was amazed by how easy it is to combine data on two different worksheets.

Once the data is combined, you can filter and sort

Continue reading Combine Data on Two Worksheets

Create Custom Reports From Pivot Tables

custom reports from pivot tables

If you're a pivot table fan, like I am, you know how quick and easy it is to summarize a massive amount of data, with just a few clicks. You can show sums, counts, averages, and other totals, without using any fancy formulas.

In the screen shot below, the pivot table is summarizing income and expenses, and there is a Slicer at the top left, for quick filtering.

Continue reading Create Custom Reports From Pivot Tables

Excel Report Diagnostic Display

excel diagnostic report

Did you ever print and distribute pivot table reports in Excel, only to discover – too late – that they weren’t showing the correct information? To help save you from wasted time and paper, and possible embarrassment, AlexJ is sharing his Report Diagnostic Display technique today.

You might remember some of his other handy tips, such as

Set Row Height With REPT Function Use Slicers to Filter a Table in Excel 2010

Before you read the rest of this article, remember that the 20% discount ends today, Oct. 16th, for Mynda Treacy’s Excel Dashboard course. To get a bonus of

Continue reading Excel Report Diagnostic Display

Unique Count in Excel Pivot Table

pivot unique count

A pivot table can sum and count and average, and do several other functions, but so far, it doesn’t have a Unique Count (Distinct Count) function. If you’ve built a PowerPivot pivot table, you’re in luck – it does have a DISTINCTCOUNT function.

For example, see a count of the stores in each region or city, instead of the number of records for each store.

Continue reading Unique Count in Excel Pivot Table

Change Pivot Table Filters With Drop Down Cell

change pivot table with cell selection

Happy Thursday! I've got two news items today , and you can read the details below:

a new sample file on my Contextures website a Microsoft Consumer Camp event in the Toronto area Pivot Table Report Filters

There are several sample files on my website that let you change all pivot tables, based on a change to one pivot table. Kevin asked about one sample, which lets you select from a data validation drop down list. In the original file, changing that drop down updated all the pivot tables in the workbook.

Continue reading Change Pivot Table Filters With Drop Down Cell

Create Pivot Table or Excel Table from Multiple Files

A few years ago, Excel MVP Kirill Lapin shared his code to create a pivot table from identically structured tables in two or more Excel files. His technique used a Union query in Microsoft Query, and you can see the details here.

You just click the button to start the macro.

Then, select the files that you want to include (press the Ctrl key, and click on multiple files)

A pivot table is created from all the data, and you can filter or sort the data, just as you would in any other pivot table.

Create an

Continue reading Create Pivot Table or Excel Table from Multiple Files

Excel PivotPower Premium Giveaway

Last week, we had a giveaway for the new Excel Tips Workbook from Vertex42. Thanks to Jon Wittwer for donating the prizes, and here are the winners:

Tim, with comment 5 Ute-S, with comment 34

Congratulations, and I will email you later today, to arrange sending your copy of the file.

PivotPower Premium

We’ve been fortunate to have some great prizes for the summer giveaways, and I really appreciate the generosity of my Excel colleagues.

Now it’s my turn to provide the prizes. This week, you’ll have a chance to win a copy of my Excel add-in for working with

Continue reading Excel PivotPower Premium Giveaway

Update Multiple Pivot Tables 20130618

If you have multiple pivot tables in a workbook, you can use programming to update all (or some) of the pivot tables, if one pivot table’s filters are changed.

I’ve written sample code that does this, and last year, Jeff Weir shared his version of the code, which runs very quickly.

Jeff’s code has another advantage too – in his version, you can specify:

any sheets you DON'T want the macro to check any specific pivot tables that you DON'T want the macro to synchronize.

NOTE: Jeff's code is intended for pivots that DO NOT all share the same

Continue reading Update Multiple Pivot Tables 20130618