## How to use Excel Formulas ? Complete Guide of Excel Formulas

What is a Formula?

A formula is basically a simple mathematical calculation similar that you want to be evaluated. All

formulas must start with an equal sign (=).

The simple formulas contain mathematical operators which are just symbols to denote the

mathematical operations.

The most common operators are addition, subtraction, multiplication, and division.

Formulas are cell entries that perform calculations and return a result.

Using formulas can be useful if you want to perform calculations on values in other cells.

This can be very useful especially if the values in other cells are likely to change.

You can either use the Formula Bar, which is located above the column headings or you could use

the mouse to select the various cells.

Using the Formula Bar

This is located below the toolbars and contains a Name box and Formula bar.

The Formula bar is the area where you can enter text, numbers, dates, formulas, etc.

**Image Missing**

The Name box displays the name of the active cell or selected cells and can be used to apply a name

to a particular cell range.

The drop-down menu next to the name box can be used to quickly navigate to particular named

ranges.

As you type your formula the text will appear in both the cell and the formula bar.

Inserting a Formula

Rather than entering your formulas through the Formula Bar, you can just enter them directly into the

cells.

This is the preferred method for a lot of people who are familiar with the function arguments.

Try to avoid hard coding values into formulas. Enter the data into cells and then reference the cells.

In this very simple example, a formula is used in cell D2 to calculate the total of the values in cell B2

and C2.

The advantage of using a formula is that it is updated automatically when the cells that it refers to

change.

Once you have finished the formula, press Enter to confirm.

After you have pressed Enter the cell will display the result of the formula as opposed to the actual

formula.

**Image Missing**

All the formulas contain basic operations such as addition, subtraction, multiplication, and division.

To represent these operations you use what are called Operators in between your numbers.

When you create a formula you need to include at least one operator.

What are the Operators ?

Operators are just symbols that represent mathematical operations.

There is a strict order of precedence among the operators which means that certain operations are

performed before others, assuming that parentheses are not used.

Operators that have high precedence are evaluated first. Negation is always the first operator to be

evaluated.

If you are not sure about the order in which operations are carried out then use parentheses to define

the order.

**Image Missing**

that some operators have the same precedence. In this case, they will be evaluated from left to

right.

Using Parentheses

Parentheses are basically brackets. Using brackets in your formulas is a good habit to get into even

when they are not strictly needed because they help to clarify the order and make the formula easier

to understand.

It is also possible to put parentheses within parentheses. When parentheses have nested the ones on the

inside are evaluated first.

When you enter parentheses you will notice that Excel tries to help you by making the matching

parentheses bold as you pass over it with the mouse.

Your parentheses must balance. For every open parentheses there must be corresponding close parentheses. If you try to submit a formula which does not contain the same number of open and

close parentheses, Excel will prompt you and tell you that the formula you have entered is incorrect.

Excel will make a suggestion as to what it thinks the formula should be. You can accept the

recommendation or press "No" to edit the formula yourself.

The table below shows the more formulas entered in column B and the result obtained in column C.

**Image Missing**

String Concatenation Operator

The string concatenation "&" operator is also known as the ampersand.

This operator can be used to join several text strings together into a single string.

When a text string is used within a formula the characters must be enclosed in speech marks " ".

You can use this operator to join any two values (text or numeric) in order to create a single text

string.

The table below shows the more formulas entered in column B and the result obtained in column C.

**Image Missing**

Note that all the logical operators return either True or False.

Things to Remember

· All formulas must start with an equal sign (=). If you type an equal sign into a cell, Excel assumes

that you are about to enter a formula. If you do not enter an equal sign then Excel will assume you

want to enter a text string.

· Formulas can contain simple mathematical operators, values, cell references, and functions.

· There is a strict order of precedence among the operators which means that certain operations are

performed before others.

· Any expression in a formula that is enclosed within parentheses is calculated first.

· When copying formulas Relative cell references are the default.

· Workbooks that contain a large number of formulas require a large amount of memory and hence

take longer to open.

· Formulas that contain links (especially to other workbooks) can often cause a real headache.

Step 1 -

Why

should I

use Cell

References

?

Instead of hard typing all the values used in your spreadsheet it is possible to use cell references as

well.

This means that your spreadsheet becomes more dynamic and will change accordingly when the

values in the cells change.

Any formulas that contain cell references that are entered in lowercase or mixed case (i.e. B3: H6)

will be automatically changed to uppercase.

An individual cell can be referenced by an unlimited number of formulas.

A cell reference does not have to contain an operator unless you want to use it to perform an

operation.

Step 2 - Cell references on the same worksheet

It is possible to use references to other cells that are on the same worksheet.

You can insert a cell reference into a formula by either typing the address directly or by selecting the

cell with the mouse.

