NORTHWESTERN UNIVERSITY

Laoag City

LESSON 7: WORKING WITH GOOGLE SHEETS

LEARNING OUTCOMES
At the end lesson, the students should be able to:
• create and format a worksheet using Google Sheets;
• create formulas and functions;
• create charts and graphs; and
• apply commands to protect sheets and ranges in a worksheet

Google Sheets

Google Sheets is an online spreadsheet app that lets you create and format spreadsheets
and work with other people. It allows you to organize, edit, and analyze different types of
information using spreadsheets. With Google Sheets, you can create and edit spreadsheets
directly in your web browser—no special software is required. Multiple people can work
simultaneously, you can see people’s changes as they make them, and every change is saved
automatically.

Google Sheets is a web-based spreadsheet application that allows you to store and
organize different types of information, much like Microsoft Excel. While Google Sheets does not
offer all of Excel's advanced features, it's easy to create and edit spreadsheets ranging from the
simple to the complex.

While you might think spreadsheets are only used by certain people to process
complicated numbers and data, they can actually be used for a variety of everyday tasks.
Whether you're starting a budget, planning a garden, or creating an invoice or just about
anything else you can think of, spreadsheets are a great way to organize information.

Create a New File

Choose an option:
• In Sheets, (sheets.google.com) click Create .
• In Drive,(drive.google.com) click New and then next to Google Sheets, point to the
Right arrow and click Blank spreadsheet or From a template.

To create a new Google spreadsheet:

1. While viewing your Google Drive, click New and select Google
Sheets from the drop-down menu.

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |1
 NORTHWESTERN UNIVERSITY
Laoag City

2. The spreadsheet will appear in a new browser tab.

3. To name your spreadsheet, locate and select Untitled spreadsheet at the

top of the page. Type a name for your spreadsheet, then press Enter on
your keyboard.

4. Your spreadsheet will be renamed.

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |2
 NORTHWESTERN UNIVERSITY
Laoag City

5. Whenever you need to view or edit your spreadsheet, you can access it
again from your Google Drive, where it will be saved automatically.

*** You may notice that there is no save button. This is because Google Drive uses autosave,
which automatically and immediately saves your files as you edit them.

The Google Sheets Interface

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |3
 NORTHWESTERN UNIVERSITY
Laoag City

1. Navigate to Google Sheets Home Screen. Click this icon to navigate back to your Google
Sheets homepage.
2. Spreadsheet Title. By default, any new spreadsheet is named Untitled Spreadsheet. Click
the title to rename the spreadsheet.
3. Toolbar Menu. You can use the toolbar menu options to edit and modify your
spreadsheet in various ways. Click a menu and select the desired action from the drop-
down list.
4. Saved Changes Notification. Google Sheets automatically saves your changes as you
work. The saved changes notification lets you know your spreadsheet has been saved.
5. Collaboration Options. Collaboration options allow you to share your spreadsheet with
others and make comments as you work.
6. Shortcut Toolbar. This toolbar provides convenient shortcut buttons for formatting the
data in your spreadsheet, like font size, text alignment, and text color.
7. Formula Bar. You can use the formula bar to enter and edit data, functions, and formulas
for a specific cell.
8. Column. A column is a group of cells that runs vertically from top to bottom. Columns are
identified by letters. Column C is selected in this example.
9. Row. A row is a group of cells that runs horizontally from side to side. Rows are identified
by numbers. Row 9 is selected in this example.
10. Cell. Each rectangle in a spreadsheet is called a cell. A cell is the intersection of a row and
a column. Click to select a cell. Cell A1 is selected in this example.
11. Sheets Toolbar. Every spreadsheet can have multiple sheets. Select the sheet tabs to
navigate among sheets, and click the plus sign (+) to add a new sheet.

Cell basics

Every spreadsheet is made up of thousands of rectangles, which are called cells. A cell is
the intersection of a row and a column. Columns are identified by letters (A, B, C), while rows
are identified by numbers (1, 2, 3).
columns
rows

Each cell has its own name—or cell address—based on its column and row. In this example, the
selected cell intersects column C and row 10, so the cell address is C10. Note that a cell's column
and row headings become darker when the cell is selected.
You can also select multiple cells at the same time. A group of cells is known as a cell range.
Rather than a single cell address, you'll refer to a cell range using the cell address of

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |4
 NORTHWESTERN UNIVERSITY
