Excel - Dueling Excel - Sales by Region & Team - Duel 188 - Episode 2167

Microsoft Excel Tutorial: Build a Sales Report by Region and Team

Original data has sales rep and region
A second (badly shaped) table organizes the sales reps in to teams
Bill method 1: Re-shape the team hierarchy data. Make both ranges into Ctrl+T tables
Create a pivot table, adding the data to the data model. Pull Team from second table.
Create a relationship
Mike Method2: Build a SUMIFS where the Criteria2 field is an array!
Pass the SUMIFS in to the SUMPRODUCT function
Bill Method 3: Rearrange the hierarchy table so sales rep is on the left.
Add a VLOOKUP to the original data
Build a pivot table
Mike Method 4: Use the Relationship icon on the Data tab of the ribbon
When you create the pivot table, choose Use this Workbook's Data Model
Bill Method 5: Power Query. Add the lookup table as a Connection Only
Add the original table as a lookup only
Merge those two tables, group by to produce the final report

Table of Contents
(00:00) Sales Team Report by Region in Excel
(00:27) The Teams table is not structured well
(01:04) Rename the Tables
(01:23) Pivot Table based on Data Model
(02:11) Create a Relationship in Excel
(02:38) Using a formula to check if the rep is in this team
(03:57) SUMIF for each rep
(04:19) Function Array Operation in Excel
(05:45) SUMPRODUCT function in Excel
(06:56) Method 3: Rearrange the Team Table
(07:20) Add a Pivot Table
(09:01) Method 4: Use Relationships icon in Excel 2016 or newer
(11:28) Method 5: Power Query
(13:30) Power Query Group By
(14:52) Recap
(15:22) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

This video answers these common search terms:
Data model
Excel 2013
Excel 2016
Format as table
Pivot table
Power Pivot
Revenue
Sales reps
Sales Team Report By Region
SUMIFS function
VLOOKUP

#excel
#microsoft
#microsoftexcel
#exceltricks
#excelduel
#excelchallenge
#excelformula
#excelformulasandfunctions
#evergreen
#pivottable
#excelpivot
#powerpivot
#powerquery