A formula calculates values to return a result. On an Excel worksheet, you can create a formula using constant values (such as 147 or $10.00), operators (shown in the table), references, and functions. An Excel formula always begins with the equal sign (=).
A constant is a number or text value that is not calculated, such as the number 147, the text “Total Profits”, and the date 7/22/2008. On the other hand, an expression is a value that is not a constant. Constants remain the same until you or the system change them. An operator performs a calculation, such as + (plus sign) or - (minus sign). A cell reference is a cell address that returns the value in a cell. For example, A1 (column A and row 1) returns the value in cell A1 (see table below).
Cell Reference Examples
|
Reference |
Meaning |
|---|---|
|
A1 |
Cell in column A and row 1 |
|
A1:A10 |
Range of cells in column A and rows 1 through 10 |
|
A1:F1 |
Range of cells in row 1 and columns A through F |
|
1:1 |
All cells in row 1 |
|
1:5 |
All cells in rows 5 through 10 |
|
A:A |
All cells in column A |
|
A:F |
All cells in columns A through F |
|
Profits!A1:A10 |
Range of cells in column A and rows 1 through 10 in worksheet named Profits |
A function performs predefined calculations using specific values, called arguments. For example, the function SUM(B1:B10) returns the sum of cells B1 through B10. An argument can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #NA, or cell references. Arguments can also be constants, formulas, or other functions, known as nested functions. A function starts with the equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. For example, the function, AVERAGE(A1:A10, B1:B10), returns a number with the average for the contents of cells A1 through A10 and B1 through B10. As you type a function, a ScreenTip appears with the structure and arguments needed to complete the function. You can also use the Insert Function dialog box to help you add a function to a formula.
By default, every time you make a change to a value, formula, or name, Excel performs a calculation. To change the way Excel performs calculations, click the Formulas tab, click the Calculation Options button, and then click the option you want: Automatic, Automatic Except Data Tables, or Manual. To manually recalculate all open workbooks, click the Calculate Now button (or press F9). To recalculate the active worksheet, click the Calculate Sheet button (or press Shift+F9).
Formulas perform calculations from left to right, according to a specific order for each operator. Formulas containing more than one operator follow precedence order: exponentiation, multiplication and division, and then addition and subtraction. So, in the formula 2 + 5 * 7, Excel performs multiplication first and addition next for a result of 37. Excel calculates operations within parentheses first. The result of the formula (2 + 5) * 7 is 49.
Each cell, the intersection of a column and row on a worksheet, has a unique address, or cell reference, based on its column letter and row number. For example, the cell reference for the intersection of column D and row 4 is D4.
The simplest formula refers to a cell. If you want one cell to contain the same value as another cell, type an equal sign followed by the cell reference, such as =D4. The cell that contains the formula is known as a dependent cell because its value depends on the value in another cell. Whenever the cell that the formula refers to changes, the cell that contains the formula also changes.
Depending on your task, you can use relative cell references, which are references to cells relative to the position of the formula, absolute cell references, which are cell references that always refer to cells in a specific location, or mixed cell references, which use a combination of relative and absolute column and row references. If you use macros, the R1C1 cell references make it easy to compute row and column positions.
When you copy and paste or move a formula that uses relative references, the references in the formula change to reflect cells that are in the same relative position to the formula. The formula is the same, but it uses the new cells in its calculation. Relative addressing eliminates the tedium of creating new formulas for each row or column in a worksheet filled with repetitive information.
If you don’t want a cell reference to change when you copy a formula, make it an absolute reference by typing a dollar sign ($) before each part of the reference that you don’t want to change. For example, $A$1 always refers to cell A1. If you copy or fill the formula down columns or across rows, the absolute reference doesn’t change. You can add a $ before the column letter and the row number. To ensure accuracy and simplify updates, enter constant values (such as tax rates, hourly rates, and so on) in a cell, and then use absolute references to them in formulas.
A mixed reference is either an absolute row and relative column or absolute column and relative row. You add the $ before the column letter to create an absolute column or before the row number to create an absolute row. For example, $A1 is absolute for column A and relative for row 1, and A$1 is absolute for row 1 and relative for column A. If you copy or fill the formula across rows or down columns, the relative references adjust, and the absolute ones don’t adjust.
3-D references allow you to analyze data in the same cell or range of cells on multiple worksheets within a workbook. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. For example, =AVERAGE(Sheet1:Sheet4!A1) returns the average for all the values contained in cell A1 on all the worksheets between and including Sheet 1 and Sheet 4.
A formula calculates values to return a result. On an Excel worksheet, you can create a formula using values (such as 147 or $10.00), arithmetic operators (shown in the table), and cell references. An Excel formula always begins with the equal sign (=). The equal sign, when entered, automatically formats the cell as a formula entry. The best way to start a formula is to have an argument. An argument is the cell references or values in a formula that contribute to the result. Each function uses function-specific arguments, which may include numeric values, text values, cell references, ranges of cells, and so on. To accommodate long, complex formulas, you can resize the formula bar to prevent formulas from covering other data in your worksheet. By default, only formula results are displayed in a cell, but you can change the view of the worksheet to display formulas instead of results.
Click the cell where you want to enter a formula.
Type = (an equal sign). If you do not begin with an equal sign, Excel will display, not calculate, the information you type.
Enter the first argument. An argument can be a number or a cell reference.
Timesaver
To avoid typing mistakes, click a cell to insert its cell reference in a formula rather than typing its address.
Enter an arithmetic operator.
Enter the next argument.
Repeat steps 4 and 5 as needed to complete the formula.
Press Return.
Notice that the result of the formula appears in the cell (if you select the cell, the formula itself appears on the formula bar).
-
To precisely adjust the length of the formula box, point to the bottom right corner of the formula box until the pointer changes to an arrow, and then drag left or right.
-
To move the formula bar, point to the title bar on the left, and then drag to move it.
-
To close the formula bar, click the Close button on the left. You can also click the View menu, and then click Formula Bar to open and close the formula bar.
To minimize typing and syntax errors, you can create and edit formulas with Formula AutoComplete (New!). After you type an = (equal sign) and begin typing to start a formula, Excel displays a dynamic drop-down list of valid functions, arguments, defined names, list names, special item specifiers—including [ (open bracket), , (comma), : (colon)—and text string that match the letters you type. An argument is the cell references or values in a formula that contribute to the result. Each function uses function-specific arguments, which may include numeric values, text values, cell references, ranges of cells, and so on.
Click the cell where you want to enter a formula.
Type = (an equal sign), and beginning letters or a display trigger to start Formula AutoComplete.
For example, type su to display all value items, such as SUBTOTAL and SUM.
The text before the insertion point is used to display the values in the drop-down list.
As you type, a drop-down scrollable list of valid items is displayed.
Icons represent the type of entry, such as a function or list reference, and a ScreenTip appears next to a selected item.
To insert an item, click the item from the drop-down list.
You can edit formulas just as you do other cell contents, using the formula bar or working in the cell. You can select, cut, copy, paste, delete, and format cells containing formulas just as you do cells containing labels or values. Using AutoFill, you can quickly copy formulas to adjacent cells. If you need to copy formulas to different parts of a worksheet, use the Clipboard.
Select the cell that contains the formula you want to copy.
Position the pointer (fill handle) on the lower-right corner of the selected cell.
Drag the mouse down until the adjacent cells where you want the formula pasted are selected, and then release the mouse button.
To change AutoFill options, click the AutoFill Options button, and then click Copy Cells, Fill Formatting Only, or Fill Without Formatting.
To make working with ranges easier, Excel allows you to name them. The name BookTitle, for example, is easier to remember than the range reference B6:B21. Named ranges can be used to navigate large worksheets. Named ranges can also be used in formulas instead of typing or pointing to specific cells. When you name a cell or range, Excel uses an absolute reference for the name by default, which is almost always what you want. You can see the absolute reference in the Refers to box in the New Name dialog box. There are two types of names you can create and use: defined name and list name. A defined name represents a cell, a range of cells, formula or constant, while a list name represents an Excel list, which is a collection of data stored in records (rows) and fields (columns). You can define a name for use in a worksheet or an entire workbook, also known as scope. The worksheet and formula bar work together to avoid overlapping content.
Select the cell or range, or nonadjacent selections you want to name.
Click the Name box on the formula bar.
Type a name for the range.
A range name can include up to 255 characters, uppercase or lowercase letters (not case sensitive), numbers, and punctuation, but no spaces or cell references.
By default, names use absolute cell references.
Press Return. The range name will appear in the Name box whenever you select the range in the workbook.
Select the cells, including the column or row header, you want to name.
Click the Insert menu, point to Name, and then click Create.
Select the check box with the position of the labels in relation to the cells.
Excel automatically tries to determine the position of the labels, so you might not have to change any options.
Click OK.
Select the cell or range, or nonadjacent selections you want to name.
Click the Insert menu, point to Name, and then click Define.
Type a name for the reference.
The current selection appears in the Refer to box.
Click the Collapse Dialog button, select different cells and click the Expand Dialog button, or type = (equal sign) followed by a constant value or a formula.
Click Add.
Click OK.
After you define a named cell or range, you can enter a name by typing, using the Name box, using Formula AutoComplete (New!), or selecting from the Use in Formula command. As you begin to type a name in a formula, Formula AutoComplete displays valid matches in a drop-down list, which you can select and insert into a formula. You can also select a name from a list of available from the Use in Formula command. If you have already entered a cell or range address in a formula or function, you can apply a name to the address instead of re-creating it.
You can simplify formulas by using ranges and range names. For example, if 12 cells on your worksheet contain monthly budget amounts, and you want to multiply each amount by 10%, you can insert one range address in a formula instead of inserting 12 different cell addresses, or you can insert a range name. Using a range name in a formula helps to identify what the formula does; the formula =TotalOrder*0.10, for example, is more meaningful than =SUM(F6:F19)*0.10.
Put your cursor where you would like the formula. Type an equal sign (=) followed by the start of a formula, such as =SUM(.
Click the first cell of the range, and then drag to select the last cell in the range. Excel enters the range address for you.
Complete the formula by entering a close parentheses, or another function, and then press Return.
Put your cursor where you would like the formula. Type an equal sign (=) followed by the start of a formula, such as =SUM(.
Click the Insert menu, point to Name, and then click Paste.
Click the name of the range you want to insert.
Click OK.
Complete the formula by entering a close parentheses, or another function, and then press Return.
In addition to the formula bar, you can also use a special calculator in Excel to create and edit formulas.The Calculator in Excel s similar to a traditional handheld calculator with the addition of a Formula pane where you can create and edit a formula and some common functions. After you create a formula, the result, if available, appears in the Answer box. When you click OK, the formula is transferred into the destination cell on the worksheet.
Click the cell where you want to insert a formula.
Click the Tools menu, and then click Calculator.
The Calculator opens, displaying an equal sign (=) in the Formula pane.
Type directly in the Formula pane or click the buttons on the Calculator to create a formula.
-
To insert a cell reference, click the cell or range in the worksheet.
Use any of the following to insert a function:
-
If. Click to add a conditional text to the formula. Specify the condition to test, a true result, a false result, and then click Insert to transfer it to the Formula pane. For a text result, Excel automatically adds quotation marks.
-
Sum. Click to add a Sum function to the formula. Select the range in the worksheet you want to add, and then click Insert to transfer it to the Formula pane.
-
More. Click to create or edit the formula in Formula Builder.
Click OK to transfer the formula into the destination cell on the worksheet.
Functions are predesigned formulas that save you the time and trouble of creating commonly used or complex equations. Trying to write a formula that calculates various pieces of data, such as calculating payments for an investment over a period of time at a certain rate, can be difficult and time-consuming. Formula Builder (New!) simplifies the process by organizing Excel’s built-in formulas, called functions, into categories—such as Most Recently Used, Financial, Text, Date and Time, Lookup and Reference, Math and Trigonometry, and other functions—so they are easy to find and use. A function defines all the necessary components (also called arguments) you need to produce a specific result; all you have to do is supply the values, cell references, and other variables. You can even combine one or more functions.
Click the cell where you want to enter the formula.
Click the Formula Builder tab on the Toolbox.
Type = (equal sign) to start a formula.
Type or select data references and to start the formula.
Continue to add data references and operators.
-
Type data references in the boxes.
-
Click the plus (+) icon to add another data reference.
-
Click the operation drop-down, and then select an operator or function.
When you’re done, press Return to complete the formula.
Click the cell where you want to enter the function.
Click the Formula Builder tab on the Toolbox.
If you know the function you want to use, start to type the function in the Search box. As you type, possible matches appear in the list.
Double-click the function you want. Excel inserts the equal sign, function name, and parentheses.
-
To find out more about a function and its arguments, read the text in the Description box.
Click the plus (+) icon (if necessary), and then type an argument to the function.
-
To insert a function as an argument to the current function (known as a nested function), double-click the function name in the list. The nested function appears separately. To switch between the functions, click the up arrow icon.
As you add arguments and elements, formula Builder evaluates the data and display the current result in the bottom right corner.
Continue to add data references and operators, such as +, -, or *, as needed.
To remove an argument or element, click the minus (-) icon.
When you’re done, press Return to complete the formula.
A nested function uses a function as one of the arguments. Excel allows you to nest up to 64 levels of functions. Users typically create nested functions as part of a conditional formula. For example, IF(AVERAGE(B2:B10)>100,SUM(C2:G10),0). The AVERAGE and SUM functions are nested within the IF function. The structure of the IF function is IF(condition_test, if_true, if_false). You can use the AND, OR, NOT, and IF functions to create conditional formulas. When you create a nested formula, it can be difficult to understand how Excel performs the calculations. You can use the Evaluate Formula dialog box to help you evaluate parts of a nested formula one step at a time.
Click the cell where you want to enter the function.
Click the Formula Builder tab on the Toolbox.
Double-click the function you want. Excel inserts the equal sign, function name, and parentheses.
For example, click the Logical and Reference button, and then click COUNTIF.
Click the plus (+) icon (if necessary), and then type an argument to the function.
To insert a nested function as an argument to the current function, double-click the function name in the list. The nested function appears separately. To switch between the functions, click the up arrow icon.
For example, =COUNTIF(E6:E19), “>”&AVERAGE(E6:E19)).
When you’re done, press Return.
Conditional Formula Examples
|
Formula |
Result |
|---|---|
|
=AND(A2>A3, A2<A4) |
If A2 is greater than A3 and less than A4, then return TRUE, otherwise return FALSE |
|
=OR(A2>A3, A2<A4) |
If A2 is greater than A3 or A2 is less than A4, then return TRUE, otherwise return FALSE |
|
=NOT(A2+A3=24) |
If A2 plus A3 is not equal to 24, then return TRUE, otherwise return FALSE |
|
IF(A2<>15, “OK”, “Not OK”) |
If the value in cell A2 is not equal to 15, then return “OK”, otherwise return “Not OK” |
An array formula can perform multiple calculations and then return either a single or multiple result. For example, when you want to count the number of distinct entries in a range, you can use an array formula, such as {=SUM(1/COUNTIF(range,range))}. You can also use an array formula to perform a two column lookup using the LOOKUP function. An array formula works on two or more sets of values, known as array arguments. Each argument must have the same number of rows and columns. You can create array formulas in the same way that you create other formulas, except you press Control+Shift+Enter to enter the formula. When you enter an array formula, Excel inserts the formula between { } (brackets).
Click the cell where you want to enter the array formula.
Type = (an equal sign).
Use any of the following methods to enter the formula you want.
-
Type the function.
-
Type and use Formula AutoComplete.
-
Use the Formula Builder.
Press Control+Shift+Return.
{ } (brackets) appear around the function to indicate it’s an array formula.
You can use lookup and reference functions in Excel to easily retrieve information from a data list. The lookup functions (VLOOKUP and HLOOKUP) allow you to search for and insert a value in a cell that is stored in another place in the worksheet. The HLOOKUP function looks in rows (a horizontal lookup) and the VLOOKUP function looks in columns (a vertical lookup). Each function uses four arguments (pieces of data) as shown in the following definition: =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup). The VLOOKUP function finds a value in the left-most column of a named range and returns the value from the specified cell to the right of the cell with the found value, while the HLOOKUP function does the same to rows. In the example, =VLOOKUP(12,Salary,2,TRUE), the function looks for the value 12 in the named range Salary and finds the closest (next lower) value, and returns the value in column 2 of the same row and places the value in the active cell. In the example, =HLOOKUP (“Years”,Salary,4,FALSE), the function looks for the value “Years” in the named range Salary and finds the exact text string value, and then returns the value in row 4 of the column.
Create a data range in which the left-most column contains a unique value in each row.
Click the cell where you want to place the function.
Type =VLOOKUP(value, named range, column, TRUE or FALSE).
Or click the Formula Builder tab on the Toolbox, double-click VLOOKUP under Lookup and Reference, and then specify the function arguments.
Press Return.
Create a data range in which the uppermost row contains a unique value in each row.
Click the cell where you want to place the function.
Type =HLOOKUP(value, named range, row, TRUE or FALSE).
Or click the Formula Builder tab on the Toolbox, double-click HLOOKUP under Lookup and Reference, and then specify the function arguments.
Press Return.
Did You Know?
You can use Paste Special to copy only formulas. Select the cells containing the formulas you want to copy, click the Copy button on the Standard toolbar, click where you want to paste the data, click the Edit menu, click Paste Special, click the Formulas button, and then click OK.
Lookup Function Arguments
|
Argument |
Description |
|---|---|
|
lookup_value |
The value found in the row or the column of the named range. You can use a value, cell reference or a text string (enclosed in quotation marks). |
|
list_array |
The named range of information in which Excel looks up data. |
|
col_index_num |
The numeric position of the column in the named range (counting from the left) for the value to be returned (use only for VLOOKUP). |
|
row_index_num |
The numeric position of the row in the named range (counting from the top) for the value to be returned (use only for HLOOKUP). |
|
range_lookup |
The value returned when the function is to find the nearest value (TRUE) or an exact match (FALSE) for the lookup_value. The default value is TRUE. |
You can use text functions to help you work with text in a workbook. If you need to count the number of characters in a cell or the number of occurrences of a specific text string in a cell, you can use the LEN and SUBSTITUTE functions. If you want to narrow the count to only upper or lower case text, you can use the UPPER and LOWER functions. If you need to capitalize a list of names or titles, you can use the PROPER function. The function capitalizes the first letter in a text string and converts all other letters to lowercase.
Create a data range in which the left-most column contains a unique value in each row.
Click the cell where you want to place the function.
Type = (equal sign), type a text function, and then specify the argument for the selected function. Some examples include:
-
=LEFT(A4,FIND(" ",A4)-1) -
=RIGHT(A4,LEN(A4-FIND("*",SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ",""))))) -
=UPPER(A4) -
=LOWER(A4) -
=PROPER(A4)
Or click the Formula Builder tab, double-click a text function, and then specify the function arguments.
Press Return.
If you have a column list with similar facts and no blanks, you can automatically calculate subtotals and grand totals in a list. Subtotals are calculated with a summary function, such as SUM, COUNT, or AVERAGE, while Grand totals are created from detailed data instead of subtotal values. Detailed data is typically adjacent to and either above or below or to the left of the summary data. When you summarize data using subtotals, the data list is also outlined to display and hide the detailed rows for each subtotal.
Organize data in a hierarchical fashion—place summary rows below detail rows and summary columns to the right of detail columns.
Select the data that you want to subtotal.
Click the Data menu, and then click Subtotals.
Click the column to subtotal.
Click the summary function you want to use to calculate the subtotals.
Select the check box for each column that contains values you want to subtotal.
To set automatic page breaks following each subtotal, select the Page break between groups check box.
To show or hide a summary row above the detail row, select or clear the Summary below data check box.
To remove subtotals, click Remove All.
Click OK.
To add more subtotals, use the Subtotals command again.
You can use conditional functions, such as SUMIF, COUNTIF, and AVERAGEIF to summarize data in a workbook. These functions allow you to calculate a total, count the number of items, and average a set of numbers based on a specific criteria. You can use the SUMIF function to add up interest payment for accounts over $100, or use the COUNTIF function to find the number of people who live in CA from an address list. If you need to perform these functions based on multiple criteria, you can use the SUMIFS, COUNTIFS, and AVERAGEIFS functions. If you need to find the minimum or maximum in a range, you can use the summarizing functions MIN and MAX.
Click the cell where you want to place the function.
Type = (equal sign), type a text function, specify the argument for the selected function, and then press Return.
Some examples include:
-
=AVERAGE(D6:D19) -
={=SUM(1/COUNTIF(C6:C19,C6:19))} -
=SUMIF(C6:C19,"Todd",Quantity_Order1) -
=SUM(Quantity_Order1)
Or click the Formula Builder tab, double-click a data function, and then specify the function arguments.
A range of cells can easily be added using the AutoSum button on the Standard toolbar. AutoSum suggests the range to sum, although this range can be changed if it’s incorrect. AutoSum looks at all of the data that is consecutively entered, and when it sees an empty cell, that is where the AutoSum stops. You can also use AutoSum to perform other calculations, such as AVERAGE, COUNT, MAX, and MIN. Subtotals can be calculated for data ranges using the Subtotals dialog box. This dialog box lets you select where the subtotals occur, as well as the function type.
Click the cell where you want to display the calculation.
-
To sum with a range of numbers, select the range of cells you want.
-
To sum with only some of the numbers in a range, select the cells or range you want using the
key. Excel inserts the sum in the first empty cell below the selected range. -
To sum both across and down a list of number, select the range of cells with an additional column to the right and a row at the bottom.
Click the AutoSum button arrow on the Standard toolbar, and then select an AutoSum function, such as Sum, Average, Count Numbers, Max, or Min.
Press Return.
The Conditional Sum command in Excel totals only numbers in a column that match your criteria, which is useful when you have different types of data in the same column. Excel uses the Conditional Sum Wizard to help you select the range you want to use, which includes the column to total, the columns containing the criteria, and the column headings, and specify the criterion you want to use to complete the sum.
Click the Tools menu, and then click Conditional Sum.
In Step 1, select the range you want to use, which includes the column to total, the columns containing the criteria, and the column headings.
Click Next.
In Step 2, click the drop-down, and then select the heading of the column to total.
Specify the first criterion by selecting a column heading and a conditional test, and type or select a value in the text box, and then click Add to include it. Continue to specify the criteria you want.
Click Next.
In Step 3, click the option you want to display only the total or the total and the criteria.
Click Next.
Select a cell in which to display the total and other criteria.
Click Finish.
In a complex worksheet, it can be difficult to understand the relationships between cells and formulas. Auditing tools enable you to clearly determine these relationships. When the Auditing feature is turned on, it uses a series of arrows to show you which cells are part of which formulas. When you use the auditing tools, tracer arrows point out cells that provide data to formulas and the cells that contain formulas that refer to the cells. A box is drawn around the range of cells that provide data to formulas.
Click the Tools menu, point to Auditing, and then click Show Auditing Toolbar.
The Formula Auditing toolbar opens, displaying buttons for tracing formula relationships.
Use any of the following options:
-
Click the Trace Precedents button to find cells that provide data to a formula.
-
Click the Trace Dependents button to find out which formulas refer to a cell.
-
Click the Trace Error button to locate the problem if a formula displays an error value, such as #DIV/0!.
-
Click the Remove Precedent Arrows, Remove Dependent Arrows, or Remove All Arrows button to remove precedent and dependent arrows.
If necessary, click OK to locate the problem.
When Excel finds a possible error in a calculation, it displays a green triangle in the upper left corner of the cell. If Excel can’t complete a calculation it displays an error message, such as “#DIV/0!”. You can use the Error smart tag to help you fix the problem. In a complex worksheet, it can be difficult to understand the relationships between cells and formulas. Auditing tools enable you to clearly determine these relationships. When the Auditing feature is turned on, it uses a series of arrows to show you which cells are part of which formulas. When you use the auditing tools, tracer arrows point out cells that provide data to formulas and the cells that contain formulas that refer to the cells. A box is drawn around the range of cells that provide data to formulas.
Select a cell that contains a green triangle in the upper left corner.
Click the Error Smart Tag button.
Click one of the troubleshooting options (menu options vary depending on the error).
-
To have Excel fix the error, click one of the available options specific to the error.
-
To find out more about an error, click Help on this error.
-
To locate the error, click Trace Error.
-
To remove the error alert, click Ignore Error.
-
To fix the error manually, click Edit in Formula Bar.
-
To modify error checking options, click Error Checking Options.
Excel comes with a tool called Error checker to help you find and correct problems with formulas. Excel uses an error checker in the same way Microsoft Word uses a grammar checker. The Error checker uses certain rules, such as using the wrong argument type, a number stored as text or an empty cell reference, to check for problems in formulas.
Open the worksheet where you want to check for errors.
Click the Tools menu, and then Error Checking.
The error checker scans the worksheet for errors, generating the Error Checker dialog box every time it encounters an error.
If necessary, click Resume.
Choose a button to correct or ignore the problem.
If necessary, click Close.
With its grid-like structure, Excel works well to create and manage lists. To create a list in Excel, you can enter data on worksheet cells, just as you do on any other worksheet data, but the placement of the field names and range must follow these rules: (1) Enter field names in a single row that is the first row in the list (2) Enter each record in a single row (3) Do not include any blank rows within the range (4) Do not use more than one worksheet for a single range. You can enter data directly in the list. Don’t worry about entering records in any particular order; Excel tools can organize an existing list alphabetically, by date, or in almost any order you can imagine. If you also use Office 2007 for Windows, lists are called tables.
Do one of the following to start the list:
-
Create a list in a new worksheet. Click the File menu, click Project Gallery, select the Blank Document category, click the List Wizard icon, and then click Open.
-
Create a list in an existing worksheet. Click a starting cell, click the Insert menu, and then click List.
-
Convert existing data into a list. Click a cell within the range, click the Insert menu, and then click List.
-
Create a list from scratch. Click a starting cell, click the Insert menu, and then click List.
In Step 1 of the List Wizard, select one of the following options for the data source:
-
None. Creates a list from scratch.
-
In an open workbook. Creates a list from an existing data range.
-
External data source. Imports data from an external source (with ODBC drivers). Click Get Data to navigate to the data file.
Click the New sheet or On existing sheet option to specify where you want to start the list.
Enter values or use the Collapse Dialog button to select a starting cell.
Click Next.
In Step 2, do one of the following to create the list:
-
Create a list from existing data. Select each column, select a data type, edit the column name, and click Modify to make the change.
-
Create a list from scratch. Enter each column name, select a data type, and click Add.
Click Settings to set formatting, conditional formatting, or validation options for the selected field.
Click Next.
In Step 3, name the list, and specify whether you want to use AutoFormat style and show a totals row.
Click Finish.
Your list appears along with the LIst toolbar, which you can use to modify the list, and even convert the list back to normal data.
After you create a list, you can enter and edit data in the list using the same techniques to enter and edit data in a normal worksheet. However, Excel also gives you another way with the use of forms. If you have a lot of data to enter, you can use a form to make it easier. You can enter new records, navigate from record to record, edit existing records, or delete records. A record is a collection of fields from a list. For example, name, address, city, state, and zip are individual fields that make up a record.
Do one of the following to add data to a list:
-
On the worksheet. In the worksheet column for the list, type data into each cell. You can use Tab or Shift Tab to move to the next or previous cell.
-
Create a new record on the worksheet. Enter data in a row marked with an asterisk (*) at the bottom of the list.
-
In a form. Click the Data menu or click the List button on the List toolbar, and then click Form. Use the form to enter or edit the data.
-
Create a record in a form. In the form, click New.
To delete a list row, select a cell in the row, click the Edit menu, and then click Delete Row.
-
You can also click the List button on the List toolbar, point to Delete, and then click Row.
In addition to using standard formatting options on the Formatting Palette tab—such as bold, italic, font, size, color, borders, and shading—to format a list, you can also use a AutoFormat style. AutoFormat allows you to quickly format a list starting from a list style, which you can further modify.
Select a cell or range in the list to which you want to apply an AutoFormat.
Click the Format menu, and then click AutoFormat.
Select the list style you want from the Table format list.
To apply other formatting options, click Options.
Click OK.
Did You Know?
You can copy cell formats with Format Painter. Select the cell or range whose formatting you want to copy, double-click the Format Painter button on the Standard toolbar, select the cells you want to format, and then click the Format Painter button.
You can print an Excel list. Click a cell within the list, click the File menu, click Print, click the List option, and then click OK.
You can get a complete look of the list. To hide the frame around the list so you can fully view the list, click the Visuals button on the List toolbar.
After you create a list, you can sort the entries, add new entries, and display totals. You can insert rows anywhere in a list or add rows at the bottom of the list. To add a blank row at the end of the list, select any cell in the last row of the list, and then press Return, or press Tab in the last cell of the last row. If you no longer need the data in list form, you can convert the list back to normal Excel data. Selecting list rows and columns is different than selecting worksheet rows and columns. Selecting cells is the same. You delete rows and columns in a list the same way you delete rows and columns in a worksheet.
Click a cell in the list where you want to insert or delete a row or column. To insert or delete multiple rows or columns, select more than one row or column.
To insert a row or column, click the Insert menu, and then click Rows or Columns.
-
You can also click the Insert Column or Insert Row button on the List toolbar.
To delete rows or columns, click the Edit menu, and then click Delete Row or Delete Column.
-
Column. Click the top edge of the column header or the column in the list to select column data.
-
Row. Click the left border of the row.
Double-click the column header for the column you want to change.
-
You can also select a cell, and then click the Column Settings button on the List toolbar.
Specify the any of the following column setting options:
-
Column name. Edit the column name for the list.
-
Data type. Select a data type for the list.
-
Formatting. Click Formatting to change cell formatting options for the list.
-
Conditional Formatting. Click Conditional Formatting to add criteria for formatting cells in the list.
-
Option options. Select check boxes to specify a default value or use unique values only in the list.
-
Validation. Click Validation to add criteria for validating data in the list.
Click OK.
Did You Know?
You can resize a list. To resize a list column, point to the border between the list headers (pointer changes to a double-arrow), and then drag. To resize the entire list, point to the bottom right corner (pointer changes to a box with arrows), and then drag.
You can convert a list to a range. Click a cell in the list, click the List button on the List toolbar, click Remove List Manage, and then click Yes.
After you enter records in a list, you can reorganize the information by sorting the records. For example, you might want to sort records in a client list alphabetically by last name or numerically by their last invoice date. Ascending order lists records from A to Z, earliest to latest, or lowest to highest. Descending order lists records from Z to A, latest to earliest, or highest to lowest. You can sort the entire list or use AutoFilter to select the part of the list you want to display in the column. You can also sort a list based on one or more sort fields—fields you select to sort the list. A sort, for example, might be the telephone directory numerically by area code and then alphabetically by last name. If you have manually or conditionally formatted a range or list column by cell or font color or by an icon set, you can sort by these cell attributes using the Sort button.
Click anywhere within the list range.
Click the Data menu, and then click Sort.
Click the Sort by drop-down, and then select a sort field.
Click the Ascending or Descending option.
If you want to sort by other fields, click the Then by drop-down, select a sort field, and then click the Ascending or Descending option.
Click OK.
Did You Know?
You can change the sort order for day or month. If you’re sorting by day or month, you can change the sorting order to calendar order instead of alphabetic order. Click the list cell you want to sort by, click the Data menu, click Sort, click Options, click the First key sort order drop-down, select the method you want, and then click OK twice.
You can sort data in rows. If the data you want to sort is listed across a row instead of a column, click the list cell you want to sort by, click the Data menu, click Sort, click Options, and then select the Sort left to right check box, and then click OK twice.
Working with a list that contains numerous records can be difficult—unless you can narrow your view of the list when necessary. For example, rather than looking through an entire inventory list, you might want to see records that come from one distributor. The AutoFilter feature creates a list of the items found in each field, which is useful in PivotTables. You select the items that you want to display in the column (that is, the records that meet certain criteria). Then you can work with a limited number of records.
Click anywhere within the list range.
Click the Data menu, point to Filter, and then click AutoFilter.
Click the field drop-down for which you want to specify search criteria.
Select the item that records must match in order to be included in the list.
To use built-in filters, select a filter option, such as Show Top 10 or Custom Filter.
Repeat steps 3 through 5, as necessary, to filter out more records using additional fields.
The drop-down displays an icon indicating the field is filtered.
To clear a filter, click the drop-down of the field, and then click Show All.
-
To clear all filters in a worksheet and redisplay all rows, click the Data menu, point to Filter, and then click Show All.
To turn off AutoFilter, click the Data menu, point to Filter, and then click AutoFilter to clear it.
There are many times you’ll want to search for records that meet multiple criteria. For example, you might want to see out-of-stock records of those orders purchased from a particular distributor. Using the AutoFilter feature and the Custom command, you can create complex searches. You can use logical operators to measure whether an item in a record qualifies as a match with the selected criteria. You can also use the logical conditions AND and OR to join multiple criteria within a single search. The result of any search is either true or false; if a field matches the criteria, the result is true. The OR condition requires that only one criterion be true in order for a record to qualify. The AND condition, on the other hand, requires that both criteria in the statement be true in order for the record to qualify.
Click anywhere within the list range.
Click the drop-down next to the first field you want to include in the search.
Click Custom Filter to enable the command (a check mark appears).
Click the Field drop-down (on the left), and then select a logical operator.
Click the drop-down (on the right), and then select a field choice.
If you want, click the And or Or option.
If you want, click the drop-down (on the left), and then select a logical operator.
If you want, click the drop-down (on the right), and then select a field choice.
Click OK.
The drop-down displays an icon indicating the field is filtered.
You can quickly total data in a list using the Total Row option. When you display a total row at the end of the list, a drop-down list appears for each total cell along with the word Total in the leftmost cell. The drop-down list allows you to select a function to perform a calculation. If the function you want is not available in the drop-down list, you can enter any formula you want in a total row cell. If you’re not using a total function, you can delete the word Total.
Click a cell in a list.
Click the Total Row button on the List toolbar.
The total row appears as the last row in the list and displays the word Total in the leftmost cell.
Click the cell in the column for which you want to calculate a total, and then click the drop-down list arrow.
From the drop-down list, select the function you want to use to calculate the total.
Timesaver
Enter a formula in the row directly below a list without a total row to create a total row without the word Total.
Did You Know?
You can create a calculated column. A calculated column uses a single formula that adjusts for each row in a list. To create a calculated column, click a cell in a blank list column, and then type a formula. The formula is automatically filled into all cells of the column. Not every cell in a calculated column needs to be the same. You can enter a different formula or data to create an exception.
The Convert to Columns Wizard helps you separate simple cell contents into different columns. For example, if a cell contains first and last names, you can use the Convert to Columns Wizard to separate first and last name into different columns. The wizard uses the delimiter—such as a tab, semicolon, comma, space, or custom—to determine where to separate the cell contents into different columns; the wizard options vary depending on the delimiter type. For example, the cell contents Julie, Kenney uses the comma delimiter.
Select the range you want to covert to columns.
Click the Data menu, and then click Text to Columns.
In Step 1, click the Delimited or Fixed Width option.
Click Next.
In Step 2, select the delimiter type you want to use, and then clear the other check boxes.
The wizard options vary depending on the selected delimiter.
Click Next.
For the Fixed Width option, click to set a column width, and then click Next.
In Step 3, click a column in the Data preview box, and then click the Text option, and then repeat this for each column you want.
Click the Collapse Dialog button, select a new destination for the separated data, and then click the Expand Dialog button.
Click Finish.
A sales report that displays daily, weekly, and monthly totals in a hierarchical format, such as an outline, helps your reader to sift through and interpret the pertinent information. In outline format, a single item can have several topics or levels of information within it. An outline in Excel indicates multiple layers of content by displaying a plus sign (+) on its left side. A minus sign (-) indicates that the item has no contents, is fully expanded, or both.
Organize data in a hierarchical fashion—place summary rows below detail rows and summary columns to the right of detail columns.
Select the data that you want to outline.
To create an outline, click the Data menu, point to Group and Outline, and then click AutoOutline.
To create a group, click the Data menu, point to the Group and Outline, and then click Group. Click the Rows or Columns option, and then click OK.
-
Click a plus sign (+) to expand an outline level; click a minus sign (-) to collapse an outline level.
Did You Know?
You can ungroup outline data.Select the data group, click the Data menu, point to Group and Outline, and then click Ungroup, click the Rows or Columns option, and then click OK.
You can clear an outline. Select the outline, click the Data menu, point to Group and Outline, and then click Clear Outline.
Worksheet cells can be adjusted so that only certain values can be entered. Controlling how data is entered decreases errors and makes a worksheet more reliable. You might, for example, want it to be possible to enter only specific dates in a range of cells. You can use logical operators (such as equal to, not equal to, less than, or greater than) to set validation rules. When invalid entries are made, a message—developed and written by you—appears indicating that the entry is in violation of the validation rules. The rule set will not allow data to flow into the cell.
Select the range you want covered in the validation rules.
Click the Data menu, and then click Validation.
Click the Settings tab.
Click the Allow drop-down, and then select a value type.
Options vary depending on the Allow value type you select.
Click the Data drop-down, and then select a logical operator.
Enter values or use the Collapse Dialog button to select a range for the minimum and maximum criteria.
Click the Input Message tab, and then type a title and the input message that should be displayed when invalid entries are made.
Click the Error Alert tab, and then select an alert style, type a title, and error message.
Click OK.
To view invalid data, click the Circle Invalid Data on the Formula Auditing toolbar. To clear the circles, click Clear Validation Circles on the Formula Auditing toolbar.
Entering data in a list can be tedious and repetitive. To make the job easier, you can create a drop-down list of entries you define. This way you get consistent, accurate data. To create a drop-down list, create a list of valid entries in a single column or row without blanks, define a name, and then use the List option in the Data Validation dialog box. To enter data using a drop-down list, click the cell with the defined drop-down list, click the drop-down, and then click the entry you want.
Type entries in a single column or row without blanks in the order you want.
Select the cell range, click the Name box, type a name, and then press Return.
Select the cell where you want the drop-down list.
Click the Data menu, and then click Data Validation.
Click the Settings tab.
Click the Allow drop-down, and then click List.
Enter values or use the Collapse Dialog button to select a range of valid entries.
Click the Input Message tab, and then type a title and the input message that should be displayed when invalid entries are made.
Click the Error Alert tab, and then select an alert style, type a title, and error message.
Click OK.
