Sparks
Fly

Excel: FormulasFormulas are the essential part of Excel or any spreadsheet program. Workbooks use formulas to perform calculations on the data entered in a spreadsheet. With formulas you can perform addition, multiplication, subtraction, division. Formulas consist of one or more cell addresses and/or values and a mathematical operator, such as + (addition),  (subtraction), * (multiplication), or / (division).
Formula Calculated average Type a
formula in the cell where you want the resulting average to appear. Mathematical Operators
Don’t
forget the Order of Operations that applies here.
In spreadsheets it is called operator precedence.
Excel performs a series of operations from left to right in the following
order 1.
All operations in parentheses 2.
Exponential equations or operations 3.
Multiplication and division 4.
Addition and subtraction Entering Formulas
·
Select the
cell in which you want the calculation to appear. ·
Type in the
equal sign (=) followed by the formula (Using the = sign identifies to Excel
that you are entering a formula). ·
Press Enter To enter
formulas using cell references ·
Select the
cell in which you want the result to appear ·
Type the =
sign ·
Click the
cell whose address you want to appear first in the formula ·
Type a
mathematical operator ·
Click on the
next cell address followed by a mathematical operator until you finish entering
the formula ·
Press Enter When you
want to view your formula, click in the cell with the result. Your formula will appear in the formula bar. Changing Formulas
Editing
formulas is the same as editing any entry in Excel. ·
Select the
cell that contains the formula ·
Position the
insertion point in the Formula bar with the mouse or press F2 to enter Edit mode ·
Use the
mouse or arrow keys to move to the place you want to edit. Type additional characters or delete to edit the formula. Excel
recalculates the formulas in a worksheet every time you edit a value in a cell.
If you want Excel to wait to recalculate you must tell it by using the
Calculate Option. Open Tools,
Options and click on Calculation. Select one of the options and click OK. Functions
Functions
are complex readymade formulas that you can use to perform a series of
operations on a specified range of values.
Example: to determine the
sum of the range A5 to G5 enter the formula =SUM (A5: G5) instead of entering
A5+B5+C5+D5+E5+F5+G5. The =SUM
function is one of many readymade functions in Excel.
To see the other functions in Excel use the Insert menu, select Functions
and take a look at the Functions Wizard.
All
functions consist of three elements. ·
An equal
sign = indicates that what follows is a formula. ·
A function
name indicates the type of operation you want to perform. ·
An argument
indicates the cell addresses of the values the function will act upon. To view the
functions select Help Topics, Index. Select
Index of Functions and click the Display button.
This opens a help screen from which you can display function categories
to see the lists of functions.
You
can enter functions by typing them in the cell or by using the Function Wizard. The Function Wizard
This Wizard
leads you through the process of inserting a function. ·
Select the
cell in which you want to insert the function ·
Open the
Insert menu and choose Function or click the Function Wizard button (the fx
button) on the Standard toolbar. ·
Locate the
Function Category list and select the type of function you want to insert. ·
Select the
function from the Function Name list and click the Next button ·
Depending on
the function you selected, another box appears.
Enter the values or cell ranges for the argument. ·
Click Finish AutoSum Tool
Summing is
the most frequently used function. Excel
provides a fast way to perform summations.
Click the AutoSum button on the standard toolbar.
AutoSum guesses what cells you want summed based on the currently
selected cell. ·
Select the
cell in which you want to insert the sum (Try to choose a cell at the end of a
row or column of data ·
Click the
AutoSum button which inserts =SUM and the range of the cells to the left of or
above the selected cell ·
If
necessary, adjust the range of cells by clicking inside the selected cell or
formula bar and edit the range ·
Press Enter Excel
calculates the total for the selected range and places the answer in the
selected cell. AutoCalculate
Excel has an
AutoCalculate feature that automatically sums up a range of cells and displays
the figure on your status bar. If
you rightclick the status bar, you can access a shortcut menu for viewing the
average of the selected cells. Copy a Formula
·
Select the
cell that contains the formula you want to copy ·
Pull down
the Edit menu and select Copy or click on Copy toolbar button ·
Select the
cell into which you want to Copy the formula ·
Pull down
the Edit menu and select Paste or click Paste on the toolbar You can also
drag and drop by selecting the cell with the formula you want to copy.
Press and hold the Ctrl key and drag the cell selector border to the
place where you want to copy the formula. When
you release the button Excel copies the formula to the new location. Adding Rows, Columns, or Cells
Excel allows
you to add, delete rows and columns when you have additions to your data.
With the commands in the Insert menu, you can insert more cells, entire
rows and columns. ·
Select the
cell or cells where you want the new cells to be inserted. ·
Open the
Insert menu and choose Cells. The
insert dialog box appears. ·
Select Shift
Cells Right or Shift Cells Down. ·
Click OK or
Enter and Excel inserts the cell or cells and shifts the data from the original
cells in the specified direction. For Rows and
columns ·
Select a
cell above which you want the new row added.
If you want to add a column select a cell
to the left of where you want the new column inserted ·
Select the
Insert menu Column or Row and Excel inserts one row or column as indicated.
For quick
insertion there is a shortcut menu. Select
one or more rows or columns then right click one of them with the mouse.
Choose Insert from the shortcut menu. Deleting Cells, Rows, Columns
·
Select the
group of cells, rows, or columns you want to delete ·
Open the
Edit menu and choose Delete ·
When the
delete dialog box appears select the desired Delete Option: Shift Cells Left,
Shift Cells Up, Entire row, or Entire Column. Make It Fit
·
To move the
lines in your spreadsheet, move your mouse pointer in the heading areas of the
column or row. The pointer will
change to a doubleheaded arrow. When
you see this shape, drag and drop the border to a new height or width. (Hold down the left mouse button and drag the row or column
to a new size to fit your data.) ·
To make a
column as wide as its widest entry, doubleclick the right border of the column
heading. ·
To make a
row as tall as its tallest entry, doubleclick the bottom border of the row
heading. ·
Use AutoFit
to determine the width and height of your rows and columns.
Select the rows or columns you want to change, open the Format menu and
choose Column or Row and select AutoFit Selection.
Excel will make the selected areas as wide or tall as needed. Making Your Document Look Great!
Use the
Formatting Toolbar to Dress up your spreadsheet.
Font
Font Size
If you
can’t decide what to do, Excel will help with AutoFormat.
Select the data you want to format then open Format menu and choose
AutoFormat. A dialog box appears
with options you can try. Look
through the Table Format list to find a style you want to use. Look at the sample, highlight, click OK and Excel formats
your data according to your choice. Attractive
Numbers
Using the
bottoms on your Format Toolbar, you can add dollar signs, percent signs, align
decimals. ·
Select the
cell or range that contains the values you want to format. ·
Open the
Format menu and choose Cells. ·
Click the
Number tab in the dialog box ·
In the
Category list, select the numeric format you want to use. ·
Click OK Alignment
Excel by
default aligns data depending on its type.
Text left, numbers right. To change this configuration ·
Select the
cell or range you want to align. To
center a title or other text over a range of cells, select the entire range of
cells in which you want the text centered, including the cell that contains the
text. ·
Open the
format menu and select Cells ·
In the
format cells dialog box, click the Alignment tab. ·
Select the
alignment you want and click OK. Decorate Your Cells
Borders and
backgrounds will enhance your worksheet. Try
the Borders choice in the format cells dialog box.
The Patters tab will also offer options for the background of your cells.
Charts
You can
create charts as part of your worksheet (embedded) or as a separate chart
worksheet. Both types of chart can
be linked to your worksheet. ChartWizard
·
Select the
data you want in your chart. Be
sure to include the labels when you select if you want them in the chart. ·
Click the
ChartWizard with the data selected. ·
Move the
mouse pointer to where you want the upper left corner of the chart to appear on
your worksheet. Hold down the mouse
button and drag to define the size and dimensions of the chart.
To create a perfect square, hold down the Shift key as you drag.
If you want your chart to exactly fit the borders of the cells if
occupies, hold down the Alt key as you drag. ·
Once you set
your parameters, release the mouse button.
The ChartWizard Step 1 of 5 will appear. Respond to the verification of the range and if correct click
Next. ·
Step 2 of 5
appears asking you to select a chart type, then click Next. ·
Step 3 asks
you to select a chart format, a variation on the selected chart type.
Click Next. ·
Step 4
requires that you determine the basis for the data series, rows or columns and
selection of the starting row and column. Click
Next ·
Step 5
allows you to add a legend, title, or axis labels then click Finish.
Your completed chart appears.
Return to Spreadsheets Handout 
This page was updated on: 04/10/02 