As part of our Excel Interview Questions series, today let’s look at another interesting challenge. How can you analyze more than 1 million rows data in Excel?

You may know that Excel has a physical limit of 1 million rows (well, its 1,048,576 rows). But that doesn’t mean you can’t analyze more than a million rows in Excel.

The trick is to use Data Model.

This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.

Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

We can select a few cells in Excel and quickly see their count, sum etc. in the status bar. Ever wanted to customize the status bar to show something else, say difference? You can use VBA add-ins with application level events to achieve this. In this VBA Example, we will look at how to set up a class module, application event in our personal macro add-in to customize status bar.

Waffles are yummy, Power BI is awesome. The combination is going to send your taste-buds on a cruise. Learn how to make a yummy waffle chart in Power BI.

Howdy folks. Almost the end of August here. Let’s wrap it up with a nice little challenge, inspired from my recent consulting gig. Say you are looking at few job titles that look similar and want to match them to correct title.

Risk comes from not knowing what you are doing. Warren Buffet If you ever ask a project manager what they are up to, they will tell you “I have no idea“. So risks are quite common in project management. That is why I made this awesome free Excel risk map template to keep track and […]

We, humans like to compare. Whether we are on Facebook or workbook, we want to compare. So how do you compare two tables and extract common values? Simple, use Excel Power Query. It can merge (a la join) tables and give you the common values.