What Are The Main Uses of A Spreadsheet?

Most finance professionals have been accustomed to regular use of spreadsheets throughout their career – but as technology advances rapidly, the question has been raised: will finance chiefs ever experience life without spreadsheets?

Different uses of spreadsheets

Spreadsheets are pervasive digital tools, bundled together with millions of computers around the world, and provide solutions for a wide range of issues, from visualising results, to designing questionnaires and managing large data sets. Their flexibility and ease of use are just two reasons why we are likely to see spreadsheets continue to be an essential business tool. 

The first spreadsheet was a program called LANPAR which ran on mainframe computers in 1969, and by 1979 a spreadsheet called VisiCalc (short for “visual calculator”) was developed for Apple computers. Four decades on, spreadsheets remain a very useful business tool.

The way spreadsheets are used will vary with the size of a business, the industry it operates within and the needs of the company's operational leaders. New digital tools are available to enhance the effectiveness of spreadsheets, and the utility and popularity of spreadsheets in the finance function is set to continue into the future. 

Advantages and disadvantages of using spreadsheets

In business, strategic planning is essential and requires valid information in order to make key decisions. Choosing the right tools for inputting, tracking, analyzing and storing data will help business owners and managers make the best choices for their company's business. One of the components within software productivity suites is the spreadsheet. Spreadsheets are popular among accountants and among those who like to collect and track data, yet there are some limitations, which may not make them the best choice for every office application.

Advantage: Organizing Data

Spreadsheets are frequently the go to tool for collecting and organizing data, which is among the simplest of its uses. Information can easily be placed in neat columns and rows and then sorted by information type. Although a large collection of data may be overwhelming to view in its raw state, tools within the program allow the user to create presentations where the data is analyzed and plugged into pie charts or tables for easy viewing and interpretation.

Disadvantage: User Bias

However, the downside is that only the information that the user chooses for analysis is included in these presentations, and therefore, other pertinent information that may influence decision making might be excluded, unintentionally. To make reporting of data more user friendly and comprehensive, companies are choosing to use reporting tools such as Tableau and Qlik, instead of relying solely on the spreadsheet.

Advantage: Streamlines Calculations

No one likes to spend all their time at work doing repetitive calculations. The great appeal of spreadsheets is that the program does all the math for the user. Once a formula is written and the program has a set command, complex calculations can easily be computed for the related data that has been input. This allows users to ask "what if" types of questions and to easily get the answers they need without the need to rework the calculations.

For example, if the spreadsheet is set up to calculate your gross profit, when any variable such as cost per unit, shipping costs, or sales discount are changed, the software automatically recalculates the new gross profit based on the new information.

1. Spreadsheets are free.

For most businesses, spreadsheet software is readily available and often free. Whether your company uses Microsoft Excel or runs on Google Workspace’s Google Sheets, most people with an internet connection can access a spreadsheet.

2. Spreadsheets require minimal training.

Whether you’ve used a spreadsheet to manage your personal finances, or have spent countless hours building pivot tables for previous roles, chances are you’ve had some hands-on experience using spreadsheets. Outside of building complicated formulas, recording information in spreadsheets is fairly intuitive and does not require intensive training to get the hang of. This makes spreadsheets an accessible option for teams that are scaling fast and don’t have time to train new employees to use complicated software.

3. Spreadsheets are customizable.

Spreadsheets are highly customizable, specially if you know how to use its multitude of formulas and functions. You can create any type of document with a spreadsheet, even a calendar.

Because they’re so customizable, they can also be intimidating to use. You’re not even sure where to begin unless there’s a spreadsheet template available for what you want to do. So if you do want to use spreadsheets for business, it’s important that at least one of your team members knows how to customize a spreadsheet to streamline a business process.

4. Spreadsheets can be more collaborative than other tools.

Most business tools these days provide some collaboration features. For instance, Trello allows teams to manage projects in one interface. But no tool provides the same amount of visibility as a spreadsheet.

