Excel Lecture 8

Using Autosum

Autosum automatically creates the formula that makes use of the sum function.  When you use autosum button, Excel anticipates that you want to add values within the reference range you have specified.

Autosum is very easy to use, just follow these three steps:

1. Select the cell where you want to hold the result of the equation. If you are adding a column or row then choose the last empty cell or the cell after to hold the formula result.
2. From the standard toolbar, look for the autosum button and click it.
3. Press Enter key. The result will be seen at the chosen cell.

What Are Functions?

Functions are ready-made formulas that perform a series of operations on a specified range of values. For example, to determine the sum of a series of numbers in cells A1 through H1, you can enter the function =SUM(A1:H1). Excel functions can do all kinds of calculations for all kinds of purposes, including financial and statistical calculations.

Every function consists of the following three elements:

• The = sign, which indicates that what follows is a function (formula).
• The function name, such as SUM, that indicates which operation will be performed.
• A list of cell addresses, such as (A1:H1), which are to be acted upon by the function. Some functions can include more than one set of cell addresses, which are separated by commas (such as A1,B1,H1).

Function	Example	Description
AVERAGE	=AVERAGE(B4:B9)	Calculates the mean or average of a group of cell values.
COUNT	=COUNT(A3:A7)	Counts the number of cells that hold values in the selected range or group of cells. This can also be used to tell you how many cells are in a particular column, which tells you how many rows are in your spreadsheet.
IF	=IF(A3>=1000,”BONUS”,”NO BONUS”)	Allows you to place a conditional function in a cell. In this example, if A3 is greater than or equal to 1000, the true value, BONUS, is used. If A3 is less than 1000, the false value, NO BONUS, is placed in the cell.
MAX	=MAX(B4:B10)	Returns the maximum value in a range of cells.
MIN	=MIN(B4:B10)	Returns the minimum value in a range of cells.
SUM	=SUM(A1:A10)	Calculates the total in a range of cells.

Specify Text with Quotation Marks When you are entering text into a function, the text must be enclosed within quotation marks. For example, in the function =IF(A5>2000,”BONUS”,”NO BONUS”), if the condition is met (the cell value is greater than 2000), the word BONUS will be returned by the function. If the condition is not met, the phrase NO BONUS will be returned in the cell by the function.

Applying Conditional Formatting

Another useful formatting feature that Excel provides is conditional formatting. Conditional formatting allows you to specify that certain results in the worksheet be formatted so that they stand out from the other entries in the worksheet.

To apply conditional formatting, follow these steps:

1.      Select the cells to which you want to apply the conditional formatting.

2.      Select the Format menu and select Conditional Formatting. The Conditional Formatting dialog box appears.

3.      Be sure that Cell Value Is is selected in the Condition 1 drop-down box on the left of the dialog box.

4.      In the next drop-down box to the right, you select the condition. The default is Between. Other conditions include Equal To, Greater Than, Less Than, and other possibilities. Use the drop-down box to select the appropriate condition.

5.      After selecting the condition, you must specify a cell or cells in the worksheet that Excel can use as a reference for the conditional formatting. For example, if you select Less Than as the condition, you must specify a cell in the worksheet that contains a value that can be used for comparison with the cells that you are applying the conditional formatting to. Click the Shrink button on the Conditional Formatting dialog box. You are returned to the worksheet. Select the reference cell for the condition.

6.      Click the Expand button on the Conditional Formatting dialog box to expand the dialog box.

7.      Now you can set the formatting that will be applied to cells that meet your condition. Click the Format button in the Conditional Formatting dialog box and select the formatting options for your condition in the Format Cells dialog box. Then click OK.

8.      After setting the conditions to be met for conditional formatting (you can click Add to set more than one condition), click OK.

Advertisement