Summary
Full-fat Microsoft Excel is still the big kid in the spreadsheet playground, and while thefree versionof Excel online is good for many use-cases, it has some key limitations compared to its desktop brother.
If you’re a business user, a power user looking to automate your household data tasks (like balancing your budget), or looking to dip your toes into data analytics, you’re missing out on a lot of advanced functionality (both new and old) that is only available in thepaid versions of Excel.
Advanced Formulas and Charts
The free version of Microsoft Excel only lets you use a limited selection of theformulasandchartsthat have been powering business apps and spicing up presentations for decades. Some of the formulas and charts you’re missing unlock some really impressive use-cases, such as XLOOKUP(), UNIQUE() LET(), FILTER(), and LAMBDA() (the latter of which lets you define your own reusable functions!).
And let’s not forgetPivot Tablesfor summarizing and helping you to analyze data. It’s probablythekiller Excel feature (at least historically), and the free version of Excel only supports basic Pivot Table functionality.
Power Query, Power Pivot and Data Analytics Tools
Data analytics has become a big thing for businesses in the last few years. The huge amount of customer data availablethrough online platformscan be readily processed and analyzed to better understand how to reach a larger audience, and how to improve your products to better meet their needs (check outgrowth marketingto see how this works in detail).
Excel has been extended with new tools to meet new data analytics requirements.Power Queryoffers ETL (extract, transform, load) functionality that is critical for big data use-cases, andPower Pivotprovides tools for creating complex relationships between data, allowing for more in-depth analysis. Power Query is limited in its functionality in free-to-use online version of Excel, and Power Pivot is missing from it entirely.
Offline Use and Reliability
Desktop apps arguablystill provide a better user experiencethan browser-based apps. You can work from wherever you want (even if you’ve got poor-to-no internet connectivity, perfect fordigital nomads), and while there have been huge leaps in the performance of browser-based apps, desktop apps (including Excel) still have big performance and functionality advantages.
On top of being more powerful and more pleasant to use, desktop apps let you keep your data on your own device. This means no limitations to how much data you want to work with, no privacy and security concerns for your data (important for business in the age ofGDPR,CCPA, and HIPAA). Working with local data also keeps it in your control, allowing you to back it up and manage it yourself, out of reach ofcloud storage bugsthat could result in it becoming inaccessible.
Macros and Automation
Excel lets you recordmacrosto play back your actions (repeating your recorded selections and button presses), and createOffice Scriptsfor automation (though Office Scripts are available in Excel Online, you need to pay for Microsoft 365 to use them). In the desktop version of Excel, you also have access to more advanced developer and automation features in the form of VBA (Visual Basic for Applications).
Those with a bit of experience with Excel may physically cringe at the mention of VBA, but they’ll admit that it’s still a powerful tool if you know how to wield it. VBA lets you create custom interfaces to fully leverage automation in Excel, and while it’s an ugly language, can be difficult to learn, and uses a not-so-intuitive editor, it lets you do so much that it’s worth overcoming these hurdles. A recent development that makes it easier to get started with Excel automation and VBA are AI tools that canhelp you write your scripts.
It’s always worth pointing out thatMicrosoft Office macros can present a cybersecurity risk, so you should only open macro-enabled documents from trusted sources, and proofread any code you find online before you run it.
More Data From More Places
With the desktop version of Microsoft Excel, you can read raw data directly from CSV files from your local disk or network (without having to upload them), allowing you to process huge amounts of data. You can also connect to database servers, or load data fromAPIsin JSON format, and even scrape data from web pages.
Being able to access data from these sources allows you to take data from a huge array of analytics and other sources, process it with other tools (likePython) and then continue refining it in Excel. Plugins are also available for desktop Excel for integration with other data sources and tools.
Microsoft Excel Still Reigns in Business (For Better or Worse)
Excel has been around for a long time, and it’s still ubiquitous in business and education, and has managed to thrive in new data analytics use-cases despite increasing competition from new tools. Despite some bits getting a bit creaky, and some baffling UI choices and functionality that have carried through decades (no doubt for compatibility and to satisfy entrenched users), it is still considered by many to be the best at what it does (and it’sstill being improved).
If you’re not sold on its features, or just don’t use spreadsheets often enough to justify theprice of a Microsoft 365 subscription, check out our list ofMicrosoft Excel alternatives, or our guide to thebest free apps for replacing your Microsoft 365 and Adobe subscriptions.