Spreadsheets can be freely edited by anyone on the team, so there’s an automatic increase in collaboration. While other tools may bar some permissions based on team members’ titles or managerial levels, there are no such restrictions with spreadsheets (unless you purposefully restrict permissions).

Do note that this is only true for online tools such as Google Sheets. A tool that’s downloaded on your local drive, like Excel, wouldn’t be as collaborative, because the file is only available on your laptop.

5. It’s easy to manipulate and analyze data.

Spreadsheets make it easy to manipulate data. You can add, subtract, divide, and multiply datasets; create pivot tables; remove duplicates; retrieve data from other tabs; and search all rows and columns for a certain phrase or parameter.

There’s a downside to this, however. You can easily break a spreadsheet if you accidentally remove a piece of data that was part of a formula or calculation. It’s also easy to accidentally transfer a cell’s information to another cell, and by the time you catch it, it may be too late to undo it. So you’d have to cross your fingers and hope you remember the data.

6. You can integrate spreadsheets with certain tools.

This is specially true for Google Sheets, which is cloud-based. Using a tool such as Zapier or Integromat, you can automatically upload spreadsheet data onto your CRM or into your project management software — so long as the spreadsheet’s columns match the input fields on the software. If there’s a mismatch, you can expect significant integration errors.

7. Spreadsheets are quick and easy to add into a workflow.

Whereas a dedicated system, such as a piece of sales software, may take time for users to adopt, spreadsheets pose no such challenges. Once you create a spreadsheet and share it among team members, it’s easy to integrate into the team’s workflow — regardless of your team’s size.

8. Spreadsheets are fantastic tools for financial documents.

There’s no other tool like a spreadsheet for creating financial documents such as balance sheets, business budgets, and cashflow statements. Once you’re ready to export the revenue and asset data from your bookkeeping tool, it’s as easy as plugging in the numbers and generating a new spreadsheet statement every quarter.

9. You have access to countless spreadsheet templates.

Earlier, I mentioned that to use a spreadsheet for your use case, you’d likely need a template. Otherwise, it’d be hard to create a spreadsheet that works for you and your team. The good news is that many templates exist for both Excel and Google Sheets. You can find nearly a hundred examples in HubSpot’s business template library.

10. You can visualize data (with caveats).

You can use spreadsheets to visualize data — but you must know how to. On a tool such as the HubSpot CRM, you simply need to access the reporting dashboards tool to see your data visualized automatically. Spreadsheets can do the same thing, but you’ll need to know how to appropriately choose the data so that you don’t get an error.

Once you learn how to handle a spreadsheet’s visualization tools, you can create charts and graphs with ease.

To summarize, spreadsheets may be sufficient for startups in the very early stages, but these advantages are often short-lived. Over time, spreadsheets can become more trouble than they're worth.

Even for a small company, managing customer information through spreadsheets is at best unproductive and at worst downright dangerous for a variety of reasons. Now let’s discuss the disadvantages of using spreadsheets to track customer information and business data.

Disadvantages of using spreadsheets: Track Customer Information & Business Data

1. Spreadsheets are not user-friendly.

There’s no way around it. Spreadsheets are simply not user-friendly. They’re customizable and familiar, but when you need to handle a lot of data, you’ll find yourself scrolling through hundreds, if not thousands, of rows and columns. It’s difficult to make spreadsheets easy to ingest and easy to read.For instance, take a look at this spreadsheet:

Image Source

And then look at similar information on HubSpot’s CRM:

 

Much easier to read and manage, isn’t it? The above view is automatically applied. If you want to make a spreadsheet more user-friendly and easy to read, you’d have to tinker significantly with the colors, font, and font sizes.

2. Spreadsheets are not secure.

As opposed to a dedicated system that requires access to log in, spreadsheets can be disseminated to anyone, anywhere with the simple send of an email. This makes it easy for a disgruntled or dishonest employee to share leads and customer data with external contacts (read: your competitors).

