Free Excel Training Online Lesson Seven: Copying Data and using Autofill

2KO Africa welcomes you back to our free Excel training online. Now that we have had a look at making Excel work presentable, we will look at how we copy data and how we use Autofill to create automatic sequences.

Lesson 7

Copying Data and Using Autofill for Automatic Sequences

Excel is set up in columns and rows. Each column and row intersects and results in a grid of cells. These cells are holders for data, whether numbers, text, dates or formulas. Each cell has an address to identify it. A cell address comprises a column letter and a row number e.g. B6 or G9.

 

Since data is stored in tables which basically are grids of cells, each one labelled, Excel provides tools to copy data across rows or down columns, or both. This is usually referred to as copy and paste.

 

Where is Data Copied to?

Microsoft uses a temporary storage called The Clipboard. This is for all Microsoft (and hence MS Office) programs. Excel's clipboard is essentially the use of the computers Random Access Memory (RAM). When a cell or cells are selected, and the user presses or clicks Copy, the highlighted data is sent to the clipboard. It sits in memory, until there is a Paste Command. If the enter key is pressed immediately after doing a copy, this acts as a Paste command.

 

When text is copied, and then pasted, it is basically duplicated. When a formula is copied, Excel chooses how to duplicate the formula.

 

How is Data Copied?

To copy data, follow these steps:

 

1.     Select the cell or cells to be copied

2.     Click the copy icon or press CTRL+C on the keyboard


Note the copy icon is found on the Home Menu tab at the left side of the ribbon)

 

3.     Then select the blank cell or cells where the copy is to go

4.     Press Enter or press CTRL+V

 

Another way to copy is to use the Fill Method

 

To copy a name for example using the drag or fill method, perform these steps:

 

1.     Select the cell or cells that contain the name(s) to be copied

2.     Position the mouse on the bottom right corner of the cell to be copied

3.     When the mouse pointer changes to a small black cross, drag the mouse to the side or up or down.

 

Let's see how this looks using the following example:

 

 

We will copy this to the right across to cell F1, using either of the above methods (the result will be the same and will look like this:

 

 

 

Copying Numbers works exactly the same way

Let's perform the same steps as above (either method) on the following example

 

 

As you will see, the result is the same.

 

 

Copying Formulas

When a formula is in a cell, and the formula is composed properly (in other words using cell address), then when the formula is copied, the resulting formulas are not a duplicate of the original, but are relative.

 

Relative Formulas

A copied formula is always relative to the original formula. When a formula is copied one cell to the right, the column letter changes by 1. So if the formula refers to column A, the formula in the next cell to the right will refer to column B. this is fantastic news, because it allows us to write one formula, and then copy it down or across, and each row or column will be referenced. Look at this example, which should make this clearer:

 

 

In the above table, a formula is entered into cell A2, which takes A1 and multiplies it by 2. The answer is displayed as 200. However, in the above table we have shown the actual formula used, rather than the result. Excel always displays the result rather than the formula, by default.

 

Now we will copy this formula in A2, across to D.

 

Look at the result.

 

 

Notice, we only did one formula (in A) and we then copied across. Like we stated above, when you copy left to right, an A becomes a B, and a B becomes a C etc.

 

Using Autofill

Autofill is an automatic process of filling values in cells, when dragging the mouse, with the black fill handle. Let me explain.

 

When you point your mouse at the bottom right corner of any cell, the mouse pointer changes from a large white cross, to a small black cross. This is the fill handle. Whenever the fill handle appears, press and drag with the left mouse button either vertically or horizontally. This will result in a sequence or a copy.

 

When will Autofill Copy and when will Autofill Sequence?

Autofill creates a copy whenever a text label is selected, which is not part of one of the pre-built sequences programmed into Excel. So, in other words, if you type the word Africa into a cell, and use Autofill, you will get multiple appearances of the word Africa. Essentially the word has been copied, in the direction you dragged. If you select a formula, the formula will be coped and will produce new formulas, each of which has the same deign as the original. If you have been following our lessons, you will know we are referring to Relative Cell Addressing.

 

However, when a word is selected that is one of the words in a pre-programmed set, then Excel will produce a sequence.

 

The following are all inbuilt sequences:

 

  • Days of the week e.g. Tuesday, Fri, Saturday, MON

  • Months e.g. March, October, Nov, Feb

  • Dates e.g. 14/07/2019, 08-Feb'

  • Quarters e.g. 1st Quarter, 4th Quarter, Quarter 4

  • Any label with a number after it e.g. Prod 1, Week 3, ID 4, ID #4

 

Practice on your own.

Create the following Table

 

1.     Place light grey shading / fill in A1:C1, as well as in A8:C8

2.     Make the heading in row 1 bold

3.     Center the entries in column A and B, as well as headings

4.     Right align the heading above the numbers

 

To start lesson 7, click here

 

To go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here