4 Things Microsoft Excel Can Do That Google Sheets Can’t

There is an ongoing debate about which is better between Microsoft Excel and Google Sheets, but it may never stop since the two tools are aimed at different audiences. Excel is widely known as the solution for professionals, organizations and businesses, especially those who need in-depth data analysis and modeling tools. Sheets is best suited to individuals and small teams who value modern workflows because it makes real-time collaboration and editing easier. Although Excel also offers these features in its desktop and web apps, they are not as seamless as in Google Sheets.

Regardless, Excel still comes out on top for many. It has existed since 1985, when it was launched on the Apple Macintosh, and it has largely established itself as the essential spreadsheet. As such, it has had time to develop a robust feature set compared to apps like Google Sheets (since 2006), which have to rely on plugins to implement some of the things Excel does natively.

Beyond advanced data analysis, you can easily work with large data sets, powerful data transformation tools, and robust forecasting and decisioning tools. Not to mention that it is also the real offline solution. There are of course more features, but it would take an entire book to list them all. It’s essential to stick to a few that have the biggest impact on people’s daily lives. This doesn’t mean that Google Sheets isn’t a match for Excel, just that you can only rely on Excel for certain things.

Google Sheets is a browser-based spreadsheet with a web-first philosophy. Microsoft Excel, on the other hand, has always been focused on offline mode. Once you download the desktop app, all of its core features, including basic calculations, complex logic, conditional formatting, and statistical analysis tools, are available offline. What you won’t have access to are Microsoft Office’s cloud and AI features like real-time collaboration, OneDrive, and Copilot.

The main barrier to entry is that the Excel desktop application is not free to use. At a minimum, you need Office 365. This requires a Microsoft 365 subscription, which starts at $9.99 for the Personal plan. You can also purchase Microsoft Office 2024 for a one-time fee of $179.99 for the Home edition or $249.99 if you also want the Business edition. However, this only gives you the classic version without the aforementioned cloud and AI features. The online version is free, but it has limited features and no offline mode.

You can work with Google Sheets offline, but you will need to download the offline extension for Google Chrome and Microsoft Edge. Once you also enable offline mode in settings, you can create, view and edit spreadsheets without an internet connection: everything will be saved on the device instead of the cloud. For files you created online, you must first make them available offline before you can access them. You will, however, be limited with features like importing documents, changing settings, and spell checking unavailable.

Data is not always presented in a clear format that you can start using immediately. Sometimes you may have duplicates, typos, extra spaces, and other issues that need to be resolved before they can be used. Excel and Sheets have functions and features that allow you to get it in a format that makes it easy to view and analyze. However, Excel has something up its sleeve that is considered revolutionary: Power Query.

Power Query is Excel’s native graphical ETL tool (Extract, Transform and Load). It allows you to import data from various sources, such as tables, text files, spreadsheets, PDFs, databases, web and cloud services. Then you can do any necessary transformations (cleaning, reshaping, and restructuring) before loading it into your workbook as a single table. You can split, merge, filter, aggregate, unpivot and much more. It’s faster, more intuitive, and less error-prone than writing formulas and scripts or clicking through menus.

Not only does it save you the tedious task of manually cleaning data, it also makes it easier to update. If new data arrives or you notice an error, simply open the Power Query editor again, add, edit, or fix what you need, then click a button to update everything. It would be a nightmare if you handled everything the traditional way. There’s even a handy step recorder that lets you track what you’ve done and remove the exact transformations you don’t need.

Excel has some great tools that allow you to analyze and model your data in ways that Google Sheets can’t. A popular example is Power Pivot, and one of its main functions is to help you establish relationships between tables so you can create relational models. It works with pivot tables, which are an incredibly powerful way to summarize your data without writing formulas. If you have a sales table with columns like “Date”, “Region”, “Product Name”, “Product ID”, “Units” and “Sales”, you can easily summarize “Sales by Region” in just a few clicks.

You may have a separate lookup table with additional columns, such as “Price” and “Department,” that you will need to use for further analysis. In this case, you can use Power Pivot to establish a connection between the sales and lookup tables using a unique key (for example, “Product ID”). Once linked, you can easily add lookup table columns to your analysis with just a few clicks.

Another of Power Pivot’s main functions is to perform custom calculations called metrics using DAX (Data Analysis Expressions), the built-in formula language. With metrics, you can create complex aggregations and analyzes of your relational data model. This may seem complicated, but DAX measures are usually a combination of Excel-like and specialized functions, and these respond to the filters you have applied to the pivot table.

Another powerful analysis tool that Excel has that is not present in Sheets is the What-If analysis tool. It includes a scenario manager, goal search and data table. These allow you to explore how different values ​​affect the results of your formulas so you can make informed decisions. If you’re not sure how a decision will impact your data, what-if analysis tools allow you to test your assumptions so you don’t have to guess. They are a great way to make predictions and see how you can achieve specific goals.

The Scenario Manager, for example, allows you to create a best- and worst-case scenario. Let’s say you have some savings and want to go on vacation. However, you want to have as much savings left as possible at the end. Instead of creating multiple tables or sheets for each destination, the Scenario Manager allows you to create scenarios for each destination and test the different inputs (for example, airfare, accommodation, food, and miscellaneous expenses) to see how they affect the results (remaining savings).

You can even create a report that lets you compare everything side by side, so you can see the cheapest destination. With this example alone, you can see how Excel can help you make forecasts and decisions beyond what Google Sheets or any other free alternative can do. You don’t even need to touch the formulas you’ve already written. When inputs change, they will affect the results of all dependent formulas.