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!
The Menu
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.
The Pivots!
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 🙁
About Me
My name is Kevin Lehrbass. I’ve been a Data Analyst since 2001.
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).
Great article, thank you
I’m glad you enjoyed the article Zaur. Thanks for visiting my blog!
Could we go a step further to see how much of ingredients are used / cost of those ingredients? If so how would we do that?
Hi Evan,
That would be very interesting to do and it’s making me want to eat tacos!
With several locations I’m sure that Tacombi has some kind of database for all menu items, the ingredients and costs. It would allow them to analyze their data and possibly concentrate on the higher profit menu items. I wish Tacombi had a location in Markham or Toronto!!
Thanks for reading my blog Evan!
Kevin