When you use the mouse to enter a cell reference the address will appear automatically and a dotted

the line will appear around the cell.

Once the cell (or range of cells) has been selected click back into the formula bar to enter the rest of

the formula.

You can use either the formula bar to add and edit your formulas or you can edit them directly in the

cell.

Select cell B2, Enter an equal sign. You can then immediately select cell D2.

**Image Missing**

Excel uses color-coding to help you manage your cell references. Each cell reference and the cell it

refers to are displayed in the same color.

The color-coding makes it very easy to identify which references in the formula match which cells

on the worksheet.

If you are using the mouse to select cell references to construct your formula, only press the Enter

key to confirm the final formula and not the individual cell references.

Step 3 - Cell references to a different worksheet in the same workbook

It is possible to use references to other worksheets in your formulas.

Let's assume that you have another worksheet in your workbook, called Sheet2 and that this

worksheet contains a number in cell D2.

To select a cell on a different worksheet you can use the worksheet tabs at the bottom to switch

between the worksheets.

Type your formula as usual and at the point where you want to include the cell reference select that

worksheet using the tabs at the bottom.

You will notice that the corresponding prefix "Sheet2!" will be automatically inserted into your

formula.

Select cell D2. Do not use the mouse to click back to the original worksheet.

Before you can select the original worksheet you must either insert another operator or you must

Press the Enter key to confirm the formula.

**Image Missing**

If you do not enter another operator before switching back then a reference to the original worksheet

is used.

Step 4 - Cell references to a worksheet in a different workbook

It is possible to use references to cells in other workbooks. These workbooks can either be open or

closed.

When the referenced workbook is open the formula is displayed as below.

The name of the workbook must be surrounded by square brackets.

If the name of the worksheet contains any spaces then the worksheet name must be enclosed in single

quotes.

**Image Missing**

Notice that all cell references that refer to other workbooks are inserted as absolute references by

default (see later).

If the referenced workbook is not currently open then the full directory location of the file can be

used.

In this case, the directory location and the workbook and worksheet name must be enclosed in single

quotes.

**Image Missing**

It is possible to type in the cell references directly into your formulas although it is much easier to

use the mouse.

To create a cell reference to another workbook, open the other workbook first and then use the

Window drop-down menu to switch to the other workbook and select the required cell.

When you create a cell reference to a different workbook the actual data is stored in the other

workbook and a copy of the data are just displayed.

The cell references will still update even when the other workbook is closed.

Every time a workbook is opened that contains cell references (or links) to other workbooks you will

be prompted as to whether you want to update these links.

Step 5 - 3D Cell References

You can use references to perform calculations on cells that span a range of worksheets in a

workbook.

This technique is extremely useful if you want to summarise a group of worksheets that all have an

identical layout.

=SUM(Sheet1: Sheet4!A2)

Only certain worksheet functions can be used in 3-dimensional formulas. Please refer to the 3D

Formulas page for more details.

It is possible to include named ranges in your formulas and using them in your formulas can often

make your formulas a lot easier to understand.

Instead of referring to a cell "=D2" (or a range of cells) using the cell address, you can actually use a

descriptive name.

In the example below a worksheet named range has been created for cell "D2" called

"Named_Range_D2".

You can insert named ranges into your formula by selecting (Insert > Name > Paste).

**Image Missing**

Step 7 - Types of Cell Reference

When you refer to cells you have two options.

You can either refer to cells using Relative References or you can use Absolute References.

The type of reference used is only relevant when you copy the formulas to other cells.

As the names suggest relative references will change so they refer to cells relative to the cell

containing the formula. Absolute references will always refer to the same cells.

You can press the F4 key multiple times when the cursor is in a cell reference to toggle between the

different types. This works in both the formula bar and in cells directly.

There are actually four types of references:

Relative Addressing ( =A1 ) - This is the default. Relative column reference and Relative row

reference.

For example, if the formula "=B2" was entered into cell C3 and then cell C3 was copied to cell E5,

the formula would change accordingly to "=D4".

**Image Missing**

Row Absolute ( =A$1 ) - Relative column reference and Absolute row reference. The row number

always remains the same.

Column Absolute ( =$A1 ) - Absolute column reference and Relative row reference. The column

the letter always remains the same.

Step 8 - Copying Formulas

When you copy and paste formulas containing Relative references, the references are adjusted

automatically.

The Relative reference is defined by the number of cells between the row and column of the cell

being referenced and that of the cell containing the formula.

When you cut and paste formulas containing Relative references, the references are NOT adjusted

automatically.

When you copy and paste formulas containing Absolute references, the references are NOT adjusted

automatically.

When you cut and paste formulas containing Absolute references, the references are NOT adjusted

automatically.

A quick way to copy formulas is to use the AutoFill handle in the bottom right-hand corner of the

active cell.

When you copy a formula that contains mixed references the only part that is adjusted automatically

is the row or column that does not have a $ sign in front of it.

Step 9 - Editing Cell References

Pressing F2 when a cell is selected allows you to edit the formula directly.

Any cell references used in the formula will be automatically highlighted and will appear in different

colours.

These coloured squares are referred to as the Range Finders.

**Image Missing**

You can click and drag any of the coloured boxes to new cells to quickly adjust the cell references

used in the formula.

The four squares in the corners allow you to increase or decrease the range of cells being referred to

by dragging these squares with your mouse.

As you change the cell references you will see the formula changing automatically.

Step 10 - Updating Cell References

A workbook can be updated (or calculated) when it is opened, closed.

You can force a recalculation in all the open workbooks at any point by pressing the F9 key. This

will only calculate formulas that have changed since the last calculation.

(Shift + F9) - This is the same as the F9 except that it only recalculates cells on the active worksheet.

There is currently no way to quickly recalculate all the cells in just the active workbook.

(Ctrl + Alt + F9) - Recalculates all cells in all open workbooks regardless of whether they need to be

recalculated. This does not seem to work in Excel 2003.

You can also normally interrupt the calculation process by pressing Escape several times.

Step 11 - Things to Remember

· The (F4) shortcut key toggles between the four different types of references.

· A quick way to copy formulas is to replace the "=" with a "#" copy and paste the formula and then

replace it back.

· Excel automatically adjusts formulas so when you insert a new row(s) or column(s) the formulas are

adjusted to include the new row(s) or column(s).

· When you cut and paste formulas containing Relative references, the references are NOT adjusted

automatically.

· You can use the formula bar to copy a formula and then paste the formula directly into another

cell. Remembering to press Escape after you have copied the formula.

· If you have a lot of complicated formulas within a workbook you should try and build-in as many

cross-checks as possible so the data can help to check itself.

· If you have linked any of your workbooks re-naming them afterwards will create problems. You

should avoid renaming any files that are referenced by other workbooks.

Step 1 -

What is

A1

Notation?

This is the default method used for creating cell references to other cells.

To refer to a cell, enter the column letter followed by the row number, for example, "=B2" to refer to

the cell which is the intersection of column "B" with row "2".

This method uses the familiar column letter and row number notation to refer to other cells.

Cell addresses consist of a column letter and arrow number. Absolute references have letters and

numbers. Relative references have a dollar in front of the letter or number.

Image Missing

Step 2 - What is R1C1 Notation?

This is another way to create cell references which use numbers for both the rows and columns.

Cell References are displayed in terms of their relationship to the cell that contains the formula rather

then their actual position in the grid.

Cells are referred to by relative notation. Absolute references have numbers. Relative references have

numbers in square brackets.

The above formulas will be changed to the following when you switch to R1C1 notation.

Image Missing

Negative row numbers mean that the referenced cell is above the cell containing the formula.

Negative column numbers mean that the referenced cell is to the left of the cell containing the

formula.

Changing to R1C1 notation will change all the formulas in that workbook.

Step 3 - Switching to R1C1 Notation

You can change your cell references to the R1C1 notation from the (Tools > Options) dialogue box.

Select the "General tab" and select the "R1C1 Reference Style" checkbox.

Image Missing

It is important to remember that changing this option will change all the formulas in the active

workbook.

There are two different types of R1C1 Notation. You can have relative references or absolute

references.

Relative References ( R[2]C[2] ) are the default and these always include square brackets around the

numbers.

Image Missing

Step 4 - Advantages of R1C1 Notation

Although this is an older style of referencing cells it can prove to be useful.

This notation can be useful when you are more interested in the relative position of a cell rather than

in its absolute position.

Step 5 - Things to Remember

· In R1C1 style, both columns and rows are labeled numerically and the references are enclosed in

square brackets

· References to cells and ranges do not have to be in the same sheet as the formula, nor even in the

same workbook.

Step 1 - (Tools

> Option)

dialogue box

Image Missing

Automatic - Calculates all dependent formulas every time you make a change to a value, formula or

name. Any workbook that contains formulas will be automatically calculated when it is opened. This

is the default calculation setting.

Automatic except tables - Calculates all dependent formulas except those in data tables. You can

calculate the formulas in data tables by pressing the "Calc Now" button,

Manual - Calculates only when requested by the user.

Recalculate before save - Calculates all dependent formulas in the worksheet before it is saved.

Calculate Now - Calculates all open worksheets, including data tables, and updates all open chart

sheets.

Calc Sheet - Calculates the active worksheet and any charts and chart sheets linked to this worksheet.

All these settings are workbook specific but it is the first workbook that is opened that determines

what the settings are. Opening subsequent workbooks will not change the options.

