Excel 365 PivotBy Function vs Excel Pivot Tables

The Microsoft Excel PivotBy Function will change how you program Microsoft Excel.  The Excel 365 PivotBy Function is a game changer.    I seem to be saying this every year, for the past 3 years, so many changes to Microsoft Excel.   In this post, we will look at the Microsoft Excel 365 PivotBy Function, as compared to an Excel Pivot Table, and quickly at Power Query’s Pivot abilities.

Question:  Top of the list of questions regarding the PivotBy Function is this, what happens to the use of the Pivot Table?

 

Will the Excel Pivot Table still be used?

YES – Soon the Pivot Table will Auto_Refresh, are you kidding me!

 

Answer:  Yes, for sure, PivotTables will still have their place.  But with that said I would expect to see a significant decrease of its use.

Expert’s Comment:  The PivotBy Function is incredibly easy to use, and yet it is so powerful.  A game changer.

Note:  Power Query can Pivot, or Unpivot like a pro.  So there are really four Pivot Options in Excel 365:

1. Pivot Table, Pivot Chart
2. Power Pivot, Data Model
3. PivotBy Function (New to Excel 365)
4. Power Query Pivot / Unpivot / Transpose

 

The image below is that of an Excel Pivot Table.  You can see why they are one of the most important components of an Executive Dashboard Solution.

Pivot Tables with Pivot Charts are the backbone of Excel Dashboards –  Executives LOVE Them

Business leaders and executives want to use Pivot Tables in their custom Excel Dashboards.  PivotTables are a large part of reporting and analysis.  Add Pivot Charts and Slicers, and the users will thank you.

That said, I see a 40% plus drop in the use of Pivot Tables, where the Excel programmer also knows the PivotBy Function.  Said another way, once you learn to use the PivotBy Function, you will dramatically cut your use of Pivot Tables.

In the image below, you see an Excel Pivot Table, two Slicers, and two Pivot Charts. A simple example of an Excel Dashboard.

 

Excel 365 Pivot Tables are not going away

Just as people still use the VLOOKUP when the XLOOKUP is available, people will still use Excel Pivot Tables, even though the PivotBy Function is available.  I do not expect to see the Pivot Table going away, and I hope Microsoft will actually improve the Pivot Table, not get rid of it.  The heart of Excel 365 Dashboards, the Pivot Table.

 

 

 

---

 

Hire our Team of Microsoft Excel 365 Consultants

Does your business need help with Microsoft Excel?  We can help you with programming and training, we can teach you all there is to know about the new GroupBy Function.

As Excel Consultants we do Excel Programming and Excel Training.  Work with our top Excel Consultants on your custom Excel project.

877-392-3539

 

---

 

What is the Microsoft Excel 365 PivotBy Function?

The PivotBy Function is one of the newest and also most powerful dynamic array functions in Excel 365.  Equally new, is the new Excel 365 GroupBy Function.

The PivotBy function allows you to create a dynamic, and interactive report in Excel, that Pivots your data.  Very much like a Pivot Table, but 100% dynamic, no Refresh needed.

Programmer’s Note:   In our mind, this is the top Function in Excel, even more so than the GroupBy Function, as this does more.  But the two of them, they are the future in Excel programming.

 

 

 

 

~ The Excel PivotBy Function is a Game Changer ~

Even better than the new Excel GroupBy Function!

Basically, using the PivotBy function will allow you to create a Pivot Report that looks very much like the results of a Pivot Table.  While you cannot use Slicers with the PivotBy Function, you use the CheckBox or DropDown list, to allow them to Filter the results.

One cell, one PivotBy Function, and you have a complete report. Anyone can use this function; it is that easy.

 

My Preference:  I prefer this method of pivoting, 1) An Excel Table holds the data, 2) Power Query Filters the data using DropDown Lists, 3) the PivotBy Function references the Power Query Table.

Power Query Note:  The same query mentioned above can be used to directly populate a Pivot Table, without placing the data on a tab.

So why not use both methods of pivoting, based on the need at hand.

 

~ Recommended Steps in Excel regarding the PivotBy Function ~

1. Place data in an Excel Table.
2. Upload your Excel Table into Power Query.
   1. Use Data Validation Lists, to allow the user to select the criteria, that goes into Power Query.
3. Download your filtered data to a Power Query Table.
4. Create Several Drop-Down Validation Controls on your Dashboard worksheet.
   1. This allows the user to pre-filter their data
5. Populate each list with the desired KPI Filter.
   1. Do as many as you like.
6. Write the PivotBy Function.
   1. .Use the Drop-Down Lists as your Filters.
      1. This allows the user to Filter the data.
7. Make your selections, review your Excel PivotBy Report.
8. Create a Pivot Table with the Power Query as its source.

 

 

 

 

