Spreadsheets

Click here to download an accessible PDF transcript.

Overview

Have you ever tried to insert a table in a word processing document and then tried to reorganize the information you entered? It is typically not an easy task. What about creating a budget worksheet? Have you ever made a mathematical error when adding up multiple number sets? What about staying organized when you plan a party or large event? No matter the task, it can be very difficult to manage large amounts of information. Spreadsheets help us stay organized with many of our daily tasks, and they have any number of capabilities. Spreadsheets can do all types of mathematics; including graphing. Once you have your information or data entered in a spreadsheet, you can manipulate it to do almost anything. Knowing how to use a spreadsheet is an essential skill you will need to utilize in almost any job. Knowing at least the basics may determine whether you or another candidate gets the job you’re after.

Common Applications

Just like any other type of software, there are many spreadsheet applications to choose from. The two most widely used are Microsoft Excel and Google Sheets. Most of the skills are transferable, meaning they all work in a very similar way.

Primary Uses and Functionality

Spreadsheets are so incredibly versatile that it would be impossible to pinpoint all the best ways to use them. Spreadsheets look like graph paper, but because they are electronic, you can use them for finance, organization, visual presentation, logging data, and more. Spreadsheet worksheets refer to the individual spreadsheet pages. A spreadsheet workbook is the name of the file that includes all the worksheets. The worksheets can be linked together and appear as tabs on the bottom of the page. On the very bottom of your sheet, you will see it look like this:

Each label is a tab that opens a separate worksheet. The entire file together is the workbook. There can be several worksheets in a workbook with options to name each, color code them, and move them into an order that works best for the project.

Basics of Setting Up a Spreadsheet

Spreadsheets offer some of the same (or very similar) options as a word processor when choosing font, color, size, and other basic formatting. Becoming familiar with the ribbon on the front of your spreadsheet will help you target some of these common formatting tools. The ribbon is the bar of icons under the File and Edit menus. Here, you can also choose quick formatting options that allow you to choose if you are working with currency, date, time, percents, whole numbers, etc. If you browse the Format menu, you will also find that you can change the size of the cells, columns, spacing, and even add headers and footers. These options are offered in both Microsoft Excel and Google Sheets.

Basics of Using a Spreadsheet

A spreadsheet looks like graph paper with lines going vertically and horizontally. The lines create columns and rows. Columns run vertically and are labeled by letters at the top of the page, while rows are presented horizontally and numbered. Each individual box is called a cell. You can enter plain text (or numbers) in a cell, but you also enter formulas. Formulas are mathematical instructions that work with the data found in other worksheet cells. For example, let’s say you create a budgeting worksheet. You can create a formula to add up the totals at the end of a row or a column. You can create a formula to calculate what your average monthly debt is and a formula to calculate how much you spend all year.

Explore Your Sheet

For this example, we will use Google Sheets. To create a Google Sheet, you need a Google account. If you have a Gmail or YouTube account, you already have one. If not, it is free to create one. To get started, type “Google Sheets” into the Google Search bar and click the first link that is listed.

When you create a new Sheet, it will look like this:

It can be kind of intimidating to start a new Sheet when you are unfamiliar with it, so let’s review what is here by starting with the ribbon at the top. Each of these icons is a quick link to frequently used options.

A- Go back or forward an option
B- Print your document
C- Format a cell and copy that format to any other cell you choose
D- Zoom- the percentage of the view you see
E- Change the number format you are using; quickly switch to currency or percentages
F- Change how many decimal places you want to display
G- Change the format of the numbers or text you use (plain text, currency, time, etc.)
H- Choose your font style
I- Choose your font size
J- Format your font: bold, italics, or strikethrough
K- Choose your font color and background color
L- Choose whether to add borders to your cells
M- Change the alignment and/or spacing of your cells
N- Hyperlink your cell to an outside source
O- Add a comment in the sidebar
P- Quickly create a chart
Q- Create filters for formatting your sheet
R- Lists mathematical functions that are capable on your sheet

Does it Add Up?

Let’s say you want to keep track of what you spend each month on rent, food, and electricity over the course of the year. Row 6 shows the total for each monthly column. The amount you put in the cells is whatever you type in. The formula in the cell on row 6 will automatically update the total based on the numbers you enter above it. This is convenient because you don’t have to calculate the math yourself and it will automatically update if you change the numbers above it. This may not seem like a big deal if you only have three or four rows of data, but when you start calculating large amounts of data, formulas are a huge help.

Poll

Overview

Because working with spreadsheets is relatively unavoidable, here are some more tricks to help you get the most from spreadsheet software. These tricks can be applied to most spreadsheet platforms.

Working Wonders

There are a few ways to create formulas. The easiest way to add the sum of consecutive cells is to highlight the cells involved and select the formulas drop-down menu. Here, you can choose ∑ SUM to automatically add the contents of highlighted cells. The total will appear in the last cell of the progression. As you can see based on the drop-down menu, many mathematical functions are available.

Customize It

Perhaps you do not want the total in that column or row. You can manually type in the formula to add any cells in the workbook whether they are consecutive or not. In the next example, cell G2 (Column G, Row 2) contains “=B6+C6+D6.” This instructs the spreadsheet to write the equivalent of column B, row 6 plus column C, row 6, plus column D, row 6. Because the cells in this example are presented consecutively along the row, a quicker way to express the formula is “=SUM(B6:D6).” You can use the colon to formulate other mathematical functions as well. For example, to calculate the average of monthly totals, we can use the drop-down menu, or type “=AVERAGE(B6:D6)” in any cell to get the average of row 6 cells of columns B through D.

Add-Ons

Some spreadsheet software also has a selection of add-ons available. These are third party applications that work with the software to allow a broader variety of functionality and tasks. Many of these add-ons make it very easy to perform intricate calculations or functions that don’t require the user to have expertise in spreadsheets. There are add-ons to easily perform a mail merge, use a spreadsheet for scheduling, analytics, surveys, emails, translations, and more.

Google Sheets

Anything you create in Google Sheets is saved in the Cloud which allows you to access it at any time. You aren’t just saving work from Google Sheets on one computer. There is even an app to use on your mobile device. You can share your files easily with others and choose to give them editing rights, the ability to comment, or just the ability to view. These options make teamwork and collaboration ideal. The best part of using Google Sheets is the autosave feature, meaning no more losing your work.

Just like other Google applications, Sheets can be downloaded in different formats to support non-Google users. By going to the file menu at the top of your Google Sheet, you can see the option to “Download As,” which lets you download as a Microsoft Excel file, pdf, csv, tsv, ods, or html file. In the file menu, there is also an option to “Email as attachment,” which lets you email your file as a pdf or Excel file.

Visit https://docs.google.com/spreadsheets and sign in (if you don’t have a Google account, you can create one for free).
Title your columns Food, Gas, Car Insurance.
Title your corresponding rows: January, February, March, April.
Add amounts to each cell for the corresponding expense (If you do not have one of these expenses, you can make up a number).
Add the data in your columns using a function.
Add the total for the four months.
Format the totals in bold to show their emphasis.

Your result should look something like this: