Try This New Formula Instead of Pivot Tables

Key Takeaways #

1. Use the Group By function instead of Pivot Tables for dynamic data updates.
2. The Group By function offers flexibility for advanced calculations and filtering.
3. The Pivot By function allows for creating tables with multiple row and column fields.

Introduction #

This tutorial introduces a new Excel function: Group By, which offers an enhanced alternative to traditional Pivot Tables. This function offers several advantages, most notably its ability to automatically update when data changes, eliminating the need for manual refreshing.

Group By Function Basics #

• How to use the Group By function:
  • : The rows you want to group (select cells with Ctrl+Shift+Down).
  • : The values you want to aggregate (select cells with Ctrl+Shift+Down).
  • : The calculation you want to perform (e.g., SUM, AVERAGE, COUNT).
• Example: This calculates the sum of sales for each country.
• Automatic updates: When you change the data, the Group By table updates automatically.

Complex Calculations with Group By #

• Calculating percentages: Use the function to calculate the sum and percentage of total.
  • Example:
• Filtering data: Use the parameter for specific filtering criteria.
  • Example: This will exclude Powerade sales from the results.

Pivot By Function #

• The Pivot By function allows you to create tables with multiple row and column fields, offering a more detailed breakdown of your data.
• Syntax:
• Example: This creates a table showing the sum of sales by country and quarter.

Additional Resources #

• Free Data Analytics Report: [Link to Hubspot's Free Guide]
  • This guide provides a comprehensive overview of data analytics, types of analysis, and best practices.

"Pivot tables can do a lot more than just that, so let's go over a harder example for the Group By."

"This top part [Pivot By function] can be really useful for something like the years, the months, or the quarters."

Conclusion #

The Group By and Pivot By functions offer powerful tools for data analysis in Excel. While traditional pivot tables remain valuable, these new functions offer enhanced functionality and efficiency, particularly when working with dynamic data. Incorporating these functions into your Excel workflow can streamline your analysis and provide richer insights into your data.

Summary for: Youtube