Introduction to Information Technology/Spreadsheets

Introduction

Spreadsheets are computer applications used to store, analyze, organize and manipulate data in the rows and columns of a grid. The program operates by taking in data, which can be numbers or text, into the cells of tables.  If the data is numbers, the program will compute it for you depending on the function you need to be completed. Microsoft Excel is currently the industry standard for spreadsheets and worksheets. It is the most used spreadsheet and is available for Windows, MacOS, Android, and iOS. Other programs used include Google sheets, a cloud web-based program, LibreOffice, and several more. The jobs that were once done by accountants are now managed and filed by a computer program for reasons of efficiency and organization. Spreadsheets and computer programs used to optimize data have changed the world for business and data analysis.

An excel table used to create a graph from a formula

Features and Terminology

Spreadsheets have many features which help users visualize and manipulate data. This allows information to be processed faster and with more efficiency. Spreadsheets allow users to enter simple or complex formulas to perform automatic calculations on data in multiple cells. Spreadsheets can also perform dynamic updates, allowing users to generate data in one cell based on the values of others. Spreadsheet software gives users the ability to generate graphs and charts based on your inputted data.

When working with spreadsheets, one of the common terminology used is "cell", without a cell, there cannot be a spreadsheet. In other words, we can say a spreadsheet is the arrangement of cells in rows and columns. A "cell" is a box where all data is inputted within a spreadsheet. A cell can be identified by the intersections of the rows and columns assigned to data that it represents, defined as the "cell address". They are usually expressed in the format of, (column row). Examples of cell addresses can be A1,(column A row 1) B2(column B row 2),C3 (column C row 3), etc. The data imputed into a cell are usually texts, a numeric value, or a formula.

Spreadsheet Software

Microsoft Excel

Microsoft Excel is the most used spreadsheet software around the world.  The spreadsheets present tables arranged in rows and columns and are used to calculate basic and complex mathematical operations and functions. Along with the ability to handle complex mathematical operations, Excel features graphing tools, pivot tables, and also features a macro programming language called Visual Basic for Applications. The first version of Excel was released on September 30, 1985, for the Macintosh. The first Windows version wasn't released until November of 1987.

Gnumeric

Gnumeric is an open source spreadsheet program that is part of GNOME free Software Desktop Project. Gnumeric version 1.0 was released on December 31, 2001. Its original intention was to replace other spreadsheet programs such as Microsoft Excel.

Formulas

Formulas used in spreadsheets, automatically process data how the user see fits. The formula takes data from certain areas in the spreadsheet, processes it, and places the output into the new area of the spreadsheet based on where the formula is written. The formula can be as simple as "=SUM(A10,A11)" (which takes the information in the 10th and 11th cells of row A and outputs the sum), or as complex as the user wishes to make it. The functions used to create the formula (such as SUM), are predesignated by the spreadsheet software.

Functions

A function uses a specific formula on an input to produce an output. They make it possible to do complicated math problems in spreadsheets without knowing the actual formula as functions are built into the software. For example, if you use a sum function for a column to find the total, all you would need to do is select all the cells you want to add and then use the SUM function. These are also useful when working with large amounts of data. With functions a complex question of, "How much money does the average customer spend in my store?" could be accounted for by summing the total of all specified cells and dividing by the average amount of money. Functions don't always have to be the right way of working with an Excel document or any spreadsheet. Oftentimes, functions are used when compiling large amounts of data that creating a function takes less time than doing estimates by hand.  

Common Functions

SUM

This function gives you a sum of all numeric data in a specified range of numbers.

Example:

SUM in action

COUNT

This function counts the number of cells (only numbers will count) that are in a specified range of numbers.

Example:

COUNT in action

COUNTA

This function counts the number of cells of any values: numbers, text, error. This function does not count any empty cells.

Example:

VLOOKUP

This function is used to find and retrieve matched values of a table. This matched values will fit a value from the first column of a table with a specified argument (you will have to specify which data is true (approximately matched) or false (exact match)).

Example:

MIN

MIN in action

This function finds the lowest value in a specified range of numbers.

Example:

MAX

This function finds the highest value in a specified range of numbers.

Example:

AVERAGE

This function gives you an arithmetic mean of numeric data in a specified range of numbers.

Example:

CONCATENATE

This function is used to combine text from left to right in one cell. It is used when you separate the list of first and last names into two columns. Then, you use CONCATENATE function to specify one first name and one last name. It combines those names from two cells in one cell.

Example:

PROPER, UPPER, and LOWER

These three functions are used to format text (word). The UPPER function capitalizes each word capitalized. A PROPER function capitalizes the first letter of each word. The LOWER function formats each word in lowercase.

Example:

NOW

This function gives you the current date and time.The date and time on your system will be recorded. It should be noted that if the date and time on your system is incorrect, the date and time inserted will also be incorrect.

Example:

TODAY

This function only shows the current date.

Example:

Spreadsheet Risk

This is the risk associated with errors being made to spreadsheets that in turn will be used to make numerical decisions. These errors include data input errors, calculation errors and formatting errors. One single mistake in input to a spreadsheet could change the end result in calculations, thus determining business decisions in an inaccurate way. Other risks can include loss of data, lack of documentation standards, and lack of skilled users.  

Analyzing Data

Pivot Tables

A pivot table is an interactive table that allows a person to categorize large amounts of data in a concise, tabular format for easier reporting and analysis. Pivot tables are used to sort, tally, and compile the data, which can be a simpler way to organize a spreadsheet. When using a pivot table, you are able to take many data items and make it simpler to summarize the information. Pivot tables allow selection of a certain set of data and see only that specific data.  Also, when using Microsoft Excel, you will have to create your own pivot table from the entire data in the original table. A list of all the column headers allows the user to input the different data types that they need, which makes it more accessible.

Multi-Dimensional Spreadsheets

A Multi-dimensional spreadsheet is a spreadsheet with a third dimension, allowing for more advanced data management. This third dimension acts like pages of a book containing multiple spreadsheets of a similar format or topic. One of the most common examples is a spreadsheet for each month of the year with different information for each month, contained in a single file. This also allows information from these multiple spreadsheets to be calculated together while in a single organized file.

Charts

Charts are visual application featured in a spreadsheet. That and other applications are useful when working with formulas and multiple cells. Charts can be linked to formulas and functions within the same spreadsheet or from a completely separate spreadsheet.  

Creating Charts

Creating a chart in most spreadsheet programs is usually as simple as defining a chart's size, then assigning properties to the chart that allow it to interact with other elements. With modern spreadsheets, this process is almost completely automated, with the user only having to add input values.