Hi - Dave here.
Happy Friday!
Last week, I sent out a survey to ask readers what version of Excel they use. Nearly 3,000 people replied, and the trend is clear: 70% are using Excel 365. If we add in Excel 2021 and Excel 2024, over 80% are using a version of Excel with dynamic array formula support.
That's good to see. Dynamic array formulas are a huge upgrade in Excel, and they drastically simplify many hard problems. In fact, I used one of Excel's new functions to analyze the survey data. The table below is generated with a single formula based on GROUPBY:
[
Download the workbook and read the full explanation]
I had a basic solution working in about 5 minutes. However, adding the percentage and custom sorting was trickier, and I got off into the weeds more than once. I wrote up detailed instructions (along with my detour into problems adding the PERCENTOF function) at the link above. Download the workbook and try it out yourself. The survey data is included.
Note: This example requires Excel 365. If you have an older version of Excel, a fun exercise is to use a Pivot Table instead. In many ways, a Pivot Table is easier, but one key advantage of GROUPBY is that it doesn't need to be refreshed manually — when data changes, the results are updated instantly.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.