Differences between PivotBy and Pivot Table

Forget RefreshAll:  There are differences.  For one, the PivotBy Function is dynamic, which means that when the data changes, the PivotBy Function results instantly update, no need to hit RefreshAll.

User Interface:   Another significant difference, when it comes to the user experience, with the PivotBy Function you can use DropDown Validation Controls and CheckBoxes to allow the user to interact with the data in a way that they cannot with a Pivot Table.

Power of Power Query:  If you base the PivotBy Function on Power Query, you can have additional filters, in Power Query.  There is a lot of power here folks.

A noticeable difference:   Is the use of Slicers, which executives love.  Pivot Tables, Excel Tables, and Power Query Tables all allow the use of Slicers, Dynamic Array Functions do not allow for the use of Slicers.

 

Differences: In the images below, you see the Slicers for Pivot Tables, DropDown Filters for the PivotBy Function, and by Power Query, and you see the Power Query Editor doing a Pivot.

 

 

 

 

 

 

---

 

Power Query Pivot or Unpivot Powerhouse

Also, a GroupBy Function Powerhouse.

Power Query can either Pivot or Unpivot a Table, programmatically.

Important Note:  When you use Microsoft Power Query as the datasource for a Pivot Table, do not load the data to a tab in the file, instead, load Power Query directly into a Pivot Table, as the Pivot Table’s datasource.

In the image below, you see as the last step, the Pivot step.  You also see that the data was Grouped, like the GroupBy Function.  We did both in the same query.  This output can feed both the Pivot Table and the PivotBy Function.

 

 

 

---

 

Which is Better PivotBy or Pivot Table?

Neither:  So why not use both, in the same file, where each shows its benefit?  Just as I use other Dynamic Array Functions to produce interactive and dynamic reports in addition to Pivot Tables.

Just as Excel Tables and Power Query Tables all provide the same results, to do PivotBy and Pivot Tables.  Use what is right for the task at hand.

Our Advice:  Learn the most important Dynamic Array Functions, such as Choosecols and PivotBy, learn how to create Pivot Tables and Pivot Charts, learn how to feed variables into Power Query.  Learn how to mix and match these into a powerful dashboard.  Add VBA as desired.   If you can do all of this, you are an expert.

 

 

---

 

 

~ Microsoft Excel PivotBy Function Example ~

 

In this post, we will compare the PivotBy Function to the Pivot Table.

We will use sample budget data to show how to use the PivotBy Function.  A super simple example, but it will allow you to see how this works.

 

In this example we show you:

1. How to make the PivotBy Function interactive, with the DropDown Lists.
2. How to use a Power Query Table as the source of the PivotBy Function.
   1. Add DropDown Lists to the tab, to be used as Filters in Power Query, before the data makes it to the table that the PivotBy Function is referencing.

 

With this function, you get:

1. Headers
2. Sub-Totals
3. Grand-Totals
4. Column Sort Options
5. Row Sort Options
6. Calculation Options
7. Total Column

• You can use any Excel 365 Function as the Calculation in the PivotBy Function.

 

 

 

In the image below, you will see an example of the new Excel 365 PivotBy Function.  It is easy to use.

The new Excel 365 PivotBy Function is so easy anyone can use it

It really is that easy.  Point, click, done.  It is that easy to write it, well, if you use Tables.  You are using Tables, right?

---

 

 

Closing – Excel PivotBy Function versus Pivot Table

The new Microsoft Excel 365 PivotBy Function is the most amazing function in Excel, other than LAMBDA.  You can basically create an interactive report, with Filters and all, with a single Excel function.  And it is so simple, anyone can write it, even if you are new to Excel.

 

Excel 365 PivotBy Function versus Excel Pivot Tables – Follow these steps.

 

Recommended Steps in Excel regarding the PivotBy Function:

1. Place data in an Excel Table.
2. Upload your Excel Table into Power Query.
   1. Use Data Validation Lists, to allow the user to select the criteria, that goes into Power Query.
3. Download your filtered data to a Power Query Table.
4. Create Several Drop-Down Validation Controls on your Dashboard worksheet.
   1. This allows the user to pre-filter their data
5. Populate each list with the desired KPI Filter.
   1. Do as many as you like.
6. Write the PivotBy Function.
   1. .Use the Drop-Down Lists as your Filters.
      1. This allows the user to Filter the data.
7. Make your selections, review your Excel PivotBy Report.
8. Create a Pivot Table with the Power Query as its source.

 

If you need help with a programming project, or if you need one-on-one training, our team of Excel Consultants are here to help.  Our consultants do Excel programming and training.  So whatever you need help with, we have you covered.

Free consultants, please call today.