Use Vba to filter Pivot Table and Slicer

Sometime happens that you need to filter in automatic way a table or a “slicer” using data written in a particular cell without select it with classical manual selection.

We start from a real Situation. I have created a simple table:

Immagine 008

In this table we have a column with the customer name, a column with product category, the scenario of analysis, the version and a value. all the values are inserted manually. The structure is a table called “Values”. The table is imported in powerpivot as linked table ( it’s the same if import in sql and afetr in a model)

Continua a leggere

PowerPivot Excel 2016 news

Microsoft has just announced new feature updates to Power Pivot for Excel 2016 as part of your Office 365 subscription. In particular:

  • Save relationship diagram view as picture
  • Edit Relationship dialog creates faster and more accurate data relationships
  • Fixes that improve user experience

Save relationship diagram view as picture

Excel 2016 users can now save the data model diagram view as a high resolution image file. To create the image file, in the Power Pivot add-in, click File and then selectSave View as Picture.

Edit Relationship dialog creates faster and more accurate data relationships

Excel 2016 Power Pivot users can now manually add or edit a table relationship while exploring a sample of the data:


Morover this visualization permit:

  • Table and column selection using keyboard navigation and with typing their name.
  • Auto column suggestion for same column name in both tables

Fixes that improve  user experience

They introduce few fixes that improve modeling user experience, including:

  • Power Pivot data model is no longer lost when working with hidden workbooks.
  • Ability to upgrade an Excel 2010 workbook with a data model to Excel 2016 is now available.
  • Ability to add a calculated column in Power Pivot, unless it contains a formula, is now available.

The download link:

KB3114374—Office 2016 Excel update from December 8, 2015

KB3114297—Office 2016 Power Pivot in Excel from December 8, 2015.