Laoag City

the first and last cells in the cell range, separated by a colon. For example, a cell range that
included cells A1, A2, A3, A4, and A5 would be written as A1:A5.

In the images below, two different cell ranges are selected:

• Cell range A2:A8

• Cell range A2:B8

Understanding cell content

Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain
several different types of content, including text, formatting, formulas, and functions.

• Text: Cells can contain text, such as letters, numbers, and dates.

• Formatting attributes: Cells can contain formatting attributes that change the
way letters, numbers, and dates are displayed. For example, percentages can
appear as 0.15 or 15%. You can even change a cell's background color.

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |5
 NORTHWESTERN UNIVERSITY
Laoag City

• Formulas and functions: Cells can contain formulas and functions that
calculate cell values. In our example, SUM(B2:B8) adds the value of each cell in cell
range B2:B8 and displays the total in cell B9.

To select cells:
To input or edit cell content, you'll first need to select the cell.
1. Click a cell to select it.
2. A blue box will appear around the selected cell.

You can also select cells using the arrow keys on your keyboard.

To select a cell range:

Sometimes you may want to select a larger group of cells, or cell range.
1. Click and drag the mouse until all of the cells you want to select are highlighted.
2. Release the mouse to select the desired cell range.

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |6
 NORTHWESTERN UNIVERSITY
Laoag City

To insert cell content:

1. Select the desired cell.

2. Type content into the selected cell, then press Enter. The content will appear in
the cell and the formula bar. You can also input content into and edit cell content
in the formula bar.

To delete cell content:

1. Select the cell you want to delete.

2. Press the Delete or Backspace key on your keyboard. The cell's contents will be
deleted.

To use the fill handle:

There may be times when you want to copy the content of one cell to several other cells in
your spreadsheet. You could copy and paste the content into each cell, but this method would be

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |7
 NORTHWESTERN UNIVERSITY
Laoag City

time consuming. Instead, you can use the fill handle to quickly copy and paste content from one
cell to any other cells in the same row or column.

1. Select the cell you want to use. A small square—known as the fill handle—will
appear in the bottom-right corner of the cell.
2. Hover the mouse over the fill handle. The cursor will change to a black cross.

3. Click and drag the fill handle over the cells you want to fill. A dotted black
line will appear around the cells that will be filled.

4. Release the mouse to fill the selected cells.

Using the fill handle to continue a series

The fill handle can also be used to continue a series. Whenever the content of a row or
column follows a sequential order—like numbers (1, 2, 3) or days (Monday, Tuesday,
Wednesday)—the fill handle will guess what should come next in the series. In our example
below, the fill handle is used to extend a series of dates in a column.

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |8
 NORTHWESTERN UNIVERSITY
Laoag City

Modifying Columns, Rows and Cells

Every row and column of a new spreadsheet is always set to the same height and width.
As you begin to work with spreadsheets, you will find that these default sizes are not always well-
suited to different types of cell content.

To modify column width:

In our example below, some of the content in column B is too long to be displayed. We can
make all of this content visible by changing the width of column B.

1. Hover the mouse over the line between two columns. The cursor will
turn into a double arrow.

2. Click and drag the column border to the right to increase column width.
Dragging the border to the left will decrease column width.
3. Release the mouse when you are satisfied with the new column width. All of the
cell content is now visible.
To autosize a column's width:

The autosizing feature will allow you to set a column's width to fit its content automatically.

1. Hover the mouse over the line between two columns. The cursor will
turn into a double arrow.
2. Double-click the mouse.
3. The column's width will be changed to fit the content.

To modify row height:

You can make cells taller by modifying the row height. Changing the row height will create
additional space in a cell, which often makes it easier to view cell content.

1. Hover the mouse over the line between two rows. The cursor will turn
into a double arrow.

I T 1 0 1: L I V I N G I N T H E I T E R A P a ge |9
 NORTHWESTERN UNIVERSITY
Laoag City

2. Click and drag the row border down to increase the height. Dragging the
border up will decrease the row height.

3. Release the mouse when you are satisfied with the new row height.

