Stop Using Pivot Tables: Use Excel's New Function Instead

Traditional Pivot Tables

• Usage: Find sales by country.
• Steps:
  1. Insert > Pivot Table
  2. Select existing worksheet location
  3. Row: Country
  4. Values: Sales
• Issues:
  • Must refresh pivot table manually after data updates (e.g., adding new rows or changing values).

Introducing Group By Function

• Advantages: Automatically updates with data changes.
• Example: Find sales by country.
  • Formula: =GroupBy(table1[country], table1[sales], SUM)
  • Updates instantly with data changes.

Comparison with Pivot Tables

• Pivot Tables: Require manual refresh.
• Group By: Updates automatically.
• Example: Adding a new row for a country (e.g., Romania) reflects automatically in Group By but not in pivot table.

Advanced Features

Sales by Country and Percentage of Total

• Pivot Table: Simple: Drag and drop fields.
• Group By: Use HStack formula to add sum and percentage.
  • Formula snippet: =GroupBy(..., HStack(sum(array1), percentage(array2)))

Customization in Group By

• Headers and Totals:
  • Control display with optional parameters.
    • Headers: Yes, No, Yes but don’t show
    • Totals: 0 to hide
  • Footer example: Suppress total when calculating average price.
• Sorting:
  • Sort order options: 1 (first column), 2 (second column), -1 (descending), etc.
  • Example: =GroupBy(..., SortOrder)

Filtering:

• Example: Filter out specific products (e.g., Powerade).
  • Formula snippet: =GroupBy(..., FilterArray NOT EQUAL 'Powerade')

Nested Grouping

• Combined Fields: Sales by country and product.
  • Formula: =GroupBy([country, product], [sales], SUM)

Introducing Pivot By Function

• Adds Column Fields: Enhances Group By: Can add both row and column fields.
• Example:
  • Row Fields: Country
  • Column Fields: Product
  • Overall Formula: =PivotBy([country], [product], [sales], SUM)
  • Visualization: Sales breakdown where columns represent products and rows represent countries.

Practical Application and Further Learning

• Exercises: Students are encouraged to practice using provided datasets.
• Additional Resource: Free Hubspot PDF guide on data analytics.
  • Introduction to data analysis, types, and best practices.
  • Link in video description.

Conclusion

• Encouraged Actions:
  • Try using Group By and Pivot By in place of traditional pivot tables.
  • Use provided datasets to practice.
  • Consider enrolling in Excel courses for deeper learning.

---

Watch the video until the end for more tips and subscribe to stay updated.