Google Sheets 5 Most Missing Functions

What limitations does Google Sheets have? How to overcome them? And why is Microsoft Excel still the leading Spreadsheet program for business purposes?

icons8-eingekreiste-1-c-96

Export csv files with semicolon

csv is the short form for: “comma separated values“. So one can argue that the values in the typical csv files are separated with comma – just as the file name says. However, users of Google Sheets often simply need csv files where the values are separated by something else, like semicolon. 

This is a particular problem in countries using the decimal comma. Since comma is already taken as a decimal separator, it cannot used as a separator within csv files. Otherwise, the data in the csv file becomes a complete mess. Actually, this problem affects many people in the world, since the majority of countries worldwide use decimal comma instead of the American decimal dot.

As you can see in the map taken from Wikipedia, the green countries use decimal comma whereas the blue countries use decimal dot.

DecimalSeparator.svg

In countries with decimal comma, values in csv files usually are separated by semicolon. Microsoft Excel allows exporting csv files with semicolon as a separator, however, Google Sheets does not offer such functionality. This puts Google Sheets at a big disadvantage compared to Microsoft Excel.

How to overcome this problem?

Luckily, there are add-ons for Google Sheets that allow the export of csv files separated by semicolon. csv Exporter and csv Exporter PRO allow the users, to choose anything as a separator. So csv files can be easily separated by semicolon, but also dots, hashtags or even whole strings are possible which may be useful for some basic encryption. Both add-ons are available in the Google Workspace (GSuite) Marketplace and are verified by Google.

While csv Exporter is free, csv Exporter PRO offers more customisation and automation options. For example, while the free version will always export the csv file to your MyDrive, the PRO version allows to specify the file destination folder. Furthermore, you can easily automate your csv export processes with the PRO version by building date or time triggers. So it allows to automatically export csv files based on a custom trigger schedule like “Every Monday at 10 o’clock“.

csv Exporter is the best rated csv export add-on with >30,000 users

icons8-eingekreiste-2-c-96

Google Finance balance sheet and income statement data

Google Finance is probably one of the most popular features of Google Sheets. Google Finance is completely free and allows pulling various almost real-time financial data right into your spreadsheet. For example, you can pull pricing data of stocks or indices with just a 20 minute delay. 

Google Finance is generally very good with price and return data. For example, you can get real-time prices, historical prices, trading volumes or the latest dividend amount and payment date. However, what is really missing from Google Finance is balance sheet and income statement data. Even though you can get data the P/E (Price/Earnings) ratio and EPS (Earnings Per Share), there is really no data on for example sales and EBITDA – classic and highly relevant metrics in the income statement – or the amount of cash and equivalents or liabilities on the balance sheet. 

Unfortunately, for proper financial analysis, all these numbers are very important and critical. This is probably the biggest obstacle for Google Finance to achieve wide spread adoption of the service. The function would theoretically have the potential to significantly increase the usage of GSheets in general. Unfortunately though, Google Finance has not developed further in the last years. New attributes were not added and it seems that Google has largely descoped Google Finance for now.

Bildschirmfoto 2020-12-26 um 18.16.03

How to overcome this problem?

Other than professional services likes Bloomberg or Thomson Reuters, there is no way to overcome this problem. Unfortunately, these are extremely expensive and usually not affordable for users other than large corporates. However, Bloomberg for example offers at least a plug-in for Microsoft Excel – something that is not available for Google Sheets. This also shows how much Microsoft Excel is leading in terms of Spreadsheet software when it comes to professional business purposes. There is after all a reason why the world financial system is based on Excel.

The available attributes for Google Finance can be found under the link below. The list is fairly limited but it might still be helpful for some basic use cases. Here, Google has yet to lift the huge potential of its GSheet product.

icons8-eingekreiste-3-c-96

Remove empty rows or columns

Empty rows or columns can be very annoying in Google Sheets. They increase the size of the Spreadsheet, thus decreasing the computational speed and they make the sheet look very confusing with a lack of data structure. This can be especially problematic if the data needs to be exported and uploaded to a third-party service. Many programmes throw errors if files with empty rows or columns are uploaded.

There are ways to delete empty rows and columns in Google Sheets. However, they usually require several manual steps like filtering for empty cells and then manually deleting those cells. Unfortunately, there is no function in GSheets that automatically removes empty rows or empty columns.

How to overcome this problem?

The add-on Delete Empty Rows does what the name says – it removes empty rows in Google Sheets. But not just empty rows, also empty columns can be removed by the add-on. The add-on works in either a single sheet tab or all tabs at once, thus further reducing manual workload.

Furthermore, Delete Empty Rows allows users to set up to 5 automations to automatically remove empty cells based on specified date or time triggers. Especially if users need to regularly export data as a csv or Excel file from Google Sheets to upload to a different service, scheduled deletion may be very useful.

icons8-eingekreiste-4-c-96

Chart formatting options

Charts are a great way to visualise data and make it easily understandable. One has to say that Microsoft Office has really mastered this. Google Sheets unfortunately is still lagging behind Microsoft in an area crucial for business purposes. 

Unfortunately, chart formatting options in Google Sheets are limited to basic needs. If you really want to create beautiful charts, Google Sheets is really not the program for you. Charts in GSheets almost always look like a sample chart that has yet to be finished up nicely. Microsoft Excel offers a large variety of formatting options for headers, data labels, axis and practically everything that you can see in a graph. Google Sheets has not.

Also, if you want to create more sophisticated charts with multiple y-axis for example, Google Sheets has no way to cope with that. Two y-axis are the maximum that GSheets is capable of, limiting its use for complicated data analysis. 

Bildschirmfoto 2020-12-30 um 10.22.53

How to overcome this problem?

Right now, when it comes to charts Microsoft Excel is the master tool. Google Sheets is improving but is still lagging behind by far. Google has acknowledged the need to improve here and is trying to add more customisation options to the chart editor. With the latest update from 2020, it is now possible to edit individual data labels and choose advanced number formats. However, GSheets still has a long way to go to match the chart formatting options of Excel.

icons8-eingekreiste-5-c-96

Very few spreadsheet templates available

One particular shortcoming of Google Sheets is that there are only very few templates available. Therefore, many users need to start and build a GSheet from scratch. Microsoft Excel on the other hand, provides many different templates for all kinds of use cases. The abundance of templates makes Excel very easy to use and limits the adoption of Google Sheets especially for users that are not very proficient with Google Products.

How to overcome this problem?

The add-on Template Gallery offers many professionally designed templates that are free to use. There are templates for CVs, calendars, schedules, financial calculators, invoices and many more. The add-on has more than 200,000 users and is available in the Google Workspace (GSuite) Marketplace.