Inserting, deleting, and moving rows and columns

After you've been working with a spreadsheet for a while, you may find that you want to add
new columns or rows, delete certain rows or columns, or even move them to a different location
in the spreadsheet.

To insert a column:
1. Right-click a column heading. A drop-down menu will appear. There are two
options to add a column. Select Insert 1 left to add a column to the left of the
current column, or select Insert 1 right to add a column to the right of the
current column.

2. The new column will be inserted into the spreadsheet.

To insert a row:

1. Right-click a row heading. A drop-down menu will appear. There are two options
to add a row. Select Insert 1 above to add a row above the current row, or
select Insert 1 below to add a column below the current row.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 10
 NORTHWESTERN UNIVERSITY
Laoag City

2. The new row will be inserted into the spreadsheet.

If you need to add more than one row at a time, you can scroll to the bottom of the spreadsheet
and click the Add button. By default, this will add 1000 new rows to your spreadsheet, but you
can also set the number of rows to add in the text box.

To delete a row or column:

It's easy to delete any row or column you no longer need in your spreadsheet. In our example,
we'll delete a row, but you can delete a column in the same way.

1. Select the row you want to delete.

2. Right-click the row heading, then select Delete row from the drop-down
menu.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 11
 NORTHWESTERN UNIVERSITY
Laoag City

3. The rows below the deleted row will shift up to take its place. In our
example, row 8 is now row 7.

There's a difference between deleting a row or column and simply clearing its contents. If you
want to remove the content of a row or column without causing the others to shift, right-click a
heading, then select Clear row or Clear column.

To move a row or column:

Sometimes you may want to move a column or row to make it more accessible in your
spreadsheet. In our example, we'll move a column, but you can move a row in the same way.
1. Select the column you want to move, then hover the mouse over the
column heading. The cursor will become a hand icon.

2. Click and drag the column to its desired position. An outline of the
column will appear.

3. Release the mouse when you are satisfied with the new location.

Wrapping text and merging cells

Whenever you have too much cell content to be displayed in a single cell, you may decide to wrap
the text or merge the cell rather than resize a column. Wrapping the text will automatically
modify a cell's row height, allowing the cell contents to be displayed on multiple lines. Merging
allows you to combine a cell with adjacent empty cells to create one large cell.

To wrap text:
1. Select the cells you want to wrap. In this example, we're selecting cell
range C3:C10.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 12
 NORTHWESTERN UNIVERSITY
Laoag City

2. Open the Text wrapping drop-down menu, then click the Wrap button.

3. The cells will be automatically resized to fit their content.

To merge cells:
1. Select the cells you want to merge. In this example, we're selecting cell
range A1:C1.

2. Select the Merge cells button.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 13
 NORTHWESTERN UNIVERSITY
Laoag City

3. The cells will now be merged into a single cell.

To unmerge a cell, click the drop-down arrow next to the Merge cells button, then
select Unmerge from the drop-down menu.

Freezing rows and columns

When working with large spreadsheets, there will be times when you'll want to see certain rows
or columns all the time, especially when using header cells as in our example below.
By freezing rows or columns in place, you'll be able to scroll through your spreadsheet while
continuing to see the header cells.

To freeze a row:
1. Locate the row or rows you want to freeze. In this example, we'll freeze the top
two rows. Note: You do not need to select the rows you want to freeze.

2. Click View in the toolbar. Hover the mouse over Freeze, then select the
desired number of rows to freeze from the drop-down menu.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 14
 NORTHWESTERN UNIVERSITY
Laoag City

3. The top two rows are frozen in place. You can scroll down your
worksheet while continuing to view the frozen rows at the top.

To freeze a column:
1. Locate the column or columns you want to freeze. In this example, we'll
freeze the leftmost column. Note: You do not need to select the columns
you want to freeze.

2. Click View in the toolbar. Hover the mouse over Freeze, then select the
desired number of columns to freeze from the drop-down menu.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 15
 NORTHWESTERN UNIVERSITY
Laoag City

3. The leftmost column is now frozen in place. You can scroll across your
worksheet while continuing to view the frozen column on the left.

To unfreeze rows, click View, hover the mouse over Freeze, then select No rows. To unfreeze
columns, click View, hover the mouse over Freeze, then select No columns.

Creating simple formulas

A convenient and time-saving feature of Google Sheets is its ability to add, subtract,
multiply, and divide numerical information for you. Google Sheets uses mathematical expressions
called formulas that make handling these calculations easy. In this lesson, we'll focus on formulas
that contain one mathematical operator.

Most of the time, you will be using a cell's address in the formula. This is called using
a cell reference. The advantage of using cell references is that you can change a value in a
referenced cell and the formula will automatically recalculate. Using cell references in your
formulas will make sure the values in your formulas are accurate.

Mathematical operators

Google Sheets uses standard operators for formulas: a plus sign for addition (+), minus
sign for subtraction (-), asterisk for multiplication (*), forward slash for division (/),
and caret (^) for exponents.

All formulas must begin with an equals sign (=). This is because the cell contains—or is
equal to—the formula and the value it calculates.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 16
 NORTHWESTERN UNIVERSITY
Laoag City

Using cell references

When a formula contains a cell address, it is using a cell reference. Creating a formula
with cell references is useful because you can update the numerical values in cells without having
to rewrite the formula.

By combining a mathematical operator with cell references, you can create a variety of simple
formulas in Google Sheets. Formulas can also include a combination of a cell reference and a
number.

Creating formulas

In our example, we'll use simple formulas and cell references to help calculate a budget.

To create a formula:
1. Select the cell that will display the calculated value.

2. Type the equals sign (=).

3. Type the cell address of the cell you want to reference first in the
formula. A dotted border will appear around the cell being referenced.

4. Type the operator you want to use. For example, type the addition
sign (+).
5. Type the cell address of the cell you want to reference second in the
formula.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 17
 NORTHWESTERN UNIVERSITY
Laoag City

6. Press the Enter key on your keyboard. The formula calculates, and
Google Sheets displays the result.
Types of Cell References

There are two types of cell references: relative and absolute. Relative and absolute references
behave differently when copied and filled to other cells. Relative references change when a
formula is copied to another cell. Absolute references, on the other hand, remain constant no
matter where they are copied.

Relative references

By default, all cell references are relative references. When copied across multiple cells, they
change based on the relative position of rows and columns. For example, if you copy the
formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are
especially convenient whenever you need to repeat the same calculation across multiple rows or
columns.

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each item's price by
the quantity. Instead of creating a new formula for each row, we can create a single formula in
cell D4 and then copy it to the other rows. We'll use relative references so the formula calculates
the total for each item correctly.

1. Select the cell that will contain the formula. In our example, we'll select cell D4.

2. Enter the formula to calculate the desired value. In our example, we'll
type=B4*C4.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 18
 NORTHWESTERN UNIVERSITY
Laoag City

3. Press Enter on your keyboard. The formula will be calculated, and the result will
be displayed in the cell.
4. Select the cell you want to copy. In our example, we'll select cell D4. The fill
handle will appear in the bottom-right corner of the cell.
5. Click and drag the fill handle over the cells you want to fill. In our example, we'll
select cells D5:D13.
6. Release the mouse. The formula will be copied to the selected cells with relative
references, displaying the result in each cell.

You can double-click the filled cells to check their formulas for accuracy. The relative cell
references should be different for each cell, depending on their rows.

Absolute references

There may be times when you do not want a cell reference to change when copying or filling cells.
You can use an absolute reference to keep a row and/or column constant in the formula.

An absolute reference is designated in the formula by the addition of a dollar sign ($). It can
precede the column reference, the row reference, or both.

You will most likely use the $A$2 format when creating formulas that contain absolute
references. The other two formats are used much less often.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 19
 NORTHWESTERN UNIVERSITY
Laoag City

To create and copy a formula using absolute references:

In the example below, we're going to use cell E2 (which contains the tax rate at 7.5%) to calculate
the sales tax for each item in column D. To make sure the reference to the tax rate stays
constant—even when the formula is copied and filled to other cells—we'll need to make
cell $E$2 an absolute reference.

1. Select the cell that will contain the formula. In our example, we'll select cell D4.
2. Enter the formula to calculate the desired value. In our example, we'll type
=(B4*C4)*$E$2, making $E$2 an absolute reference.

