From Excel to Spaceship with Two Add-ins 😎

Excel, used by 90% of companies and cursed behind its back, actually uses only a fraction of its capabilities. Data is entered manually, types are corrected by hand, and the question "Will it update automatically?" elicits a heavy sigh. Because two tabs in the menu remain untouched.

Most people's routine is: download a file, clean it manually, add formulas, send it to the boss. The next day, start over. The day after, start over. Deadly boring. 😔
Forty minutes daily on work that should take three clicks.

Now add 2 million rows. Excel hits a ceiling at a million rows and crashes. OK, you split the file into parts. You lose relationships between tables, pivot tables start lying.

But you can enable 2 add-ins and use Excel to its fullest.
I got acquainted with these add-ins in 2017. I was freelancing on the side. WordPress sites and various routine tasks. A task came in. I needed to extract only certain product groups from 60 large Excel files and combine everything into one file. They gave me two days. I tried to manually assemble 2 files. Terrible, boring, and tedious. I started scouring the web, wondering if there was a magic pill. And I found Power Query. After a bit of fiddling with the interface, I connected all the files from a folder and assembled the target file in 10 minutes. I almost stood up. Okay, let me tell you what these add-ins are 💪

1️⃣ Power Query: Data Without Hands

It's already built-in. The "Data" tab, "Get & Transform Data" section. You connect the source once, configure the transformation, then click "Refresh". The process that took an hour now takes ten seconds.

But there's a catch. Power Query "guesses" data types by looking only at the first 200–1000 rows, depending on the source. For CSV, 200 is enough; for other formats, a bit more. But the gist is the same: if the data is heterogeneous, numbers quietly become text without a single warning.

So to sleep soundly, always set data types manually. Don't trust auto-detection. Yes, it takes longer. But then you don't have to rework old settings and hunt for the problem.

Also, don't make one huge query. Always use an iterative approach in your work.
A chain works better: a separate step for cleaning, a separate one for transformation. Loading—also separate. When something breaks (and it will), you'll find the problem in a minute, not an hour.

And yes, PQ allows you to connect to databases directly ✅

2️⃣ Power Pivot: Excel Without a Ceiling

1,048,576 rows is the limit of regular Excel. Power Pivot handles tens of millions via VertiPaq compression (the same engine inside Power BI). The xlsx file itself becomes heavier, as a database is packed inside. But in memory, data takes up much less space: VertiPaq compresses roughly 10 times (well, that's the order of magnitude—the exact number depends on the data), stores column-wise rather than row-wise. Big data flies.

The main thing it has is measures. Don't put everything into calculated columns: they are always computed and bloat the model, while a measure is computed only when you see it in a pivot table, under a specific filter context. The difference is orders of magnitude.

The heart of Power Pivot is the CALCULATE function. It changes the calculation context. "Sales without returns", "plan only for Moscow", "what if the price increased by 10%". That's all CALCULATE.

For Apple lovers, which I am too 😁
Power Pivot is not available on macOS. Sad but true. If there are colleagues on Mac in your team, they simply won't see this wonder.

If your reports revolve around Excel—try these two add-ins 👇

#excel

@data_dzen 🙂