Excel or not Excel: how to reduce the risk of errors in data management

December 02, 2019
5 min read

MS Excel is one of the most popular programs for business. Excel and Google Sheets are often used for accounting, creating CRM analogs, building content plans, etc. However, only real experts, who are actually very few in number, can avoid mistakes when using these programs.

MS Excel and its “younger brother” Google Sheets have many advantages: loads of functions to work with numbers, various data visualization tools. The Google application is free and widely used; you can open files from almost any device. 90% of Russian online marketplaces and retailers use Excel tables to collect product data from manufacturers and re-sellers, according to Brandquad’s research.

Still, these programs also have disadvantages. The interface is far from intuitive; not everybody can use the formulas; misuse errors are quite common in complex datasheets. The statistics show that every second dataset contains a work-impacting error. About half of the data inaccuracies are caused by human errors.

How to minimize risks using MS Excel and Google Sheets?

1. No freedom of action

An error in adding data into a table can lead to critical issues for the business. One of our customers, manufacturer of tobacco heating systems IQOS, delivered to a marketplace a datasheet which contained an error – a pack price was set for a block of products. As a result, the company was selling the product at a record low price over the whole week. 

It is important that employees have minimum freedom of action in working with tables. For example, data can be picked exclusively from the earlier prepared list. A unified approach in typing items will help easily sort data by partner and contractor.

To create a drop-down list in MS Excel or Google Sheets, sel ect Data > Data validation, then select List option, and then choose a source of standard items. 

2. Perfect order 

Marketplaces often request product information in Excel files and then process them either manually or with the use of special scripts or programs. These simple tools usually check the data accuracy and then upload the files to a website. However, these programs also sometimes misbehave. For example, duplicate items or double spaces may cause problems in operation.

There are different ways to keep order in the cells. To remove duplicate values in Excel or Google Sheets, select the Data section, then Remove Duplicates. The Google app also has the Trim Whitespace option in the Data section. It can also convert a specified string to lowercase (use formula “=LOWER” in a separate cell), or capitalize each word in a specified string (use formula “= PROPER”). 

When sharing an Excel file, do not forget that the program versions may be different. Files in version 2003 and earlier have a .xls format, while newer versions – .xlsx. The old version will not open .xlsx files. Moreover, some features (formulas, styles, for example) may not be available when you use a newer version of Excel to open workbooks created in the earlier versions (2003 and earlier), which could lead to a significant loss of functionality. Before sharing a file, make sure you use the right format.

3. Spruce-up

Google Sheets is often functionally more productive than MS Excel. For example, several people can simultaneously edit the document, add comments, etc. At the same time, Sheets lacks some important functions, like a wide variety of styles. It is an important function, as visual aspects help the user navigate through the table data and avoid input errors.

There are special apps that can be found at Google Suite Marketplace and installed to Google Sheets to “color” your files and make them better structured. For example, Table Styles add-on includes 24 pre-supplied styles with an option to create your own template. Sort Range Plus app, apart fr om applying a style, allows you to sort your data by color.

4. Safety comes first

Datasheets often contain commercially sensitive information (logins, passwords, sales data, etc.), so it is essential that this information is properly protected. You should protect your Excel files with passwords to prevent unwanted changes, or restrict access to the selected content. Use the Review section for such options.

Google Sheets also has different options that help protect the information, like hiding data, setting restrictions for editing, adding certain editors.  

Why is it so important? A serious security leak that occurred in 2018 can prove this. Internet users discovered that the search engine Yandex was indexing Google docs and that everyone could get access to the data not sufficiently protected by the privacy settings.

5. What if not Excel?

Of course, excel is a very familiar data tool for employees, and, probably, the most widely used one. Yet, it is hardly the only tool available. If you are really sick of Excel, you can completely give up using this program in some business processes.  

There are professional CRM systems for accounting, logistics, or customer relations – 1C, SAP, and others. There are plenty of options, but remember that it may cost you pretty much to integrate such programs with your system.     

You can use PIM (Product Information Management) system to manage all the information to distribute your product. Special DAM (Digital Assets Management) module provides solutions to manage media files. Some players in the retail market are already using such systems. When selecting an appropriate program, you need to pay attention not only to its price and integration cost but also to its functionality and usability. Brandquad, in particular, uses automated channels to transfer data and can deliver content to each online or offline retailer in Russia, regardless of the channel format.     

MS Excel and Google Sheets software work well with small businesses that employ not many people, but as your business grows, you should consider using special solutions developed for specific business processes. Otherwise, a great threat of errors and failures exists, which could lead to critical losses in your business.