When would you use the Excel desktop app vs Excel online?

Is it worth paying for Microsoft Excel?

You can use Excel online for free with a Microsoft account. There are other similar offerings available, notably Google Sheets. All these packages let you prepare basic spreadsheets but are limited in how deeply they can be integrated into a business. Given the cost of an annual subscription to Microsoft Office is less than an office worker’s daily pay, Excel4Business recommends an Excel license to almost every client.

Aside from functional limitations discussed elsewhere, the biggest issue with free access to the Excel web app is that it is tied to a user’s individual Microsoft account. Any files produced are stored in that employee’s personal cloud. Although files can be shared through OneDrive, that creates a clear risk to your business’ data and any cost savings are quickly gobbled up by time spent sharing files and folders between users.

Microsoft does let you purchase OneDrive or Sharepoint licenses for your business to bring everyone under a single umbrella. All plans provide user identities alongside access to the Office web apps, though the cheapest plans for frontline workers only let users view documents. One advantage of these basic Sharepoint plans is that Microsoft is happy letting colleagues share spreadsheets that contain embedded Office scripts, allowing for a degree of automation in the solutions.

Should I use Excel online when building a solution for multiple users?

There are only two reasons to develop spreadsheets in Excel online when the desktop version is available. Firstly, it would ensure all features of the file were accessible to other web app users. Secondly, it might allow for collaboration with external users.

Microsoft Excel is seen primarily as a tool for the office where you can view spreadsheets on decent sized screens, generally in a landscape orientation. Excel4Business has in the past worked with a flooring company sending salesmen to take measurements on-site. They’ve chosen to use Excel because it can store a list of available materials, store a project’s details and calculate indicative prices. They would prefer to use Excel because they’re already paying for the licenses and it would ideally be accessed on a smartphone.

In that scenario, it makes no sense to layout the Excel file on a desktop computer being navigated by a mouse when the end user is going to be using their fingers on a smaller screen. Using Excel online also removes the temptation to include macros that won’t be available to the end user. The basic rule is to use Excel online for tablets and phones, otherwise use a computer.

A poor reason to use Excel online would be to allow multiple users to work on the same file at the same time. This is not required. The Excel desktop app has contained an auto-save feature for several years that means multiple users can edit a file simultaneously. This is the default option for files stored in the Microsoft cloud or, when configured, on a business’ own servers.

This does mean auto-save isn’t available if you want to work on a spreadsheet with a user outside your business. In those situations, you could access the Excel web app using a personal account and share a file with an external user. Note that you would have to be able to grant them editing rights which may be a reserved function.

Excel4Business tends to find clients want external users to fill out spreadsheets to be processed internally. A separate file is assigned to each respondent so, although the process is being followed by multiple users, there is only a single person accessing each spreadsheet at any given time. Given the above, it would be rare for us to favour the web app to ease collaboration.

Can I automate Excel online?

The Excel web app contains its own coding language, Office Scripts. These Java based scripts can automate most tasks, even allowing the creation of PDFs. If you want to integrate Excel online with other Office apps then you will need to use Power Automate. A significant limitation is that scripts cannot be triggered as selectively as with Visual Basic in the desktop app so it may not be possible to run code seamlessly in the background of a file, and you may be forced to rely more on users knowing they have to click a button to complete a task.

This can prove limiting. An owner of a small haulage firm might need to keep a simple record of their driver’s expenses and fuel costs. New entries are made every day but the owner wants to ensure historic records are not being accidentally changed. If using desktop Excel, our consultants would install a macro that identifies any cells changed. The code would allow new entries but might prompt a user to think twice before editing existing data. At the time of writing, an Office script would not be able to show such a message at the appropriate times.

In the web app, an alternative procedure might be to have a spreadsheet that is protected from editing but where new records can be added using a combination of Microsoft Forms, Power Automate, and an Office Script. Suffice to say, it would be less efficient and may prevent users quickly fixing typos immediately after entering fresh data.

Even with AI’s ability to write much of the code, the number of moving parts means automating Excel online will often be trickier than automating desktop Excel.

