• 8 heures
  • Facile

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 09/12/2022

Enter, copy, and reference data in your spreadsheet

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.

Here, the border is around A1, so it's the contents of A1 which will change if you start typing.
Here, the border is around A2, so it's the contents of this cell that will change if you start typing.

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

Add something in the cell
Have a go, and enter whatever you like!

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. 

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

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:

By adding B2 and B3 for example, you are referencing the cell B2 and the cell B3
You reference cells constantly when making calculations in spreadsheets like this!

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.

A formula in one column copied and pasted into another, will adapt to reference the correct cells in the new column
An example of of relative reference. 

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:

I've sold the same item , at a fixed price of 100 dollars, in several countries and I want to see how much I've made in each country
I've sold the same item in several countries, and I want to see how much I've made.

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

In the first line, Germany sold 40 items which gives a total of 4000 dollars
We can all agree that 40*$100 is $4000! 👍

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

The next row shows 50 items sold but the total shown after copying and pasting the formula is 0
😱 No way does  50*100 = 0; and I got a funny error message for 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.

When I copied and pasted, the formula switched from C1 to C2.
When I copied and pasted, the formula switched from C1 to C2 . But C2 had nothing in it. 🤦‍♀️
In the next row, the formula switched from C1 to C3 which gave an error message.
For  C6, the formula switched from C1 to C3, which had the word Total in it, rather than a number. No wonder I was getting an error message!

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:

The calculation was correct this time
🎉 It worked this time! Adding $ signs on either side of C, and then copying the formula into the cells below, meant the $100 was carried over!

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!

Exemple de certificat de réussite
Exemple de certificat de réussite