For more information about the Calculation tab of the (Tools > Options) dialogue box, please refer to

the Calculation Tab page.

Step 2 - What does Automatic Calculation mean?

The default setting is Automatic this means that Excel will automatically recalculate all the cells in a

workbook when any value in the workbook changes.

This means that the data and formulas in your workbook are constantly up-to-date.

Most of the time when the workbook is fairly simple recalculation takes a fraction of a second since

the majority of the cells will be unaffected.

This type of calculation is the default and is what some people call a minimum recalculation. This

means that only the necessary cells are recalculated.

When you have a complicated workbook containing a lot of formulas Excel will determine which

cells to recalculate and in what order.

It is important to remember though that Excel can only track 65,536 dependencies to unique

references.

If Excel finds more than 65,536 dependencies then the whole workbook is calculated and the word

"Calculate" will appear in the status bar.

Image Missing

For more information please refer to this Knowledge Base Article (243495).

Step 3 - What does Manual Calculation mean?

This lets you select or clear the Recalculate Before Save checkbox.

The "Recalculate Before Save" checkbox controls if the formulas are recalculated before the

the workbook is closed.

This is often a good idea, as it prevents a user from opening the workbook to display data that is not up-to-date.

The manual calculation is a good idea when you have large workbooks as you want to control when a

recalculation occurs.

If your workbook contains any uncalculated formulas in the active workbook when you are working

in Manual calculation, the word "Calculate" will be displayed in the status bar.

Image Missing

Step 4 - Tips for Optimisation

1) Arrange your worksheets in alphabetical order with the sheets containing cells that are referred to

a lot being at the start of the alphabet and those that contain a lot of formulas using those cells should

be last.

2) If you are including user-defined functions in your formulas, then try to put them at the end of the

formula. For example instead of having "=UDF() + B2" use "=B2 + UDF()".

3) Any cell values which a user-defined function might refer to are obtained at the top of the function

and not after a lot of initial calculations.

Step 5 - Things to Remember

· (F9) - Recalculates any cells that have changed in all open workbooks. This will only calculate

formulas that have changed since the last calculation.

· (Shift + F9) - This is the same as the F9 except that it only recalculates cells on the active

worksheet.

There is currently no way to quickly recalculate all the cells in just the active workbook.

· (Ctrl + Alt + F9) - Recalculates all cells in all open workbooks regardless of whether they need to be

recalculated. This does not seem to work in Excel 2003.

· (Ctrl + Shift + F9) - Recalculates all cells in the active workbook regardless of whether they need to

be recalculated. This does not seem to work in Excel 2002 or Excel 2003.

· (Ctrl + Alt + Shift + F9) - Recalculates absolutely everything in all open workbooks regardless of

whether they need to be recalculated. This includes all custom worksheet functions and external

worksheet functions.

Check

your

Option

s

Check your (Tools > Options)(Edit tab, Extend data range formats and formulas) to ensure that this

the tag will be displayed.

Image Missing

· Any data pasted from the clipboard will not have automatic formulas applied to it.

Step 1 - Finding any Formula

Errors

Sometimes when you enter a formula an error will occur. This is to indicate that the formula syntax

is incorrect.

If this error occurs press OK to be taken back to the formula bar. You can either correct the formula

or press ESC to remove the formula completely.

This error may be caused by missing parentheses or incorrect arguments being passed to functions

(e.g. passing a string when it is expecting a number).

To quickly locate any cells that contain errors, select (Edit > GoTo > Special) and tick the Formulas,

Errors checkbox.

Image Missing

Step 2 - Different Types of Errors

The following errors can be returned from your formulas.

Even formula that has the correct syntax can occasionally return error values.

The following is a list of the possible error values and the reasons the error might have been generated.

Image Missing

The formula is cell B2 tries to divide D2 by D3, which generates an error as division by zero is not

possible.

The formula is cell B3 includes the ISERROR() function as a wrapper around the formula.

Step 4 - Things to Remember

· To prevent the misspelling of named ranges select the Name Box to insert them into your

formulas.

· The AutoCorrect feature will often eliminate some of the more common formula entry errors.

Check your

Options

Check your (Tools > Options)(Error Checking tab, Enable background error checking) to ensure that

this tag will be displayed.

The Error Checking smart tag will only be displayed if this option selected.

Image Missing

All the formulas in column F are equivalent and all return the total 110.

Formulas can work with arrays that are contained in cells, arrays that are constant as well as an array

formulas.

Step 4 - Things to Remember

· You cannot change any of the individual cells within an array block.

· If you accidentally enter an array formula by not pressing (Ctrl + Shift + Enter) then the formula will

either return an incorrect value or #VALUE! will be returned.

What is

a Mega

Formula?

Quite often a formula requires a number of intermediate formulas in order to produce the correct

