Using Excel Basics Copying, Pasting Filling | Lecture notes Statistics

Steve Sawin

Statistics

Using Excel

Basics

Each of the little rectangles in the grid you see is called a cell. The cell can hold a

number, text, formatted information, or a formula. If you click on the cell, you can edit or

write its contents in the text box above the grid. Choose from the formatting palette to do

any in a wide range of formatting for the individual cell. Especially important for us will

be that you can edit the formatting of numbers, controlling how many decimals show etc.,

and you can format something as currency (so 3.5 shows up as $3.50, etc.) or as a date.

Each cell is in a row labeled by a number, and in a column labeled by a letter. In

formulas, that is how you will refer to a cell. So for example if you enter into a cell

=A5 + C7

Excel will add the contents of the cell in row 5 column A to the contents of the cell in

row 7 column C and display the result in the cell you typed the formula into.

In general, if you begin what you type with an equals sign “=” Excel will interpret

whatever follows as a formula. As the contents of the rest of the spreadsheet change

Excel will keep recalculating the formula and dispaying the result. There are thousands

of functions you can build your formula from. If you choose the function wizard, it will

give you an organized list of functions, and when you click on one it will describe itself and

prompt you for the information it requires.

Some of the formulas can take as their input not the contents of a single cell but of

a range of cells. For example AVERAGE computes the average of a range of cells. So

=AVERAGE(A1:A30) computes the average of the thirty numbers in A1 through A30.=AV-

ERAGE(A1:D1) computes the average of the the first three things in the top row, and =AV-

ERAGE(A1:F30) computes the average of 180 things which appear in the first six columns

and the first 30 rows. A handy shorthand is =AVERAGE(B:B) to average all the things oc-

curing in the second column (it averages only those entries which it recognizes as numbers,

so blanks and text are not counted). There are other versions of AVERAGE which behave

a little differently.

Copying, Pasting Filling

As you would expect, you can cut copy and paste groups of cells. Use your mouse to

highlight a rectangular array of cells (or click at the top of a column, left of a row, or upper

left of a worksheet to select the whole thing), use the Edit menu or keyboard shortcuts to

Cut or Copy, select a cell somewhere else, and Paste. The contents of the cells will be copied

Partial preview of the text

Download Using Excel Basics Copying, Pasting Filling and more Lecture notes Statistics in PDF only on Docsity!

Steve Sawin Statistics

Using Excel

Basics

Each of the little rectangles in the grid you see is called a cell. The cell can hold a number, text, formatted information, or a formula. If you click on the cell, you can edit or write its contents in the text box above the grid. Choose from the formatting palette to do any in a wide range of formatting for the individual cell. Especially important for us will be that you can edit the formatting of numbers, controlling how many decimals show etc., and you can format something as currency (so 3.5 shows up as $3.50, etc.) or as a date. Each cell is in a row labeled by a number, and in a column labeled by a letter. In formulas, that is how you will refer to a cell. So for example if you enter into a cell

=A5 + C

Excel will add the contents of the cell in row 5 column A to the contents of the cell in row 7 column C and display the result in the cell you typed the formula into. In general, if you begin what you type with an equals sign “=” Excel will interpret whatever follows as a formula. As the contents of the rest of the spreadsheet change Excel will keep recalculating the formula and dispaying the result. There are thousands of functions you can build your formula from. If you choose the function wizard, it will give you an organized list of functions, and when you click on one it will describe itself and prompt you for the information it requires. Some of the formulas can take as their input not the contents of a single cell but of a range of cells. For example AVERAGE computes the average of a range of cells. So =AVERAGE(A1:A30) computes the average of the thirty numbers in A1 through A30. =AV- ERAGE(A1:D1) computes the average of the the first three things in the top row, and =AV- ERAGE(A1:F30) computes the average of 180 things which appear in the first six columns and the first 30 rows. A handy shorthand is =AVERAGE(B:B) to average all the things oc- curing in the second column (it averages only those entries which it recognizes as numbers, so blanks and text are not counted). There are other versions of AVERAGE which behave a little differently.

Copying, Pasting Filling

As you would expect, you can cut copy and paste groups of cells. Use your mouse to highlight a rectangular array of cells (or click at the top of a column, left of a row, or upper left of a worksheet to select the whole thing), use the Edit menu or keyboard shortcuts to Cut or Copy, select a cell somewhere else, and Paste. The contents of the cells will be copied

in, with the upper left hand corner of your original rectangle going into the cell you just clicked on. There are some subtleties in how this happens in Excel that are very important though. These are strange, but they are usually what you want. First, when you cut and paste a formula with references to other cells, it does not simply copy the references, it moves them in accordance to how you moved the cell. An example is necessary to see what is meant here. Suppose the cell B3 contains the formula =A1-A2, so that what you see in the cell is the difference of the values in A1 and A2, and you copy the contents of B3 into D3, thus moving it over two columns. Then D3 will contain the formula =C1-C2, which you see replaces the cells A1 and A2 originally referred to by the cells two columns over. If you copied B3 to B4, you would get the formula A2-A3. Likewise if you copy the formula =SUM(A1:A30) (which adds up the first thirty things in column A) one column to the left, it will read =SUM(B1:B30). the references always move with the formula. This is usually what you want. If A1-A30 contains a list of heights in feet and you wanted them in inches, you could type the formula =A112 in cell B1. Then B would contain the same height as A1, but expressed in inches rather than feet. If you copied this formula to B2, it would then read =A212, and thus B2 would contain A2’s height in inches. You could keep doing this, and while it would be easier than doing all the calculation yourself, it would still be tedious. A quicker way would be to Fill the column. After typing =A1*12 into B1, highlight B1:B30, and choose Edit↪→Fill↪→Down. It will copy over the B1 formula (adjusting it on the fly) all the way down the column, so that each cell in column B will be 12 times the value of the cell to its left, just what you would want! You can similarly fill up, or left or right (though right and down are the usual ones). A second and sometimes faster way to fill cells is as follows. Click on the cell with the formula you wish to replicate. The cell will be surrounded by a dark line, and on the lower right hand side of the dark line there will be a little box, called the fill square. Click on that box and drag it down. When you lift up the formula will have been Filled as far down as you went. Works for filling in the other directions as well. One last handy trick involving fill. If you type the number 7 into a cell and fill down, it will of course copy the number and you will get a whole column of 7s. Not so useful. But if you type 1 in cell A1 and 3 in cell A2, say, and then highlight both and drag the little box down, it will continue the sequence with 5, 7 , 9 , etc. This only works for the mouse version of fill, but is a handy way to get a sequence of numbers.

When That’s Not What You Want

Excel does what it thinks you want when copying and pasting, and it is usually right, but sometimes not. Suppose you wanted to add the value in B1 to each of the numbers in A1 through A30, and put the results in C1 through C30. Of course you could type =A1+B into C1, and then fill down into C1 through C30, but C30 would contain the formula =A

B30, not the formula A30+B1 that you want. You want to tell it not to adjust the ”1” in B1 when it copies formulas. The thing that tells it that is a dollar sign, as in B$1. If you fill the formula =A1+B$1 in C1 down through C30, you will get the formulas =A1+B$1, =A2+B$1, =A3+B$1,... =A30+B$1. The dollar sign before the ”1” tells Excel to leave

Using Excel Basics Copying, Pasting Filling, Lecture notes of Statistics

Related documents

Partial preview of the text

Download Using Excel Basics Copying, Pasting Filling and more Lecture notes Statistics in PDF only on Docsity!

Using Excel

Basics

Copying, Pasting Filling

When That’s Not What You Want