Sparks Fly
[Home] [PBS VideoDatabase] [Social Studies] [TSU] [UVA]

Excel:  Formulas

 Formulas 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 ready-made 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 ready-made 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 right-click 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 double-headed 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, double-click the right border of the column heading.

·       To make a row as tall as its tallest entry, double-click 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

                    Change font look  Alignment  Format Numbers      Color

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