result.

After you have got all your formulas working it is possible to eliminate the intermediate formulas

and create one big "mega formula".

Image Missing

Formulas can only contain a maximum of 1024 characters.

If your "Mega formula" is longer than this then you should consider creating a user-defined function.

What are the Advantages?

· Fewer Cells

· Recalculation is a lot faster

· Size of the workbook is reduced

What are the Disadvantages?

· Significantly harder (if not impossible) to understand and modify.

Example

illustrating the following

concatenating

finding the first space

finding the next space

removing excess spaces

getting first, middle and last words

Rather than edit all these manually you could opt for a formula based solution.

When you are satisfied that the mega formula is returning the same result you can delete all the

intermediate formulas.

It is often to keep a copy of the intermediate formulas in case you need to make an amendment to

the formula at a later date.

Conditional

l Formula

Allows you to perform calculations on only those numbers that meet a certain condition.

To create a condition you can use various comparison operators, such as greater than (>), greater

than or equal to (>=), less than (<), less than or equal to (<=) and equal to (=).

Type a comparison operator, the condition and then a comma.

=SUM( IF(Named_Range1 > 10,Named_Range2) )

The IF function can be used with array formulas

Must be entered with (Ctrl + Shift + Enter)

What is a 3D Formula?

These are also known as cubed formulas.

A reference that refers to the same cell or range on multiple worksheets is called a 3D reference.

Using 3D formulas allows you to calculate data throughout a workbook using multiple worksheets.

All 3D formulas are based on the syntax: Sheet1:Sheet4!A2: B5.

A 3D formula is a formula that refers to the same cell (or range of cells) on multiple worksheets.

The 3D formula "=SUM(Sheet1: Sheet4!A2)" can be used to add up the numbers in cell "A2" on 4

different worksheets.

Summarising your worksheets

Image Missing

Let's assume that we have a workbook that contains five worksheets and that four of them contain

data for specific years.

Four of the worksheets correspond to the sales figures for the years 2005 - 2002 and the first

worksheet is intended to be a summary of these four years.

Image Missing

On the Summary worksheet, we want to be able to quickly return the total for all the Regions and for

the months.

It is possible to create 3D formulas which refer to all four worksheets which make creating a

summary worksheet very easy.

Let's assume that each of the four worksheets contains the following table of data.

Image Missing

Things to Remember

· It is possible to create named ranges for 3D cell references which

can be used to simplify your 3D formulas.

· For more information on 3D named ranges, please refer to the 3D

Named Ranges page.

Protecting your

formulas can prevent

accidental editing and

viewing

In order for a cell to be protected - The worksheet

must be protected and the "Locked" checkbox on the

(Format > Cells)(Protection tab) must also be

checked. (this is the default).

(Format > Cells)(Protection tab) ??

If you only want to protect a few cells - first remove the locked property from all the cells. Select

(Ctrl + "A") and clear the locked checkbox. This will mean that even if the worksheet is protected

the cells will not be.

Find the individual cells that contain formulas you want to hide and change the property back to

Locked.

If you want to protect a few formulas without protecting the whole sheet select the cells and choose

(Data > Validation), Select custom in the first window and type (="") in the second window.

A few

facts

about

formula

A formula cannot include more than 1,024 characters.

If you have formulas linking to a workbook and when this workbook is open you press (File >

SaveAs) to create a backup, you will automatically change the link formulas to refer to this new file.

It is possible to enter fractions but always precede with a space to ensure that Excel does not interpret

it as a date.

When entering your formulas you can press F3 when you are in the formula bar to quickly insert a

named range.

To quickly copy a formula down of a cell down that have data in either of its surrounding columns

just double click on the fill handle in the bottom right corner of the cell. ???

If you have really complicated formulas you may find it useful to edit the formula directly in the cell.

Press F2.

What does the "(Calculate)" mean in the status bar ??

Copying and Pasting

A quick way to copy a large number of formulas that contain relative references without the

references changing is to (Edit > Replace) the "=" with "#" before copying and pasting and then (Edit

> Paste) the "#" with "=".

You can mess up your links by renaming the source workbook when the dependent workbook is not

open.

You can easily create link formulas that refer to cells in other workbooks. If the workbook name in

the reference included one or more space you must enclose it (and the worksheet name) in single

quotation marks.

Evaluating parts of your formulas

You can use the F9 key to evaluate parts of your formulas. Highlight the portion of the formula that

you want to resolve and press the F9 key.

Always press the ESC key afterwards. Be careful not to press the Enter key as this will result in your

formula being permanently changed. This can be used to see the values that a range is actually

returning.

Format your Formulas

It is possible to enter extra spaces and carriage returns in your formulas to make them easier to read.

You can enter a carriage return by pressing (Alt + Enter).

Image Missing