3. It’s hard to tell who edited the spreadsheet.

While spreadsheets are excellent collaborative tools that allow anyone in your team to add information, it’s hard to tell who edited a cell. This can make it difficult to pinpoint the appropriate party if an error happens or if something else goes amiss with the sheet.

In sales specifically, establishing an orderly system to divvy up leads and customers when you're working from spreadsheets is tough. Unless you train your reps to take meticulous notes and follow a rigorous documentation process, there's no indication of who last reached out to a customer or prospect, what the content of the message was, and when the interaction took place.

4. There will be multiple versions of the truth.

Everyone might start out working from the same data ... but it probably won't stay that way for long. Every time a rep makes a change to the master spreadsheet, they'll need to upload it to a central repository for the edits to be communicated to the rest of the team.

And if they forget to do that, you'll have several versions of the "single" spreadsheet — all with slightly different data. How will you know which one is right? That's a sure recipe for creating a messy sales process.

5. Spreadsheets are prone to errors.

Well, spreadsheets aren’t prone to errors, but its users certainly are. (Guilty.)

With so many people having their hands on a single spreadsheet, and with so many edits and calculations happening at once, it’s natural that spreadsheets will contain some type of error. If you're hoping for accuracy, don't expect to get it from spreadsheets. That’s why it’s wise to invest in a database software that can quickly and accurately resolve errors on its own, or flag them to you for fixing it. Because the worst part about spreadsheets, specially large ones, is that it’s easy to miss errors unless you’re continuously combing it row-by-row.

6. Reporting is painful.

It's hard enough compiling all the various versions of a spreadsheet into one master copy, but then managers have to assemble meaningful reports based on the data. As anyone who has tried to report from Excel knows, it's not for the faint of heart. And the more complex your data, the harder reporting becomes.

7. Visualizing data is difficult.

As mentioned, users can create charts and graphs in spreadsheets. But "create" is the key word. Assembling any kind of data visualization in a spreadsheet is time-consuming and frustrating. Managers could opt to skip the visuals altogether — after all, they're not necessary. But presenting a spreadsheet thousands of rows long is guaranteed to make your team members’ eyes cross.

8. Critical customer data is at everyday life's mercy.

If you’re using Excel, you risk losing data. Lots of data. Data that lives solely on individual computers can be lost forever if your laptop takes a tumble or gets doused in coffee. While Google Sheets is a great alternative, it can also be subject to data loss if you lose your internet connection.

9. There’s no native integration with business systems.

If you want to connect your spreadsheets to the other systems you use, you’d have to hope that an integration exists on Zapier or a similar tool. If it doesn’t, you’re out of luck, and you’d have to manually transfer the data over to the other systems.

A lack of integration means that customers and prospects might receive redundant information... or none at all. Neither of these scenarios translates into sales or growth for your business.

10. Spreadsheets make it harder for managers to manage team members.

To effectively coach and mentor, managers need to have visibility into the daily actions and processes of their team members. This is all but impossible to do through spreadsheets. Both team members and managers will quickly become frustrated with the seemingly endless cycle of uploading, downloading, attaching, and emailing spreadsheets.

A much better alternative is a project management tool or a CRM that keeps record of all rep activity.

11. Spreadsheets don’t offer mobile access.

This is bad for any team, but it’s specially bad for sales. Mobile access to sales systems has been proven to increase rep productivity — by as much as 24%, according to some estimations. But regardless of the other virtues of spreadsheets, they're not exactly mobile-friendly. Dragging out a laptop each and every time you need to enter or change customer data gets old in a hurry.

In comparison, the HubSpot CRM offers a mobile app for reps to quickly enter their activity.

12. Spreadsheets are difficult to scale.

Once your company — and your customer list — starts growing, sales spreadsheets will need to as well. However, the bigger the spreadsheet, the more likely it is to contain errors and broken formulas. Not to mention that large spreadsheets are exponentially more unwieldy for users and administrators alike.

