Excel Tips and Tricks #446 – Desktop vs Online

Two Versions to Rule Them All

Excel Online was, perhaps remarkably, first launched in 2010, though has gained popularity in recent years due to the closer integration with wider Microsoft Office apps, including SharePoint and OneDrive. Today, it actually offers much the same functionality as the Desktop version (with some notable exceptions, which we’ll get to in a minute), but crucially, for personal users, it is free providing you have a (free) Microsoft Account not exceeding the 5GB OneDrive storage limit (which most personal users will struggle to fill with purely Office documents). For business and enterprise users, it is an integrated part of the Microsoft 365 offering, and has some fairly neat features that are (at least currently) missing from the Desktop version.

Why Ditch the Desktop?

The Online version of Excel benefits from a cleaner, simpler user interface, but remains familiar to Excel users:

By default the toolbar is a single line ribbon but this can be changed by clicking on the small down arrow at the far right side of the toolbar – ‘Classic Ribbon’ will take up a bit more space on the screen, but is closer to the Excel Desktop experience.

One of the major advantages of Excel Online is, quite simply, that Excel files stored in SharePoint or OneDrive can be opened and edited anywhere, on any device, without the Desktop version. This is also beneficial in terms of interoperability between Mac and Windows versions of Excel, which has often been a cause of headaches; if everyone uses the Online version, then everyone gets the same experience. That online element also benefits the collaboration capabilities – while on paper these are the same between Desktop and Online versions, in my (very non-scientific) testing of the functionality, Excel Online appears much more responsive and quicker to update when multiple users are working simultaneously on the same file.

Another very useful feature of Excel Online (which is, belatedly, coming soon to Excel Desktop) is the ability to quickly toggle between editing and viewing the spreadsheet. One of the perennial problems in Excel is accidentally editing cells that should be left untouched – very easy to do with just a slip on the keyboard. This is even more of an issue in the world of autosaving, where you might not even realise you’ve changed the value in a cell to 0 until it is too late. The Editing/Viewing toggle means you can ensure such slips are a thing of the past.

Automation in Excel Online

Eagle-eyed users will notice that those with a Microsoft 365 Business or Enterprise license have access to an additional toolbar tab, for Office Scripts:

The easiest way to describe this tab is as a simpler online version of VBA. In the same way that you can ‘Record Macro’ in the Desktop version, you can ‘Record Actions’ in the Online version. You can also write scripts from scratch, should you be sufficiently familiar with the TypeScript language (a variant of JavaScript, which notably is also used in Google Apps Script). These are stored as a script that can then be run against the workbook, or can be shared to be made available in other workbooks.

Office Scripts in Excel are a pretty nifty online-exclusive feature that we may well delve into another time, and are much more user-friendly than VBA, but with that less feature-rich – for now, they can only interact with the open Excel workbook. That being said, they do offer integration with Power Automate which VBA does not, allowing users to create flows across Office Apps that trigger the execution of a script against any workbook stored in OneDrive or SharePoint (where the user has sufficient permissions, of course).

What Desktop Does Best

In some ways, the answer to this is pretty simple – the Desktop version has more functionality. There are probably too many differences to cover here, but some of the key ones include:

• File formats – while the Online version can open a few different file formats, if it isn’t XLSX or XLSM it will initially convert to an XLSX file. This means the Desktop version has far greater range of supported file formats, including the ability to edit and save files in non-Open XML formats. That includes CSVs and the legacy XLS format.
• VBA – much maligned, but still incredibly widespread, VBA macros can only be run or edited through the Desktop version – macro-enabled workbooks can be opened in Excel Online, but the macros themselves are effectively disabled which may render the workbook redundant.
• Customisation – Excel Online doesn’t support add-ins or customisation of the toolbars – so pretty much everyone who uses Excel Online gets the exact same experience (bar the Automation point noted above). Some would view this as a positive, but others may prefer the flexibility of the Desktop version.
• Power Query and external data sources – Excel Online does not currently have a ‘Get Data’ option, and it has limited refresh capabilities for existing data connections (perhaps understandably associated with where the data is coming from). However, Power Query does exist online through other services such as Power Automate.
• Save location – it is worth stating the obvious that the Online version can only be used for files stored in SharePoint or OneDrive, while the Desktop version will open any file stored anywhere that you have access to. Needless to say, you do also have to be online to access the Online version, whereas this is not the case for Excel Desktop.
• Workbook protections – while Excel Online can handle the various levels of workbook protection from a read perspective, Excel Desktop is better for managing them. At the sheet level the two are comparable and, arguably, the user interface for managing sheet protection is better in Excel Online. However, at the Workbook level, protections might be enforced in Excel Online but they cannot be edited – if you want to add or remove Workbook protection or Workbook encryption, this can only be done in the Desktop version.

Conclusion

If you need a ‘lite’ spreadsheet experience, where you are collaborating at pace with others, then the Online version is likely to be the way to go. For the full-fat Excel experience, then Desktop is what you need.

There are of course many, many more differences than we can cover here, but this hopefully provides a brief overview to help users make the right choice.

As a bit of a footnote - you may have noticed that, by default, if you are navigating through SharePoint or OneDrive in your browser, clicking on an Excel file will open the Online version by default. This is a setting which can be changed, but only by the site owner and it is a setting that Microsoft seems to make deliberately hard to find. Far simpler is to navigate to your OneDrive or SharePoint files in Windows Explorer, to open them in Excel Desktop.