Formula Autocorrect

Excel identifies and suggests corrections for 15 of the most common formula errors. If an incorrect

formula has been entered, then a message box will appear with the option to accept or cancel the

correction.

You can construct formulas using natural references as well as using named ranges.

Printing your formulas

(Ctrl + "~") - You can toggle between displaying the values and formulas by pressing

Alternatively, you could press (Tools > Options)(View, Formulas)

Hiding your Formulas

It is very common to actually remove the formula after it has done its calculation. This can be done

by passing the resulting number as a value (i.e. not a formula). Copy the cell contents and select (Edit

> Paste Special)(Values).

You can quickly select all the cells that contain formulas by using the (Edit > GoTo > Special) and

selecting Formulas ??

By default, if a worksheet is protected the formulas can still be viewed. Before protecting the

worksheet you must indicate if you want the formulas to be hidden. (Format > Cells) (Protection tab)

select the hidden checkbox for all your cells. To ensure changes canÄ±t be made to ensure the locked

checkbox is checked.

Debugging Formulas

If you are checking that formulas are correct, you can create a new window of the same workbook

and view the values in one window and the formulas in another window. You can quickly toggle

between the values and formulas by pressing ??

If you have a large number of intermediate formulas you can combine them into one large formula.

The advantage of this is that the recalculation of the spreadsheet is faster.

The quickest way to convert formulas to values is to move the formulas one cell to the right, and then

hold down the right mouse button, when you drag them back to the original position. Choose "copy

as values" from the shortcut menu ??

If you enter a large formula and it is not correct, press the OK to edit the formula, press HOME to

take you to the start of the formula and enter an apostrophe. This will enter your formula as text and

allow you to edit it easily

You can examine the components of a large formula by dragging the pointer to highlight part of the

formula and pressing the F9 key to evaluate only the highlighted part. Remember to press the ESC

key afterwards.

You can quickly select all the cells that contain formulas by choosing (Edit > GoTo > Special) and

select formulas ??

The N() worksheet function is a way to include a text description into a cell containing a formula,

without it affecting the formula.

You probably won't use the R1C1 notation as your default although it is very useful for checking

your copied formulas. Every cell should have the same R1C1 formula.

You can retrieve data from a file without actually opening it (e.g. use the formula

"=[File_Name.xls]Sheet1!A1").

You can easily display leading zeros by using a custom number format "000000". This will mean

that 6 numbers are entered and any that are not entered will be zero.

You may find it helpful when editing cell references that link to other worksheets to temporarily

change the worksheet name to a shorter one. Making changes with a shorter worksheet name is easier

and the name can then be changed back afterwards.

Highlighting all cells that contain a formula using Conditional Formatting

This method uses the XLM language which is really old ??

Enter the formula =GET.CELL(48,INDIRECT("RC",FALSE) in the reference field of the Define

Name and call the named range "FormulaInCell"

This can then be used as a condition in the Conditional Formatting

Fixing formula problems

Whenever you insert or move rows and columns at the edge of cell ranges referred to by formulas,

the formulas are adjusted automatically.

To help you identify these problems small triangular indicators will appear in the upper left corner of

a cell if Excel thinks they may be a problem. When you select the cell a smart tag will appear to

display the Ä±Formula Omits Adjacent CellsÄ± menu.

Solving a set of simultaneous equations

Start with equations that are linearly independent so there is a solution

17 = 5x + 3y + 2z

13 = 2x + 4y + z

22 = 3x + 2Y + 5z

Put the coefficients of the unknowns in, e.g. Ä±A1:C3Ä± (i.e. 5,3,2 in A1:C1; 2,4,1 in A2:C2 and 3,2,5 in A3:C3)

Put the constants (17,13,22) in D1:D3

Highlight E1:E3 and enter Ä±=MMULT(MINVERSE(A1:C3,D1:D3)Ä± as an array (press Ctrl + Shift + Enter)

and the solution vector (1,2,3) will appear in E1:E3 meaning x=1, y=2, z=3

If the set of equations does not have a solution then Ä±#VALUEÄ± will appear

This formula returns an array of only the unique items from an expanding column

list Ä±=IF(T(OFFSET(TheList,sArray,,1))=Ä±Ä±,N(OFFSET(TheList,sArray,,1)),T(OFFSET(TheList,sArray,,1)) )Ä±

where TheList = OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),)

and sArray =

