What are spreadsheets again?
In my last article I talked about the basic layout of a spreadsheet. I also mentioned a few of the most common uses for teachers and educators. Based on my experience, teachers have used spreadsheets primarily for the following:
1. Lists: tasks, items in an inventory, birthdays, emails, attendance
2. Grades: test scores, letter grades, average scores, percentages
3. Schedules: digital lesson planners, daily time tables, weekly schedules, yearly calendars
4. Forms: survey questions, quizzes, personal information
When I started with spreadsheets, I just used it as a list. I thought of it as a table with the boxes and lines already set up. I didn’t know much about the actual functionality of a spreadsheet. The first “formula” I learned about in spreadsheets was cell referencing.
Cell referencing is a way to make some information in a cell show up in another cell. It is kind of like copying a cell. The only difference is that the cells are linked together. This means that if information in the original cell is changed, the other cell will update automatically.
OK, so you might be wondering how this is helpful. Let me show you an example using a class list of names. This is the first way I used it. Download a sample spreadsheet to play with.
On a tab called Class Roster, I have a list of student information. Included in the list is student names. I also have other tabs in my spreadsheet. I have a tab for attendance, math scores and reading scores. I want to be able to enter different information on each tab for the students. However, I want to have the same students show up on each tab. I could easily copy and paste the information from the Class Roster tab to the other tabs.
Of course, this will work. But what happens when a new student joints the class? I would have to go in and enter that student in each of the tabs. Or maybe I need to update information about the way a student’s name is spelled. I would have to go into each tab and update these individually. This is where cell referencing saves time and increases efficiency.
Just above the columns (A,B,C…) there is the formula bar. The formula bar tells you what information is in the cell. You can see the fx symbol on the left. If you click on a cell with information in it already, such as a name or number, you will see that information show up in the formula bar. In the image above, you can see the cell address as C2 and the formula bar contains “James Riley” which is the data in C2.
How do you get cell referencing to work?
1. Click on an empty cell. Let’s choose D2.
2. Type the = sign
3. Click on a cell you want to copy like a student name. Let’s use C2. In the formula bar, you will see the = and then the cell address (C2). The color of the text should change. This means that you are entering a formula instead of just numbers or text.
4. Click return. You should see the same information show up. It looks like a copy of the information.
5. Go back to the original cell (C2).
6. Change the text to something else. When you click return, the information in both C2 and D2 should update. If D2 changed as well, you have correctly set up cell referencing.
Let's do a bit more
So, you can do the same thing for the other names below. For example, if you click on cell D3 and then type = and click on cell C3, click return. It will copy the name again. You can imagine this could takea long time. It might almost seem faster just to copy and paste, or to just type it in. Hang with me for a bit longer.
(Get the spreadsheet here.) One faster way to to this is to drag the formula down. Hover your cursor (which looks like an arrow) over the lower right hand corner of a cell (you’ll see a small square). You’ll notice your cursor changes to a plus sign. At this stage, click and hold down the mouse button while dragging the mouse down the column. You’ll see dotted lines form around the area as you drag. Release the mouse button and the information should be copied to the new columns. Likewise, you can highlight the cells and the copy (⌘ + C) and paste (⌘ + V).
Why would you want to just cell reference on the same page? You probably wouldn’t. This was just to show you how it works. Now, let’s put it into something practical. We have our Class Roster tab. Maybe we have an Attendance tab. We want to have the same student names show up. Let’s assume we want have our names show up in the same place on an Attendance tab. Here’s how it works:
Create a new sheet:
1. Click on the plus sign to the left of the spreadsheet tabs to create a new sheet.
2. Double-clicking on the spreadsheet tab (Sheet 2, Sheet 3, etc) will allow you to rename the tab.
3. Name it Attendance. You can type in some Titles in Row 1, if you like.
Cell reference student names from the Class Roster tab.
3. Now, go to the Class Roster tab and click on cell C2 in the Class Roster tab.
A. In the formula bar, it should show =’Class Roster’!C2
B. The exclamation mark means it is a name of a tab in your spreadsheet.
4. Click return and it should copy the same name.
5. You can then hover your mouse over the lower right hand corner until it changes to a plus sign. Click and drag down to copy the formula down the column.
More tabs with the same information
If you wanted to create more tabs with student names connected to the Class Roster tab, you can do this as well. The formulas will stay.
1. Right click on the spreadsheet tab that you want to copy. Let’s use the attendance tab.
2. Select “duplicate.”
3. This will make a new tab called “Copy of Attendance”
4. Rename the tab to something else, for example, Math Scores.
The new tab should have the same names still in the tab. Click on a cell with a student name. Look in the formula bar and it should say something like =’Class Roster’!C2. Now your tabs are linked together.
You can test this by going back to the Class Roster tab and changing a student name. Go back and check the Attendance tab and Math Scores tab. It should have the name updated now. Pretty cool, huh? You just need to update your information in one place and it will update in all the other tabs. This is where we start to save time and leverage the power of a spreadsheet.
Sometimes our spreadsheets get messed up. With cell referencing, maybe another tab won’t update. Usually, this is because the formula in another tab has been erased or written over. If you decide to update a name or some other data on a tab other than the Class Roster tab, in this case, it will erase the cell reference formula. Just double-check that and fix the formula, if needed.
In the next article, I will show you a way to combine data from more than one cell and put it together into a single cell. I will also show you how you can break apart data in a single cell across multiple cells.