Spreadsheet Copy Down Chaos

So you’ve got a spreadsheet with a few formulas in it. You want to use those formulas in other cells by just copying the cells with the original formulas. Easy right? Well, maybe not!

copying cells in a spreadsheet can cause a few issues Click To Tweet

If you don’t set it up correctly in the first place, copying cells in a spreadsheet can cause a few issues. So, how do you prevent these issues?

The first step is to understand what is actually going on.
By default, in any spreadsheet all cell references are relative references. This means that when you copy a formula across multiple cells, the formula will change based on its relative position.

Suppose you have written a formula that looks like =C3+C4. If you copy this formula into neighbouring cells, it automatically changes to =C4+C5. If this is not what you intended, suddenly all your calculations will be looking rather odd.

You will see in the example below that we have made up a grade book for assessments. The formula for the first result(expressed as a percentage) is correct. We have simply divided the mark obtained by the total marks and multiplied it by 100.
Spreadsheet

Instead of writing out this formula multiple times we just copy it down to the other cells . This is where the problem occurs. Because the formula automatically changes based on the relative position of the cells, we end up dividing by the row above rather than the total of the test.

Spreadsheet_2

This is obviously wrong! What we want to do is to divide each student’s result by the total marks which is located in C2. We could re-write the formula each time, but there is a far easier way.

The $ sign in a cell reference tells the spreadsheet that the reference is absolute – that it should not change when the formula is copied to another cell.If you put a dollar sign in front of the column reference, then this will not change when you copy the formula. The same applies when a dollar sign is used in a row reference.

Using our simple example above, if we add the dollar sign to the row reference (the number), then =C$3+C$4 is copied as =D$3+D$4. The dollar sign is like an anchor and anchors a column or a row when you copy it. This is called absolute referencing.

Looking at our simple marks book, all we need to do is:

 Spreadsheet

Then when we copy the formula down, we will always have the correct references and hence the correct percentages for each student.

To copy quickly
Highlight the cell you wish to copy down.
Move your cursor to the bottom right of that cell (where you see a small blue square).
Hover over the small blue square until it ‘turns into’ a cross
Double click with the cross and your formula will be copied down to the last row of your data.

Spreadsheet_4

The result looks like this.

Spreadsheet_5

Bonus time-saving tip! Use F4 to save time when absolute referencing Click To Tweet

Bonus time-saving tip!

Adding in a $ each time you have a big formula can be time consuming. Here is another handy hint:
Type in the formula normally.
Highlight the cells in the formula that need a $ in front of them.
Push the F4 key on your keyboard and dollar signs will be added automatically.

Spreadsheet_6

Spreadsheet_7

 

By understanding what the spreadsheet is doing and learning a couple of handy hints, you will be creating spreadsheets quicker and using them more effectively to improve your workflow.

You will be creating spreadsheets quicker and using them more effectively to improve your workflow. Click To Tweet
Digitally Certified_2

6 reasons why you should become digitally certified

One of the most important tools we have in the modern classroom is a digital one. We use laptops and tablets for communication, creation, assessment and probably most importantly, learning. And it’s no wonder that for teachers, one of the important parts of our roles as educators is to know

VIEW POST
IT support

Why we need to start paying more attention to IT support in schools

When things don’t go to plan with technology in the classroom, educators need to know they can rely on someone to help them out. They also need to be confident that their technology environment won’t suddenly change unexpectedly. Having a customer-focussed, well-resourced and supportive IT department is the keystone to creating this type of confidence. Yet, too often, IT support in schools is seen as a necessary evil. Here are my four key improvements to help change that.

VIEW POST
Outclassed Podcast

Fake News and Getting Google Search to Work For You Ep 27

Get Notified Of Future Episodes: Apple Podcasts | Spotify | Google Podcast | Stitcher | TuneIn + Alex | Podcast Addict | Podchaser | Deezer | Listen Notes In this Episode: To see all the OutClassed episodes go to utb.fyi/outclassed Podcast Episode Highlights: Coming Resources and links mentioned: Transcript: Mike

VIEW POST

Here’s What
Our Clients Say

PR Industrya

Client

In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices

Download your skills checklist to see how you score...

 

Sign Up Now
To Get The Latest Updates

 

Holiday Rescue Maker Camp

Student to agree to and tick:
Guardian to agree to and tick: