Copy and Paste Part 2: Formulas

This is the second in a series about copying and pasting in Excel.  It may seem a little too basic of a topic, but Excel offers several helpful pasting options that many folks do not even know exist. In this series, I will show y'all those features that I use regularly when working with my Evergreen reports.

In the previous post in this series, I talked about selecting multiple cells in Excel so you can copy and paste data throughout a table. The example I used in the video demonstration came from a question about step #5 in this handout where we must paste a formula in multiple cells.

What I did not happen to mention in the video was that Excel was doing more than just simple copying and pasting. It was not just taking the same data and duplicating it "verbatim" into each cell. Excel was taking the formula that I had copied and reconstructing it relative to each cell.

Now for simple numbers, text and dates, Excel handles copying and pasting like most other computer programs. It pastes the information exactly as it was copied, including any special formatting like font color, size, etc.  BUT for formulas, Excel does things differently. 



Take the following simple example:

For Excel to add the total, we type the following formula into B5:

  =SUM(B2:B4)

This will add up everything from B2 through B4,
giving us 15.



But there's a little more to know about the formula we just typed into B5:

It is using what is called relative cell references. We know this because we do not see the symbol "$" in the formula.  The "$" indicates absolute cell references, which we will talk about later.

Because the formula is using relative cell references, what the formula is really stating is "add together the 3 cells above me" ...as illustrated here.




We then add two more columns of data, which means we need the "add together the 3 cells above me" formula again:

All we need to do is simply copy B5, select the C5 and D5, and then paste!






Excel will automatically change the cell references relative to the cell in which you paste the formula:

(Toggle to view formulas, instead of values, by clicking Ctrl + `)

The automatic updating of cell references is a lovely feature of Excel.  It is so useful that relative cell references are the default for formula creation.  BUT sometimes you do not want Excel to change the cell reference when you are copying and pasting formulas.  This is when you want to use absolute cell references in a formula.

As I mentioned previously the "$" sign before a cell reference indicates that it is absolute. In other words, it is unchanging; it is not relative.  

• Both column and row references can be absolute ($A$1). 
• Or only the column reference, but the not the row ($A1). 
• Or vice versa (A$1).

Basically, whatever part of the reference you want to stay the same in the formula, regardless of where you move that formula, you must add a "$" before it. 

So to continue with this simple example, we have totaled the number of programs by month and by type, with the grand total in cell E5:

We now want to know how the programs were distributed across the months as a percentage of the total programs.  So we add a new header to column F and then add the following formula to cell F2:

=E2/$E$5

The first part of the formula uses relative cell reference and the second part uses absolute.  The formula translates to "divide the cell directly to my left by E5," as illustrated below:



Now when we copy and paste that formula into the cells below, Excel will change the cell references appropriately  in first part of the formula, but always use the grand total value in E5.

 









Now that we understand absolute and relative cell references, I can keep my promise and explain the formula I introduced in my last post: