Master the Basics of Spreadsheets

As mentioned in the previous chapter, a spreadsheet is made up of columns and rows. Rows are numbered, and columns are alphabetical. Where they intersect, a cell is made.

For example, when  column A  meets  row 2, it intersects at cell  A2.

The first column of cells and the first row of cells are generally where the headers for the information are placed. After the first row and column, you can enter data into each cell. Data are the values you want to organize and analyze.

Enter data

To enter something into a cell, click in the cell you want to start recording data in. You’ll notice the cell has a border around it identifying that it is involved in the action you’re taking.

At this point, you’ll need to enter in a number or text, depending on the data you’re organizing.

Copy data

To copy data from one cell to another, select the cell you want to copy data from (your source cell), and then you can either right-click (on a PC) or command + C on a Mac, or use the menu by going to Edit then Copy in the dropdown.

Here is a quick video where I share my screen and show you what that would look like:

You can also copy and paste multiple cells of data. Here is how that would go:

Reference data

One of the benefits of spreadsheets is that they help ensure there is good quality data by referencing another cell instead of having you retype it or copy and paste it manually. In other words, you are fetching the contents of a cell to use it elsewhere, either simply as the cell contents, or so it can be used in a formula.

All you need for this is the  =  sign, and the row and column of your original cell.

=B2 is a simple reference to bring up the contents of  B2 somewhere else in a spreadsheet. If you type  =B2,  the cell where you do this will populate with the data in  B2.

Note that you can reference cells in another worksheet within the same workbook. To do this, you need to type in the sheet name between apostrophes, then add an exclamation point followed by the original cell reference.

In the example below, the sheet name I'm referencing is  Division Sales 2014-2018, and you can I added an  !  in there after the sheet name, and before the cell reference. 

However, it's quite common that you won't stop there, you'll want to reference cells to include them in a calculation.

=B2+B3  means you can add up the contents of  B2  and  B3.

Check out what this looks like in our spreadsheet:

When I click enter, my result appears -  $2,024,121  - and references to my original cells disappear.

I'll focus on that now in this last section of this chapter.

Copying functions and formulas

Hopefully, now copying cells seems simple. But watch out when you copy ones that contain functions or formulas, this needs to be done with more care.

This is when you'll need to understand the difference between relative references and absolute references. 

Relative References

Relative references are very common and it is what’s used in the examples above. It refers to the position of the rows and columns. When you copy and paste the contents of the cell with the formula in it, the spreadsheet thinks that you want to keep the general idea of the formula, adding two things up for example, but referencing different cells.

For example, I copied and pasted the formula from  B6  into  C6, and the spreadsheet didn't copy and paste my formula  =B2+B3  exactly but replaced it cells from the row below,  =C2+C3.

However, things might not be so simple if your data looks a little different. Copying and pasting your formula with relative references could give you puzzling results!

For example, take the table of data below, where we want to calculate totals:

To do this, you can use referencing. It works fine for the first total  C4=B4*C1:

But check out what happens when I copy and paste the contents of  C4  into  C5  and  C6:

This is because I didn't lock in one of my cells; in this case, the  C1  cell -  $100.00.Therefore,  my formula was looking in the wrong places after I copied and pasted.

There are going to be times when you’ll want to lock in a location when you copy and paste. In that case, add a  $  sign as an indicator that says, “I don’t want this reference to change.” This is absolute referencing! Let's look at this now.

Absolute references

In these cases, you use the  $  sign.

I want to lock in  C1  in my example.

For the spreadsheet to understand this, it is necessary to transform the relative reference into an absolute reference in the formula. The formula must be changed from  C1  to  $C$1. The  $  sign must be inserted before the column and the line.

See how it looks in the images below:

Let's recap!

• To enter data, click on the cell in which you want to add a value, and start typing.

• You can copy from one cell to another, or multiple cells at a time.

• When copying and pasting formulas or functions, absolute references lock in cells; whereas, relative references change the cells which are used in the pasted formulas or functions.

Now you are more comfortable entering data and moving it around in a spreadsheet, check out the next chapter which will explain how to autocomplete in the blink of an eye!