SMALL(IF(MATCH(TheList,TheList,0)=ROW(TheList),ROW(TheList),Ä±Ä±),ROW(INDIRECT(!1:Ä±&SUM(N(MATCH(TheList,1Ä±

Note that this is too slow for arrays > 1000 items

Shortcut Keys

(Ctrl + ~) - You can toggle between displaying the values and formulas by pressing

(F2, F9) - Pastes a formula as values.

(Ctrl + Home) - Moves to the beginning of a formula when you are editing it.

(Ctrl + End) - Moves to the end of a formula when you are editing it.

Financial Formulas - by Category

For convenience, I've listed all of the Excel Financial functions, with a * next those that

are only available after installing the Analysis ToolPak (To install, go to Tools > Add-ins

> and select Analysis ToolPak).

Depreciation Formulas

· DB - Fixed-Declining Balance

· DDB - Double-Declining Balance

· SLN - Straight-Line Depreciation

· SYD - Sum-of-Years' Digits

· VDB - Variable Declining Balance

· * AMORLINC - (for the French accounting system) Depreciation for each

accounting period

· * AMORDEGRC - (for the French accounting system) Uses a depreciation

coefficient

Formulas for Interest, Cash Flow, Investments, Annuities

· * CUMIPMT - Cumulative Interest Payment

· * CUMPRINC - Cumulative Principal

· * EFFECT - Effective annual interest rate

· FV - Future Value of an investment

· * FVSCHEDULE - Future Value with a variable rate

· IPMT - Interest Payment for an investment or loan

· IRR - Internal Rate of Return

· ISPMT - Interest Payment during a Specific period (for compatibility with Lotus)

· MIRR - Modified Internal Rate of Return

· NPER - Number of Periods for an investment or loan

· NPV - Net Present Value formula

· PMT - Periodic Payment for an annuity

· PPMT - Payment on the Principal for an annuity or loan

· PV - Present Value of an investment

· RATE - Interest rate per period

· * XIRR - Internal Rate of Return (not necessarily periodic)

· * XNPV - Net Present Value (not necessarily periodic)

Functions for Coupons

· * COUPDAYBS - Days from the Beginning of the Coupon period to the

Settlement date

· * COUPDAYS - Days in the coupon period that contains the settlement date

· * COUPDAYSNC - Days from the Settlement Date to the Next Coupon date

· * COUPNCD - Next Coupon Date after the settlement date

· * COUPPCD - Previous Coupon Date before the settlement date

· * COUPNUM - Number of coupons between the settlement and maturity date

Finance Formulas for Securities

· * ACCOUNT - Accrued Interest

· * ACCRINTM - Accrued Interest at Maturity

· * DISC - Discount rate

· * DURATION - Annual Duration

· * INTEGRATE - Interest rate for a fully invested security

· * DURATION - Macauley modified duration (with an assumed par value of

$100)

· * NOMINAL - Annual nominal interest rate

· * ODDFPRICE - Price per $100 face value with an Odd First period

· * ODDFYIELD - Yield with an Odd First period

· * ODDLPRICE - Price per $100 face value with an Odd Last period

· * ODDLYIELD - Yield with an Odd Last period

· * PRICE - Price per $100 face value

· * PRICEDISC - Price per $100 face value of a Discounted security

· * PRICEMAT - Price per $100 face value of a security that pays interest at

Maturity

· * RECEIVED - Amount received at maturity for a fully invested security

· * YIELD - Yield on a security that pays periodic interest

· * YIELDDISC - Annual yield for a discounted security (Treasury bill)

· * YIELDMAT - Annual yield of a security that pays interest at maturity

Formulas for Dollar Price Conversions

· * DOLLARD - Converts a dollar price from a Fraction to a Decimal number

· * DOLLARFR - Converts a dollar price from a Decimal number to a Fraction

Treasury Bill Functions

· * TBILLEQ - Bond-equivalent yield for a Treasury Bill

· * TBILLPRICE - Price per $100 face value for a Treasury Bill

· * TBILLYIELD - Yield for a Treasury Bill

Excel: Create a hyperlink to another cell

Question: I want to create a hyperlink in Excel. How do I specify a particular location

that a hyperlink should point to?

Answer: To create a hyperlink to another cell in your spreadsheet, right-click on the cell

where the hyperlink should go. Select Hyperlink from the popup menu.

Image Missing

When the Insert Hyperlink window appears, click on the "Place In This Document" on

the left. Enter the text to display. In this example, we've entered "Hyperlink to cell A5".

This is the value that will be displayed in Excel.

Next enter the cell reference that the hyperlink points to. We've chosen to link to cell A5.

Click the OK button.

Image Missing

Excel: Delete all hyperlinks on a sheet

Question: I've had a hyperlink problem in my Excel files for ages: false hyperlinks had

crept in (even in empty cells) and were multiplying regularly whenever I inserted new

lines. How can I delete all hyperlinks in a sheet at once and not have to delete them cell

by cell?

Answer: You will need to create a macro to delete the hyperlink addresses in your Excel

sheet.

Open your Excel spreadsheet that you wish to remove the hyperlinks from. Press

<ALT>-F11 to go to the Visual Basic editor. Create a new module. You can do this by

selecting Module under the Insert menu.

Paste the following code into your new module:

Sub RemoveHyperlinks()

'Remove all hyperlinks from the active sheet

ActiveSheet.Hyperlinks.Delete

End Sub

Close the Visual Basic Editor window by selecting "Close and Return to Microsoft Excel"

under the File menu.

Image Missing

Excel: View hyperlink results at the top of

viewing area

Question: I have an Excel worksheet that has the first 6 rows frozen. In the first 6 rows,

I have hyperlinks to different cells in column B. When I click the hyperlinks, Excel tends

to show the cell at the bottom of the viewing page.

I want the cell that the hyperlink refers to be displayed in the next row after the frozen

rows. Can this be done?

Answer: Let's take a look at an example.

Below, we have an Excel spreadsheet with the first 6 rows hidden. In cell B5, we've

created a hyperlink to cell B30. When we click on the hyperlink in cell B5, the

spreadsheet looks as follows:

Image Missing

The hyperlink is at the bottom of the viewing area in the spreadsheet. We want to see a row

30 directly under row 6.

To fix this, select Options under the Tools menu. When the Options window appears,

click on the Transition tab. Then select the option called "Transition navigation keys".

Click on the OK button.

Image Missing

Excel: VLookup Function

In Excel, the VLookup function searches for a value in the left-most column of

table_array and returns the value in the same row based on the index_number.

The syntax for the VLookup function is:

VLookup( value, table_array, index_number,

not_exact_match )

value is the value to search for in the first column of the table_array.

table_array is two or more columns of data that is sorted in ascending order.

index_number is the column number in table_array from which the matching value must

be returned. The first column is 1.

not_exact_match determines if you are looking for an exact match based on value. Enter

FALSE to find an exact match. Enter TRUE to find an approximate match, which means

that if an exact match if not found, then the VLookup function will look for the next

largest value that is less than value.

Note:

If index_number is less than 1, the VLookup function will return #VALUE!.

If index_number is greater than the number of columns in table_array, the VLookup

the function will return #REF!.

If you enter FALSE for the not_exact_match parameter and no exact match is found, then

the VLookup function will return #N/A.

For example:

Let's take a look at an example:

Image Missing

=VLookup(10251, A1:B21, 2,

FALSE)

would return "Tofu"

=VLookup(10251, A1:C21, 3,

FALSE)

would return $18.60

=VLookup(10248, A1:B21, 2,

FALSE)

would return #N/A

=VLookup(10248, A1:B21, 2,

TRUE)

would return "Queso

Cabrales"

Frequently Asked Questions

Question: In Excel, I'm using the VLookup function to return a value. I want to sum the

results of the VLookup, but I can't because the VLookup returns a #N/A error if no match

is found. How can I sum the results when there are instances of #N/A in it?

Answer: To perform mathematical operations on your VLookup results, you need to

replace the #N/A error with a 0 value (or something similar). This can be done with a

formula that utilizes a combination of the VLookup function, IF function, and ISNA

function.

Image Missing

Based on the spreadsheet above:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP(E2,$A$2:$C

$5,3,FALSE))

would return 0

First, you need to enter a FALSE in the last parameter of the VLookup function. This will

ensure that the VLookup will test for an exact match.

If the VLookup function does not find an exact match, it will return the #N/A error. By

using the IF and ISNA functions, you can return the Unit Price value if an exact match is

found. Otherwise, a 0 value is returned. This allows you to perform mathematical

operations on your VLookup results.

Question: I have a list of #s in column A (let's say 1-20). There is a master list in another

column that may not include some of the columns A #s. I want a formula in column B to

say (if A1 exists in the master list, then "Yes", "No". Is this possible?

Answer: This can be done with a formula that utilizes a combination of the VLookup

function, IF function, and ISNA function.

Image Missing

Based on the spreadsheet above:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)),"No","Yes")

would

return

"No"

=IF(ISNA(VLOOKUP(A5,$D$2:$D$185,1,FALSE)),"No","Yes")

would

return

"Yes"

First, you need to enter a FALSE in the last parameter of the VLookup function. This will

ensure that the VLookup will test for an exact match.

If the VLookup function does not find an exact match, it will return the #N/A error. By

using the IF and ISNA functions, you can return a "Yes" value if an exact match is found.

Otherwise, a "No" value is returned.

Question: Is there a simple way in Excel to VLookup the second match in a column? So,

for instance, If I had an apple, pear, apple listed in the column (each word in a separate cell),

would there be a way to look up the values to the right of the second "apple"?

Answer: This can be done with a formula that utilizes a combination of the Index

function, Small function, Row function (all in an array formula).

*44 Pages!*
Image Missing

Labels:
Excel

## No comments:

## Post a Comment