Spreadsheets sound like a hard “no,” don’t they? But when do you use a spreadsheet over other types of tools? Let’s go over those instances now.

When to Use a Spreadsheet

When You Want to Manage a Large Dataset At One Time

Spreadsheets are an excellent tool when you need to handle and manipulate a large dataset on a one-time basis. Let’s say you’ve exported all traffic data for your website, and you want to find the pages that earned the most traffic. Spreadsheets can help you see that data for a single date or a single time range rather quickly in one familiar interface.

However, if you want to track traffic over time, spreadsheets aren’t the best tool, because you’d have to export the dataset each time you want to access it. There’s also no way to visualize the data easily. You still could, but you’d have to use complicated formulas to retrieve data and visualize it properly.

When Your Company is Just Starting Out

Spreadsheets are cheap — and sometimes free, as is the case with Google Sheets. When a company first starts out, the money isn't exactly rolling in. Startups devote all of their cash to their product — and make do with the equivalent of duct tape on the operations side.

This means that when it’s time to track customer and contact data, spreadsheets are the natural choice. It makes sense, too. When you only have a handful of customers, it’s easy to simply pop their information into a spreadsheet and share it with the one or two salespeople on staff.

However, once your business starts growing, it’s important to upgrade to a database.

When No Dedicated Tool Exists for What You Want to Do

In some cases, there might not be a tool that can provide the dedicated organization and collaborative features that spreadsheets offer.

For instance, let’s say that you’re part of a large writing team with distributed team members. You may want a tool that allows you to see and sort through all the articles that everyone’s working on all at once, instead of needing to click from project board to project board, as you’d need to do in a project management software.

Spreadsheets are a great tool for that. Using formulas and functions, you can create a spreadsheet that does exactly what you need it to do.

If you don’t fall under these camps, you’re better off using a database as opposed to a spreadsheet, specially if you’re in sales.

Using Databases Compared to Spreadsheets

Databases are programs that are designed to store, organize, and retrieve data. In sales, the most common type of database used is a client relationship manager, also known as a CRM. First, learn about the benefits of a CRM. Then, let’s compare working with a database to working out of a spreadsheet.

1. A database serves as a central resource.

A CRM database is a tool that stores all of your client and customer contact information in one central place that can be easily accessed by your entire team across multiple devices. This system can organize all of your contact related data, including emails, phone calls, and live chat with customers and prospects.

2. It offers a seamless integration with other software.

Disadvantage: Learning the Syntax Takes Skill

The difficult part for many users, is that the calculations must be entered into the spreadsheet as formulas. This requires learning the correct syntax for each type of calculation you wish to make. Although many classes are available to learn the skills necessary to use these formulas, many users still find them difficult. If the syntax is incorrect, the program will not return the correct information when the calculations are run. Additionally, if users input the wrong data, even in only one cell of the spreadsheet, all related calculations and cells will be affected and have incorrect data.

Advantage: Multiple User Access

In today's collaborative work environment, multiple users within an office often need access to the same documents. If using Microsoft Excel, the spreadsheets can be shared, but only one user can change data at a time. If local copies are made and updated, other users will not have access to the new data. Google Sheets offers the solution of file sharing and allowing multiple users to access and update a single form.

Be aware that, in both cases, there is no file history. Therefore, no matter who makes changes at any time, when any changes are made, the previous information history is lost.

Disadvantage: Lack of Security

Another spreadsheet disadvantage is the lack of security for your files. Typically, spreadsheets are not that secure and therefore are at greater risk for data corruption or mismanagement of information. Files that contain sensitive financial information may not be safe from hackers, even if password protected.

Other types of data collection software therefore may be a more suitable option. Access, Oracle or some other form of relational database has built in safeguards that protect data integrity and prevent the reorganization of information. For example, in a spreadsheet, a user might sort a column of information and may inadvertently cause related information, such as first and last name, to become out of sync. In contrast, a database will keep all parts of a record unified, thereby ensuring better data integrity.

How are spreadsheets still useful?

CPA Lynda James runs Valo Financial. She says spreadsheets come into their own when a business has highly complex operations. Such firms generally have many channels of income and many avenues in which a product or service is sold. They also have varying levels of margins and prices, and different product variations.

“In this instance, it can be difficult and expensive to have a database pull all this information together because the environment is so dynamic and the source information comes from many different programs,” she says. “If CFOs want the information grouped together, one system usually can't cope. However, spreadsheets can as they are able to collate internal source documents and external reports to provide a holistic perspective around the health of a business.” This flexibility is a key reason why spreadsheets will continue to be used.

Another important and ongoing use for spreadsheets involves forecasting. Many management accountants and business analysts use retrospective data reports that are manipulated in spreadsheets to produce forecast and scenario analysis for particular variables a business may be considering.

“Whether these variables include increasing sales via different channels or introducing a new product or offer, accountants can easily use spreadsheets to project a possible outcome and adjust pricing or volume accordingly to work out the best way to reach a financial goal or sales target,” James says.

Medium-sized businesses, where larger volumes of data are not as common, tend to rely on spreadsheets to help CFOs gain an understanding of the health of their business.

“Finance chiefs can easily download reports in CSV format from bookkeeping packages and change variables to work out how to enhance profits or sell more products,” James says.

“Using a spreadsheet for these types of scenarios is affordable and easy. It also becomes to some degree a living document for the business owner, unlike standardised reporting from software,” she adds.

There's an opportunity to improve the financial literacy levels of people in the finance team, especially new hires, with spreadsheets.

“I've found customised financial spreadsheets are a great way to show people information at a level they understand,” she says. “I then slowly increase the complexity or change the key performance indicators as their knowledge grows so they can make better business financial decisions.”

Spreadsheets are improving but require good financial understanding

Standardised financial reporting has improved as bookkeeping systems advance. But without a good financial understanding it can be difficult for finance teams to fully appreciate what they're seeing in a spreadsheet.

Having an experienced accountant build a customised spreadsheet, which includes key performance indicators, is invaluable, says James. She adds that these performance indicators could include a monthly summary that highlights the key numbers to watch and track, and which also act as a “red flag” for when KPIs move in an unexpected way.

“The ability to compare KPIs, such as the average cost of goods sold or the average sales revenue of goods sold, are not typically available with standardised reports,” she says. “Also, there is some sales data that is never entered into bookkeeping software, such as when your e-commerce website or store 'cash register' is not linked to your bookkeeping software.”

James says without knowing this information it's difficult to compare what the business sells per item, sales revenue per item and direct and operational costs per item.

“These calculations tell a good story of what is happening in your business each month,” she says.

When should other technology be used?

While spreadsheets are great at showing the numbers, add-ons to bookkeeping packages and other reporting tools such as Power BI may provide a better visual representation of figures.

James says they also offer automation features that can extract data, feed it into customised reporting outputs and send the information to stakeholders.

“If you have good source data this can be a big time-saver, although you generally need a good analyst to set this up,” she says. “In Excel or another program, you would need to build the graphs and charts yourself.”

The growing use of technology within bookkeeping systems and accounting packages is helping CFOs obtain more clear, accurate and timely information about their business. James says these innovations have been fantastic for finance teams.

“The industry is changing for the better as technology evolves,” she says. “Digital tools are leading to the more routine tasks such as data inputting and compliance becoming redundant. This means accountants can focus more on providing strategic advice.”

James adds a report can tell CFOs how and when money has been earned, and eventually how much tax is needed to be paid.

“Accountants can help finance chiefs to not only expand and grow their business, but to create a sustainable income,” James says. “And spreadsheets are one tool that continues to help all firms future-proof their business.”