Summary
- Microsoft Excel online lacks functionality compared to paid versions that include features including advanced formulas and charts.
- Powerful data analytics tools like Power Query and Power Pivot are only available with a Microsoft 365 subscription.
- Desktop Excel offers additional features, better offline use, improved reliability, and more automation capabilities.
Full-fat Microsoft Excel is still the big kid in the spreadsheet playground, and while the free version of 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 the paid versions of Excel.
Advanced Formulas and Charts
The free version of Microsoft Excel only lets you use a limited selection of the formulas and charts that 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 forget Pivot Tables for summarizing and helping you to analyze data. It’s probably the killer Excel feature (at least historically), and the free version of Excel only supports basic Pivot Table functionality.
Data analytics has become a big thing for businesses in the last few years. The huge amount of customer data available through online platforms can 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 out growth marketing to see how this works in detail).
Excel has been extended with new tools to meet new data analytics requirements. Power Query offers ETL (extract, transform, load) functionality that is critical for big data use-cases, and Power Pivot provides 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 arguably still provide a better user experience than browser-based apps. You can work from wherever you want (even if you’ve got poor-to-no internet connectivity, perfect for digital 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 of GDPR, 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 of cloud storage bugs that could result in it becoming inaccessible.
Macros and Automation
Excel lets you record macros to play back your actions (repeating your recorded selections and button presses), and create Office Scripts for 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 can help you write your scripts.
It’s always worth pointing out that Microsoft 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 from APIs in 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 (like Python) 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’s still being improved).
If you’re not sold on its features, or just don’t use spreadsheets often enough to justify the price of a Microsoft 365 subscription, check out our list of Microsoft Excel alternatives, or our guide to the best free apps for replacing your Microsoft 365 and Adobe subscriptions.
