How Do Spreadsheets Work?

by Cynthia Gaffney ; Updated November 09, 2018
Spreadsheet bank accounts accounting with calculator and magnifying glass.

Spreadsheets are a popular tool in the office and can come in handy at home, as well. Whether you need to have a schedule to detail out and automatically add up the costs for a wedding, produce an invoice for your freelance client or create a set of financial statements for your business, spreadsheets are up to the task.

There might be a learning curve if you want to use some of the more advanced features, but once you understand how spreadsheets work and their capabilities, you'll see their uses are almost endless.

What Is a Spreadsheet?

A spreadsheet is an electronic document that uses rows and columns to store and manipulate data. It's a digital, software-based worksheet, modeled after the paper form of an accountant's grid paper. The rows and columns form cells which hold separate pieces of data. You can use spreadsheets to perform mathematical calculations and other manipulations on the data you enter.

Many people also use spreadsheets to record and store data that has multiple attributes. For example, a person's name, their address details, the name of the company they work for, their title and business relationship status can be used for a sales prospecting database.

You can sort data in a spreadsheet and perform various types of analysis on it. You can also apply various formulas and functions to the data. Spreadsheets offer the ability to organize your information and use it as the basis to create tables, charts and graphs for analysis.

Why Use a Spreadsheet?

One of the most popular reasons for using spreadsheets is to store, record and print data. It's easy to organize and sort data with a spreadsheet, and because of the layout with rows and columns of cells, you can click anywhere to update the information easily without having to worry about invisible spaces, returns or tabs as you would in some other programs such as a word processor.

You can automate various functions to run calculations, look up certain items or pull updates information from other programs or the web. For example, accountants might add new data each month to their spreadsheets and use a simple "sum" formula to update the total of the data automatically.

Spreadsheets offer a quick, easy way to display data and share it with others. You can type in data or copy it from another program and paste it into a spreadsheet, save your file and easily email it to someone else. Office workers can save a spreadsheet on their office computer network to allow other people to open the file and update various parts of it.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla

Spreadsheets offer the ability to store large amounts of data in one place because a spreadsheet file has more than one sheet in it. Each tab of the file is called a worksheet, and the file is called a workbook. You could store financial information, formatted into financial statements, for a small business, and use a different tab for each month of the year. You can then add another tab that uses links to summarize and add up all the data into an annual figure.

How to Use Google Excel

Excel is the name of the Microsoft Office spreadsheet product, but you can use it with Google, instead of using Google's spreadsheet application. One of the biggest advantages of using the Microsoft Excel spreadsheet program within Google's application Google Drive is that you can work on Excel spreadsheets online.

With Microsoft Office, unless you have the online Office 365 subscription, you will only be able to access your Excel spreadsheet files from your own computer's hard drive or network file folder. If you want to work on Excel spreadsheets within Google drive, you can also convert your Excel spreadsheet to a Google Sheets file. If you upload an Excel file to store in your Google Drive and try to open it, you will see a static document that will not allow you to make any changes.

However, Microsoft and Google have improved their ability to work together on Microsoft Office files within Google. You can install Google Drive's Microsoft Office plug-in, found in Google Play. Once you've installed the plug-in when you open your Microsoft Office applications, you will see a drop-down menu that says Google Drive, and it will give you various options to open, edit and save your Microsoft Office files, keeping them in the Microsoft Office format while storing them inside your Google Drive.

To get the plug-in, perform an online search in your browser for Google Drive plug-in for Microsoft Office. You'll be asked to download the "_driveforoffice.exe"_ file and follow a simple installation process.

How to Use Google Sheets

To create a spreadsheet within Google Sheets, make sure that you are logged into your Google account, open a browser tab, navigate to drive.google.com, or sheets.google.com and create a new Sheets document.

Once you have navigated to Google Drive, using the menu bar on the left side of your screen or the red "+" button on the lower right of your screen, create a new Sheets document, and you'll be ready to start using your spreadsheet. Google Sheets allows you to easily collaborate with other people online once you send them a link to your spreadsheet. Several people can actively work in the file at the same time, and you can see who's in the file and even what spreadsheet tab and cell they're working on. With Sheets, you can also use Google's other integrated tools, such as translating text in your spreadsheet, using formulas to automatically grab stock price data from online sites and importing other types of data directly from web pages into your spreadsheet.