3. Press Enter on your keyboard. The formula will calculate, and the result will
display in the cell.
4. Select the cell you want to copy. In our example, we'll select cell D4. The fill
handle will appear in the bottom-right corner of the cell.
5. Click and drag the fill handle over the cells you want to fill
(cells D5:D13 in our example).
6. Release the mouse. The formula will be copied to the selected cells with
an absolute reference, and the values will be calculated in each cell.

You can double-click the filled cells to check their formulas for accuracy. The absolute reference
should be the same for each cell, while the other references are relative to the cell's row.

Be sure to include the dollar sign ($) whenever you're making an absolute reference across
multiple cells. Without the dollar sign, Google Sheets will interpret it as a relative reference,
producing an incorrect result when copied to other cells.

Working with Functions

A function is a predefined formula that performs calculations using specific values in a

particular order. Excel includes many common functions that can be used to quickly find
the sum, average, count, maximum value, and minimum value for a range of cells. In order to
use functions correctly, you'll need to understand the different parts of a function and how to
create arguments to calculate values and cell references.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 20
 NORTHWESTERN UNIVERSITY
Laoag City

The parts of a function

Similar to entering a formula, the order in which you enter a function into a cell is important. Each
function has a specific order—called syntax—that must be followed in order for the function to
calculate properly. The basic syntax to create a formula with a function is to insert an equals sign
(=), a function name (AVERAGE, for example, is the function name for finding an average), and
an argument. Arguments contain the information you want the formula to calculate, such as a
range of cell references.

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed
within parentheses. You can include one argument or multiple arguments, depending on the
syntax required for the function.

For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the
cell range B1:B9. This function contains only one argument.

Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3,
C1:C2, E1) will add the values of all of the cells in the three arguments.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 21
 NORTHWESTERN UNIVERSITY
Laoag City

Creating a function

Google Sheets has a variety of functions available. Here are some of the most common functions
you'll use:

• SUM: This function adds all of the values of the cells in the argument.
• AVERAGE: This function determines the average of the values included
in the argument. It calculates the sum of the cells and then divides that
value by the number of cells in the argument.
• COUNT: This function counts the number of cells with numerical data in
the argument. This function is useful for quickly counting items in a cell
range.
• MAX: This function determines the highest cell value included in the
argument.
• MIN: This function determines the lowest cell value included in the
argument.

To create a function using the Functions button:

The Functions button allows you to automatically return the results for a range of cells. The
answer will display in the cell below the range.

1. Select the range of cells you want to include in the argument. In our
example, we'll select D3:D12.

2. Click the Functions button, then select the desired function from the
drop-down menu. In our example, we'll select SUM.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 22
 NORTHWESTERN UNIVERSITY
Laoag City

3. In the cell directly below the selected cells, the function appears.

4. Press the Enter key on your keyboard. The function will be calculated,
and the result will appear in the cell. In our example, the sum
of D3:D12 is $765.29.

To create a function manually:

If you already know the function name, you can easily type it yourself. In the example below,
which is a tally of cookie sales, we'll use the AVERAGE function to calculate the average number
of units sold by each troop.

1. Select the cell where the answer will appear. In our example, we'll select C10.
2. Type the equals sign (=), then type the desired function name. You can also
select the desired function from the list of suggested functions that appears
below the cell as you type. In our example, we'll type =AVERAGE.
3. When typing a function manually, Google Sheets also displays a window that lists
the specific arguments the function needs. This window appears when the first
parenthesis is typed and stays visible as the arguments are typed.
4. Enter the cell range for the argument inside parentheses. In our example, we'll
type (C3:C9). This formula will add the values of cells C3:C9, then divide that value
by the total number of values in the range.
5. Press the Enter key on your keyboard, and the answer appears.

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 23
 NORTHWESTERN UNIVERSITY
Laoag City

Google Sheets will not always tell you if your function contains an error, so it's up to you to check
all of your functions.

References:

GCFGlobal (2022). Getting Started with Google Sheets.

https://edu.gcfglobal.org/en/googlespreadsheets/getting-started-with-google-sheets/1/

Google (2022). Get Started with Sheets.

https://support.google.com/a/users/answer/9310369?hl=en

I T 1 0 1: L I V I N G I N T H E I T E R A P a g e | 24