A further difference between the Java based Office scripts and traditional Visual Basic macros is that VBA can be processed by the computer’s own hardware, whereas Office Scripts run on Microsoft’s own servers. That means any data in the file is being temporarily moved outside a business. This poses a security risk.

Can I build data entry forms in Excel?

Yes, the desktop version of Microsoft Excel lets a user create pop-up forms. These forms were originally just an extension of the Windows operating environment as pop-up form is simply a window that you design yourself. That meant they weren’t available on Macs. That limitation has broadly speaking been removed. However, it is not possible to use pop-up forms in the Excel web app because Microsoft believe it poses a security risk.

A data entry form in desktop Excel can be created in the Visual Basic Editor and every aspect of its behaviour has to be governed by macros. If it contains dropdown lists, these need to be updated by code whenever the form is opened. If you want a calendar to highlight a selected date when clicked then some code has to run whenever a date is clicked. The fact the data entry forms are programmed makes them incredibly powerful because we can check data for errors whilst it is being entered.

If a user types into a cell within a spreadsheet, no code will run until they hit enter or move to another cell. If a user types into a cell in a form, then our experts can write code that responds to each keystroke and filters a product list as they type.

The Excel web app is far more limited because Microsoft does not permit pop-up forms. Instead you could use Microsoft Forms to collect information. Microsoft Forms is more a publishing product designed to collect survey responses than it is a data management tool so does not really fill in the gap created by the absence of Visual Basic forms in Excel online.

Can I use external data sources in Microsoft Excel?

Microsoft has positioned Excel as the world’s leading data analysis and visualization tool. As they are not trying to compete with SQL for efficiency of data storage, Excel can connect directly to external databases. Over recent years, they’ve added PowerQuery so non-specialists can filter and combine data sources easily. There are limitations in Excel online than in the desktop application.

If you navigate to the Data ribbon in the Excel desktop app and click ‘Get Data’, you will see there are a huge range of options for connecting to databases. The advantage of connecting to a database on a server is that your Excel file can be brought up-to-date simply by refreshing the workbook. Combined with PowerQuery, Excel becomes a front end for an enterprise’s data.

Sometimes it may not be so easy to connect directly to a data source. At Excel4Business, we see this most frequently with accounting packages like Quickbooks. Such browser based software often lets a user export their information in a text or CSV format. Excel lets you import that data and organise it into columns. This can create issues such as Excel not recognising dates in the imported data. An Excel expert can save you time by automating these manual steps but the imported data will become stale as it isn’t updated live.

Unfortunately Excel online is a little more limited. The free version does not provide access to PowerQuery and the licensed version still cannot connect to on-site servers. You also need the desktop app to create any connections in the first place. You also cannot use data exported from a web server in Excel online. That’s because the processing of the data would have to occur on Microsoft’s own servers. Therefore they force you to use the desktop version for such things.

Excel online can be used with external data sources but it requires careful planning and is clearly inferior to the desktop version.

Can you be as productive with Excel online as the desktop application?

Aside from the loss of macros, forms and some external data connections, the main issue with Excel online is that you can suffer from latency and lag as you work on a spreadsheet. Every action you take is sent to a data centre instead of remaining on your desk. A simple request to print preview can cause a considerable delay. Although our consultants prefer to keep spreadsheets slim, Excel online is particularly punishing for bloated spreadsheets with a 30 second maximum load time when opening a file.

As a business owner, one issue with Excel online may be employee unfamiliarity. As it is designed to work well on tablets, the ribbon is hidden by default when not in use and there are generally fewer directly clickable options. If you want to adjust margins on a printed sheet then you have go to Page Setup. Having spent a while working out where to change margins, an employee may then want to add a header or footer to their output. Unfortunately, such an option does not exist. So users can end up wasting time looking for things that don’t exist.

Excel online has very few productivity advantages over the desktop app. When you start typing a formula, Microsoft will provide a fuller explanation of a SUM function than in the desktop version. It also syncs your changes automatically with colleagues but that’s a marginal benefit because the desktop version would generally allow that but more selectively.

It may be that the productivity gap shrinks as Excel becomes more reliant on the cloud based abilities of Microsoft Copilot but it is hard to imagine the gap disappearing entirely.