During our recent vacation to New York City my wife and I loved eating tacos at TACOMBI. However, when I saw the menu it reminded me of a common data challenge!
They give you this menu and you fill in the items that you want.
The Imaginary Scenario
Tacombi hires us to analyze their daily orders from all their locations. We’ll use Pivot Tables to quickly analyze the data so we can get back to eating delicious tacos.
Note: some questions (i.e. top 3 items by quantity) could be answered by simply looking at the pic below. However, other questions are more complex and Pivot Tables would really help.
The Data Challenge!
We’ve got the order information already entered into Excel.
So what’s the problem?
If data isn’t arranged correctly you can’t create a proper Pivot Table. The data seen below isn’t fully normalized. The category (dark red) doesn’t repeat beside each item.
The pic below has 4 pieces of data: Quantity, Category, Item and Price.
We’ll get more insights if we rearrange the data into this pivot friendly layout seen below:
Each of our 4 pieces of data now has it’s own column and datatype.
- QTY = number
- ITEM = text
- CATEGORY = text
- PRICE = number
Each row is a distinct food item and tells a story. Example:
ITEM ‘AL PASTOR’ from CATEGORY ‘TACOS’ costs PRICE 3.95 each and QUANTITY 108 were sold!
How Do We Normalize the Data?
Discovering ways to normalize the data is almost as fun as eating tacos!
- Solution A uses array formula plus IF and INDEX functions
- Solution B helper formula beside the data plus MATCH CIELING IF and INDEX functions
- Solution C uses Excel’s amazing Get & Transform tool
- Solution D uses a single helper column plus IF function. Not a perfect layout but it works
Can you think of a solution? Add a comment below.
With normalized pivot friendly data we can easily move things around inside Pivot Tables to uncover insights from the data! This data set is very small but it demonstrates how powerful and easy pivot tables are!
Download My Excel File
Get it here! See the menu data, 4 methods to normalize the data and how Pivot Tables easily answer questions about Tacombi’s data.
Watch My Excel Video
Now that you’ve got my Excel file you can watch the video and follow along.
Learn more about PIVOT TABLES
Consider this training from John Michaloudis! I’ve recommended it to so many people who are either just starting to learn or need a good refresher. Pivots are perhaps Excel’s best feature because you can analyze your data so quickly and easily without writing any formulas.
Learn more about GET & TRANSFORM!
Ken Puls & Miguel Escobar created this amazing training. Get & Transform (aka Power Query) is the best new Excel feature since Pivot Tables.
I’m currently taking this course because it’s a must know feature for a Data Analyst like me.
DISCLAIMER: I’m an affiliate for both training links above. Unfortunately I’m not a Tacombi affiliate 🙁
My name is Kevin Lehrbass. I’m a Data Analyst at McKinsey & Company.
I love data and I love tacos! I love data about tacos!
This is me at Tacombi Mexican restaurant at the bottom of the Empire State Building in Manhattan.
Years ago I lived in Mexico City. My favorite place for tacos was near the Normal subway station.
Now I live in Markham Ontario (near Toronto).