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!
44 Pages!
Image Missing
