What are spreadsheets and how do they work?

Commonly used spreadsheet features

The following are a few of the features available in most spreadsheet programs:

• Cell formatting. Within a spreadsheet, selected cells can be formatted to represent various numeric values. For example, financial data can be given accounting formatting, which will apply decimal places and commas to represent dollars and cents.
• Formulas. Under the formula bar, users can perform calculations on the contents of a cell against the contents of another cell. For example, if a person uses a spreadsheet to reconcile transactions, they could highlight all the cells that need to be added up and insert a sum function.
• Pivot tables. Using a pivot table, users can organize, group, total, average or sort data via the toolbar. The exact tools and functions vary depending on the application the user chooses.

Common spreadsheet applications

Daniel Bricklin and Bob Frankston created the first spreadsheet application in 1978, named VisiCalc for "visible calculator." It was popular on the Apple II, one of the first computers used by businesses.

Lotus 1-2-3 surpassed VisiCalc to become the program that cemented the IBM PC as the preeminent PC in business during the 1980s and 1990s. IBM acquired Lotus in 1995 and continued selling Lotus 1-2-3 through 2013, when it discontinued the spreadsheet application, which had fallen behind Microsoft Excel in the 1990s and never recovered.

Examples of commonly used spreadsheet software programs include the following:

• Apache OpenOffice Calc is available for Linux, macOS, Windows, as well as other operating systems (OSes). The Apache Software Foundation developed Apache OpenOffice and first released the free, open source office productivity suite in 2012, but its roots go back to 2002.
• Google Sheets is part of Google's web-based application suite, Google Workspace. Google Sheets is free and available as a desktop application for Google Chrome OS and mobile Oses, such as Android and iOS.
• LibreOffice Calc is a full-featured spreadsheet app released in 2011 as part of The Document Foundation's free LibreOffice suite. LibreOffice and ApacheOpen office trace their roots to the same codebase, hence the same name for their spreadsheet applications.
• Microsoft Excel is part of the Microsoft Office suite and cloud-based subscription service Microsoft 365. Excel spreadsheets are available for Apple macOS and iOS, Google Android and Microsoft Windows OSes.
• Thinkfree Office was developed by Thinkfree Inc. as a free productivity suite with spreadsheet software specifically designed to provide a consistent experience across different devices. Each application in Thinkfree is compatible with the corresponding Microsoft Office application. An online spreadsheet version is available with cloud storage.

While Lotus 1-2-3 was the first to introduce cell names and macros, Microsoft Excel spreadsheets implemented a graphical user interface and the ability to point and click using a mouse.

Most professional spreadsheet applications offer tutorials and templates to help users get started using the tool.

Pros and cons of spreadsheets

Spreadsheets are useful and convenient for managing and analyzing large amounts of data, creating visual representations of a data set, and automating repetitive functions. However, they do have some drawbacks, such as the following:

• Spreadsheets can handle large amounts of data but not as easily as a database.
• Human error in data entry is a vulnerability.
• Data integrity is challenging and can require extra effort.
• The ability to share spreadsheets with multiple users can add complications.
• There are security vulnerabilities.
• Scalability issues can place limits on a spreadsheet's ability to meet increased requirements.

Using spreadsheets for project management

Properly configured, spreadsheets can serve as effective project management tools. While not as feature-rich as many project management tools, such as Microsoft Project, they can nonetheless support some needs.

For example, rows can be set up with specific project workflow activities, such as project start, project meeting dates and target completion dates for specific activities. Columns can be set up to track various events, such as planned start date, delay date, restart date and completion date, and send notifications when they occur. Cells can be configured to add a specific number of days to a specific start date to establish the planned completion date. Visual status reports can be created using a spreadsheet's graphics functions.

What are spreadsheet controls?

Spreadsheet controls are measures a business's accounting team uses to safeguard the integrity and accuracy of its bookkeeping practices and financial records. They provide an ongoing effort to rapidly detect and resolve errors and maintain the security of all data. Thorough training is important to the success of spreadsheet control programs.

Features of a spreadsheet control program should include the following:

• Access control, including usernames, passwords and biometric controls.
• An up-to-date list of authorized users and review of access rights.
• Segregation of duties.
• Access restrictions on cells performing critical computations, such as the locking of formula cells.
• Strong encryption and authentication techniques.
• Accuracy checks of outgoing and incoming data.
• Spreadsheet formula reviews.
• Routine maintenance of network hardware.
• Prevention of data loss or corruption through cross-referencing checksum processes.
• Comprehensive testing of the latest spreadsheet functions and features
• Scheduled archiving and incremental backup
• Backup and archiving redundancy, use of multiple media types and storage provisions offsite.
• Tracking of spreadsheet changes.
• Data recovery procedures.
• Ease of use and flexibility.

Tips for ensuring spreadsheet controls

Certain best practices are recommended to ensure spreadsheets have proper controls in place:

• Establish standards for preparing spreadsheets. Document guidance on how spreadsheets are to be created, including the correct syntax, appropriate cell values and range of cells to be used.
• Review and test formulas and formatting. This ensures the results are accurate.
• Develop a change control system. Establish an interactive process to change spreadsheets.
• Back up spreadsheet data regularly. This ensures that data is protected, whether backed up locally or in the cloud.
• Password protect spreadsheets. This prevents unauthorized users from accessing a spreadsheet.
• Store spreadsheets securely. Have spreadsheets stored in a secure location, whether locally or in the cloud.
• Lock selected cells. Ensure prior approval is required for modifying any cells that shouldn't be regularly changed or modified.
• Block incorrect or inappropriate data entry or data types. Set parameters for the kinds of data to enter in specific cells, such as numeric only or a range of acceptable values.
• Design spreadsheets for ease of use. Ensure users know how to use specific spreadsheets.
• Test and review spreadsheets and controls. Periodically check spreadsheet controls to ensure they are performing as designed.

Besides ensuring that spreadsheets aren't vulnerable to cyberattacks, use as many controls as possible to ensure that data is protected and secure.