Getting help in Excel
To enter data into a cell
To select Cells in contiguous and
Enter a formula into a cell
Make plots of data in cells
Practice what you have learned
- To enter
data into a cell
- Select a cell. When
you click on a cell, it is "open". When you click on another
cell, or press tab or enter, the cell becomes
"closed". Be sure you close the cell you are working on before
entering anything into another cell.
- Select a cell, enter a
number, then press return. The next cell
below is now opened and ready for data entry.
- Select a cell, enter a number, then press the tab key.
The adjacent cell to the right is now opened and ready for data entry.
- You can also move from
one cell to another using the arrow ¬
- To enter a number into
a range of cells - Select a cell, and enter a number. Then click on the
small box at the bottom lower right of the selected cell and drag
vertically or horizontally to fill a range of cells. Notice that all the
cells you select will be filled with the number. OR Select a cell,
and enter a number. Then highlight the cell with the number and the cells
to be filled. Then
- go to Edit and
select Fill and the direction of the cells to be filled from the
original one (for example, down) or
and hold Alt then press e. Release Alt and press i followed by one of u, d, r or l.
- Each cell has a row
and column "address". The upper leftmost cell has the address
A1. Columns are letters and rows are numbers.
Enter a "1" into cell A2. Select cells A2
to A10. Then go to the edit menu and choose "Fill ---> Down". Notice that all of the selected cells get a
"1" in them. Ok, marginally interesting. Enter a "1" into
cell A12. Select cells A12 to A20. Then go to the edit menu and choose
"Fill ---> Series". A dialog box comes up and you fill in the step
value and the stop value. Put 2 into the step value and 20 into the stop value.
When you click "Ok", the cells fill down as integers stepping by 2.
Only the selected cells fill.
Experiment with this, and the other fill options.
select Cells in contiguous and non-contiguous regions
- To select contiguous
cells - click in a cell and drag to select adjacent cells. To practice:
select a row of cells, a column of cells, and a block of cells.
- To select non-contiguous
cells - select one cell or a non-contiguous block of cells. Then hold
down the Ctrl key and click on another cell. If you are selecting another
block of cells, hold down the Ctrl key and drag over the cells to be
selected. Any number of non-contiguous areas may be selected.
- Enter a
formula into a cell
A formula is an equation that analyzes data on a
worksheet. Formulas perform operations such as addition, multiplication, and
comparison on worksheet values; they can also combine values. Formulas can refer
to other cells on the same worksheet, cells on other sheets in the same
workbook, or cells on sheets in other workbooks. The following example adds the
value of cell B4 and 25 and then divides the result by the sum of cells D5, E5,
- All formulas start
with the = sign. This is the message that the following items are a
formula, not just a string of characters. You can either type = or click
on the = sign on the tool bar just above the body of the spreadsheet.
- First, select a cell,
then enter "=3" (omit the quotes). You have set the cell equal
to 3. Big deal. But, try "=3*5". The cell contents are now 15.
You can do calculations this way.
- Type the formula:
"= sqrt(45)" (omit the quotes).
- Click on the = sign on
the tool bar. You will then see a down arrow( Ñ ) button at the left of the tool bar.
Click on it and you will see names of common formulas. Try using one. If
you need help click on the ? button
and a wizard will appear. If you do not see a formula you want, click on
the words "more functions" and you will see an index of all
formulas available. Select the one you want.
- Enter a formula into a
range of cells. Select a cell, and enter a formula. Then click on the
small box at the bottom lower right of the selected cell and drag to fill
a range of cells. This is the same procedure as used for filling cells
with a number, but there is a twist.
Fill a series starting at 1 and counting to 10, in
cells A1 to A10. In cell B1, type "=A1". Click and drag the expansion
"handle", to select B1 to B10. Now you should have the value of the
number in the A column in the corresponding cells of the A column. Click on any
of the cells in the B column and notice, for example that B3 will have the
equation "=A3" in it. The formula in each cell has been changed so
that each B cell refers to the corresponding cell to the left. This property is
quite handy, and occurs during may Excel formula
BUT what if you do not want to change the
cell reference in the formula? This might happen if you want to have a series
of formulas that refer to a cell that has a constant value in it.
- Refer to cells in
relative and absolute forms (in equations).
Try this: Into cell B1, enter the formula
"=A1*$C$1". Then, enter "5" into cell C1. Then drag the
"expansion handle" of B1 to fill down toB10. Notice that the formula
in each of the B column cells advances in the first term, but still refers to
$C$1. The dollars signs force Excel to make an "absolute reference"
to the cell C1. Note that you could have put in C$1 or $C1,
which would only have held the row (the "1") or the column (the
"C") constant as the formula was expanded. You will see many
uses for this interesting behavior of Excel formula expansion as you become
more familiar with it.
- Fill cells with series
and random data
- Clear your
spreadsheet. Into cell A1, enter "=rand()*100".
Then, expand the formula down to A20. This puts a random number into
each of the A1 to A20 cells. This random number is equally likely to
occur between a lower and upper limit. Can you guess what the limits
- Into cell A21, put
"=sum(A1:A20)". This is a formula
that adds cells A1 to A20 together. Notice the answer.
- Now, change the
formula in A21 to "=sum(A1:A20)/count(A1:A20)".
The "count" function simply counts the number of cells that
contain a number, so it's value should be 20.
This is the average value of the numbers in the column. Press the keys:
Ctrl= (On a Mac Apple=). That is, hold down on the Ctrl (Apple) key
while pressing the equals sign "=". Notice that the numbers
change. This command forces Excel to redo all calculations on the
spreadsheet. Since the numbers are random, new random numbers are
computed. Notice that the average value changes also.
- Make plots
of data in cells.
- Click on the Insert
at the top of the Excel toolbar and then on chart. You should see a
Chart Wizard, to help you design your chart.
- Select a plot that is
a series of vertical bars representing the values of your random numbers
in the range A1:A20.
- Click on Next
then choose the area on the spreadsheet that includes your data. You can
either type the range into the data range window or click on the red
arrow which sends you to the spreadsheet. There you can highlight the
range of data to be plotted and then hit enter. Note you should
also be sure to indicate whether your data sets (if more than one) are
in rows or columns. When you have set the correct range, click Next.
- The third page of the
Wizard provides many options for enhancing your chart. Experiment with
these to see what they do. Then click Next.
- The fourth page of
the Wizard gives you the option of either plotting the chart on the same
page as the data, or of putting it on a separate page in the workbook.
- Press Ctr= (Apple =) and watch the chart change as
the data in the random number column changes. You can see that the chart
reflects whatever is in the cells selected for plotting. If these data
change, so does the chart.
Practice what you have learned
Expand the formula from A1 to E1, then expand each
column down to the 20'th row (e.g. C1 would be expanded down to C20, etc). You
should end up with a block of random numbers. Examine the formula in several
cells to convince yourself that this is so. Expand A21 to E21. Examine the
formulae to make sure that it is computing the average value of the random
numbers in the column above it.
Make a bar chart of the 5 average values contained in A21 to E21. Set the
vertical axis to a minimum of 40 and a maximum of 60. You do this by clicking
on the vertical axis of the chart until a dialog comes up that lets you select
the "Scale". You may have to experiment a bit. Explore. Press Ctr = (Apple = ) and watch
the bar chart change.
- Getting help
Click on the help icon at the top of the Excel toolbar. Browse through the
help index. Most of your questions can be answered using this help feature. Be
sure you become familiar with how to use it. It will save you much time.