Google Sheets offers pre-built templates for various uses such as a budget, freelance invoice, company financial statements, project tracker, Gantt chart, employee shift schedule, sales contact database and more. You can also find these and other templates within Microsoft Excel and other spreadsheet programs. One of the benefits of Google Sheets is that your work is saved automatically every few seconds. If you use the Excel spreadsheet program you have the option of saving automatically, but it's not the default feature, and you may lose work unless you manually save your work frequently.

Basic Spreadsheet Functions

The basic functions of a spreadsheet involve storing three types of data, which are text, numbers and formulas or functions. Most people use spreadsheets to perform basic math computations, such as addition, subtraction, multiplication and division on their data. You can also store rows of data with several attributes, such as name and address details.

Spreadsheets typically have many rows to accommodate a large quantity of data, with a maximum of two-million cells made up of however many columns and rows you need, up to that cell limit. Microsoft Excel's latest version currently offers 1,048,576 rows and 16,384 columns for you to store your data.

Spreadsheet data can be copied as tables and pasted into other programs, such as a word processor or slide presentation program, to create various schedules for analysis, reporting and presentation purposes. You can also format your data using colors, lines, text boxes, images and many other features to call attention to certain parts of your data or your spreadsheet schedule.

What Are Advanced Spreadsheet Functions?

Depending on whether you use Google Sheets, Microsoft Excel or another spreadsheet program, you'll have access to many advanced features that can help you build all types of models to process and analyze data.

For example, you can use spreadsheets to perform statistical regression analyses, discounted cash flow modeling and many other exercises. You can create a workbook with several spreadsheets in it that hold financial statement data for each month of a year. You can then create another spreadsheet in the same workbook or a new workbook, that has assumptions for business growth, sales growth, percentage changes in costs and other assumptions. Using formulas and links, you can then create a forecasted financial statement that automatically updates whenever you change assumptions in your spreadsheet assumptions tab.

You can use a variety of different automated formulas and functions, including "if/then" formulas which allow you to say if "X" condition is met, then perform "Y" function or formula. You can even nest these if/then statements and Microsoft Excel can handle up to seven if/then statements nested inside each other, meaning that you can have up to seven conditional functions, dependent upon each other, automatically applied to your data.

Some spreadsheet programs offer a macro recorder, which allows you to record various steps of a routine process such as spreadsheet formatting or entering certain types of data without needing to know Visual Basic or any other programming language. In the future, instead of performing the actual functions in your spreadsheet, you can use shortcut keys, and your process performs automatically because the steps have been saved in the form of a macro.

Spreadsheets offer functions such as "goal seek," which allows you to iterate formulas and solve them for certain defined answers and change the inputs accordingly. If you have large sets of data to work with, certain functions allow you to identify and remove duplicates, sort and filter the data, consolidate it, subtotal it, rank it and look up data values vertically or horizontally and return them into a new table. These are just a few examples of the multitude of advanced functions that spreadsheets offer.

Spreadsheets Versus Database Programs

Many people use spreadsheets to store rows of data, and you might wonder why you wouldn't just use a database program such as Microsoft Access to store the data instead of using a spreadsheet. While database and spreadsheet programs can both store rows of data with multiple attributes, each has their strengths.

Excel, Google Sheets and other spreadsheets store data with headers in the same manner that a database program would. However, with spreadsheet data, you can do automatic formula calculations or manual calculations easily on the data. Spreadsheets are also simple to use, and you can start entering and working with your data right away instead of having to set up the database as you would in a program such as Access. With spreadsheets, you can also easily create charts and graphs based on your data.

On the other hand, databases tend to be more stable. With spreadsheet data, it's easy to mistakenly delete a cell, row or column of data if you're not paying careful attention, which is much more difficult to do within a database program. Additionally, if you have large quantities of data, they could become unmanageable in a spreadsheet, where database programs are specifically designed for the management of thousands and even millions of rows of data records. When you need to continually update elements of each row of data records or perform various types of sorting and reporting, a database program might make more sense than a spreadsheet.

Resources

About the Author

Cynthia Gaffney has spent over 20 years in finance with experience in valuation, corporate financial planning, mergers & acquisitions consulting and small business ownership. She has worked as a financial writer and editor for several online finance and small business publications since 2011, including AZCentral.com's Small Business section, The Balance.com, Chron.com's Small Business section, and LegalBeagle.com. A Southern California native, Cynthia received her Bachelor of Science degree in finance and business economics from USC.

Cite this Article A tool to create a citation to reference this article Cite this Article