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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

Update Pivot Table With Scroll Bar

A couple of months ago, I shared an example with a scroll bar that selects the dates for an Excel report. There is a pivot table on a hidden sheet, and a summary report uses GetPivotData formulas to pull data from that pivot table.

This technique works well when there are a few static row headings, like the East and West region names, in this example. If the row headings will change every month, or if you have a larger number of categories, my technique won’t work as well.

Filter the Data With a Scroll Bar

AlexJ has created

Continue reading Update Pivot Table With Scroll Bar

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

List All Pivot Table Formulas

A few months ago, I shared my code for listing all the formulas in an Excel workbook. The code creates a new worksheet, with details on each formula’s worksheet name, cell address, the formula and the formula in R1C1 format.

Pivot Table Formulas

If you create calculated fields or calculated items in a pivot table, those formulas are not included in the list of worksheet formulas.

However, you can manually create a formula list, for any pivot table, by using a built-in pivot table command.

List the Formulas in Excel 2010 Select any cell in the pivot table. On

Continue reading List All Pivot Table Formulas

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

Change All Pivot Charts With One Filter

Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In earlier versions, you can use programming to change the report filters in multiples pivot tables.

AlexJ has shared a nice technique for using a single Report Filter to update multiple pivot tables. This technique uses slicers, but they are stored on a different worksheet, so they don’t take up room on the dashboard.

Single Report Filter

Here is a screen shot of AlexJ’s dashboard, with two filters at the top of the sheet. The pivot charts are all

Continue reading Change All Pivot Charts With One Filter

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest