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
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
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.
Continue reading Create Pivot Table or Excel Table from Multiple Files
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.
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
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
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
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
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
Recently, I enrolled in an online Infographics and data visualization course, and the classes started last week.
The instructor is Alberto Cairo, who wrote The Functional Art: An Introduction to Information Graphics and Visualization.
He uploaded the first two chapters of his book for us to read during week one, and I really enjoyed it. There was some history, some theory, and plenty of graphics, to illustrate the text.
Improve the Infographic
One of the assignments this week was to suggest improvements to an infographic on Social Web Involvement. There are 16 countries in the infographic, overlaid on a world
Continue reading Link Pivot Chart Title to Report Filter
Do you use the free PivotPower add-in that's available on my Contextures website? I created PivotPower to make my own work easier to do, and shared it on my website to help you with your pivot table tasks.
It automates some of the features that aren't built in to an Excel pivot table, and makes some of the buried Excel pivot table features easier to access. For example, there is a command that changes all the data fields to SUM, which is handy when Excel defaults to COUNT.
PivotPower Premium Now Available
The free version is still available, and
Continue reading PivotPower Premium Excel Add-in