Just About Excel

Excels Keyboard Shortcuts Shortcut key Action

Menu equivalent comments

Version

Ctrl+A

Select All

None

All

Ctrl+B

Bold

Format, Cells, Font, Font Style, Bold

All

Ctrl+C

Copy

Edit, Copy

All

Ctrl+D

Fill Down

Edit, Fill, Down

All

Ctrl+F

Find

Edit, Find

All

Ctrl+G

Goto

Edit, Goto

All

Ctrl+H

Replace

Edit, Replace

All

Ctrl+I

Italic

Format, Cells, Font, Font Style, Italic

All

Ctrl+K

Insert Hyperlink

Insert, Hyperlink

Excel 97/2000

Ctrl+N

New Workbook

File, New

All

Ctrl+O

Open

File, Open

All

Ctrl+P

Print

File, Print

All

Ctrl+R

Fill Right

Edit, Fill Right

All

Ctrl+S

Save

File, Save

All

Ctrl+U

Underline

Format, Cells, Font, Underline, Single

All

Ctrl+V

Paste

Edit, Paste

All

Ctrl W

Close

File, Close

Excel 97/2000

Ctrl+X

Cut

Edit, Cut

All

Ctrl+Y

Repeat

Edit, Repeat

All

Ctrl+Z

Undo

Edit, Undo

All

F1

Help

Help, Contents and Index

All

F2

Edit

None

All

F3

Paste Name

Insert, Name, Paste

All

F4

Repeat last action

Edit, Repeat. Works while not in Edit mode.

All

F4

While typing a formula, switch between absolute/relative refs

None

All

F5

Goto

Edit, Goto

All

F6

Next Pane

None

All

F7

Spell check

Tools, Spelling

All

F8

Extend mode

None

All

F9

Recalculate all workbooks

Tools, Options, Calculation, Calc,Now

All

F10

Activate Menubar

N/A

All

F11

New Chart

Insert, Chart

All

F12

Save As

File, Save As

All

Ctrl+:

Insert Current Time

None

All

Ctrl+;

Insert Current Date

None

All

Ctrl+"

Copy Value from Cell Above

Edit, Paste Special, Value

All

Ctrl+'

Copy Fromula from Cell Above

Edit, Copy

All

Shift

Hold down shift for additional functions in Excel's menu

none

Excel 97/2000

Shift+F1

What's This?

Help, What's This?

All

Shift+F2

Edit cell comment

Insert, Edit Comments

All

Shift+F3

Paste function into formula

Insert, Function

All

Shift+F4

Find Next

Edit, Find, Find Next

All

Shift+F5

Find

Edit, Find, Find Next

All

Shift+F6

Previous Pane

None

All

Shift+F8

Add to selection

None

All

Shift+F9

Calculate active worksheet

Calc Sheet

All

Shift+F10

Display shortcut menu

None

All

Shift+F11

New worksheet

Insert, Worksheet

All

Shift+F12

Save

File, Save

All

Ctrl+F3

Define name

Insert, Names, Define

All

Ctrl+F4

Close

File, Close

All

Ctrl+F5

XL, Restore window size

Restore

All

Ctrl+F6

Next workbook window

Window, ...

All

Shift+Ctrl+F6

Previous workbook window

Window, ...

All

Ctrl+F7

Move window

XL, Move

All

Ctrl+F8

Resize window

XL, Size

All

Ctrl+F9

Minimize workbook

XL, Minimize

All

Ctrl+F10

Maximize or restore window

XL, Maximize

All

Ctrl+F11

Inset 4.0 Macro sheet

None in Excel 97. In versions prior All to 97 - Insert, Macro, 4.0 Macro

Ctrl+F12

File Open

File, Open

All

Alt+F1

Insert Chart

Insert, Chart...

All

Alt+F2

Save As

File, Save As

All

Alt+F4

Exit

File, Exit

All

Alt+F8

Macro dialog box

Tools, Macro, Macros in Excel 97 Tools,Macros - in earlier versions

Excel 97/2000

Alt+F11

Visual Basic Editor

Tools, Macro, Visual Basic Editor

Excel 97/2000

Ctrl+Shift+F3

Create name by using names of row and column labels

Insert, Name, Create

All

Ctrl+Shift+F6

Previous Window

Window, ...

All

File, Print

All

Ctrl+Shift+F12 Print

Alt+Shift+F1

New worksheet

Insert, Worksheet

All

Alt+Shift+F2

Save

File, Save

All

Alt+=

AutoSum

No direct equivalent

All

Ctrl+`

Toggle Value/Formula display

Tools, Options, View, Formulas

All

Ctrl+Shift+A

Insert argument names into formula

No direct equivalent

All

Alt+Down arrow

Display AutoComplete list

None

Excel 95

Alt+'

Format Style dialog box

Format, Style

All

Ctrl+Shift+~

General format

Format, Cells, Number, Category, General

All

Ctrl+Shift+!

Comma format

Format, Cells, Number, Category, Number

All

Ctrl+Shift+@

Time format

Format, Cells, Number, Category, Time

All

Ctrl+Shift+#

Date format

Format, Cells, Number, Category, Date

All

Ctrl+Shift+$

Currency format

Format, Cells, Number, Category, Currency

All

Ctrl+Shift+%

Percent format

Format, Cells, Number, Category, Percentage

All

Ctrl+Shift+^

Exponential format

Format, Cells, Number, Category,

All

Ctrl+Shift+&

Place outline border around selected cells

Format, Cells, Border

All

Ctrl+Shift+_

Remove outline border

Format, Cells, Border

All

Ctrl+Shift+*

Select current region

Edit, Goto, Special, Current Region All

Ctrl++

Insert

Insert, (Rows, Columns, or Cells) Depends on selection

All

Ctrl+-

Delete

Delete, (Rows, Columns, or Cells) Depends on selection

All

Ctrl+1

Format cells dialog box

Format, Cells

All

Ctrl+2

Bold

Format, Cells, Font, Font Style, Bold

All

Ctrl+3

Italic

Format, Cells, Font, Font Style, Italic

All

Ctrl+4

Underline

Format, Cells, Font, Font Style, Underline

All

Ctrl+5

Strikethrough

Format, Cells, Font, Effects, Strikethrough

All

Ctrl+6

Show/Hide objects

Tools, Options, View, Objects, Show All/Hide

All

Ctrl+7

Show/Hide Standard toolbar

View, Toolbars, Stardard

All

Ctrl+8

Toggle Outline symbols

None

All

Ctrl+9

Hide rows

Format, Row, Hide

All

Ctrl+0

Hide columns

Format, Column, Hide

All

Ctrl+Shift+(

Unhide rows

Format, Row, Unhide

All

Ctrl+Shift+)

Unhide columns

Format, Column, Unhide

All

Alt or F10

Activate the menu

None

All

Ctrl+Tab

In toolbar: next toolbar

None

Excel 97/2000

Shift+Ctrl+Tab In toolbar: previous toolbar

None

Excel 97/2000

Ctrl+Tab

None

In a workbook: activate next workbook

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.

Cancel - Cancels the immediate changes made to the active cell. Enter - Enters the number, text or formula into the active cell. 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.

If we change the value in cell B2 to 9, the formula is re-calculated automatically and the correct result is displayed.

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

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 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 a 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. Order

Symbol

Operation

1

-

Negation

2

%

Percentage

3

^

Exponentiation

4

/

Division

4

*

Multiplication

5

+

Addition

5

-

Subtraction

6

&

String concatenation

7

=

Equal To

7

<>

Not Equal To

7

<

Less Than

7

>

Greater Than

7

<=

Less Than or Equal To

7

>=

Greater Than or Equal To

Notice 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 are 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 a 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.

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.

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 strictly order of precedence among the operators which means that certain operations are performed before others. • Any expression in a formula that is enclosed within a parentheses is calculated first. • When copying formulas Relative cell references is 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 containan 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 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.

The cell addresses of any cells that you select will automatically be included in your formula. You can easily include more cell references by inserting an operator and then selecting on another cell.

Excel uses colour coding to help you manage your cell references. Each cell reference and the cell it refers to are displayed in the same colour. The colour 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. Lets 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.

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.

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.

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 is 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. Step 6 - Named Ranges

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).

For more information on named ranges, please refer to the Named Ranges section. 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".

Absolute Addressing ( =$A$1 ) - Absolute column reference and Absolute row reference. For example if the formula "=$B$2" was entered into cell C3 and then cell C3 was copied to cell E5, the formula would not change and would remain "=$B$2".

It is also possible to have "Mixed References".

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 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 infront 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.

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 to 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 Notatio n? 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.

Step 2 - What is R1C1 Notation ? This is another way to create cell references which uses numbers for both the rows and columns. Cell References are displayed in terms of their relationship to the cell that contains the formula rather than 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.

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) dialog box. Select the "General tab" and select the "R1C1 Reference Style" checkbox.

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.

Absolute References ( R2C2 ) are do not include square brackets around the numbers.

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 labelled numerically and the references are enclose 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) dialog box

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) dialog 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.

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 check box. The "Recalculate Before Save" check box controls if the formulas are recalculated before the workbook is closed. This is often a good idea, as it prevents a user opening the workbook to display data that is not up-todate. 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.

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 tag will be displayed.

Automatically Extend Formulas

Things to Remember

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.

Step 2 - Different Types of Errors The following errors can be returned from your formulas. Even formula that have 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 be generated. ######

This is displayed when a column is not wide enough to display the result. This is not technically an error. Using a negative date or time. A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date or time formula that produces a negative result. Try increasing the width of the column.

#DIV/0!

Dividing a number by zero. Dividing a number by the contents of an empty cell. (xlErrDiv)

#N/A

Using VLOOKUP, HLOOKUP or MATCH functions when they do not return a match (or the list is not sorted). Using a custom worksheet function that is not available. Using a worksheet function without submitting all the required parameters. You have used the NA() worksheet function. (xlErrNA)

#NAME?

Referring to a named range that does not exist. Using a worksheet function that does not exist (or has been spelt incorrectly). Using labels when labels are not allowed.

Entering text that is not enclosed in double speech marks ("some text"). Using an invalid cell range reference. Referring to another worksheet that does not exist. (xlErrName) #NULL!

Using an incorrect range separator. Finding the intersection of two cell ranges that do not intersect. (xlErrNull)

#NUM!

Passing the incorrect argument to a worksheet function. Using a function that iterates (e.g. IRR or RATE) and no result can be found. The number returned is too big or too small for Excel to recognise it. (xlErrNum)

#REF!

Referring to cells that do not contain any data. Referring to cells that may have been deleted. Using a Dynamic Data Exchange Link that is not available. (xlErrRef)

#VALUE!

The formula contains a parameter of the wrong datatype, i.e. the formula is expecting an integer but you have passed in some text. (xlErrValue)

Step 3 - Trying to Preventing Errors A common method used to try and eliminate errors from appears on your worksheet is to use the ISERROR() worksheet function as a wrapper.

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.

Smart Tag Options The exact options that are displayed on the smart tag will vary depending on the type of error.

Divide by Zero Error Invalid Name Error Help on this Error Ignore Error Edit in Formula Bar Error Checking Options Show Formula Auditing Toolbar These options are also sometimes displayed as well. Formula Omits Adjacent Cells Update Formula to Include Cells Please contact us if you have any useful formulas. To make a contribution please e-mail us. [email protected] 1) Insert random numbers between 1 and 25 A 1 15 =INT(RAND()*25)+1=15

B 6

=INT(RAND()*25)+1=6 2) Rounding numbers to the nearest fraction A

B

1 12.485

4.26

2 12.3

74.2

3 12.29

123.456

=ROUND(A1/0.2,0)*0.2=12.4 (rounded down) =ROUND(A2/0.2,0)*0.2=12.4 (rounded up) =ROUND(A3/0.2,0)*0.2=12.2 (rounded down) =ROUND(B1/0.5,0)*0.5=4.5 (rounded up) =ROUND(B2/50,0)*50=50 (rounded down) =ROUND(B3/25,0)*25=125 (rounded up) 3) Obtaining just the workbook name from a folder path and workbook name A 1 Book1.xls 2 C:\Temp\BetterSolutions.xls 3 C:\Temp\SubFolder\Another Folder\Workbook_Name.xls =MID(A1,FIND("#",SUBSTITUTE("\"&A1,"\","#",LEN(A1)LEN(SUBSTITUTE(A1,"\",""))+1)),LEN(A1))="Book1.xls" =MID(A2,FIND("#",SUBSTITUTE("\"&A2,"\","#",LEN(A2)LEN(SUBSTITUTE(A2,"\",""))+1)),LEN(A2))="BetterSolutions.xls" =MID(A3,FIND("#",SUBSTITUTE("\"&A3,"\","#",LEN(A3)LEN(SUBSTITUTE(A3,"\",""))+1)),LEN(A3))="Workbook_Name.xls" Step 1 What are Arrays in Formula s? For more details on array formulas please refer to the Array Formulas section. It is possible to use arrays in your formulas and the reason you may want to do this is because it lets you enter the values directly as opposed to having to use cells. When you use arrays in formulas you do not need to enter them using (Ctrl + Shift + Enter). To enter the formula press Enter. Step 2 - Examples of Array in Formulas Example 1 - The table below shows how you can use an array constant.

Exactly the same result can be obtained by placing the numbers into an array and passing the array (as a whole) to the worksheet function. Array Constants can contain numbers, logical values (i.e. True or False) or text. You can use different types of data in the same array constant. Example 2 - The table below shows more array constants.

Step 3 - Difference between Arrays in Formulas and Array Formulas Example 3 - The table below shows more array constants.

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 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 Formul a? 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".

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 ammendment to the formula at a later date. Conditiona 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 thorughout 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

Lets 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.

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 makes creating a summary worksheet very easy. Lets assume that each of the four worksheets contains the following table of data.

Inserting the Formula We are going to insert a 3D formula for each of the items we want to total. The first item in our summary table is the total for Region 1. Select cell "C2" and insert the SUM function as normal.

Select the "2005" worksheet tab with the mouse. Hold down the Shift key and select the "2002" worksheet tab with the mouse.

Select the cell you want to sum in this case cell "G3". Enter a close bracket ")" to complete the formula.

Press Enter to return to the Summary worksheet. Create the following table on the Summary worksheet.

Repeat the above steps for the other six totals to create your summary worksheet. Worksheet Functions It is important to realise that not all the functions will accept a 3D formula. The following functions can all be used in 3D formulas. AND

The logical AND for any number of arguments.

AVEDEV

The average deviation for a list of numbers.

AVERAGE

The arithmetic mean of a list or array of numbers.

AVERAGEA

The arithmetic mean of a list or array of numbers, including text and logical values.

COUNT

The number of cells with a numeric value in a list or cell range.

COUNTA

The number of non blank cells in a list or cell range.

DEVSQ

The sum of squares of deviations of data points from their sample mean.

LARGE

The Kth largest value in an array of numbers.

MAX

The largest value in a list or array of numbers.

MAXA

The largest value in a list or array of numbers, including text and logical values.

MEDIAN

The median of the numbers in a list or cell range.

MIN

The smallest number in a list or range.

MINA

The smallest number in a list or range, including text and logical values

OR

The logical OR for any number of arguments.

PERCENTILE

The Kth percentile of values in a range.

PRODUCT

The product of all the numbers in a list or cell range.

QUARTILE

The quartile of a data set.

RANK

The rank of a value in a range (in descending order).

SKEW

The number representing the skewness of a distribution.

SMALL

The Kth smallest value in an array of numbers.

STDEV

The standard deviation based on a sample.

STDEVA

The standard deviation based on a sample, including text and logical values.

STDEVP

The standard deviation based on an entire population.

STDEVPA

The standard deviation based on an entire population, including text and logical values.

SUM

The total value of the numbers in a list or cell range.

SUMSQ

The sum of the squares of all the values in a list or cell range.

TRIM

The text string with all spaces removed from the beginning and end.

VAR

The compound variance based upon the numerical values in the range.

VARA

The compound variance based upon the numerical values in the range.

VARP

The variance based on an entire population.

VARPA

The variance based on an entire population, including text and logical values.

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 check box. 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 s

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).

Formula Auto correct 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 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 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(TheL 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 • • • • • • • • • • • • • • • • • •

* ACCRINT - Accrued Interest * ACCRINTM - Accrued Interest at Maturity * DISC - Discount rate * DURATION - Annual Duration * INTRATE - Interest rate for a fully invested security * MDURATION - 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 • •

* DOLLARDE - 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.

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.

Now when you return to the spreadsheet, you should see the hyperlink.

If you click on the hyperlink, your active cell should become cell A5.

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 -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.

Now, go to the sheet that contains the hyperlinks that you wish to delete. Here is an example of a sheet with multiple hyperlink addresses:

Under the Tools menu, select Macro > Macros. Highlight the macro called "RemoveHyperlinks" and click on the Run button. Now your hyperlinks should be deleted as you can see by the example below:

If you need to remove hyperlinks from other sheets, just repeat the steps above.

Excel: View hyperlink result at 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:

The hyperlink is at the bottom of the viewing area in the spreadsheet. We want to see 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.

Now when you click on the hyperlink in cell B5, your spreadsheet should look as follows:

Excel: VLookup Function In Excel, the VLookup function searches for 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 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:

Based on the Excel spreadsheet above: =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.

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 (lets say 1-20). There is a master list in another column that may not include some of the column 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.

Based on the spreadsheet above: would =IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)),"No","Yes") return "No" would =IF(ISNA(VLOOKUP(A5,$D$2:$D$185,1,FALSE)),"No","Yes") 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 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).

If you wanted to return the quantity value for the second occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),2),2) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),2),2)}

If you wanted to return the quantity value for the third occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),3),2) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),3),2)}

If you wanted to return the bin # for the second occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),2),3) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),2),3)}

If you wanted to return the bin # for the third occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),3),3) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)ROW(A2)+1,ROW(C6)+1),3),3)} As you'll shortly see, Pivot Tables can become quite complex. So we'll take it easy and just design a simple one. But first, what is a Pivot table?

What is a Pivot table? A Pivot table is to a way to extract data from a long list of information, and present it in a readable form. Remember the data we had from the student scores spreadsheet? You could turn that into a pivot table, and then view only the Maths scores for each pupil. Or view just Paul's scores, and nobody else's. To get a clearer idea of just what a Pivot Table is, examine the one below. You'll be designing this very Pivot Table yourself shortly.

In this school, there is a test every month (it's a tough school!). The Pivot Table above shows the marks that Elisa got in January, February, and March. There were tests for only 6 subjects. Notice the black down-pointing arrows in the Pivot Table. On Row 1 we have Student Elisa. If the black arrow were clicked, a drop-down box would appear showing a list of the other students. We could click on a student and view the marks he or she achieved. Or we could select which subjects to view, or choose only one month. But Excel does most of the work for you, and puts in those drop-down boxes as part of the wizard. But you'll see how it works. Before you can construct a Pivot Table, you need some data in list form. We're going to construct our Pivot Table using only two students, Elisa and Mary. We'll use just three months worth of data, and six subjects. You'll be glad to know that this has already been done for you. So download the spreadsheet below: Download the Pivot Table data In the next part, you'll start work on constructing the Pivot Table itself.

Creating a Pivot Table in Excel The Pivot Table is constructed using a Wizard. To create yours, do the following: • • • •

Click inside cell A2 on the spreadsheet you downloaded above From Excel's menu bar, click on Data From the menu that drops down, click on PivotTable and PivotChart Report The Pivot Table wizard starts up

As the Wizard says, this is step one of three. There's nothing much for us to do in step one because the options we want are selected: "Microsoft Excel list or database" and "Pivot Table". With these options chosen, click the Next button at the bottom. The Wizard moves on to Step Two. It looks like this:

The Range of cells that Excel will include in our Pivot Table is A1 to D37. (You can change this if you wanted.) Because we clicked in cell A2 to begin with, Excel has taken that as the first Row of Data. Excel uses the labels from Row 1 as Headings. Excel will use these for our drop down boxes and data. Click the Next button on Step Two. Step Three of the Wizard appears. It's a little more complicated, this time.

We'll accept the default position for the location of the Pivot Table - New worksheet. The button we're after is Layout. So click the Layout button to see a quite complicated dialogue box. This one:

The Field Buttons the Wizard is talking about are those four on the right: Month, Subject, Student and Score. The idea is that you click on a button. Hold down your left mouse button and drag to an area on the left. We're going to drag one button to the Column area, one to the Row area, and one to the Data area.

In the Row area, we'll put Month; in the Column area, we'll put Subject, and in the Data area we'll put Score. We'll do something with the Student button after the Pivot Table has been constructed. So do the following: • • • • • •

Click on Month Hold down your left mouse button With the mouse button held down, drag the mouse pointer over to the Row area Let go of the button when it's there A button will appear in the Row area The images below show the process in action

Hold Down the left Mouse button and drag

Drag the mouse pointer over to the Row area

Let go of the left mouse button when the pointer is over Row

When you have the Month button in place, drag the Subject button to the Column area, and the Score button to the Data area. Your dialogue box will then look like this:

Click OK when your dialogue box looks like the one above. You will be taken back to Step Three of the Wizard. Click the Finish button and you're done. You'll then have a spreadsheet that looks like this one:

If you don't see the Pivot Table toolbar, click on View > Toolbars > Pivot Table. I'm sure you'll agree - our Pivot Table is coming along nicely. In the next part, you'll learn how to manually add a button to a Pivot Table. We're now going to put the Students button on the Pivot Table. So do the following: •

Locate the Student button on the Pivot Table toolbar, as in the image below:

• • •

Hold down your left mouse button on the Student button Keep the left mouse button held down Drag the Student button to the top of the Pivot table, where it says "Drag Page Fields Here." Let go of the left mouse button Excel adds the Student field to the pivot table The two images below show the process in action

• • •

Drag the Student button to the top of the Pivot table

Release the left mouse button and Excel adds the Student Field

We're almost there, now. Only a couple more things left to do. First, take a look at the scores. What the Pivot table is doing is adding all the scores up. That's because of cell A3. Notice that it says "Sum of Score". We don't want it to do that. An Average is much better for our purposes. To change the Scores to Averages, do this: • •

On the Pivot Table toolbar, click on the Pivot Table button A menu pops up like the one below:

• •

To change the Scores to Averages, click on Field Settings The following dialogue box appears:

There are not too many functions to choose from in the Summarize by list, but Average is on there. So click on Average, and then click OK. The scores will change on the spreadsheet. Some of the scores in the Grand Total Row and Grand Total column will be a bit long. But you can format the numbers to in the usual way. • • • • • • •

So highlight the Grand Total row From the Excel Menu Bar, click on Format From the drop down menu, click on Cells When the dialogue box appears, select the Number tab strip Format to 1 decimal place Do the same for the Grand Total row Your Pivot Table should now be looking like the one below:

We can now take a look at those drop down boxes. We'll start with the Student box. At the moment, the Student box says All. Click the black down arrow to see the list of students.

Our two Students are listed there. Click on Elisa, then click the OK button. Notice how your spreadsheet has changed. It should now only be showing you Elisa's results. Click the black down arrow in cell B1 again, and click on Mary. Then click the OK button. Your spreadsheet will change to show only Mary's results. Try clicking the black down arrow of Subject, in cell B3. You should see this:

All the Subjects have ticks in them. Click on a tick and it will disappear. Try un-ticking a few of the subjects. Then click the OK button to see what happens. The Month list in cell A4 shows a similar list with ticks in them. Un-tick a month and see the results when you click OK. You can add comments to pivot tables. The one below shows a Comment about Elisa's English scores:

Another thing you can do is change the type of Pivot Table Report. From the Pivot Table toolbar, click the Pivot Table button. From the menu that pops up, select Format Report. Click on any of the formats you like then click OK to see what happens. If you don't like what you see, click Edit > Undo AutoFormat to get back to your Pivot Table.

And that wraps up this short introduction on Pivot Tables. But they are a good way to summarise long lists of data. In the next section, we take a look at Excel Forms, and see how to download data from a web page straight into Excel.

Excel Topics: Pivot Tables Excel: Create a pivot table Question: How do I create a pivot table in Excel?

Answer: We'll start by creating a very simple pivot table. The example that follows has been done in Excel 2000, so the screen may look different if you are using a different version of Excel. Either way, it will give you a basic understanding of pivot tables. First, our data that we want to use to populate the pivot table resides on Sheet1.

Under the Data menu, select "PivotTable and PivotChart Report".

A PivotTable wizard should appear. Make sure that the "Microsoft Excel list or database" and "PivotTable" options are chosen. Click on the Next button.

Select the range of data for the pivot table and click on the Next button. In this example, we've chosen data from Sheet1.

Select the position to create the new pivot table. It will automatically default to the cell that was highlighted when you started this process. In this example, we want to create our pivot table on Sheet2 in cell A1. Click on the Layout button.

Now drag the fields that you want to appear in the Page, Row, Column, and Data sections of the pivot table. In this example, we've dragged the Order ID field to the Row section and the Quantity to the Data section. Click on the OK button to continue.

Now click on the Finish button.

Your pivot table should now appear on Sheet2. What this pivot table displays is the total quantity for each order ID.

Excel: Refresh a pivot table Question: How do I refresh a pivot table?

Answer: To refresh a pivot table in Excel, select a cell in the pivot table. Right-click and then select "Refresh Data" from the popup menu.

Excel: Automatically refresh pivot table when file is opened Question: How do I get a pivot table to automatically refresh when the Excel spreadsheet is opened?

Answer: Select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable Options window appears, check the checkbox called "Refresh on open". Click on the OK button.

Excel: Refresh multiple pivot tables with a button Question: Is it possible to create a button in Excel that will refresh/update multiple pivot tables?

Answer: Yes, you can refresh multiple pivot tables with a button. To do this: Under the View menu, select Toolbars > Forms.

Create a button in your spreadsheet using the Forms toolbar. To do this, click on the button icon (currently highlighted in picture below) and click on your spreadsheet where you would like the button to appear.

After creating the button, the Assign Macro window should appear. Click on the "New" button.

Then paste in similar code as below in the Button_Click event: Sheets("Sheet1").Select ActiveSheet.PivotTables("PivotTable1").RefreshTable Sheets("Sheet2").Select ActiveSheet.PivotTables("PivotTable2").RefreshTable

You will need to replace the Sheet1 and Sheet2 with the names of your sheets and PivotTable1 and PivotTable2 with the names of your pivot tables.

To find out the name of a pivot table, right-click on the pivot table and select Table Options.

Excel: Automatically refresh pivot table when data in a sheet changes Question: I'm looking for a macro that would automatically refresh a pivot table whenever data is changed in an Excel worksheet. Is this possible?

Answer: There are several "events" available within an Excel spreadsheet where you can place VBA code. In your case, we want to refresh the pivot table when the "Worksheet_Calculate" event fires. Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, there are two sheets - one is called Data which contains the source data for the pivot table. Another sheet is called Pivot which contains the pivot table. On the sheet called Data, we've placed code on the "Worksheet_Calculate" event, so that whenever the data changes on the "Data" sheet, the pivot table will be refreshed. You can press Alt-F11 to view the VBA code. Note: This only will work, if you have the spreadsheet set to calculate "automatically". This is the default for most Excel spreadsheets, but some people turn this feature off.

Macro Code: The macro code looks like this:

Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Pivot").PivotTables("PivotTable1").RefreshTable End Sub

Excel: Remove grand totals for columns Question: On a pivot table, how do I remove grand totals for columns?

Answer: Below is a pivot table with a grand total for the column called OrderID.

To remove this column grand total, select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable Options window appears, uncheck the checkbox called "Grand totals for columns". Click the OK button.

Now when you return to the spreadsheet, the grand total for the OrderID column will no longer be visible.

Excel: Remove grand totals for rows Question: On a pivot table, how do I remove the grand totals for rows?

Answer: Below is a pivot table with a grand total for the row called Product.

To remove this row grand total, select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable Options window appears, uncheck the checkbox called "Grand totals for rows". Click the OK button.

Now when you return to the spreadsheet, the grand total for the Product row will no longer be visible.

Excel: Change pivot table name Question: How do I change the name associated with a pivot table?

Answer: Select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable Options window appears, enter the new name for the pivot table in the Name field. Click the OK button. In this example, we've renamed our pivot table to ExamplePT.

Excel: Do not save data with table layout Question: After I created a pivot table, the size of the Excel spreadsheet became quite large. This was due to the fact that the data behind the pivot table was saved with the table layout. How do I prevent Excel from saving the data with the table layout?

Answer: Select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable Options window appears, uncheck the checkbox called "Save data with table layout". Click on the OK button.

Excel: Display pivot table wizard Question: Once I've created a pivot table, how do I get back to the Pivot Table Wizard?

Answer: To return to the Pivot Table Wizard, select any cell in the pivot table.

Right-click and then select "Wizard" from the popup menu.

You should now see the Pivot Table Wizard.

Excel: Remove subtotals on pivot table rows Question: How do I remove subtotals on a pivot table row?

Answer: Select the row heading that you wish to remove subtotals on. Right-click and then select "Field Settings" from the popup menu. In this example, we've chosen the row heading called Order ID.

When the PivotTable Field window appears, select the "None" subtotals option. Click on the OK button.

Now when you return to the spreadsheet, the subtotals for each OrderID are no longer visible.

Excel: Remove subtotals on pivot table columns Question: How do I remove subtotals on a pivot table column?

Answer: Select the column heading that you wish to remove subtotals on. In this example, we've chosen the column heading called Discount.

Right-click and then select "Field Settings" from the popup menu.

Under subtotals, select the "None" option. Click on the OK button.

Now when you return to the spreadsheet, the subtotals for each Discount are no longer visible.

Excel: Show Top 10 results Question: How do I show only the top 10 results in a pivot table?

Answer: Select a row heading in the pivot table. In this example, we've chosen the row heading called Order ID.

Right-click and then select "Field Settings" from the popup menu.

When the PivotTable Field window appears, click on the Advanced button.

Under the AutoShow options, select Automatic. Select Top and the number of items that you wish to view. Now click on the OK button. In this example, we've chosen the Top 10 values based on the "Sum of Quantity" field.

This will return you to the PivotTable Field window. Click on the OK button.

Now when you view your spreadsheet, you should only see the top 10 values based on quantity.

Excel: Show Bottom 10 results Question: How do I show only the bottom 10 results in a pivot table?

Answer: Select the row heading in the pivot table. In this example, we are selecting the Order ID heading.

Right-click and then select "Field Settings" from the popup menu.

When the PivotTable Field window appears, click on the Advanced button.

Under the AutoShow options, select Automatic. Select Bottom and the number of items that you wish to view. Now click on the OK button. In this example, we've chosen the Bottom 10 values based on the "Sum of Quantity" field.

This will return you to the PivotTable Field window. Click on the OK button.

Now when you view your spreadsheet, you should only see the bottom 10 values based on quantity.

Excel: Change how errors are displayed Question: I don't want to see errors in the pivot table. How do I replace all errors with another value?

Answer: Let's first take a look at an example of an error in a pivot table. This is a picture of our underlying data for the pivot table. You can see in row 16 that the quantity entry has an error in it. This could be the result of a vlookup function, for example.

Now when you take a look at the pivot table that uses this data, you can see the error in the pivot table.

You can replace this error with a more appropriate value. To do this, select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable window appears, check the checkbox called "For error values, show". Then enter the value that you wish to see in the pivot table instead of the error. Click on the OK button. In this example, we've decided to replace the error with a 0.

Now when we return to the pivot table, this is what we'll see.

Excel: Change how empty cells are displayed Question: I don't want to see empty cells in a pivot table. How do I replace all empty cells with another value?

Answer: First, let's explain what an empty cell is. In the example below, we have order # 10253 that does not have any quantity (row 10 in spreadsheet). Therefore, the quantity is showing as a blank cell.

We'd like to replace all empty cells with the value "n/a". To do this, select any cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable window appears, check the checkbox called "For empty cells, show". Then enter the value that you wish to see in the pivot table instead of the empty cell. Click on the OK button. In this example, we want all blank cells to show as "n/a".

Now when we return to the pivot table, we see "n/a" as the quantity value for order #10253.

Excel: Display the fields in the Data Section in multiple columns Question: I've created a pivot table in Excel with two fields in the Data Section of the pivot table. These fields are the sum of the Quantity as well as the sum of the Total cost of an order. The pivot table seems to be populating the Data Section in a single column and I want to see the results in two columns. How can I set up my pivot table so that the Quantity and Total cost (in the data section) values show up in two columns instead of being listed in one?

Answer: By default, Excel will probably format your data section of your pivot table to populate all values in one column, as seen below:

In this example, you can see that the "Sum of Quantity" as well as "Sum of Total Cost" fields are being displayed in one column. To change the pivot table so that each field is in its own column, first highlight the label called "Data".

Next, while your mouse cursor is over the Data label, click and hold down the Left Mouse Button. Slowly drag your mouse cursor to the right until you see the mouse cursor change to a picture similar to the above. This is usually when your cursor is over the label called Total. Now, let go of the Left Mouse Button.

Now your pivot table should display the "Sum of Quantity" and "Sum of Total Cost" fields in their own columns.

Excel: Display the fields in the Data Section in a single column Question: I've created a pivot table in Excel with two fields in the Data Section of the pivot table. These fields are the sum of the Quantity as well as the sum of the Total cost of an order. The pivot table seems to be populating the data section in multiple columns and I want to see the results in a single column. How can I set up my pivot table so that the Quantity and Total cost (in the data section) values show up in a single column instead of being listed in two?

Answer: Excel may format your data section of your pivot table to populate your values in multiple columns, as seen below:

In this example, you can see that the "Sum of Quantity" as well as "Sum of Total Cost" fields are being displayed in multiple columns. To change the pivot table so that the fields are populated in a single column, first highlight the label called "Data".

Next, while your mouse cursor is over the Data label, click and hold down the Left Mouse Button. Slowly drag your mouse cursor to the left until you see the mouse cursor change to a picture similar to the above. This is usually when your cursor is next to your Row Section (ie: Order ID field). Now, let go of the Left Mouse Button.

Now your pivot table should display the "Sum of Quantity" and "Sum of Total Cost" fields in a single column, populating down.

Excel: Hide a value Question: How do I hide a value in a pivot table?

Answer: Select the value that you wish to hide. In this example, we are going to order Order #10249.

Right-click and the select "Hide" from the popup menu.

The Order #10249 is now hidden.

Excel: Display a hidden value Question: How do I display a hidden value in a pivot table in Excel?

Answer: To explain how to display a hidden value in an Excel pivot table, we'll take a look at an example. In this case, the entry for Order ID 10249 is hidden.

Click on the arrow to the right of the field that has the hidden value. In this example, the field that has the hidden value is called Order ID, so we'll click on the arrow to the right of the Order ID field.

Check the box to the left of the value that you want to display. Click on the OK button. In this example, we've checked the box for Order ID 10249. Just to summarize, all checked values are visible in the pivot table and all unchecked values are hidden in the pivot table.

Now when we return to the pivot table, we can see the details for Order ID 10249.

Excel: Hide empty cells in a pivot table Question: How do I hide empty cells in an Excel pivot table?

Answer: To hide empty cells in a pivot table, select the empty cell in the pivot table.

Right-click and select Hide from the popup menu.

Now whenever the OrderID is blank, the data will be hidden in the pivot table.

Excel: Hide zero value lines within a pivot table Question: I wonder if you can help, is there a way in which you are able to hide zero valued lines within a pivot table ? i.e. I have a table with two columns branch name and stock value and I would like to hide the branches with no value against them - is this possible ? Your help on this would be much appreciated.

Answer: Let's take a look at an example. Below is an Excel spreadsheet that contains values that are zeros. We want to hide these lines from being displayed in the pivot table.

Right-click in the Pivot table and select Wizard from the popup menu.

When the PivotTable Wizard appears, click on the Layout button.

Drag the STOCK VALUE field to the PAGE section. Click on the OK button.

Click on the Finish button to return to the spreadsheet.

Double-click on the Stock Value field in the Page section.

Highlight the 0 in the Hide Items window. Click on the OK button.

Now when you return to the spreadsheet, the blank/zero lines should be hidden.

Excel: Sort pivot table results Question: How do I sort the pivot table results?

Answer: Select the row heading that you wish to sort on. Right-click and then select "Field Settings" from the popup menu. In this example, we've selected the row heading called Order ID.

When the PivotTable Field window appears, click on the Advanced button.

Under the AutoSort options, select either Ascending or Descending depending on the sort order that you wish to select. Click on the OK button. In this case, we've chosen to sort the Order ID field in ascending order.

This will return you to the PivotTable Field window. Click on the OK button.

Excel: GetPivotData Example #1 The GetPivotData function returns data from a pivot table. It can retrieve summary data from a pivot table as long as the summary data is visible. The syntax for the GetPivotData function is:

GetPivotData( pivot_table, name ) pivot_table is generally a named range that has been set up to point to the pivot table. name is the name of the summary value that you wish to retrieve.

For Example: Let's take a look at an example. Below we have an Excel spreadsheet that has a pivot table on Sheet2. We've set up a named range called PivotTable that references this pivot table (Sheet2!$A:$E).

=GetPivotData(PivotTable,"10249 Qty")

would return 49

=GetPivotData(PivotTable,"10249 Price/unit")

would return 61

=GetPivotData(PivotTable,"10249 Total_Cost")

would return 1863.4

=GetPivotData(PivotTable,"10249 Tofu Total_Cost")

would return 167.4

Excel: GetPivotData Example #2 The GetPivotData function returns data from a pivot table. It can retrieve summary data from a pivot table as long as the summary data is visible. The syntax for the GetPivotData function is: GetPivotData( pivot_table, name ) pivot_table is generally a named range that has been set up to point to the pivot table. name is the name of the summary value that you wish to retrieve.

For Example: Let's take a look at an example. Below we have an Excel spreadsheet that has a pivot table on the sheet called "Pivot Table". We've set up a named range called team_count that references this pivot table ('Pivot Table'!$A:$C).

=GetPivotData (team_count,"'Team 1' 'Count of B'")

would return 1

=GetPivotData (team_count,"'Team 2' 'Count of B'")

would return 3

=GetPivotData (team_count,"'Team 2' 'Count of C'")

would return 12

Working with Pivot Tables Contents PivotTables Share PivotTables but Not Their Data Automate PivotTable Creation Move PivotTable Grand Totals Efficiently Pivot Another Workbook's Data

PivotTables allow you to pivot data using drag-and-drop techniques and receive results immediately. PivotTables are interactive; once the table is complete, you very easily can see how your information will be affected when you move (or pivot) your data. This will become patently clear once you give PivotTables a try. Even for experienced PivotTable developers, an element of trial and error is always involved in producing desired results. You will find yourself pivoting your table a lot!

PivotTables PivotTables are one of the wildest but most powerful features of Excel that may take some experimentation to figure out. We use PivotTables a lot when we develop spreadsheets for our clients. Once a client sees a PivotTable, they nearly always ask whether they can create one themselves. Although anyone can create a PivotTable, unfortunately many people tend to shy away from them, as they see them as too complex. Indeed, when you first use a PivotTable, the process can seem a bit daunting. Some persistence is definitely necessary. You'll find that persistence will pay off once you experience the best feature of PivotTables: their ability to be manipulated using trial and error and immediately show the result of this manipulation. If the result is not what you expect, you can use Excel's Undo feature and have another go! Whatever you do, you are not changing the structure of your original table in any way, so you can do no harm. Why Are They Called PivotTables? PivotTables allow you to pivot data using drag-and-drop techniques and receive results immediately. PivotTables are interactive; once the table is complete, you very easily can see how your information will be affected when you move (or pivot) your data. This will become patently clear once you give PivotTables a try. Even for experienced PivotTable developers, an element of trial and error is always involved in producing desired results. You will find yourself pivoting your table a lot!

What Are PivotTables Good For? PivotTables can produce summary information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, and Zip Codes. With a PivotTable, you very easily and quickly can find out: • • •

How many people have the same name How many people share the same Zip Code How many people have the same occupation

You also can receive such information as: • •

A list of people with the same occupation A list of addresses with the same Zip Code

If your data needs slicing, dicing, and reporting, PivotTables will be a critical part of your toolkit. Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability? Perhaps the biggest advantage to using PivotTables is the fact that you can generate and extract meaningful information from a large table of data within a matter of minutes and without using up a lot of computer memory. In many cases, you could get the same results from a table of data by using Excel's built-in functions, but that would take more time and use far more memory. Another advantage to using PivotTables is that if you want some new information, you can simply drag-and-drop (pivot). In addition, you can opt to have your information update each time you open the workbook or click Refresh. PivotCharts Extend PivotTables Microsoft introduced PivotCharts in Excel 2000. The table you create via the PivotTable Wizard produces a PivotChart (or, more accurately, a PivotTable and PivotChart Report). When you create a PivotTable, you also can create a PivotChart at the same time, with no extra effort. PivotCharts enable you to create interactive charts that previously were impossible without using either VBA or Excel Controls. The PivotTable Wizard is discussed in more detail later in this tutorial. PivotCharts are not available in Excel for the Macintosh.

Creating Tables and Lists for Use in PivotTables When you create a PivotTable, you must organize the dataset you're using in a table and/or a list. As the PivotTable will base all its data on this table or list, it is vital that you set up your tables and lists in a uniform way. In this context, a table is no more than a list that has a title, has more than one column of data, and has a different heading for each column. A list often is referred to in the context of a table as well. The best practices that apply to setting up a list will help you greatly when you need to apply a PivotTable to your data. When you extract data via the use of lookup or database functions, you can be a little less stringent in how you set up the table or list. This is because you can always compensate with the aid of a function and probably still get your result. Nonetheless, it's still easiest to set up the list or table as neatly as possible. Excel's built-in features assume a lot about the layout and setup up of your data. Although they offer a degree of flexibility, more often than not you will find it easier to adhere to the following guidelines when setting up your table or list: •

• •

• •

Headings are required, as a PivotTable uses them for field names. Headings should always appear in the row directly above the data. Also, never leave a blank row between the data and the headings. Furthermore, make the headings distinct in some way; for instance, boldface them. Leave at least three blank rows above the headings. You can use these for formulas, critical data, etc. You can hide the rows if you want. If you have more than one list or table on the same worksheet, leave at least one blank column between each list or table. This will help Excel recognize them as separate entities. However, if the lists and tables are related to each other, combine them into one large table. Avoid blank cells within your data. Instead of leaving blank cells for the same data in a column, repeat the data as many times as needed. Sort your list or data, preferably by the leftmost column. This will make the data easier to read and interpret.

If you follow these guidelines as closely as possible, using PivotTables will be a relatively easy task. Figure 4-1 shows a well-laid out table of data, and a PivotTable in progress. Note that many of the same dates are repeated in the Date column. In front of this data is the Layout step for the data showing the optional Page, Row, and Column fields, as well as the mandatory Data field. Figure 4-1. PivotTable generated from a well-laid out table of data

The PivotTable and PivotChart Wizard As noted earlier, to help users create PivotTables, Excel offers a PivotTable and PivotChart Wizard. This Wizard guides you through the creation of a PivotTable using a four-step process, in which you tell Excel the following: • • • •

How the data is set up and whether to create an associated PivotChart (if PivotCharts are available in that version of Excel) Where the data is stored-e.g., a range in the same workbook, a database, another workbook, etc. Which column of data is going into which field: the optional Page, Row, and Column fields, as well as the mandatory Data field Where to put your PivotTable (i.e., in a new worksheet or in an existing one)

You also can take many side steps along the way to manipulate the PivotTable, but most users find it easier to do this after telling Excel where to put it. Excel 2000 and later versions have a major advantage over Excel 97: they enable you to choose how to set up your data after the Wizard is finished.

Now that you know more about PivotTables and what they do, it's time to explore some handy techniques that can make this feature even more powerful.

Share PivotTables but Not Their Data Create a snapshot of your PivotTable that no longer needs the underlying data structures.

You might need to send PivotTables for others to view, but for whatever reason you cannot send the underlying data associated with them. Perhaps you want others to see only certain data for confidentiality reasons, for instance. If this is the case, you can create a static copy of the PivotTable and enable the recipient to see only what he needs to see. Best of all, the file size of the static copy will be only a small percentage of the original file size. Assuming you have a PivotTable in a workbook, all you need to do is select the entire PivotTable, copy it, and on a clean sheet select Edit Paste Special... Values. Now you can move this worksheet to another workbook or perhaps use it as is. The one drawback to this method is that Excel does not paste the PivotTable's formats along with the values. This can make the static copy harder to read and perhaps less impressive. If you want to include the formatting as well, you can take a static picture (as opposed to a static copy) of your PivotTable and paste this onto a clean worksheet. This will give you a full-color, formatted snapshot of the original PivotTable to which you can apply any type of formatting you want, without having to worry about the formatting being lost when you refresh the original PivotTable. This is because the full-color, formatted snapshot is not linked in any way to the original PivotTable. To create a static picture, format the PivotTable the way you want it and then select any Select Entire Table. cell within it. From the PivotTable toolbar, select PivotTable With the entire PivotTable selected, hold down the Shift key and select Edit Copy Picture. From the Copy Picture dialog box that pops up, make the selections shown in Figure 4-2, then click OK. Figure 4-2. Copy Picture dialog in action

Finally, click anywhere outside the PivotTable and select Edit Paste. You will end up with a fully colored and formatted snapshot of your PivotTable, as shown in Figure 4-3, complete with formatting. This can be very handy, especially if you have to email your PivotTable to other people for viewing. They will have the information they need, including all relevant formatting, but the file size will be small and they won't be able to manipulate your data. Also, they will be able to see only what you want them to see. Figure 4-3. Original PivotTable contrasted with a picture of the PivotTable

You also can use this picture-taking method on a range of cells. You can follow the preceding steps, or you can use the little-noticed Camera tool on your toolbar. To use this latter method, select View Toolbars Customize.... From the Customize dialog, click the Commands tab, from the Categories box, select Tools, and from the Commands box on the righthand side scroll down until you see Camera. Leftclick and drag-and-drop this icon onto your toolbar where you want it to be displayed. Select a range of cells, click the Camera icon, and then click anywhere on the spreadsheet, and you will have a linked picture of the range you just took a picture of. Whatever data or formatting you applied to the original range will automatically be reflected in the picture of the range.

Automate PivotTable Creation The steps you need to follow to create a PivotTable require some effort, and that effort often is redundant. With a small bit of VBA, you can create simple PivotTables automatically.

PivotTables are a very clever and potent feature to use on data that is stored in either a list or a table. Unfortunately, the mere thought of creating a PivotTable is enough to prevent some people from even experimenting with them. Although some PivotTable setups can get very complicated, you can create most PivotTables easily and quickly. Two of the most commonly asked questions in Excel concern how to get a count of all items in a list, and how to create a list of unique items from a list that contains many duplicates. In this section, we'll show you how to create a PivotTable quickly and easily that accomplishes these tasks. Assume you have a long list of names in column A, with cell A1 as your heading, and you want to know how many items are on the list, as well as generate a list of unique items. Select cell A1 (your heading) and then select Data PivotTable and PivotChart Report (or Data PivotTable Report on Macs) to start the PivotTable Wizard. Make sure that either Microsoft Excel List or Database is selected, or that you have selected a single cell within your data. This will allow Excel to automatically detect the underlying data it is to use next. If you're using a Windows PC, select PivotTable under "What kind of report do you want to create?" (This question isn't asked on Macintoshes.) Click the Next button. The PivotTable Wizard should automatically have picked up the correct range for your data in column A and will highlight it in your sheet. If it is highlighted, click the Next button. Otherwise, use your mouse to select the range. Click the Layout button and drag to the Data area what will be your only field-you should see your title as it appears in cell A1 floating about. Drag the field again, this time into the Row area. Your screen should look something like Figure 4-4. Click OK. At this stage, if you want you can double-click the Field button in the Data area (this is labeled Count of Names in Figure 4-3) and change the Summarize by: option to a function of your choice-e.g., Sum, Average, etc. Excel will by default use the COUNT function if it's working with text and use the SUM function if it's working with numbers.

Figure 4-4. PivotTable Field and PivotTable Layout dialogs

Finally, select New Worksheet as the destination of your PivotTable Report and click the Finish button. You should see your PivotTable on a new worksheet containing the unique items from your list along with a count of how many times each item (name) appears in your list. What if you want to have a macro perform all those steps for you, creating a PivotTable from any column you feed it? If you simply record a macro, you'll find it often works only if your data has the same heading. To avoid this, you can create a simple macro stored in your workbook or in your personal macro workbook (described in Tutorial 7) that you can use to create a PivotTable on any list of items. This requires that you write some generic VBA code and enter it into a standard module in your personal macro workbook or in any other workbook. To start, select Tools Macro Visual Basic Editor (Alt/Option-F11) and then select Insert Module. Enter the following code: Sub GetCount( ) Dim Pt As PivotTable Dim strField As String

strField = Selection.Cells(1,1).Text Range(Selection, Selection.End(xlDown)).Name = "Items"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:="=Items").CreatePivotTable TableDestination:="", _ TableName:="ItemList"

Set Pt = ActiveSheet.PivotTables("ItemList") ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1) Pt.AddFields RowFields:=strField Pt.PivotFields(strField).Orientation = xlDataField

End Sub

To return to Excel, close the Script window, or press Alt/

-Q, and save your workbook.

Before running this code, select the heading of your list and ensure that your list contains no blank cells. Sorting your list will remove blank cells quickly.

The code will automatically create a named range of your list, called Items. It will then create the PivotTable based on this named range on a new worksheet. The next time you have a long list of data, you can simply select its heading and run this macro. All the PivotTable setup work will be done in the blink of an eye.

Move PivotTable Grand Totals One of the most annoying things about PivotTables is that the Grand Total that summarizes your data always ends up at the bottom of the table, meaning you have to scroll down just to see the figures. Move your Grand Total up to the top where it's easier to find. Although PivotTables are a great way to summarize data and extract meaningful information, there is no built-in option to have the Grand Total float to the top for a quick bird's-eye view. Before we describe a very generic method to move the Grand Total to the top, we'll explain how you can accomplish this with the GETPIVOTDATA function, which is designed specifically to extract data from a PivotTable.

You can use the function like this: =GETPIVOTDATA("Sum of Amount",$B$5)

or like this: =GETPIVOTDATA("Amount",$B$5)

Either function will extract the data and will track the Grand Total as it moves up, down, left, or right. We used the cell address $B$5, but as long as you use any cell within the PivotTable, you always will pick up the total. The first function uses the Sum of Amount field, while the second one uses the Amount field. If your PivotTable has the Amount field in the Data area, you need to name the field Amount. If, however, the Amount field is being used two or more times in the Data area, you must specify the name you gave it, or the name you accepted by default (see Figure 4-5). Figure 4-5. The Amount field used twice and named Sum of Amount in one case and Number Sold in the other

You can double-click these fields to change them. This issue can become confusing if you are not up to speed with PivotTables. Luckily in Excel 2002 and later, the process is much easier, as you can have a cell fill in the arguments and give the correct syntax by using the mouse pointer. In any cell, type = (an equals sign) and then use your mouse pointer to click in the cell currently housing the Grand Total. Excel will automatically fill in the arguments for you.

Unfortunately, if you use the Function Wizard, or first type =GETPIVOTDATA( ) and then click in the cell currently housing the Grand Total, Excel makes a mess by trying to nest another GETPIVOTDATA function within that cell.

Probably the easiest, if least sophisticated, way to extract the Grand Total is to use the following function: =MAX(PivGTCol)

where the column currently housing the Grand Total is named PivGTCol. You also can use the LARGE and SMALL functions to extract from a PivotTable a host of figures according to their size. The following formula, for instance, extracts the second largest figure from a PivotTable: =LARGE(PivGTCol,2)

You can add some extra rows immediately above the start of the PivotTable and place these formulas there so that you can see this type of information instantly, without having to scroll to the bottom of your PivotTable.

Efficiently Pivot Another Workbook's Data Use data residing in another workbook as the source for your PivotTable. When creating a PivotTable in Excel, you have lots of options for your data source. By far the easiest and most powerful approach is to use data that resides within the same workbook. Unfortunately, for whatever reason, this is not always possible or feasible. Perhaps the data that resides in another workbook is entered daily, for instance, and the users entering the data should not see the PivotTable. Using a dynamic named range will greatly decrease the refresh time needed for your PivotTable to update. As you cannot reference a dynamic named range from another workbook, this also means you prevented the PivotTable from referencing perhaps thousands of blank rows and causing the file size to increase substantially. This way, you can pull in data from another workbook, and then base your PivotTable on the data in the same workbook rather than referencing it externally. In the workbook that will contain your PivotTable, insert a new worksheet and call it Data. Open the workbook containing the data to be referenced, and ensure that the

worksheet containing the data is the active sheet. In any spare cell on this worksheet, enter this formula: =IF(A1="","",A1)

where A1 is the very first heading of your data table. Select cell A1. Then cut it, activate your original workbook, and paste cell A1 in cell A1 on the Data sheet. This will give you the reference to the other workbook. Copy this cell across as many columns as there are headings in your data source. Then select Insert Name Define, and in the Names in Workbook: field, type PivotData. In the Refers to: box, type the following: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Click Add, then click OK. Next, to insert some code that will run each time the workbook is opened, right-click the Excel icon (located at the top left corner of the screen) and enter the following code: Private Sub Workbook_Open( ) With Worksheets("Data") .Range("2:1000").Clear .Range("1:1").AutoFill .Range("1:1000") .Range("2:1000") = .Range("2:1000").Value End With End Sub

This shortcut isn't available on a Mac. You'll have to open the VBE by pressing Option-F11, or by selecting Tools Macro Visual Basic Editor. Then Ctrl-click This Workbook in the Projects window.

To return to Excel, close the script window or press Alt/

-Q.

The preceding code includes only 1,000 rows of data. This figure should always be greater than the number of rows you believe you will need. In other words, if your table in the other workbook contains 500 rows, add a few hundred more to accommodate any growth in the original table.

Avoid using an extremely high row number (like 10,000, unless you actually have that much data), as this will greatly impact how quickly the code runs and the data updates.

Save the workbook, close it, and then reopen it, making certain that you enabled macros. The code you added will fire automatically and will copy the formulas in row 1 on the Data sheet, then automatically convert all but row 1 into values only. This will leave you with a copy of your original data source, which will update each time you open the workbook. Now you can hide this sheet if you want by selecting Format using the method described in [Example #5].

Sheet

Hide or by

Now, to base a PivotTable on this dynamic named range, select anywhere within the PivotTable, then select the Wizard option from the PivotTable toolbar. Click the Back button until you reach Step 1 of the Wizard. Select the first option, Microsoft Excel List or Database, click Next, and in Step 2, type =PivotData (the name of the dynamic named range). Then click Finish. You will not experience the lag that often occurs when a PivotTable is referencing an external data source because now the data itself is stored within the same workbook. As an added bonus, because you can use a dynamic named range, the PivotTable is dynamic without having to reference heaps of blank rows, and the file is kept to a manageable size.

Finance Functions

Introduction

Microsoft Excel provides a series of functions destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated. Many of these functions deal with investments or loan financing. The Present Value is the current value of an investment or a loan. For a savings a customer could pledge to make a set amount of deposit on a bank account every month. The initial value that the customer deposits or has in the account is the Present Value. The sign of the variable, when passed to a function, depends on the position of the customer. If the customer is making deposits, this value must be negative. If the customer is receiving money (lottery installment, family inheritance, etc), this value should be positive. account,

The Future Value is the value the loan or investment will have when the loan is paid off or when the investment is over. For a car loan, a musical instrument loan, a financed refrigerator, a boat, etc, this is usually 0 because the company that is lending the money will not take that item back (they didn't give it to the customer in the first place, they only lend him or her some money to buy the item). This means that at the end of the loan, the item (such as a car, boat, guitar, etc) belongs to the customer and it is most likely still worth something. As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value. The Number Of Periods is the number of payments that make up a full cycle of a loan or an investment. The Interest Rate is a fixed percent value applied during the life of the loan or the investment. The rate does not change during the length of the Periods. It is very important to understand how these two arguments are passed to a function. The period could be the number of months of a year, which is 12; but it could be another

length. Suppose a customer is getting a car loan that would be financed in 5 years. This is equivalent to 5 * 12 = 60 months. In the same way, a cash loan can stretch from 0 to 18 months, a carpenter truck loan can have a life financing of 40 months, and a condominium can be financed for 15 years of 12 months plus an additional 8 months; this is equivalent to (15 * 12) + 8 = 188 months. Here is the tricky part, especially as far as Microsoft Excel deals with its finance functions. If you pass the number of Periods in terms of years, such as 5 for a car loan that stretches over 5 years, then you can pass the Rate as a percentage value, such as 8.75%. If you pass the number of Periods in terms of months, for example you can pass it as 44 for a car that is financed in 3 years and 8 months, then you must communicate this to the Rate argument by dividing the Rate by 12. In other words, a Rate of 8.75% would be passed as 8.75%/12. If the Rate was typed in a cell named B2 that displays 8.75%, you can pass it as B2/12. For deposits made in a savings account, because their payments are made monthly, the rate is divided by the number of Periods of a year, which is 12. If an investment has an interest rate set at 14.50%, the Rate would be 14.50/12 = 1.208. Because the Rate is a percentage value, its actual value must be divided by 100 before passing it to the function. For a loan of 14.50% interest rate, this would be 14.50/12 = 1.208/100 = 0.012. The Payment is the amount the customer will be paying. For a savings account where a customer has pledged to pay a certain amount in order to save a set (goal) amount, this would be the amount the customer would pay every month. If the customer is making payments (car loan, mortgage, deposits to a savings account, etc), this value must be negative. If the customer is receiving money (lottery installment or annuity, family inheritance, etc), this value must be positive. The Payment Type specifies whether the payment is made at the beginning or the end of the period. For a monthly payment of an item financed like a car, a boat, a guitar, or a house this could be the end of every month. The Future Value of an Investment

To calculate the future value of an investment, you can use the FV() function. The syntax of this function is: FV(Rate, Periods, Payment, PresentValue, PaymentType)

Practical Learning: Calculating the Future Value

1. Start a new workbook and fill up Sheet1 as follows:

2. Save it as Business 3. Double-click Sheet1 to put its label into edit mode. Type Future Value and press Enter 4. Click cell C8 and, on the main menu, click Insert -> Function... 5. In the Paste Function dialog box, in the Function Category list, click Financial. In the Function Name list, double-click FV and move the FV window so you can see the values on the worksheet 6. Click the box to the right of Rate and, on the worksheet, click cell C5 and type /12 7. In the FV window, click the box to the right of Nper and, on the worksheet, click cell C7 8. In the FV window, click the box to the right of Pmt and type 9. On the worksheet, click cell C6 10. In the FV window, click the box to the right of Pv and type 11. On the worksheet, click cell C4

12. Since this is a loan, the payments are expected at the end of the month. Therefore, in the FV window, click the box to the right of Type and type 0

13. Click OK

The Number of Periods of an Investment

To calculate the number of periods of an investment or a loan, you can use the NPER() function. Its syntax is: NPER(Rate, Payment, PresentValue, FutureValue, PaymentType);

Here is an example:

Investment or Loan Payment The PMT() function is used to calculate the regular payment of loan or an investment. Its syntax is: PMT(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

In the following example, a customer is applying for a car loan. The cost of the car will be entered in cell C4. It will be financed at a rate entered in cell C6 for a period set in cell C7. The dealer estimates that the car will have a value of $0.00 when it is paid off. Practical Learning: Calculating the Monthly Payments of a Loan 1. Double-click Sheet3 to put it in edit mode. Type Payments Amount and press Enter 2. Complete the worksheet as follows

3. 4. 5. 6. 7. 8.

Click cell C8 and type =PMT( Click cell C6 and type /12, Click cell C7 and type ,Click cell C4 and type , Click cell C5 Type ,0) and, on the Formula Bar, click the Enter button

9. Suppose that, during the evaluation, a customer decides that she doesn't need a brand new car anymore. Also, she thinks that a 5-year car loan is too long. Furthermore, she wants to make a $4500.00 down payment to reduce the monthly payments. On the other side of the desk, the salesperson who wants to make a juicy commission on this loan has decided to increase the interest rate. Change the new values of the worksheet as follows and see the result

10. Save the workbook

The Amount Paid As Interest During a Period When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount. The IPMT() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment. It is important to understand what this function calculates. Suppose a customer is applying for a car loan and the salesperson decides (or agrees with the customer) that the loan will be spread over 5 years (5 years * 12 months each = 60 months). The salesperson then applies a certain interest rate. The IPMT() function can help you calculate the amount of interest that the lending institution would earn during a certain period. In essence, you can use it to know how much money the company would earn in the 3rd year, or in the 4th year, or in the 1st year. Based on this, this function has an

argument called Period, which specifies the year you want to find out the interest earned in. The syntax of the IPMT() function is: IPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The Rate argument is a fixed percent value applied during the life of the loan. The PresentValue is the current value of the loan or investment. It could be the marked value of the car, the current mortgage value of a house, or the cash amount that a bank is lending. The FutureValue is the value the loan or investment will have when the loan is paid off. The NPeriods is the number of periods that occur during the lifetime of the loan. For example, if a car is financed in 5 years, this value would be (5 years * 12 months each =) 60 months. When passing this argument, you must remember to pass the right amount. The Period argument represents the payment period. For example, it could be 3 to represent the 3rd year of a 5 year loan. In this case, the IPMT() function would calculate the interest earned in the 3rd year only. The PaymentType specifies whether the periodic (such as monthly) payment of the loan is made at the beginning (1) or at the end (1) of the period. The FutureValue and the PaymentType arguments are not required. Practical Learning: Calculating the Monthly Payments of a Loan 1. To add a new worksheet, on the main menu, click Insert -> Worksheet 2. Double-click the new Sheet1 tab to put it in edit mode. Type Periodic Interest Earned and press Enter 3. Move the new worksheet to be the most right 4. Complete the worksheet as follows

5. Click cell C9 and type =IPMT( 6. Click cell C5 and type /12, 7. Click cell C6 and type , 8. Click cell C7 and type ,9. Click cell C4 and type , 10. Click cell C8 and type , 11. Type ,0) and, on the Formula Bar, click the Enter button

12. Save the workbook

The Amount Paid as Principal While the IPMT() function calculates the amount paid as interest for a period of a loan or an investment, the PPMT() function calculates the actual amount that applies to the balance of the loan. This is referred to as the principal. Its syntax is: PPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The arguments are the same as described in the previous sections. Practical Learning: Evaluating the Amount Paid As Principal 1. Change the Periodic Interest Earned worksheet as follows

2. 3. 4. 5. 6. 7. 8.

Click cell C10 and type =PPMT( Click cell C5 and type /12, Click cell C6 and type , Click cell C7 and type ,Click cell C4 and type , Click cell C8 and type , Type ,0) and, on the Formula Bar, click the Enter button

9. Save the workbook The Present Value of a Loan or an Investment The PV() function calculates the total amount that a future investment is worth currently. Its syntax is: PV(Rate, NPeriods, Payment, FutureValue, PaymentType)

The arguments are the same as described earlier. The Interest Rate Suppose a customer comes to a car dealer and wants to buy a car. The salesperson would first present the available cars to the customer so the customer can decide what car he likes. After this process and during the evaluation, the sales person may tell the customer that the monthly payments would be $384.48. The customer may then say, "Wooooh, I can't afford that, man". Then the salesperson would ask, "What type of monthly payment suits you". From now on, both would continue the discussion. Since the salesperson still wants to make some money but without losing the customer because of a high monthly payment, the salesperson would need to find a reasonable rate that can accommodate an affordable monthly payment for the customer. The RATE() function is used to calculate the interest applied on a loan or an investment. Its syntax is: RATE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)

All of the arguments are the same as described for the other functions, except for the Guess. This

argument allows you to give some type of guess for a rate. This argument is not required. If you omit it, its value is assumed to be 10. Practical Learning: Calculating the Interest Rate 1. 2. 3. 4.

To add a new worksheet, on the main menu, click Insert -> Worksheet Double-click the new Sheet1 tab to put it in edit mode. Type Interest Rate and press Enter Move the new worksheet to be the most right Change the Interest Rate worksheet as follows

5. 6. 7. 8. 9.

Click cell C8 and type =-RATE( Click cell C7 and type , Click cell C6 and type ,Click cell C4 and type , Click cell C5 and type ,0)*12 and, on the Formula Bar, click the Enter button

10. To use the ABS() function, change the function in cell C14 to =ABS(RATE(C7,C6,-C4, C5, 0)*12) and press Enter 11. Save the workbook

The Internal Rate of Return The IRR() function is used to calculate an internal rate of return based on a series of investments. Its syntax is: IRR(Values, Guess)

The Values argument is a series (also called an array or a collection) of cash amounts that a customer has made on an investment. For example, a customer could make monthly deposits in a savings or credit union account. Another customer could be running a business and receiving different amounts of money as the business is flowing (or losing money). The cash flows don't have to be the same at different intervals but they should (or must) occur at regular intervals such as weekly (amount cut from a paycheck), bi-weekly (401k directly cut from paycheck), monthly (regular investment), or yearly (income). The Values argument must be passed as a collection of values, such as a range of selected cells, and not an amount. Otherwise you would receive an error. The Guess parameter is an estimate interest rate of return of the investment. Practical Learning: Calculating the Internal Rate of Return

1. To add a new worksheet, on the main menu, click Insert -> Worksheet 2. Double-click the new Sheet1 tab to put it in edit mode. Type Internal Rate of Return and press Enter 3. Move the new worksheet to be the most right 4. Change the worksheet as follows

5. Click cell D12 and type =IRR( 6. Select cells D4:D10 and, on the Formula Bar, click the Enter button

7. In cell D11, type 12 and click cell D12 8. In the Formula Bar, change the function to =IRR(D4:D10, D11) and press Enter (you shouldn't need any significant difference unless you change the range of cells such as D4:D8) 9. Save the workbook

The Net Present Value The NPV() function uses a series of cash flows to calculate the present value of an investment. Its syntax is: NPV(Rate, Value1, Value2, ...)

The Rate parameter is the rate of discount during one period of the investment. As the NPV() function doesn't take a fixed number of arguments, you can add a series of values as Value1, Value2, etc. These are regularly made payments for each period involved. Because this function uses a series of payments, any payment made in the past should have a positive value (because it was made already). Any future payment should have a negative value (because it has not been made yet). Practical Learning: Calculating the Net Present Value

1. To add a new worksheet, on the main menu, click Insert -> Worksheet 2. Double-click the new Sheet1 tab to put it in edit mode. Type Net Present Value and press Enter 3. Move the new worksheet to be the most right 4. Change the worksheet as follows

5. Click cell C14 and type =-NPV( 6. Click cell C13 and type , 7. Select cells C4:C12 and, on the Formula Bar, click the Enter button

8. To use the ABS() function, change the function in cell C14 to =ABS(NPV(C13,C4:C12)) and press Enter 9. Save the workbook

Using Excel’s Financial Functions To See If You Can Afford That New Car by Linda Johnson, MOS The average person shies away from the financial functions in Excel because they think they are only for the mathematical wizards. But, if you use the Insert Function button, some of them aren t too hard to learn and can answer some questions we all have.

Scenario You want to buy a new car and the car dealers all offer different interest rates and payment plans. They throw a lot of numbers and plans at you, but usually all you want to

know is how much of a downpayment do I need to make so I can afford the monthly payments? Well, Excel can figure this out for you pretty easily, using the PMT function. Let s say you know that the car you want costs $25,000 and you know you can only afford $450 a month for car payments. You have $6,000 in your savings account, but you don t necessarily want to use it all for a downpayment. So, how much do you really have to put down on this car in order to get the payments you can afford? First, talk to your lending agency and find out how many years you have to pay and what the interest rate is that they are offering. Then enter this information in a new blank worksheet. A 1 2 3 4 5 6 7

Car Loan Number of payback years Total Number of payments Loan Amount Interest Rate for Loan DownPayment MONTHLY PAYMENT

B 4 =B2*12 =25000-B6 4.9% 6000

This shows, in cell B4, you are borrowing $19,000 ($25,000 minus the downpayment of $6000 which you put in B6), over four years (B2), totaling 48 payments (B3, which multiplies the number of years by 12 months), at a yearly interest rate of 4.9%. Notice that I put formulas in both B3 and B4, so you can change the number of payback years and the downpayment amount and everything else will be adjusted automatically. Now let s figure out what the monthly payment would be with these figures. 1. Click inside B7 and click on the Insert Function button on your toolbar

(or go to the Insert menu and choose

Function )

2. Choose Financial from the box that says Or select a category and choose PMT from the list below. Notice it tells you at the bottom, that the PMT function Calculates the payment for a loan based on constant payments and a constant interest rate.

3. Click OK and you will be presented with this box:

4. Now, all you have to do is fill in the boxes and Excel will give you your answer: a. In the Rate box, type B5, which is the cell that contains your interest rate. However, the lending agency

will give you the YEARLY rate and what you need is the MONTHLY rate, so, you need to put B5/12 in this box (that s B5 divided by 12 months in a year) b. Hit your tab key to move to the

Nper

box.

c. The Nper box is for the number of payment periods and we have that entered in B3, so put B3 in this box. (TIP: You don t have to type B3 in the box. If your cursor is positioned in the box, you can just click on cell B3 and Excel will put it in the box for you.) d. Hit your tab key to move to the

Pv

box.

e. The Pv box is for the present value, or the amount you are going to pay. That value is in B4, so put B4 in that box. f. Hit your tab key to move to

Fv .

g. Notice that Fv and Type are not in bold like the others. This is because these arguments are optional, whereas the bold ones are mandatory. See at the bottom of the box that Excel tells you that Fv is the future value and if you leave it empty, Excel will assume 0 (zero). Since you want to pay this loan off entirely, you want a zero balance, so just hit your tab key to leave this empty and move to the Type box. h. Lending agencies often offer different payment amounts if the loan is paid at the beginning of the month than if it is paid at the end of the month. Your lending agency will tell you if they offer this. For now, put a zero (0) in that box, assuming you will pay this loan at the end of the month. i. Make sure your choices look the same as mine below, then click OK.

Your spreadsheet should now look like this and you see your monthly payments with these choices are $436.70, which is lower than the $450 a month we can afford.

Click in cell B6 and change the downpayment to $3000. And look how close we still are! $505.65. and only using half of our savings for the downpayment! Can we get it even lower? Sure! Click in cell B7 where your PMT formula is and look in your formula bar at the top of your screen and see the formula is =PMT(B5/12,B3,B4,0,0). Change that last zero to a 1 (assuming your lending agency allows for paying at the beginning of the month). That saved you another two bucks and now your payment is $503.59. Maybe your lending agency allows you to pay over 5 years instead of 4? If so, change the 4 in cell B2 to a 5 and notice your number of payments in cell B3 adjusts to 60 and your monthly payment drops to $412.48. WOW! Maybe we can make even less of a downpayment!

Click in cell B7 and change that downpayment amount to $1000 and see that your monthly payment is $449.97 and right in line with what you want to pay. And, you still have FIVE THOUSAND BUCKS left in your savings account! So, you can see that Excel can be a handy tool when you are trying to figure out stuff like this. And, you can go back to that car dealer armed with all you need to know to bargain with him/her. And, if you want to know if you can retire early, try Excel s Financial Function named FV and see if you can figure out how to determine what that savings plan you have will be worth in 25 years. Remember, Excel doesn t require that you are a mathematician. Excel does the work for you!

Linda Johnson is a college instructor of all of the Microsoft Office programs and also offers private training to companies in the Philadelphia area. Companies outside that area can also buy corporate licenses for distributing her ebooks and CD or send their employees to her online classes. See more info about Linda on the TechTrax consultants page or go directly to her services page to learn more about her online classes, ebooks and CD: http://www.personal-computer-tutor.com/services.htm.

DB Returns the depreciation of an investment using the fixed-declining balance method for the specified period. The 'life' and 'period' arguments must use the same units of measure.

Syntax DB(cost,salvage,life,period,{months})

Arguments cost The initial cost to depreciate. Must be a positive number. salvage The value of the item at the end of the depreciation period. Must be a positive number. life The number of periods in the depreciation cycle. Must be a positive number. period The period of time to compute the depreciation. Must be a positive number. months The number of months in the first year of depreciation. If omitted,

12 months is used. Must be a positive number.

Example DB(10000,1000,5,1) is $3,690. A $10,000 investment depreciates $3,690 in the first year if it is depreciated over 5 years with a final salvage value of $1,000.

DDB Returns the depreciation of an investment using the variable declining balance method for the specified period. The life and period arguments must use the same units of measure.

Syntax DB(cost,salvage,life,period,{factor})

Arguments cost The initial cost to depreciate. Must be a positive number. salvage The value of the item at the end of the depreciation period. Must be a positive number. life The number of periods in the depreciation cycle. Must be a positive number. period The period of time to compute the depreciation. Must be a positive number. factor The rate of balance decline. If omitted, the system assumes 2 (double declining balance method). Must be a positive number.

Example DDB(10000,1000,5,1,3) is $6,000. A $10,000 investment depreciates $6,000 in the first year if it is depreciated over 5 years with a final salvage value of $1,000, and a depreciation factor of 3.

FV Returns the future value of an investment based on constant periodic payments and a constant interest rate.

Make sure that the rate corresponds to the number of payment periods. For a monthly payment period remember to divide an annual interest rate by 12 (months) to get a monthly interest rate. Enter cash paid out as negative values, and any cash received as positive values.

Syntax FV(rate,periods,payment,{pv},{atEnd})

Arguments rate The interest rate for the period of the investment. periods The number of payment periods. payment The amount paid each period. pv The present value of the investment (optional, default is 0). If TRUE payments take place at end of a payment period. atEnd (optional, default is TRUE.)

Example FV(.10/12,5*12,-100) = $7,743.71. You save $7,743.71 after 5 years depositing $100 a month with no initial deposit and a 10% interest rate.

NPER Returns the number of periods for an investment with a constant cash flow and interest rate given a present and future value. Make sure that the rate corresponds to the number of payment periods. For a monthly payment period remember to divide an annual interest rate by 12 (months) to get a monthly interest rate. Enter cash paid out as negative values, and any cash received as positive values.

Syntax NPER( rate, pmt, pv, {fv}, {atEnd} )

Arguments rate The interest rate for the period of the investment. payment The amount paid each period.

pv The present value of the investment. fv The future value of the investment (optional, default is 0). atEnd If TRUE payments take place at end of a payment period. (optional, default is TRUE.)

Example NPER(.08/12,-100,2000) = 21.54. At $100.00 per month it takes 21.54 months to repay $2,000.00 at 8% interest. NPER(.08/12,-100 0,2000) = 18.84. At $100.00 per month in an 8% interest account, it takes 18.84 months to save $2,000.00.

PMT Returns the payment amount needed to finance an investment. Make sure that the rate corresponds to the number of payment periods. For a monthly payment period remember to divide an annual interest rate by 12 (months) to get a monthly interest rate. Enter cash paid out as negative values, and any cash received as positive values.

Syntax PMT( rate, periods, pv, {fv}, {atEnd} )

Arguments rate The interest rate for the period of the investment. periods The number of payment periods. pv The present value of the investment. fv The future value of the investment (optional, default is 0). atEnd If TRUE payments take place at end of a payment period. (optional, default is TRUE.)

Example PMT(.08/12,12*3,10000) = -$313.36.

$10,000 borrowed at 8% annual interest requires a monthly payment of $313.36 to pay off the loan in 3 years.

PV Returns the present value of an investment based on constant periodic payments and a constant interest rate. Make sure that the rate corresponds to the number of payment periods. For a monthly payment period remember to divide an annual interest rate by 12 (months) to get a monthly interest rate. Enter cash paid out as negative values, and any cash received as positive values.

Syntax PV( rate, periods, payment, {fv}, {atEnd} )

Arguments rate The interest rate for the period of the investment. periods The number of payment periods. payment The amount paid each period. fv The future value of the investment (optional, default is 0). If TRUE payments take place at end of a payment period. atEnd (optional, default is TRUE.)

Example PV(.10/12,5*12,-100,10000) is -$1371.35. Depositing $100 a month into a 10% savings account requires an initial outlay of $1,371.35. to have $10,000 in the account in 5 years. PV(.08/12,3*12,-300) is $9,573.54. A $9,573.54 loan at 8% can be paid off in 3 years with monthly payments of $300. The financial function we're going to explore will calculate the monthly payment amounts on a loan. The function we're going to use is PMT( ).

The PMT ( ) Function

If you want to know how much a loan will cost you, then the PMT ( ) function is ideal. How much will I have to pay back if I borrow a thousand pounds over 2 years? Over 5 years? Over 10 years? You can calculate this with the PMT ( ) function. The PMT( ) function expects certain values in between those two brackets. The values are known as arguments. The arguments that the PMT( ) function expects are these: PMT(rate, nper, pv, fv, type) The last two, fv and type, are optional. If all this sounds very confusing, then don't worry: we'll clear it up with an example. The example is this: You want to borrow ten thousand pounds from your friendly banker. You also want to pay it back over 5 years. What you need to know is - how much will this loan cost me every month? We'll work it out with the PMT( ) function. First, create a new spreadsheet like the one below:

The figure in cell B1 is the amount we want to borrow - ten thousand pounds. Our financial function PMT( ) will go under Monthly Amount, in cell D4. Before we can enter our function, we need to work out the Interest Rate, the Number of Payments, and the Present value. These are the arguments for our function. Let's start with the Interest Rate, the first of our arguments (rate). The bank tells us that the interest rate is 12 percent. This is for the entire year. As we want to know how much to pay back each month, we need to divide this figure by 12 (12 months in a year; divide this into 12 percent). Of course, this is a simple division: 12 divided by 12 is 1. But we'll let Excel work it out. So do the following: • •

Click inside cell A4 Click inside the formula bar

•

Enter the following formula: = 12% / 12

• •

The press the return key on your keyboard Excel will enter 0.01 in cell A4

The next thing we need to work out is how many payments there are in total. We are paying back the loan every month for 5 years. As there are 12 months in a year, the formula is just 12 multiplied by 5. So do this: • • •

Click inside cell B4 Click inside the formula bar Enter the following formula: = 12 * 5

• •

Press the Return key on your keyboard Excel will enter 60 in cell B4

OK, we have now worked out the second argument for our PMT( ) function - the nper bit. We can now move on to the pv part of the argument, or Present Value. The Present Value is sometimes known as the Principal. It is what the loan is worth now, and not say 5 years into the future. In other words, it's ten thousand pounds for us. So for the Present Value column in your spreadsheet, do this: • • •

Click inside cell C4 Click inside the formula bar Enter this: = B1

• •

Press the return key on your keyboard Excel will enter 10000 in cell C4, because this is the figure you entered into cell B1

Your spreadsheet should now look like the one below:

Time now to enter our PMT( ) function in cell D4. So, do the following:

• • •

Click inside cell D4 Click inside the formula bar Enter the following function: =PMT(A4, B4, C4)

•

Press the return key on your keyboard

Excel should now have entered the monthly payments in cell D4. It is usually in red, and with a minus sign at the start (minus because it's what you owe to the bank). The answer you should have in D4 is -£222.44. So we have to pay back to the bank every month two hundred and twenty two pounds forty four pence. The final column is "Total Paid Back". To work out that answer you need to multiply the monthly payments by the number of payments. Which should cause you no problems at all. When you're done, the final spreadsheet looks like this:

Varying the Interest Rate We'll now change that spreadsheet slightly. What we'll do is vary the number of payments. At the moment, we're saying 12 * 5 for the number of payments. But what if we decide we want to pay it back over ten years? How much are the monthly payments then? And how much do we pay back in total?

We also want to vary that interest rate. There is plenty of competition for loans. What if we can get a better deal for our interest payments? How does it effect the monthly payments if the interest rate is 11 percent? We can make only a few slight changes to the spreadsheet to answer these questions. Insert two more rows into your spreadsheet, and add two labels. Your spreadsheet sheet match the one below:

To get the interest rate, we entered a formula. We entered this: = 12 % / 12 If we put the percentage figure in a cell of its own, we could then reference that cell in our formula. We could just put this: = B3 / 12 Then we could vary the interest rate by changing the number in cell B3. To clear any confusion, do the following: • • • • • • • • • • •

Click inside cell B3 Click inside the formula bar Type in = 12 % Press the Return key on your keyboard Click back inside cell B3 because we need to format the cell as a percentage To format the cell as a percentage, click Format from the menu bar From the drop down menu, click on Cells. The Format Cells dialogue box appears Select the Number tab strip Under Category, click on Percentage. Click the OK button when you are done Cell B3 should now read 12.00 %

You can now change the formula for your interest rate. So click inside the cell where you interest rate is, probably cell A6. Click inside the formula bar. Change the formula from this: = 12 % / 12 to this:

= B3 / 12 When you press the Return key on your keyboard, all of your monthly payment terms should stay the same. The difference is that you can now alter the interest rate from cell B3. Test it out. Change the interest to 10 percent and see what happens to your Monthly Amount figure.

Exercise The Number of Payments formula currently reads = 12 * 5. Change this formula so that number of years is coming from cell B2. When you're finished, you final spreadsheet will look like this one:

Play about with the figures in cells B1 to B3 and watch the D6 and E6 figures change. What about a ten year loan with interest of 15 percent?

PMT extra You can use the PMT( ) function to work out your savings. You might want to know how much you'll have to put away in the bank if you want to save 2 thousand pounds in one year. Remember the full PMT( ) function? It was this: PMT(rate, nper, pv, fv, type) To work out how much you'll have to save each month, you need that fv part. The fv stands for Future Value. You would then use the function like this (assume that the interest rate is a massive 10 percent): = PMT(10% / 12, 12 * 1, 0, 2000)

The zero is for the pv part. The pv was Present Value. Because you haven't saved anything yet, the present value of your savings is zero. The 2000 is how much you want to save over 1 year

In the next part, we'll take a look at how to use IF Statements in your spreadsheets. This is known as Conditional Logic. Conditional logic(IF) is used quite frequently in modern spreadsheets. But let's start by asking What is Conditional Logic?

Conditional Logic For our purposes, conditional logic is two words - "IF" and "Then". You use conditional logic all the time in your daily life, without realising it. You might say to yourself, "IF I eat this delicious cream cake THEN my diet will be ruined." You're using conditional logic to make a decision: IF I do this THEN that will happen. Some more examples: IF I buy this lovely coat THEN I will look beautiful IF I watch one programme THEN I can tape the other IF I win the lottery THEN I will be happy Those are all example of conditional logic. Excel also uses the IF word for conditional logic. You can test what is in a cell, and say what should happen if it is one value rather than another. For example, suppose cell A1 has the number 6 in it. In cell A2, you can enter an IF function to test whether cell A1 is above 5 or below 5. IF it is above 5 THEN one thing happens; IF it is below 5 THEN another thing happens. The correct format to use for the IF function is this: IF(logical_test, value_if_false, value_if_true) So the IF function takes three arguments: logical test, value if true, value if false. Let's break those three arguments down a little more: Logical Test: This is what you want to test for. In our example, we wanted to test whether cell A1 is greater than or less than 5. Excel uses this symbol > for greater than and this symbol < for less than. So for the first argument, we would put A1 > 5 Value If True:

This is the THEN part of the IF statement. Excel needs to know what you want to happen IF your condition is met. You can put in text surrounded by quotes, or another formula. Value If False: You also need to say what should happen IF your condition is not met.

Let's clarify all this with a spreadsheet example. So, start a new spreadsheet and do the following: • • • • • •

Click inside cell A1 and enter the number 6 Press the return key on your keyboard Widen the B column of your spreadsheet. Make it nice and big. Click inside cell B1 Click inside the formula bar Enter the following formula: =IF(A1 > 5, "Greater than Five", "Less than Five")

• •

Press the return key on your keyboard Your spreadsheet should look like this one below:

It's important you get all the syntax right in your IF function. In other words, don't forget the commas, and the double quote marks around text. But click inside cell A1 and change the number 6 to the number 4. Then press the return key on your keyboard. Excel should put Less than Five in cell B1. Now put the number 5 in cell A1 and see what happens. Nothing happened, right? That's because the IF function is very precise. You didn't tell Excel what to do if the number equalled 5. To get round this, we can use the Greater Than ( > ) symbol and the Equal symbol together. Like this: A1 >= 5 That says "A1 greater than or equal to 5". When we amend our function, it looks like this:

=IF(A1 >= 5, "Greater than or Equal to Five", "Less than Five") You can also test to see if something is Less Than ( < ) or Equal to. You could use this for the formula: =IF(A1 <= 5, "Less than or Equal to Five", "Greater than Five")

You can even test for Not Equal To. Just join the Less Than (<) symbol to the Greater Than (<) symbol. Like this: =IF( A1 <> 5, "Not 5", "Is 5") So to sum up: after typing IF you tell Excel what you want to test for. Then you say what should happen if the condition is true. Then you say what should happen if the condition is false.

In the next part, we'll take a look at Conditional Formatting. If statements in spreadsheets can be very long and complicated. Consider this problem. You have a spreadsheet of student exam marks. Suppose you want to add the grades as well. If the student scored above 80 it's an A; if the student scored between 60 and 79, it's a B; if the student scored between 45 and 59, it's a C; if the student scored between 30 and 44, it's a D; and if the student scored less than 30, it's Fail. The question is, what does your IF statement look like? Well, it might look like this: =IF(B2>=80, "A", IF(B2>=60, "B", IF(B2>=45, "C", IF(B2 >=30, "D", "Fail")))) What the IF statement is doing is nesting another IF statement on the False condition. So it says, "If B2 is greater than or equal to 80 THEN put an A in cell B15; if it's not, execute another IF statement." But don't worry if that long IF statement is making your brain hurt - it is quite complicated! However, we'll use that long IF statement to explore Conditional Formatting. You'll be glad to know that there is a spreadsheet already prepared for you, for this lesson. So right click the link below and save the spreadsheet to your own computer. Download the Spreadsheet for this lesson

Open up the spreadsheet you've just downloaded, and take a look at it. It should be this one:

It looks a bit hard to follow at the moment. It's difficult to know what the data is supposed to represent. Conditional formatting will help to clarify things.

Conditional Formatting We can use Conditional logic, however, to format the spreadsheet. What we'll do is to colour those Overall Averages depending on the grade. One colour will represent a high grade, a second colour will mean an average grade, and a third colour will mean a low grade. So let's get started. • • • •

With your Spreadsheet open, Highlight the cells with Overall Averages in them (From B12 to I12) With the cells highlighted, click on Format form the menu bar From the drop down menu, click on Conditional Formatting A dialogue box appears, the one below:

At the top, it says Condition 1, and there are 4 text boxes to fill in. The first two "Cell Value Is" and "Between" will suit our purposes, so: • • •

Click inside the third text box and enter 80 Click inside the fourth text box and enter 100 You should have "Cell Value Is between 80 and 100"

You can now set the colour to use for this condition. So click the Format button. Another dialogue box appears. From the Patterns Tab Strip, select a colour or pattern to use. Then click the OK button at the bottom. Your dialogue box will look something like the one below:

You can now add a second condition. To do that, click the Add button at the bottom of the dialogue box. Condition 2 will appear. Repeat the above process: enter some numbers for the middle set of grade (65 to 79, perhaps), and select a different colour by clicking the Format button. Add a third condition for the lowest grades. When you're done, click the OK button. Your Overall Averages should change colour depending on what grade the student got. (Unfortunately, Excel only allows you a maximum of 3 conditions.) Your spreadsheet might look something like the one in the next image:

The conditional formatting allows you to see at a glance which students are doing well, and which are doing badly. Poor Mark!

Exercise Format the Student Grades so that your spreadsheet looks like the one below (you can use different colours, if you like).

In the spreadsheet above, you can now see more easily who's doing well and who isn't. In the next section, we'll take a look at CountIF. Again, we'll use the student spreadsheet. The next Conditional function we're going to use is CountIF. This one is fairly straightforward. It looks at a range of cells and tells you how many of them meet the criteria that you set. Once again, we'll use the student exam marks spreadsheet.

Copying and Pasting Data to a new Sheet Open up the Spreadsheet you used for the last section. (If you haven't yet downloaded the spreadsheet, click here: Download the spreadsheet.)

Because the exam spreadsheet might be a little full, we'll start on a new sheet. We'll copy and paste the student data onto sheet 2. To do that, follow these steps: • •

Highlight the data you want to copy For us, that's the student data, as in the image below:

• •

With the data highlighted, click on Edit from the menu bar From the drop down list, click on Copy. The marching ants surround your highlighted data Look at the bottom of the spreadsheet and locate Sheet2, as in the next image:

•

• • • •

Click on Sheet2 with your left mouse button The blank Sheet2 spreadsheet should appear Click inside cell A1 Press the Return key on your keyboard to paste the data (Or click Edit > Paste from the Excel menu bar)

Add the following labels to Sheet2 of your spreadsheet:

We'll now use the CountIF function to see how many of the students were above a score of 70 in each subject.

CountIF The CountIf function only calculates things when a certain condition is met. For example, only count the students whose grades are above B. The CountIF function uses this syntax: COUNTIF(range, criteria) In other words, it takes two arguments. The first argument is range, and this means the range of cells you want to count. The criteria is what you want excel to look for when it is counting. Let's see it in action. • • •

Click inside cell B15 Click inside the formula bar Enter the following function: =CountIf(B2:I2, ">= 70")

• •

Press the return key on your keyboard The answer you should have got was 4

The formula says, look in cells B2 to I2. Then check for a score of greater than or equal to 70. If you find this score, keep a Count. So 4 students achieved a mark of 70 or above for Maths. To do the rest of the scores, you can use AutoFill. When you have finished, it should look like this one below:

CountIF is a very useful function for simple data analysis. In the next part, we'll take a look at two more useful functions: AND and SUMIF. Two more useful Conditional Functions are AND( ) and SUMIF( ). We'll stay with the students exam results to examine these two functions. If you haven't yet downloaded the spreadsheet we're using for these lessons, click below. (If you've been following along, you can use this new spreadsheet: it has the CountIF already done!) Download the spreadsheet for this section

The AND Function Here's the scenario. The government have decided to take a closer look at school averages. If 4 or more students in a subject get a score of 70 or above, then the school gets a cash bonus for that subject. There are 8 subjects, so 8 cash bonuses are up for grabs. First, we'll use the AND function to work out if 4 or more students have scores of 70 or above. The AND( ) function checks a list of arguments and determines whether they are true or false. If all the arguments are true then the function gives you an answer of TRUE. If one or more of the arguments are false, then the function gives you an answer of FALSE. To give you an easy example. You can check whether two plus two does indeed equal 4. Click inside an empty cell of your spreadsheet, then click inside the formula bar. Enter this: =AND(2 + 2 = 4) When you press the Return key, Excel gives you the answer TRUE. Now change it to 2 + 3 = 4 and see what happens.

So Excel checked the argument to see whether it was true or false. That's all it will check for, an either or answer. You can have up to 30 arguments between the AND brackets. You could have this, for example: = AND(2 + 2 = 4, 1 + 2 = 4) There's two arguments to check there. Excel will check the first one and return an answer of TRUE. When it checks the second one it will return an answer of False. The answer to the whole function will then be FALSE. (It's false because all conditions have to be true before the overall answer is TRUE.) Our AND function is going to be quite simple. We're going to check the B column, the Number of students who have Below Average scores. • • •

So click inside C15 Click inside the formula bar Enter the following formula : = AND(B15 >= 4)

• • • •

Press the return key on your keyboard Excel will enter True in this cell (B15 is the cell where we had a score of 4) Use Auto Fill to calculate the rest of the AND functions Your spreadsheet should look something like the one below:

All we're saying in our function is "IF the cell B15 is greater than or equal to 4 THEN put True in cell C15, else put False". Remember: the government pays out if 4 or more students get above a score of 70 for a lesson Now that we have some True/False values for our C column, we can check all these True/False values. We want to add up all the cash values associated with our TRUE cells. However, we haven't got any cash values yet, so let's do that now. Enter the same cash values as in the image below:

The values are just potential values. A school only gets them if 4 or more students gain a score of 70 or above in that subject. So there's a potential ten thousand for Maths, five thousand for English, 8 thousand for Science, etc. OK, we have 2 cells with TRUE in them. There is a different cash value associated with each subject. We have a TRUE for English, so the school will receive 5 thousand pounds for this. The school can expect 10, 000 for Maths. But we need a way to add all the cash values associated with the TRUE values. We can use SUMIF for this.

SumIF SUMIF is a little bit more complicated than AND, but not much more. This function adds up things depending on the criteria you give it. (Add up the cost of all apples, for example.) The function expects certain arguments. These are: SUMIF(range, criteria, sum_range) The first argument, range, is the cell or cells you want to check. For us, this would be the TRUE and FALSE values in the D column. The second argument, criteria, is what you want to check for. In our case this is the value TRUE. The third argument, sum_range, are the cells to add up. The figures we want to add up are all in the E column. • • •

So click in an empty cell (E24 is ideal for us) Click inside the formula bar Enter the following SUMIF function:

=SUMIF(C15:C22, TRUE, E15:E22) • •

Press the return key on your keyboard Excel adds up only the value associated with TRUE

If everything went well, then you should have a figure of 11 thousand for your SUMIF function. Your spreadsheet might look like the one below:

So our SUMIF function said "Check the cells C15 to C22. If a cell has TRUE in it, make a note of the ammount in the E cell next to it. When you've finished, add them all up." The SUMIF might be a bit tricky to master, but it can come in quite handy, and it's worth making the effort to understand exactly how it works. In any case, that concludes are little journey into conditional logic. Hope you're not too disappointed to be leaving the subject behind! In the next section of the Excel course, we'll take a look at Tables, Scenarios and Goal Seek. In Excel, a data Table is a way to see how altering the values in a formula effects the result. Excel will work out the new results for you, based on the new values you give it. Take the following as an example: You decided to take out a loan of ten thousand pounds. You want to pay back the loan over 5 years. The first bank you try sets a interest rate of 9 percent per year. You use the PMT formula to work out how much you have to pay back every month: =PMT(9% / 12, 12 * 5, -10000) The formula gives you a figure of £207.58 per month.

However, another three banks are after your business. They are offering interest rates of 8 percent, 7 percent, and 6 percent. You can use the PMT function again to work out the monthly payments for these interest rates, or you could just use a cell reference for that 9% figure in the formula. Another way to work out the monthly payments for the new interest rates is to use a Table. Excel will then use the PMT function, and the new interest rates, and work out the answers for you. We'll see how to do that now.

Excel Tables • • •

Start a new spreadsheet Enter the same labels as in the image below Enter the same values for the Rate, Months, and Loan

So the Rate (interest rate) is 9 percent, the Months value is 60, and the Loan is 10, 000 pounds. • • •

Click inside cell D2 Click inside the formula bar Enter the following formula: =PMT(B3 / 12, B4, -B5)

• •

Press the return key on your keyboard Excel should give you the answer £207.58

Just in case you're unsure about that PMT Function, here's what it's doing. The first argument for PMT is the rate, meaning the interest rate. The cell B3 is where we had our interest rate of 9 percent. We need to divide that by 12 (the number of months in a year), otherwise the bank will be charging us 9 percent a month! The second argument, where we have B4, is nper. This is just the total number of months in our loan. The third argument is how much we want to borrow. We have this amount in cell B5. It is a minus figure because it's a debt.

Now that we have a function in place, we can construct our Excel Table. First, we need to tell Excel about those other interest rates. It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT function. So it needs some new values to calculate with. • •

So in cells C3, C4, and C5 enter 8%, 7%, 6% Your spreadsheet should look like the one below

We have deliberately put the PMT function in cell D2. This is one Row up, and one Column to the right of our first new interest rate of 8%. The new monthly payments are going to go in cells D3 to D5. Excel needs you to set the table out this way. So that Excel can work out the new totals, you have to highlight both the new values and the Function. • • •

So click inside cell C2 Highlight the cells down to D5 Your spreadsheet should look like this one:

As you can see, the cells C2 to D5 are highlighted. This includes our new interest rate values, and our function in cell D2. We can now create a Table. So do this: • • •

From the Excel menu bar, click on Data From the drop down menu, click on Table A small dialogue box appears like the one below:

There's not much to fill in on that dialogue box. But the term "Input Cell" does need explaining. The Input Cell is the cell that you want Excel to substitute. The thing we want Excel to substitute is the interest rate. We had our interest rate in cell B3. So we use this as the Input Cell. We wanted Excel to fill downwards, down a column. So we need the second text box on the dialogue box "Column input cell". If we were filling across in rows, we would use the "Row input cell" text box. • • • • •

So click in side the "Column input cell" text box Enter B3 as the Input Cell Click the OK button Excel will work out the new monthly totals for you You should have the same values as in the image below:

So at an interest rate of 9 percent, we would be paying back just over two hundred and seven pounds. Excel has worked out that an interest rate of 8 percent will lower the monthly payments to just over two hundred and two pounds. At a 6 percent interest rate, the payments will be just over one hundred and ninety three pounds. If you click inside cells D3, D4 and D5, then look at the formula bar, you will see this: {=TABLE(,B3)} That's Excel's way of telling you that a Table has been created. We'll do one more Table. This time we'll use a more simple formula than PMT, and we'll use Rows instead of Columns. We'll do that in the next part.

In the previous part, we saw how to use an Excel Table to work out interest rates. The Table was used to fill out several values at once. We'll now see how to use a Table to examine profir margins. Here's the scenario: You take 250 items to a Car Boot sale. Your Unique Selling point is this: All items only a pound each! Except you feel that one pound might be a bit expensive, especially for the goods you're selling! What you want to know is how much profit will you make if you reduce your prices to ninety pence each, how much if you reduce to 80 pence each, and how much if you reduce to 70 pence each. Assume that everything gets sold. To start creating your Table, construct a spreadsheet like the one below. Make sure that you start on a new sheet.

Number is the number of items we're taking to the Car Boot sale. Price will be used in our formula. Reductions is the amount we want to reduce by. Sales is the amount of profit we expect to make. Our formula will go in cell B4. So click inside cell B4 and enter the formula: = B1 * B2 The answer is, of course, 250. But the answer is in cell B4 for a reason. This is because when you want Excel to recalculate a Table in Rows, the formula must be inserted one Column to the Left of your first new value, and then one Row down. Our first new value is going in cell C3. So one column to the left takes us to the B column. One row down is Row 4. So the formula goes in cell B4. Next, click inside cell B3 and highlight to cell E4. Your spreadsheet should now look like the one below:

Excel is going to use your formula in cell B4. It will then look at the new values on Row 3 (not counting the zero), and then insert the new totals starting in cell C4.

So with the data highlighted, click on Data from the menu bar From the drop down list, click on Tables The Table dialogue box appears

We want Excel to fill our new values in Row 4, so we need the Row input cell. But what is the Input Cell this time? Ask yourself what you are trying to work out, and what you want Excel to recalculate. You want to work out the new prices. So you need the old price. The old price of each item was one pound, and you used this in the formula. • • • •

So on the Table dialogue box, click inside the "Row input cell" text box Enter your cell reference Click OK when you are done If you got it right, your spreadsheet will look like the one below:

So, if you don't reduce your prices, you will make two hundred and fifty pounds. If you reduce the price of each item to seventy pence, you will make one hundred and seventy five pounds. Tables are a useful tool when you want to analyze values that can change. Before moving on, try this exercise.

Exercise Amend the table above. This time, instead of reducing your prices, you are going to increase the price to £1.25 per item, and increase the number of items you sell. You currently sell 250 items. Work out the new profits if you sell 300 items, 350 items, and 400 items.

In the next part, we'll move on to Scenarios. Scenarios are similar to Tables, in that you are asking "What if"? "What if I change this value, or that amount? What will the spreadsheet look like then?" The difference is that you can create a number of different scenarios and save them. An example of a scenario you might want to create is this: You've worked out the family budget, and find you have barely enough left for a night out. The question is, what can you cut back on to give yourself more spending money? We'll create a spreadsheet to tackle that problem, and you'll see how scenarios work. To get started, there's a new spreadsheet for you to download: Download the Spreadsheet for this section When you open up the spreadsheet, you'll see there is only 46 pounds left at the end of every month. And the cost of the car hasn't even been included yet! Clearly, some cutbacks have to be made. With a scenario, we can switch between our different budgets and see which one we like best. The best way to see how a scenario works is to construct one yourself.

Scenarios To create your scenario, do the following: • • •

From the Excel menu bar, click on Tools From the drop down menu, click on Scenarios The Scenario Manager dialogue box pops up

• •

We want to create a new Scenario, so click the Add button The Add Scenario dialogue box pops up

•

Click inside the Scenario Name text box and type Original Budget

You now need to tell Excel which cells will be changing. Although nothing will be changing in this scenario (because it's our original), we still need to specify which cells will be changing. We want to reduce the Food bill, the Clothes Bill, and the Phone bill. •

So click inside the Changing Cells text box

• •

Click back on your spreadsheet and highlight cells B7 to B9 The "marching ants" will appear around your selected area, and your dialogue box will look like this one:

• • • •

Click the OK button at the bottom Excel will prompt you for the Scenario Values We don't want the values on the original to change, so just click OK You are taken back to the Scenario Manager dialogue box, and you'll see the scenario you have just created displayed.

We now need to create another Scenario, so that we can switch between the original budget and the new one. So click the Add button to add a new scenario. • • • •

When you get the Add Scenario dialogue box back up again, click inside the Scenario Name text box and type Budget Two. The Changing cells text box should read B7:B9. These are the cells we want to change, so leave them alone Click the OK button at the bottom of the Add Scenario dialogue box You will be taken to the Scenario Values dialogue box. It looks like the one below:

The values in the Text Boxes are the ones from cells B7, B8, and B9. Click inside each text box and type in a different value. The 280 spent on food can be changed to something like 180. Take 50 off the clothes bill. And take 20 off the phone bill. Your dialogue box will then look like this one:

Click the OK button when you're done. You'll be taken back to the Scenario Manager. And this is where the fun starts. To view a scenario, click on one from the list. Then click the Show button. In the image below, Budget Two has been selected:

After you click the Show button, have a look at your spreadsheet. The figures should have changed. Select Original Budget from the list, then click the Show button. Your spreadsheet should show the original figures. Click the Close button on the dialogue box when you're done. To view your two scenarios again, just click on Tools > Scenarios. This will bring up the Scenario Manager again. So a Scenario offers you different ways to view a set of figures, and allows you to switch between them quite easily.

Scenario Reports Another thing you can do with a scenario is produce a report. This is quite easy. To produce a report of your scenarios, do the following: • • • • •

Click on Tools from the menu bar From the drop-down menu, click on Scenarios The Scenario Manager dialogue box appears Click on the Summary button The following dialogue box appears

• • • • • • • •

To change the result cells, click on your spreadsheet Click individual cells by holding down the Ctrl key on your keyboard, and then clicking inside a cell with your left mouse button So hold down the Ctrl key and click on cell D3 (income) Hold down the Ctrl key and click on cell B12 Hold down the Ctrl key and click on cell D13 If you make a mistake and want to get rid of a highlighted cell, just click inside it again with the Ctrl key held down Click OK when you're done Excel will produce the Summary for you. It will look something like the one below:

All right, it's not terribly easy to read, but it looks pretty! Perhaps it will be enough to convince our family to change their ways. Unlikely, but a nice diagram never hurts! In the next part, we'll take a look at Goal Seek, what it is and how to use it. We're not going go too deeply into Goal Seek, because it can get quite complicated. We'll stick with a basic outline, and an example, of just what it is.

Goal Seek

Goal Seek is used when you know what answer you want, but don't know the exact figure to input for that answer. For example, you're quite certain that 8 multiplied by something equals 56. You just not sure what that missing number is. Is it 8 multiplied by 6? Or Is it 8 multiplied by 7? Goal Seek will tell you the answer. We'll test that example out right now. So start a new spreadsheet, and create one the same as in the image below:

Before you can use Goal Seek, Excel needs certain things from you. First it needs some sort of formula to work with. In the image above we have the simple formula =B1 * B2. We've put this in cell B3. But the answer is wrong for us. We had a Goal of 56 (8 times something). We want to know which number you have to multiply 8 by in order to get the answer 56. We tried 8 times 6, and that gave the answer of 48. So we have to try again. Instead of us puzzling the answer out, we can let Goal Seek handle it. So do the following: • • •

From the Excel menu bar, click on Tools From the drop down menu, click on Goal Seek A dialogue box pops up like the one below:

The dialogue box needs a little explaining. "Set cell" is the answer you're looking for, this is the Goal. Set cell needs a formula or function to work with. Our formula is in cell B3, so if your "Set cell" text box does not say B3, click inside it and type B3. "To Value" is the actual answer you're looking for. With "Set cell", you're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of 56 for our formula. So click inside the "To Value" text box and type 56.

"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer you want. In our formula we have an 8 and a 6. Clearly, the 6 is the number that has to go. So the cell that needs to change is B2. So go ahead and enter B2 in the "By Changing Cell" text box. Your dialogue box should now look like this:

Click OK when your dialogue box looks like the one above. Excel will then Set the cell B3 to the Value of 56, and change the figure in cell B2. You'll also get a dialogue box like the one below:

Click OK on the dialogue box. Your new spreadsheet will look like this one:

So Goal Seek has given us the answer we wanted: it is 7 that when times by 8 equals 56.

Increase your profits with Goal Seek

To give you a more practical example of what Goal Seek does, consider this problem. You have a business that generates 25 thousand pounds worth of profit. You currently sell 1000 items at 25 pounds each. You want to increase your profit to 35 thousand pounds. Assume that you're still going to sell 1000 items. By how much does the price of each item have to increase by in order to generate the new profit total? We'll work it out together using Goal Seek. And then you can have a try yourself with an exercise. First, here's a new spreadsheet for you to download: Download the Goal Seek Spreadsheet When you open the spreadsheet, you'll notice that the Current Sales Figures and the Future Sales Figures are exactly the same. The formula in Cells B4 and E4 is = B2 * B3 We can use Goal Seek to solve our problem. What we want to know is, What should be the new Price Per Item in order to generate Profits of 35 thousand? • • •

So, from the Excel menu bar, click on Tools From the drop-down menu, click Goal Seek The Goal Seek dialogue box appears

This time, our formula is in cell E4. So we want to Set the cell to the cell that has our formula. So type E4 into the "Set cell" text box. The "To Value" text box will hold our new Profits. The Goal we are aiming for is 35 thousand. So type in 35000 in the "To value" text box. The cell we want to change is the Price Per item figure. So in the "By changing cell" text box, type in E3. Click OK when you're done. Excel will give you this dialogue box:

The dialogue box is telling you that Goal Seek has found a solution. Click OK. Your spreadsheet will already have changed. The new Future Sales Figures will be displayed. Your spreadsheet should look something like the one below:

Goal Seek has given us the answer of 35 pounds. So the cost of each item has to increase by 10 pounds if we want a profit of 35 thousand. And now it's your turn. Try this exercise.

Exercise You've had a board meeting. It has been decided that the Price Per Item will remain the same - 25 pounds. But you still want to generate Profits of 35 thousand. Use Goal seek to work out how many Items will now have to be sold in order to reach your target.

And that's it for this section. In the next section, we'll take a look at Absolutes versus Relative cell referencing, Named Ranges and Pivot Tables. There's a lot to get through! There are two important spreadsheet concepts we haven't yet explored: absolute cell references, and relative cell references. We'll explore these two ideas now. First, absolute cell references.

Relative cell references Take a look at the spreadsheet below (create it for yourself):

As you can see, it's quite simple. All we are doing is adding together the numbers in cells A1 and A2. We then put the answer in cell B2. But suppose we wanted to copy the formula in cell B2 to cell B3. Let's try it and see what happens. • • • • • • • •

Click inside cell B2 Then click once with your right mouse button A menu pops up With your left mouse button, click Copy Now click inside cell B3 Click once with your right mouse button When the menu pops up, click on Paste Your spreadsheet will look like the one below:

Excel has done something rather odd. It has given us the answer 25! The sum 20 + 25 clearly does not equal 25, so what's going on? Well, look inside the formula bar. The formula is now reading = A2 + A3. Yet that was not the formula we pasted from cell B2. We copied and pasted the formula = A1 + A2. So why did Excel copy and paste the wrong formula? The answer is that we used a Relative cell reference for B2. We have been using Relative cell references throughout this book. This is Excel's default, and it works like this when you copy a formula: The formula is = A1 + A2. The answer is in cell B2. When copying the formula to cell B3 Excel will note that the cells for the formula start UP one Row, and LEFT one column. When you paste the formula somewhere else, Excel will not paste the formula, but paste this UP one then Left one. So starting at Cell B3, which is where we're pasting to, go UP one Row. This takes you to row 2. Then go one column Left. This takes you to Column A. So the start for the new formula is cell A2. Your formula will now read = A2

+ A3. As there is nothing in cell A3, the formula is really = 25 + 0. Which gives the answer 25. And that's Relative cell referencing.

Absolute cell references If you want to keep a reference to the original cells, A1 and A2, you need to use Absolute cell references. Absolute cell referencing is done with dollar signs. Change your formula in cell B2 to this: = $A$1 + $A$2 Then copy and paste the new formula to cell B3. You should now get the answer you were looking for: 45. Your spreadsheet will look like the one below:

To recap, then: • •

When you want to copy and paste formulas, use Absolute cell references To use Absolute referencing, place a dollar sign before the Column letter and a dollar sign before the Row number (You can mix absolute and relative cell references, but we won't go into that.)

In the next part of this section, we'll take a look at Named Ranges. Instead of using something like = SUM(A2:A5) to add up a column of numbers, you can replace the A2:A5 part of the function with a more descriptive name. This is known as a Named Range. Examine the spreadsheet below:

In the Results Row, cell B5 is a result of adding up cells B2 to B4. The formula used is just this: =Sum(B2:B4) Now examine the same spreadsheet, but with a Named Range used:

This time, cell B5 doesn't have in it the formula = Sum(B2:B4). As you can see, it has =SUM(Monthly_Totals). This is the label from B1. We have created a Named Range. The formula in cell B5 is now more descriptive. We can tell at a glance what it is we're adding up. Excel has replaced the B2:B4 part with the name we gave it. Behind the scenes, though, we're still adding up the numbers in cells B2 to B4. Excel has just hidden the cell references behind our descriptive name. You'll now see how to create your own Named Ranges.

Creating a Named Range Start a new spreadsheet, and enter the same data as in the image below:

Make sure you have the same formula in cell B5 =Sum(B2:B4). We're going to create a Named Range, and then pop it in cell B5. To create a Named Range then, do the following: • • • •

Highlight the B column, from B2 to B4 (Don't include the formula when you're highlighting. Just highlight the same cells as the ones in the function) From the menu bar, click on Insert From the drop-down menu select Name A sub menu appears like the one below:

There's a two-step process involved with setting up a Named Range. The fist thing to do is Define the name. You then Apply the name to your formula. • •

So select Define from the sub menu The Define Name dialogue box pops up. This one:

With the B column highlighted, Excel will use your label at the top as the name (Monthly_Totals for us). But you can change it if you want. Notice the narrow text box at the bottom, "Refers to". This is showing the highlighted cells. Click OK on the dialogue box. You are returned to your spreadsheet. Nothing will happen. This is because we have haven't done step two of the two-step process - Applying the name. To apply your new name to a formula, do this: • • • • •

Click inside the cell where your formula is, B5 in our case Click on Insert from the menu bar From the drop down menu, select Name From the sub menu that appears, click on Apply A dialogue box will appear showing a list of all the Names you have set up

You'll have only one Name set up , so there's not much to do except click the OK button. When you click OK, Excel should adapt your formula in cell B5. If you've done it right, your spreadsheet should look like the one below:

As you can see, the cell B5 now reads =SUM(Monthly_Totals). Excel has hidden the cell references behind the Name we defined. If you didn't get the Name, but instead got the error message below, then there are a couple of things you can do:

Before you click Insert > Name > Define, make sure you highlight only the same cells as the ones in your formula. Make sure that there is a formula in the cell B5, and that it says = SUM(B2:B4)

We can enter another Named Range for our Monthly Tax column, column C. Here's a break down of the Two-Step process involved with setting up a Named Range. Step One - Define the range of cells • • • • •

Enter your Formula (In cell C5, enter = SUM(C2:C4)) Highlight the same cells that are going in your formula From the menu bar, click Insert > Name > Define From the Define Name dialogue box, either accept the name Excel gives you, or type your own name for the range of cells you're going to define Click OK

Step Two - Apply the Name • • • • •

Click inside the cell where the formula is (cell C5 for us) From the menu bar, click Insert > Name > Apply From the Apply Name dialogue box, click on the Name you want to use Click OK Excel will insert the name, if it can, and hide your cell references behind the name

So go ahead and insert a Named Range for cell C5. When you're finished, the spreadsheet should look like the one below:

As you can see, cell C5 no longer reads = Sum(C2:C4). Instead, we have a Named Range in cell C5.

In the next part, we'll see how to use the Named Ranges you have just set up. In the previous part, you saw how to define and apply Named Ranges. In this part, you'll learn how to use the Named Ranges you have just set up.

Using Named Ranges The spreadsheet you have created so far looks like this:

We have a Named Range in Cell B5 Called Monthly_Totals, and a Named Range in Cell C5 Called Monthly_Tax. We can use the Named Ranges to deduct the Tax from the Monthly Totals. But we need to set up two new Named Ranges. One for cell B5 and one for cell C5. You might question the need to set up two more Ranges, on the grounds that we have just set up two Named Ranges in cell B5 and C5. Strictly speaking, we don't need the new names. To deduct the Tax from the Monthly Total using Named Ranges we could just do this: =Sum(Monthly_Totals) - SUM(Monthly_Tax) And it would work. Excel would take one from the other. (The following, however, would not: = Monthly_Totals - Monthly_Tax.) But if we set up two more Named Ranges, we can just use the single totals, instead of telling Excel to use a Function to add them up first. So, to define two new Names, do the following: • • • • • •

Click inside cell B5 From the menu bar, click on Insert > Name > Define When the dialogue box appears, click inside the top text box and type in Monthly_Result. (Don't forget the Underscore!) Click OK Click inside cell C5 and do the same. This time, though, type in Tax_Result as the Name. Your Define Name dialogue box will then look like this one:

Click the OK button when you're done. Now that we have the Names defined, we can enter a formula and Apply the names into the formula. First, amend your spreadsheet so that it looks like this one:

As you can see, a new label has been added, along with the formula in cell B7: = B5 C5. Now that we have a formula we can replace the cell references B5 and C5 with the names we set up. So click on B7 and do this: • • • • • • •

Click on Insert > Name > Apply The Apply Names dialogue box appears Click Monthly_Result select it Click on Tax_Result to select it Click the OK button Excel replaces your cell references with the Names Your spreadsheet will then look like this one:

The cell references in cell B7 have been replaced with our Named Ranges, making the information easier to read. And that's enough of Named Ranges. We'll move on to Pivot Tables. When inputting data into a spreadsheet, often you will find yourself having to type the same data into cells. That's where Data Validation comes in handy. Instead of typing the same thing over and over again, you can turn the cells into drop-down lists. That way, you could just quickly select an item from the list and move on to the next entry. For example, suppose you had a column heading called "Student Grade". Even though there are only two grades available, Pass and More Work Needed, it can become quite laborious having to type either one or the other. Spelling mistakes will become increasingly more likely the more times you have to enter the grades. Much better to have a drop down list where you could select the grade. And no more spelling mistakes!

Data Validation We'll now construct a spreadsheet with drop-down lists. The one we're going to construct takes us back to the classroom and our students from previous section. So start a new spreadsheet, and format it to match the one below:

Before we can turn the cells in an entire column into drop down lists, we need some data to go in the lists. So starting at cell F2, add the following to your spreadsheet:

The data in columns F, G and H will be going into our lists. We can then hide this data so that it's not messing up our spreadsheet. You'll see how to do this later. But we can now turn Columns A, B and C into lists. To turn the cells in an entire Column into a list, do the following: • • • •

Highlight the whole of Column A by clicking on the letter A at the top of the column With the whole of Column A highlighted, click on Data from the Excel menu bar From the drop down menu, click Validation The following dialogue box appears:

• • • •

Make sure the Settings tab strip is selected Click the black down arrow just to the right of “Allow: Any Value” A drop down list appears Choose List

•

A Source box appears on the dialogue box

The Source is the data that is going into your list. So you need to select the cells with the students in them. To select the cells with the students in them, do this: •

Click on the icon to the right of the Source text box:

• • • • •

When you click the icon, the dialogue box contracts Click inside cell F2 on your spreadsheet Hold down your left mouse button, and drag to cell F9 Then click on the icon again to expand the dialogue box The images below show the highlighting and expanded process in action

Click the icon and highlight F2 to F9

Click the icon again to expand the dialogue box

•

If you have done it all correctly, your dialogue box will now look like this one:

So the Validation criteria should be: "Allow List", and the Source should be = $F$2:$F$9. Click OK when your dialogue box reads the same as the one above. The cells in you entire A column will now be drop down lists. Test it out. Click on cell A3, for example. It should look like this:

If you click the black down arrow, you should see your list of students. Like the one below:

Click on any student in the list. The student appears in cell A3. Click on another cell in column A and try it again. I'm sure you'll agree that it's much better than having to type out a student's name over and over again.

Except we have a slight problem. If you click inside cell A1 you'll see that this too has a drop down list. Clearly we don't want this to happen for our heading. To turn off the list in cell A1, do the following: • • • • •

Click inside cell A1 From the Excel menu bar, click on Data From the drop down menu, click Validation From the Settings tab strip of the dialogue box, change "Allow List" to "Allow Any Value" Click OK

Time for you to try it alone. Change the Subject and Grade columns into drop down list, and then turn off the list for the headings cells B1 and C1. The drop down lists for the Subject and Grade columns should look like these when you're done:

In the next part, you'll learn how to display error messages. That way, you can control what data your users are allowed to enter. n the previous part, you created drop down lists so that you can simply select the data you want, rather than typing it in all the time. In this part, we'll display error messages if a user types in too much data.

Displaying Error Messages We can add Validation to the Comments field in our spreadsheet. We'll restrict the amount of text that can go in the Comments field to a maximum of 25 characters. The comments field you should have is this one:

So highlight the Comments column and bring up the Data Validation dialogue box again (Click Data > Validation from the menu). This time, in the Allow drop down box select "Text Length". A few more fields will appear on the dialogue box:

The Between in the Data text box is exactly what we're looking for. But we need to enter values for the Minimum and Maximum fields. These are the Minimum and Maximum text lengths that can put in any cell in the comments column. We'll restrict the length to 25 characters, just so you can see how it works. • • • • •

So click inside the Minimum text box Enter the number 1 Click inside the Maximum text box Enter the number 25 Don't click OK just yet

We can add an error message, too, so that we can tell users what they did wrong. To add an error message, do the following: • •

Click on the Error Alert tab strip of the Data Validation dialogue box The dialogue box will change to this:

If your dialogue box doesn't look like the one above, make sure there is a tick in the box at the top "Show error alert after invalid data is entered." There are three different Styles you can choose from for your error message. Click the black down arrow just below Style to see them. Click on each one in turn and see what happens. Then set it back to Stop. • • •

Click inside the Title text box and type "Too many characters" Click inside the Error message text area and type "The maximum number of characters for this field is 25 - please try again" Your dialogue box will then look like this:

Click OK when you've finished. To test it out, click inside cell E2 and type the following: Steven can do a lot better than this. Then press the return key on your keyboard. Your error message should pop up and look like this one:

The error alert gives the user the changes to either Cancel the data already input, or to Retry. The only thing spoiling the look of our spreadsheet are the cells starting at F2, the ones from our list. We can hide all that data from prying eyes.

Hiding Data in a Spreadsheet To hide data on a spreadsheet, do the following:

• • • • •

Highlight the data you want to hide (in our case, highlight F2 to H9) From the Excel menu bar, click on Format From the drop down menu, select Column From the sub menu that appears, click Hide The three columns with the list data in them will disappear

Now try this. Click anywhere on the D column. Click Format > Column > Unhide. What happens? Nothing happened, right? So why didn't Excel Unhide your columns? It's because you did not tell Excel which columns you wanted to Unhide. To do that, highlight columns E and I. Then click Format > Column > Unhide. Your data should return. If you don't want anyone else to Unhide your data, you can Protect the worksheet from unwanted changes. Just click on Tools > Protection > Protect Worksheet. A dialogue box appears. Select your options, and then click OK.

And that's it for our brief look at Forms. You can do a whole lot more with Forms, but that enough for us. We can move on to Web Integration.

Excel: Filter a single column based on 3 or more criteria Question: I have an Excel spreadsheet and I would like to filter more than 2 types of criteria from a single column. The custom AutoFilter only allows for up to 2 at a time. How can filter a single column based on 3 or more criteria?

Answer: You can filter a single column based on 3 or more criteria by applying an advanced filter. To do this, open your Excel spreadsheet so that the data you wish to filter is visible.

In a blank column, add the column heading and the values that you'd like to filter on. In this example, we want to filter the Order ID column to display the orders 10248, 10251, and 10253. We've typed these values into column F.

Highlight the data that you wish to filter. We've highlighted columns A to D. Under the Data menu, select Filter > Advanced Filter.

When the Advanced Filter window appears, the List range field should display the data that you highlighted in the previous step. Next, select the Criteria range. These are the filter values. In our example, we've entered the filter values into cells F1 to F4. (ie: Order IDs 10248, 10251, and 10253). Then click on the OK button.

Now when you return to your spreadsheet, your data should be filtered. (Note: The row numbers on the left will appear in blue when your data has been filtered.)

Excel: Filter 2 columns based on 3 or more criteria

Question: I have an Excel spreadsheet and I would like to filter more than 3 types of criteria from more than 1 column. The custom auto filter only allows for 2 types at a time. How can I filter more than 1 column based on 3 or more criteria?

Answer: You can filter multiple columns based on 3 or more criteria by applying an advanced filter. To do this, open your Excel spreadsheet so that the data you wish to filter is visible.

In a blank column, add the column heading and the values that you'd like to filter on. In this example, we want to filter on both Order ID and Quantity. The filter should display all records with the following conditions: • • •

Order ID of 10248 and quantity > 5 Order ID of 10251 and quantity >=7 Order ID of 10253 and quantity < 40

We've entered these values into columns F and G.

Highlight the data that you wish to filter. We've highlighted columns A to D. Under the Data menu, select Filter > Advanced Filter.

When the Advanced Filter window appears, the List range field should display the data that you highlighted in the previous step. Next, select the Criteria range. These are the filter values. In our example, we've entered the filter values into cells F1 to G4. Then click on the OK button.

Now when you return to your spreadsheet, your data should be filtered. (Note: The row numbers on the left will appear in blue when your data has been filtered.)

Excel: Apply a filter to an existing filter Question: After I've sorted my data and I'm in 'filter mode' (the numbers are highlighted in blue), is there a way that I can actually save that filtered data and filter it again (perhaps in 'auto filter') without losing my original filter? (ie. If I've filtered data lines 1-5 and the final results show lines 1,2 and 5 , can I filter again and not have lines 3 and 4 show up?)

Answer: To apply one filter and then another filter without losing the original filter, select a cell within the data that you wish to filter. Under the Data menu, select Filter > AutoFilter.

Next, apply your first filter. In this example, we've selected to filter by "Boston Crab Meat" as the product.

In our example, we see only 3 rows where the product is Boston Crab Meat.

Next, apply your second filter. We've selected an Order ID of 10267. This second filter takes the filtered data and applies a second filter to this data.

Now our spreadsheet will display only those rows whose product is Boston Crab Meat and whose Order ID is 10267.

Excel: Copy filtered data to a new worksheet Question: I've filtered my data in Excel and I want to copy the filtered data to another worksheet. How do I do this?

Answer: In newer versions of Excel, the copy of filtered data does not copy the hidden rows. Because of this, we will provide a few scenarios on how to copy filtered data.

Solution #1 - Copy only visible filtered data Our first solution demonstrates how to copy only the visible filtered data to a new worksheet. To do this, open your Excel spreadsheet and select the filtered data. Press Ctrl-C to copy the data.

Next, select the worksheet where you'd like to paste the data. Press Ctrl-V to paste the data into the new worksheet.

The data that is pasted will only be the visible data from the filter. The rows that were hidden by the filter will not be pasted.

Solution #2 - Copy visible and hidden filtered data Unfortunately, you can not copy and paste any of the hidden filtered data with the standard copy and paste functionality inherent in Excel. If you wish to copy all of the data, you'll have to remove the filter before copying. To do this, select a cell in one of the filtered rows. Under the Data menu, select Filter > Show All.

Now all of the rows should be visible. Select all of the data and press Ctrl-C.

Next, select the worksheet where you'd like to paste the data. Press Ctrl-V to paste the data into the new worksheet.

Now all of the rows should appear in the pasted data. You can now re-apply your filters.

Excel: Sort data in alphabetical order Question: In Excel, I'm trying to put a chart in alphabetical order. There are 4 columns and over 2,000+ rows of information. Only the first column needs to be placed in alphabetical order. How do I do this?

Answer: To apply a sort in Excel, highlight the data that you wish to sort.

Under the Data menu, select Sort.

When the Sort window appears, select the columns that you wish to sort by. In our example, we are going to sort the Product column in ascending order. Click on the OK button.

Now when you return to the spreadsheet, the data should be sorted.

Spreadsheet Password Recovery Can't remember your password? These vendors sell password recovery software. I haven't tried any of them. • • • • • • • • • • • •

AccessData Crak Software Elcomsoft Erlandsen Data Consulting Excel Key Excel Password Remover (free) Fast Data Recovery Lab Intertek MSOfPass97 Password Crackers Inc. PWD Service PW Finder

Non-English Links Non-English Excel Links • • • • • • • • • • • • • • • • • • • •

Add-In World German - add-ins and newsletter from SmartTools Publishing AO-Excel Hebrew Aquira's Excel-VBA Memorandum Japanese DataSpectrum Czech De Web-Expert Dutch Desarrollo de Soluciones con Microsoft Excel 97 / 2000 Spanish Die MacHero Excel Homepage German Excelabo Frency Excelformeln - ÜBER UNS German Excel - arkusz dla kaŜdego Polish Excel Downloads French Excel Inside German Excel pour les statistiques, la qualité et l'intelligence artificielle Frency Excel VBA Access French Excel Expert Training Thai Excel Mailing List Portuguese Excel Skill Training Hall Japanese Excel przyjazny Tobie Polish ExcelSpecialist Dutch Excel Turkiye Turkish

• • • • • • • • • • • • • • • • • • • • • • •

Exemples de programmation Excel VBA French FAQ du forum Microsoft.Public.Fr.Excel French Fernando Cinquegrani Italian (great charts!) Free Add-Ins For Excel German Hans Herber German Herzlich Willkommen German Idema Design Dutch Jiri Cihar, Dataspectrum Czech J-M Lambert French Kent's Excelsida Swedish Laurent Longre - Compléments Excel 97-2000 French Mabat3 Hebrew Monika Weber's Office Help Desk German Mixa Excel FAQ Russian Mondial Portuguese netprof.ch German Office Assistant French Ole P.'s Excel Tips Norwegian PolyKromy English and French Poradnik Excela / Excel's Vademecum Polish Rang-x Spanish Rente in Excel Dutch Rolf's Page German and English

• • • • • •

Schmitti's Page German Statistik mit Excel German Technical Calculations Czech and English Uno's LIbrary Korean Web-Expert Dutch XL-Dennis Swedish

Excel Consultant Links Excel Consultants These links take you to the web sites of consultants who work with Excel. Listing a consultant here does not constitute an endorsement. They are listed for your convenience • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •

Ability Software Consultants AbleOwl Excel Specialists Accounting Advisors Application Professionals Application Solutions International Arclight Consulting Ask Mr. Excel Consulting Base2 Consulting Blueswift Solutions CMS Consulting David Gerster Excel Modeling Services Decision Models DotXLS Excel Consulting ExcelAid EXCELent Consulting Service John F. Lacher's Excel Help Page LCL Resources LJM Analysis Ltd JKP Application Development Services The Macro Factory MaKro Consulting Market Fact Systems Melf Computing MG Moreira Michael Payne Software Services Office Automation Ltd. OzGrid Business Applications Payne Consulting Group Pearson Software Consulting Services Rodney Carr's Home Page Safari Software SpeedySum Spinnaker Software Solutions Spreadsheet Developers Spreadsheet Experts Spreadsheet Style

Excel Publication Links Excel-Related Publications This section contains links to a variety of publications (printed and electronic) that deal with Excel. • •

• • • • •

•

Excel Experts E-Letter (EEE) By David Hager Able Owl Excel Specialists Monthly Excel magazine, seminars, add-ins, support, and bespoke Excel systems ExcelTips A weekly newsletter published by Vital News Informant Office VBA resources Inside Microsoft Excel A newsletter Spreadsheet User Journal Biannual publication Spreadsheets in Education An electronic journal devoted to the publication of high quality articles concerned with studies of the role that spreadsheets can play in education Woody's Office Watch An email newsletter from Woody Leonard

Excel-Related Product Links Product Links The links below lead to sites of third-party developers and shareware authors who provide Excel-related products. Listing a product here does not constitute an endorsement. They are listed for your convenience. • • • •

• • • • • • •

• • • • • •

2000 Tools Home of DateSpy - Identify Y2K risks. 4Tops Access and Office products Abstract Pro Lease Manager v 2.0 Accmatrix Accounting A fully integrated, multi user client/server accounting software product that uses Excel as a front-end ActiCalc An Excel-compatible desktop calculator. Adarus Business Plan: An add-in and template for preparing business plans. Advanced Numerical Methods Abacus, an engineering function library AlgoLab Raster to Vector Conversion Toolkit Alyuda NeuroSignal XL - neural network add-in for stock prediction alphaWorks ExcelAccessor bean suite (access Excel workbooks from Java) Alyuda Alyuda Forecaster XL. An add-in for forecasting and data analysis based on neural networks Analyse-It Statistical add-in Analyzer Ltd AnalyzerXL: Stock market technical analysis tool Angoss Software Data mining solutions Applied Decision Analysis DPL: Excel-compatible decision analysis software Bentz Engineering Interactive 3D Dynamic Data Viewer Beside Software Import Wizard for Excel

• • • • • • • • • • • • • • • • • • • • • • •

BitStar BrainSheet: Data analysis using neural network techniques Brandywine Software LLC Makers of Spreadsheet xlNavigator and Analyze with xlEquations Builder Place Construction industry costing and estimating Business Calculators Add-in that automatically crates Excel user-defined functions Business Functions Function library for financial planning and analysis Byg Software Byg Utilities, Excel Auditor Calibrex Finance tools for Excel Chemeng Software Fluid Flow spreadsheet templates, for use by chemical and mechanical engineers ChemSheet Thermodynamic calculations The Chicago Innovative Business Alliance Visual Baler spreadsheet compiler Cimware Software to recover damaged MS Office files Communication Enhancement Products Distributed Spreadsheet: Planning, budgeting, forecasting tool CompuQuest SpreadMsg Lite: monitor stocks, etc. Compressia Spreadsheet file compression utilities Concept Data Recover data from corrupt Excel files. CPR International Home construction cost estimator CustomGuide Computer courseware for Excel (and other products) D.A. Martin Software SPC Orchestra: Statistical Process Control DataTrend Software Graph digitizing software for Excel Data Shaping Solutions Real-time strategy design, backtesting, and implementation Data Instrument Group DIG: Data analysis tool DataShenk Excel training DateWise, Ltd. Abater for Excel

• • • • • • • • •

• • • • • • • • • • • • •

Decision Models Ltd. FastExcel Decision Support Systems Decision-tree add-in (shareware) Decisioneering Crystal Ball forecasting and risk analysis add-in. Decision Systems Inc Web-based management reporting using Excel Derivicom Financial Add-ins for Excel: FinOptions XL and FinExotics XL Dimensions 5 Miner3D for Excel DirtSoft Construction Constuction management and personal productivity templates DJI Computer Solutions Tax Assistant, Golf Tracker, Checkbook, Handicap Manager, Task Manger DPS Payroll Service A home page about spreadsheets and programming Dust Free Solutions Spreadsheet Composer Dwipiform Excel accounting add-ins Easy Spreadsheet Solutions Various spreadsheet products Easy Spreadsheets Various spreadsheet applications Edco Software IRS Form 2210 tax calculator. IRS Sched D-1 printer and Wash Sale finder Entisoft Excel products for programmers Edwin's Power Tools Shareware for Windows and Mac, and other Excel files EM7 Makers of Portal - an ActiveX charting component that works with Excel. Engineering Software Services Unit Conversion Add-In Enhanced Datasystems Worksheet auditing, circular reference tracing, and chart manipulation tools Ericson Enterprises Excel tips and products ExcelApple File recovery ExcelEverywhere Converts an Excel spreadsheet into a calculating and interactive web page

• • • • • • • • • • • • • • • • • • • • • • •

ExcelFIX A data recovery utility for corrupt Excel files Excel Business Tools Business templates Excel Online Training Training ExcelQuant Investment software for Excel Excess Consulting Synkronizer: compare and synchronize spreadsheets Extentech ExtenXLS - Java/XLS toolkit Family Finances Excel template for family finances Forgram Excel add-in to extract and import XML data Formula Software Excel Compare: Compare Excel files and spreadsheets Frontline Systems The company that developed Solver. They sell more powerful versions. GreenTeck Spreadsheets for business and personal use Hoadley's Option Tools Option pricing and trading profitability tools Hotsheets A few files to download Hycones IT AIRA: A data-mining tool for Excel. InterGral.net Math Easy for Excel IntelligentApps Create reports against Microsoft Analysis Services and local cub files. Inventure Data browser for Excel Ironman Software IMSToolpak: Structural engineering add-in (and other files) Jabsoft Financial Advisor J&E Research Opti-Calc, Opti-Utility, Opti-Exotic KD Calc Converts Excel into Java or .NET Knowledge Dynamics KDCalc: Compiles an Excel spreadsheet into Java Legend Software Quixl: Add-in to collect stock quotes and perform technical analysis

• • • • • • • • • • • • • • • • • • • • • • •

LiveWebs Excel to HTML publishing tool Lumenaut Statistical and decision tree add-Ins Macro Systems Download the Spreadsheet Assistant, plus several Excel add-ins Mark's Maths and Excel Pages Tutorials and downloads MathTools MatriXL -- a library of matrix math functions for Excel MECA Consulting Mechanical, structural, and civil engineering spreadsheets Mgmoreia ForecastingTools Graph and ExcelSmartTools Microsoft Excel Specialists Microsoft Excel Magazine, spreadsheet user group provides seminars and support Millenium Software Balance sheet and financial ratio templates minder3D.com Converts Excel data into an interactive 3D information space Miricle Solutions Risk-management software MITCalc Engineering, industrial, and technical calculations MoneyCops Excel plug-ins NET-SMNP Report generator that produces Excel files Neuro Neural Network add-in Neutronz Technologies Neutronz Express: Adds a handy command pane to Excel 2000. Numerical Algorithms Group Statistical add-in ODBCFace Gives Excel read/write access to ODBC and OLE/DB compliant databases Office-Addins From Metiz Business Ltd. Office Certification Information From OfficeCert.com Operis Operis Analysis Kit for auditing spreadsheet models Oraxcel Excel add-in that lets you query Oracle databases. Password Solutions Recover lost passwords from Excel, Word, and other files

• • • • • • •

• • • • • • • • • • • • • • •

PATools Excel add-in tools, plus free downloads Pi Blue Software Optimizer add-ins Planning Templates Financial planning calculator workbooks Plumtree Gadget Framework for Microsoft Excel Polycert, Ltd. Fitter add-in PortfolioScience RiskAPI Excel Add-In PracticalTaxSolutions Spreadsheets for U.S. corporations that merge data with IRS tax forms (5471, 1120L, 1120PC, 990, 990T, 990PF). PrecisionCalc High precision math functions Production-Scheduling Production scheduling spreadsheet Professional Engineering Free Beam System Design Excel application ProjeX Add-in for Gantt and PERT charts Queuing Theory Software Workbooks to analyze a wide range of queuing models Quadrant Monte Carlo risk analysis add-in Quick Links Toolbar QuietSoft XLL Add-ins for mathematics, engineering, and astronomy QuoteIN An add-in for streaming and snapshot quotes Reaction Design Process Engineer's Toolbox Refinate Add-in that facilitates working with lists ReportMaker Excel report maker RSD Associates Various statistical add-ins. Ruhrmann & Linneberg GbR Excel converters for MS Works 3/4/2000 and Framework III/IV Sarkett & Associates Option Wizard & BackTest Wizard

•

SETI League Free templates for analyzing SETI data.

•

Shale Software Mapping software Sheetware Products for spreadsheet users SM Software Delphi and ActiveX libraries and components for working with Excel files Software Illustrated MapLand: mapping Software for Excel Sonalysts, Inc. Fuzzy Query for Excel Spreadsheet Detective Auditing tools for Excel Spheresoft Spheresoft Modeler and Spheresoft Highlighter add-ins Spreadsheet Auditing Excel auditing application Spicer-Baer Associates PerpetualBudget spreadsheet networking application Spinnaker Software Solutions List/Dbase add-ins, Alerts, and Stats. Spredgar Software EDGAR financial ratios and graphs STAAP An album program for stamp collectors SyTech Automated report generation TA-LIB Technical Analysis Library for Excel Use programming languages like C++, JAVA, and Visual Basic to analyze stocks and commodities TechHackers Inc. @nalyst financial functions add-in Tidestone Technologies Formula One: Spreadsheets on the Web using Java and ActiveX UDA Excel templates for estimating construction costs Universal Class Online course: Excel Basics for Beginners u-turns Body Fitness Profile Test Vertex^42 Niche Excel Solutions Vertigraph, Inc. BidPoint XL: Excel add-in for digitizers (construction management)

• • • • • • • • • • • • •

• • • • • • •

• • • • • • • • •

• • • •

Visual Numerics Smart Table: Converts spreadsheets to Java without programming Vista Excel report builder VORSIM A framework for building spreadsheet simulation models Windmill Software Ltd. Software to acquire data from serial instruments and send it to Excel Wingate Financial Engineering iLib: Financial add-in library xlCBT A CBT authoring tool for Excel XL Consulting GmbH Synkronizer for Excel xlCubed Reporting within Excel against Microsoft Analysis Services XLMiner Techniques for classification, prediction, affinity analysis, data exploration, and data reduction. xlSTAT A statistical add-in (English and French). xlStockCharts Create stock charts from data on the Net XYFIT Curve-fitting ZCalc Templates and functions for tax and estate planning

General Excel Links General Excel Information The links below are to sites of general interest. This symbol indicates a link that is particularly useful. • • • • • • • • • • • • • • • • • •

Andrew's Excel Tips An Excel Blog Andy Pope's Excel Information Excel downloads and charting examples Ask Mr. Excel Excel Tips and solutions ASSUME The Association of Statistics Specialists Using Microsoft Excel Barrie Davidson's Excel Page VBA and formula examples, and help via email Bella Online Excel tips and links Bob Houghton Spreadsheet information and downloads Contextures Excel tips and techniques CSA-NET Help and downloads Daily Dose of Excel An Excel blog DataPig Downloads and tutorials Epsen Gaarder Haug Option pricing calculators Daniel Lamarche Excel 2000 Without Pain (tutorials) Excel Advisor Tips and tutorials Excel By Example Screen cam videos Excel Experts Club A Yahoo club for high-level Excel users Excel Help Page John F. Lacher's site Excel Logic Page By Aaron Blood

• • • • • • • • • • • • • • • • • • • • • • •

Excel Maniacs Excel tips and discussion board ExcelTip Excel tips and tricks ExWorks Excel tips and downloads Financial Modeling Information regarding financial modeling The Fortress Joseph Moosman's home page. Has some interesting Excel files. Fred Cumming's Excel Page Excel files to download GantTool Create Gantt charts. Free download. Google Excel Search Add-In From Ron de Bruin Help Talk Online Discussion for Excel and other Office products. John's Finance Page Lots of Excel files to download Laurent Longre Free Excel XLL add-ins (statistical and general purpose) Laroux Virus Remover For Excel 97 (Windows or Mac) Linda's Computer Stop Fun stuff to do in Excel Magic Box Web Tutor for Microsoft Excel Online Excel tutorial Mathtools.net Technical computing portal for scientists and engineers MeadInKent Help with Excel functions, graphs and spreadsheet checking Mike's Excel and Maths Page Downloads MM's Little Spreadsheet Helpers Excel workbooks for mathematics and physics My Excel Pages By David McRitchie National Marine Mammal Laboratory Excel Geometry Functions, written in VBA NEKO to Excel VBA Games Neville Hunt Producing Statistical Tables Using Excel OneOnOne "Quick and Dirty" task guides

• •

• • • • •

•

• •

• •

OzGrid Business Applications Excel tips, tricks, downloads, newsletter Peter Noneley Excel Function Dictionary. Download a workbook that contains more than 150 examples of Excel's functions. Resources For MS Excel Excel product features, support, Knowledge Base article links. Richard Knights Computer modeling examples using Excel Sam Raheb's Microsoft Excel Web Site Lots of useful downloads Simtools and Formlist add-ins Freeware statistical and auditing tools. Spreadsheet Autopublisher An experimental service for teachers and others who would like to publish their spreadsheet models on the Web, but do not have their own Web sites Spreadsheets in Education Erich Neuwirth's site, which includes mathematical, scientific, and statistical applications. Tattsoft Spreadsheet Productions Files to download TOPS Free Excel downloads (time series analysis, financial market tools, numerical computation). Virtual Help Desk Resources and Help for Excel WinSite Excel files for downloading

Developer Links Links of Interest to Developers The links below are of use to developers. Some of these sites also include information relevant to non-developers. This symbol indicates a link that is particularly useful. •

•

• • • • •

• •

• • • • • • •

AddReg AddReg - a utility that will automatically load your Excel add-in in as part of the installation process. Alan Beban Array Functions: 23 procedures for manipulating arrays and ranges Alan's Excel Goodies VBA code examples, formula and custom function examples Ananda's Access Coach eHome A resource for Excel and Access users Anthony's VBA Page VBA examples, mostly financial and statistical applications Apps Pro Rob Bovey's Excel utilities Astronomical Functions (VBA) By Keith Burnett Beyond Technology Development Corp. Developer tips and examples Business Modeling Solutions Ltd. Stephen Bullen's site contains great examples of various Excel programming and charting techniques. Client Applications JavaScript and DHTML-based spreadsheet DarDevel Software Code examples and downloads David McRitchie's Excel Pages Excel information and tips. Decision Models Ltd. Excel calculation secrets Ed's Excel Page Some interesting charting and VBA examples by Ed Ferrero. Exceldev.com Examples Excel a la Carte Dave Steppan's VBA tips and tricks.

• • • • • • • • • • • • • • • • • • • • • • •

Excel VBA Access Pierre Leclerc's site Financial Modeling Examples From Simon Benninga. Fred Cumming's Excel Page Excel files to download High Precision Calculations in Excel Richard Huxtable - Calculations to potentially thousands of decimal places Igor Kolupaev's Page VBA examples and Q&A Jon Peltier's Excel Page Good information on charts Julian's Macro Tips Excel macro Tips for beginners in Excel Programming New Guidelines for Writing Spreadsheets by John F. Raffensperger Nico Sterk's Excel Pages Excel examples, primarily related to mathematics Ole P.'s Excel Tips Excel and VBA tips and files OzGrid Business Applications Tips, tricks, and code Pearson Software Consulting A wealth of useful information Perl and Excel A tutorial Rob's Excel VBA Pages Code examples and free utilities from Rob Bruce Ron de Bruin Code for sending mail from Excel Spreadsheet Web By R. Smits. VBA examples Straight-Line Depreciation Function By Brier Pty Limited Tommy Flynn Excel/VBA programming examples Tushar Mehta Consulting Tutorials and add-ins VBA Tutor Tutorials for Word, Excel, and Access VBA Pro VBA help, especially for Excel VBA-Programmer VBA code snippets Virtual Help Desk Resources and Help for Excel

• • • •

Visual Basic Users VB and VBA resource center William Whooper Excel tips WriteExcel Create Excel files with Perl XcelFiles Things that you may have thought impossible

Microsoft Links Excel Information From Microsoft Microsoft's web site contains a wealth of information, but it is probably one of the worst sites in the world in terms of ease of navigation. To make matters worse, the site is constantly changing so links to specific pages are often broken. This symbol indicates a link that is particularly useful. • • • • • • • • • • • •

Drivers and Downloads For all versions of Excel Excel FAQS Covers Excel 95, 97, and 2000 Excel for Windows Home Page Excel's official home page Excel Newsgroups Information about Microsoft's Excel-related newsgroups Excel Support Options How to get help from Microsoft Microsoft Office Update Downloads and other information about Excel and the other MS Office apps Microsoft Knowledge Base Access the latest information about Excel, searchable by keywords. Microsoft Office Developer Forum (Excel Section) For VBA programmers Microsoft Office 2000 Resource Kit A complete book, accessible online. Microsoft Office 97 Visual Basic Programmer's Guide A complete book, accessible online. Training and Certification Information Information about a variety of training resources VBA 101 An introduction to Visual Basic for Applications

Web Integration and Excel What you're going to do in this section is to download data from the internet, and put it straight into an Excel worksheet. We have a page on our website that is set up for you to download straight into a spreadsheet, and we'll give you the address shortly. An example of why you would want to download data from a website is this. You are salesperson out in the field, visiting clients in their homes or offices. You have a laptop that can connect to the internet. There is a spreadsheet on your laptop. The spreadsheet includes lots of data about the products you sell. One piece of data is the prices of each item. Your company is rather ferocious on prices, and it changes them all the time. The question is, how can you, as a salesperson in the field, keep up to date with the changing prices? One answer is to use an Excel Web Query. The company will update the prices on the website. You can then run a Web Query to download the latest changes into your Excel spreadsheet. That way the clients get the new prices "Hot of the Presses". We'll see how to run a Web Query soon. But you need a connection to the internet before you can run a Web Query. In other words, you need to be online. (However, if you know some HTML coding, you can construct a table in a web page that is on your own computer. Then you point the Web Query to the web page on your computer, rather than a web page on the internet. Don't worry if you don't know any HTML coding - it's not necessary for this section.) But don't go online just yet. Read a few more pages first. You create a web query in Excel when you want to pull data from an internet page and pop it into a spreadsheet. You can retrieve text from a web page, data in tables, and data that is preformatted on the web page. To see how all this works, do the following: • • • • • • •

Open a new Excel spreadsheet Connect to the internet, if you're not already When you are connected, click inside cell A1 of your spreadsheet From the Excel menu bar, click on Data From the drop down menu, select Get External Data A sub menu appears From the sub menu, click on New Web Query

•

A dialogue box pops up like the one below:

There are three sections to the dialogue box. The first section is where you type in the address of the internet page that you are trying to pop into Excel; the second section is where you specify which part of the web page you want to insert into your spreadsheet; and the third section sounds a bit technical, but basically you are choosing the type of formatting to use: do you want to keep the colour scheme, or strip it bare? In the first section, you need to type in the name of our internet page. The internet page contains a table with some product information and some prices. By all means, load it into your browser and have a look at it. But first, you need the address. So: •

In the text box right at the top of the dialogue box, type in this internet address. Make sure you spell it exactly as it is below: http://www.homeandlearn.co.uk/ME/webquery1.htm

• • •

When you have typed the address in the text box, Select "The entire page" from section 2 of the dialogue box. From section 3 of the dialogue box, select "None" Click OK when you're done

When you click the OK button, another dialogue pops up. This time your are asked where in your spreadsheet you want to put the data. You only need to specify the starting cell. The dialogue box looks like this:

The dialogue box is already set up to put your data into the spreadsheet starting at cell A1. But you could change that, if you wanted. Cell A1 is fine for us, so just click the OK button. Excel will now look for the internet address you typed. When it finds the web page, it will then take the text and the contents from the table and put them into your spreadsheet. If the Web Query seems to be taking too long, you can Refresh it. To do that, click on View > Toolbars > External Data. A toolbar pops up on your page. Click the Refresh icon, as shown below:

If everything goes well, you should have a spreadsheet that looks like the following one:

As you can see, Excel has imported the data from a web page on the internet into a spreadsheet. You now have a way to get the latest prices from Head Office!

In the next part, we'll import a web page with better formatting. The problem with the spreadsheet in the previous section is that it looks a little dull. This second web page you're going to download into Excel is little more colourful. So do the following: • • •

Click on Sheet 2 of your spreadsheet, and click inside cell A1 Run a Web Query just like you did in the previous section: Data > Get External Data > New Web Query This time, when the dialogue box pops up, type in this new address: http://www.homeandlearn.co.uk/ME/webquery2.htm

• • • • •

In section 2 of the dialogue box, select "The entire page" In section 3 of the dialogue box, select "Full HTML formatting" Click the OK button Click OK in the next dialogue box that asks you where you want to put the data (=$A$1) Excel will place the data from the new web page into sheet 2 of your spreadsheet

Although the table itself might look nice and bright, that text heading looks chopped of. Your heading might look like the following one:

The reason it's chopped of is because the cells on row 1 are not formatted properly. If you highlight row 1, from A1 to G1, and then click Format > Cells from the menu bar, you can merge all those cells, and centre the text (Alignment tab strip, centre, tick box at bottom "Merge Cells".)

The image below shows a tidied up version of the downloaded spreadsheet (you can do all the cell formatting before you download):

And that's all there is to the basics of downloading data from the internet and importing it into an Excel worksheet.

In the next part, we take a look at adding hyperlinks to your spreadsheets. If you have a lot of different worksheets and want a quick way to jump between them, you can use Hyperlinks. Hyperlinks are those underlined bits of text on the internet that lead to web pages To get a feel for how they work in Excel, start a new spreadsheet and do the following: • • • •

Click inside cell A1 of the spreadsheet On the Excel menu bar, click on Insert From the drop down menu, click on Hyperlink The dialogue box below will appear:

The icons on the left of the dialogue box are all the places you can link to: Existing file or web page, This document, New document, or an Email address. We're going to place links in specific cells of three different worksheets. So: • • •

Locate the text area under "Or select a place in this document" If there is a Plus sign ( + ) next to "Cell Reference", click on the plus sign to expand it. You should see the same choices as in the image above Click on "Sheet 3

Now that we have a selected a page to link to, we can specify that Excel goes to a cell of our choice: •

In the text box below "Type the cell reference", type in C10

Our A1 cell has nothing in it. So to insert some text for our link, do this: • • •

Click inside the "Text to display" text box Type in "Sheet 3" Your dialogue box should now look like the one below:

We can add a screen tip as well. Click the button in the top right of the dialogue box "Screen Tip". Another dialogue box pops up, like the one below:

• • • • • •

Click inside the text box and type "Jump to Sheet3 Cell C10" (without the quote marks.) Click Ok to return to the main dialogue box On the main dialogue box, click OK again A hyperlink will be inserted into cell A1 of Sheet 1 Move your mouse pointer over cell A1 Your spreadsheet will look like the one below:

Click on the link and see what happens. Now it's your turn to try it.

Exercise In cell C10 of the spreadsheet, insert a Hyperlink to take you to cell B10 of Sheet 2. Add a suitable Screen Tip. In cell B10 of sheet 2, insert a Hyperlink to take you to cell A1 of Sheet 1. Add a suitable Screen Tip.

And that ends this somewhat brief introduction to Web integration. There a whole lot more you can do with Excel and an internet connection: Upload data from a spreadsheet to a web page; Save a spreadsheet as a web page; Publish a spreadsheet to web page that others can interact with; Hold an Online discussion. In fact, an entire book could be written on Excel and the Internet! To wrap up this Excel course, the final section details a few Extra things you can do with Excel. It starts with something called Object Linking and Embedding. Spreadsheets are inserted into Word documents using something called Object Linking and Embedding. Object Linking and Embedding (or OLE), can be a complicated subject, but basically it's used when you want to insert something from one programme into another programme. The example you're going to see will embed a spreadsheet chart into Microsoft Word. You have two choices when you want to embed something from Excel into another programme - to use linking, or embedding. If you use linking, you can update the data in Excel and see the changes in the other programme; if you use embedding, any changes you make to Excel will not show up in the other programme. First, here's how to create a Linked Object. The two programmes used will be Excel and Word. So if you have Microsoft Word and Excel, open up both programmes.

In Excel, create the following spreadsheet:

All the spreadsheet does is multiply whatever is in cell E1 by 12. The answer goes in cell E3. (This example is not terribly functional: you wouldn't really want to use OLE with this particular spreadsheet. But it's easy to create, and will serve as an example of how to use Linking.) • • • • • • •

Once you have created your spreadsheet, highlight from A1 to E3 With your data highlighted, click on Edit form the menu bar From the drop down menu, click on Copy Switch to Microsoft Word In Microsoft Word, click on Edit from the menu bar From the drop down menu, click on Paste Special A dialogue box pops up in Word like the one below:

• •

In the main list box, click on Microsoft Excel Worksheet Object Of the two Option buttons on the left, Paste and Paste Link, choose Paste Link by clicking on it. Click the OK button at the top

•

Word now goes to work and embeds your spreadsheet into the Word processed document. Because we chose Paste Link, we will be able to view any updates made from Excel. The Word document should look like the one below:

To see that it really does update in Word, do this: • • • • • • •

Go back to your Excel spreadsheet Click inside cell E1 Type in the number 7 Press the Return key on your keyboard The number 84 should appear in cell E3 Switch back to Microsoft Word and view the results The Word document will now look like this

As you can see, the numbers from the Excel spreadsheet are now in the Word document. The link worked! If you don't want Word updating the embedded object, you would select Paste instead of Paste Link from the Paste Special dialogue box. Everything else is the same. Things like Charts and Pivot Tables are the ones usually embedded into a Word document, all ready for the company presentation.

In the next part, we'll see how to reference formulas and data on other worksheets You can put the answer to a formula on a different worksheet. It doesn't have to go into the same sheet you're working on. For example, think back to the exam marks spreadsheet we did earlier. We had a spreadsheet that had the average scores for the students. Below that we had each exam mark as a letter of the alphabet: A, B, C, etc. The spreadsheet we created was this one below:

Instead of putting all those Grades on the same Sheet, we could have put them on Sheet2 of the Workbook. If you want to put a formula on a different work sheet, you have to set a reference to the Sheet that contains the numbers going into the formula. An example might clear things up. • • • • • •

Start a new spreadsheet In cell A1 of Sheet1 enter the number 4 In cell A2 of Sheet1 enter the number 5 Click on Sheet2 at the bottom of the spreadsheet Click inside cell A1 of Sheet2 Click inside the formula bar and enter this formula: =Sheet1!A1 + A2

• •

Press the Return key on your keyboard Excel should give you the answer 4

Four plus five is clearly not four, so what went wrong? Well examine the way we set a reference to the sheet that held our numbers. It was this: =Sheet1!A1 When you are setting a reference to a different worksheet, you need the name of the Sheet. Then you type an exclamation mark (or a bang as it's sometimes known). You then type the cell that you are referring to. So in our formula, we were saying to Excel "Find the worksheet that has the name Sheet1. Now find Cell A1 on the workbook called Sheet1." However, the whole formula was this: =Sheet1!A1 + A2 For the second part of the formula, we have + A2. But we haven't told Excel the name of the Sheet we're referring to. We've just put A2. If the cell reference in the formula doesn't have the name of a worksheet in front of it, Excel will assume you mean the current worksheet. Our current worksheet is Sheet2. Cell A2 of Sheet2 is empty. So the formula adds up the number 4 from cell A1 on Sheet1 and the blank cell A2 on Sheet2. Which gets an answer of 4. To solve the problem, tell Excel that you want to take the number from cell A2 on Sheet1. So change your formula to this: =Sheet1!A1 + Sheet1!A2 This time, Excel will give you the correct answer of 9. The main point to bear in mind when referencing data that is on another worksheet is this: Give Excel the name of the worksheet followed by a Bang!

In the next part, you'll learn how to Insert Drawing objects into your Spreadsheets. You can draw on your spreadsheet. You can even put a picture on a spreadsheet. Sometimes a drawing can help to illustrate the data or function on the spreadsheet. The drawing in the spreadsheet below is illustrating what the function does:

We'll see how to draw and format the triangle shape now. (Don't worry about all that Cosine stuff. But we'll give you the formula, just in case you want to duplicate the spreadsheet exactly.) Before you can draw shapes on your spreadsheet, you need to display the Drawing Toolbar. To display the Drawing Toolbar, do this: • • •

From the Excel menu bar, click on View From the drop down menu, select Toolbars From the sub menu that appears, click on Drawing

The Drawing Toolbar should appear, but it might be at the bottom. It looks like this:

Click on AutoShapes to see the kind of shapes you can add to Excel. To get the triangle, do this: • • • •

Click on AutoShapes from the Drawing Toolbar A menu pops up Move your mouse up to Basic Shapes A box of shapes appears:

• • • • • •

Click on Right Triangle, as in the image above Move your mouse on to the spreadsheet. The mouse pointer will now be in the shape of a thin cross Hold down your left mouse button Keep the left mouse button held down and drag Let go of the mouse button when you're happy with the size of your triangle You spreadsheet will look something like this one:

The white squares around the shape are the sizing handles. To resize your triangle, move your mouse over a white square. The pointer will change to the shape of a double-headed arrow. Hold down the left mouse button and drag. To move the triangle somewhere else, move your mouse pointer somewhere in the middle of the shape. When your mouse pointer turns into a arrow-headed cross, hold down the left mouse button and drag the shape somewhere else. Our triangle is facing the wrong way. To turn it round, do this: • • • •

Click on the triangle with the right mouse button A menu appears Click on “Format AutoShape”, but click with the left mouse button A dialogue box appears

• • •

Select the Size tab strip Click inside the Rotation textbox and change it to 270 degrees Click OK

To add some colour to your shape, again Right click on the shape and select Format AutoShape. This time, select the Colours and Lines tab strip. Click the down arrow of the "Fill Color" box. Choose a colour for your shape.

To add the letter B to your triangle, you need to add a text box on top of it. So, locate the Text Box tool on the Drawing Toolbar. Click on it with your left mouse button. Position your mouse at the bottom of your triangle. Hold down the left mouse button and drag out a text box. Your spreadsheet will look something like this one:

Click inside the text box and type the letter B. Now click on one of the edges of the text box, somewhere near a white square. But click with your Right mouse button. A menu will pop up. When the "Format Text Box" dialogue box appears, select the Colours and Lines tab strip. Under "Line Color" set it to No Line, as in the image below:

In the "Fill Color" box, set it to No Fill. Then click the OK button. Your final shape will then look like this:

If you want to add the Cosine formula to the spreadsheet, it was this: =DEGREES(COS(E4 / E5)) Cell E4 will then be the input cell for the Hypotenuse, and cell E5 will be the input cell for the Adjacent.

In the final part, you'll see how to insert an image into a spreadsheet.

Excel’s VLOOKUP Function: How to Use It and How to Nest It by Linda Johnson, MOS The VLOOKUP function is a handy one to know when you want Excel to lookup a value in one place and insert it in another. For example, let’s say you have a list of all of your customers on a sheet named “Accounts” and an invoice on another sheet named “Invoice”. When you type in their account number on the Invoice, you want Excel to fill in the name of the customer and their address (and this information is included for all customers on the Accounts sheet). A VLOOKUP will do this for you. Make a small sample workbook to try this out. Name Sheet One “Invoice” and name Sheet Two “Accounts”. On the Accounts sheet, put three columns of data. Column A would be Account Numbers, Column B would be Customer Name, and Column C would be Address. Add at least five pretend customers, so you have enough to play with. On the Invoice sheet, just add these five column headings in cells A1:E1 – Date, Product Ordered, Account #, Customer Name, Address -but don’t put any data in there yet. (In reality, this sheet would be an actual invoice which included sections for you to add ordering info for any products they buy, etc. But for this example, let’s keep it simple.) Now, before we try the VLOOKUP, the best thing to do is name the range of data that includes the info you want to pull over from the Accounts sheet. You can do VLOOKUPs without naming the range, but then you MUST be sure to use absolute cell references. So, I find naming the range a much easier way to do it. Highlight all the data on the Accounts sheet and name it Customers (don’t include the column headings in the named range – just the data). If you don’t know how to name ranges, read this TechTrax article I wrote on how to do that. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=281 Now that you have your data and have named the range, let’s look at building a simple VLOOKUP formula. Assume we will be typing account numbers into cell C2 and wanting the customer’s name and address to be filled into D2 and E2.

The best way to learn new formulas is to use the Insert Function button . In Excel 97 and 2000, it's a button on your Standard Toolbar. In Excel 2002/2003, it's on your Formula Bar. On the Invoice sheet, click into cell D2 and click on the fx button.

(The picture above shows how this box looks in Excel 97/2000. In 2002/2003 it’s slightly different, but I think you can figure it out.) If you know what type of function you are looking for, you can select the category and all the functions within that category will be listed. However, if you don't know what category you need, you can select "All" in the category list and all of Excel's functions will be listed. Notice as you click on any function name, Excel displays a description of what that function does below the boxes. For this exercise, select the "Lookup & Reference" category and scroll down to select the VLOOKUP function. Note that it tells you that this function “searches for a value in the leftmost column of a table and returns a value from the same row in that table based on what column in that row you specify.” Click OK. Once you click OK, you will get the wizard which helps you with your VLOOKUP formula. Now, because we will be typing an Account # in cell C2, that is the value we must put in the first box of this wizard which will tell Excel to look for whatever is in C2 in the leftmost column of our lookup table (which we created on the Accounts sheet and named "Customers"). So, enter C2 into the top box. Click into the next box where it says "Table_array". Notice at the bottom of this box, it tells you what each box you click inside needs. This is where we need to identify our table so Excel knows where to look. So, in this box, simply type Customers. (If you didn’t name the range, you will have to put the absolute reference including the sheet name here … this is why it’s easier to just name the range.) Click into the third box. This one wants to know the number of the column we want returned. Remember that what you entered in the first box in this wizard must ALWAYS be in the first column of your lookup table. So, in our table, the Account # is in the first column and the Customer Name is in the second column. Since the customer name is

what we want to put here, just type a 2 to let Excel know we want what is in the second column. Notice the last box is labeled "Range_lookup" and it is the only label that is not bold. Whenever a label in this wizard is not bold, that means this "argument" of the function is not required. However, if you do not enter anything in this box, Excel will apply the default. If you read the instructions at the bottom of this box, you will see that the default for this box is "true" which will find the "closest match", whereas "false" will find an "exact match". Since we want an exact match, type false in this box. This is what it should look like if you have entered all the info correctly:

Click OK and you will see that cell D2 now shows #N/A, which simply means that there is no value yet in C2, so the information is "not available". Look in your formula bar and you will see the formula is =VLOOKUP(C2,Customers,2,FALSE). As you get more used to using functions, you won't have to use the wizard as much if you take the time to look at the formulas and start to understand how they work. Now click into cell E2 and add a VLOOKUP formula which will find the Address in our table. The formula will be exactly the same, except the "Col_index_num" will be 3 instead of 2 because we want to return the address, which is in the third column of our table. Once you have added this, you should see another #N/A in cell E2. The formula will be =VLOOKUP(C2,Customers,3,FALSE). NOTE: All other information in the second VLOOKUP formula will be exactly the same as the first one. We are still looking for the value that will be placed in C2. We are still looking in the table named Customers. And we still want false for an exact match. The ONLY thing that is different is we are now going to pull the information from Column 3 instead of 2.

Move to cell C2 and type in one of the Account numbers you have in your Accounts sheet and you will see Excel fills in the Customer Name and Address for that account number However, if you type a number that does not exist on your Accounts sheet, Excel will leave the #N/A because the information for that number is "not available To see a VLOOKUP formula in action, go to this page at my website and view this interactive formula: http://www.personal-computer-tutor.com/vlookup.htm Now that we've gotten more comfortable with VLOOKUP, let's look at nesting formulas, one inside another. We can use the VLOOKUP formula we just made as a starter. Let's say we don't want to see that nasty old #N/A every time one of our VLOOKUP formulas refers to an empty cell or value that is not in our lookup table. What we need to do is tell Excel to show us the result of the VLOOKUP only IF it does not return a #NA result and, otherwise, just leave the cell empty. So we need to use three different functions in one formula: VLOOKUP, IF, and ISNA. Remember how we made an IF statement. =IF(, ,). If you don’t remember, read these two TechTrax articles I wrote: Intro to IF Statements: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=225 Nesting IF Statements: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242 So, our criteria is IF the VLOOKUP returns #N/A, give me an empty cell, but if it doesn't, give me the result of the VLOOKUP. Let's try it. First we need to think about each of the three formulas and what they must include. We already have our VLOOKUP formula and we already know what an IF function must include, so the only one we need to learn is the ISNA function. All the ISNA function does is return a value of true or false. True if the cell has a #N/A error and false if it doesn't. So, since an IF statement wants to know if something is true or false, these two functions work very well together. Click into cell D2 in your Invoice sheet where your first VLOOKUP formula is. The thing you need to understand is that when you nest formulas within formulas, Excel performs the operation dictated by the deepest nested formula first, then works its way outward. So, enter the following formula into cell D2, then enter an account number in cell C2 that does not exist on your Accounts sheet and you will see that the #N/A error does not show.

=IF(ISNA(VLOOKUP(C2,Customers,2,FALSE)),"",(VLOOKUP(C2,Customers,2,FALSE)))

NOTE: Be sure to include all the proper commas and parentheses. Remember that all opening parentheses must have closing parentheses or Excel will give you an error message. Remember that Excel performs the deepest nested function first, so looking at the formula above, you will see: • •

•

The first thing Excel does is perform the first VLOOKUP (since that's the one that is nested deepest) Then it performs the ISNA function on the result of the VLOOKUP to see if the VLOOKUP gave a #N/A error or not. The ISNA function returns a value of true if it sees the #N/A error and a value of false if it doesn't Then Excel performs the IF function on the result of the ISNA function. If it sees a "true", it returns nothing (which is what the "" tells it). If it is "false", it returns the result of the VLOOKUP function, which is what the last VLOOKUP function is telling it to do if the value is false.

Getting the hang of it? Excel has other lookup and reference formulas that you can explore using the Insert Function box. For example, HLOOKUP is similar to VLOOKUP, but it looks for Horizontally laid out data, instead of Vertical. More information on different lookup and reference formulas is available in this article from my ezine, ABC ~ All ‘Bout Computers:

Excel VLOOKUP Say you want to create an invoice that will automatically add the prices when you enter the item numbers. Create your invoice on Sheet One, and create a table of your item numbers and their corresponding prices on Sheet Two. In Sheet One you will add a VLookup formula that will pull the prices from Sheet Two when you enter the item number on Sheet One. Try it here. Change the item numbers in Cells A4 through A6 and see how the price changes in Cells C4 trough C6. This interactive worksheet works in Internet Explorer only. Those using some other browser click here to see a screenshot of the spreadsheet if you do not see the Interactive version displayed. (You won't see it if you don't have Excel installed on your computer either. Or, if you are using a version of Excel other than 2002, the clsid value in the registry is different and you can download this registry fix to cure that. Just extract it to your desktop and double click it, then you can delete it.) Excel

1 2 3 4 5 6 7 8 9 10 11

A INVOICE

B

C

ITEM # 101 105 104

QTY. 2 4 6

PRICE $14.00 $70.00 $192.00

D

E TABLE

F

ITEM # 100 101 102 103 104 105

DESCRIPTION Hat Scarf Socks Jewelry Shirt Tie

The formula in Cell C4 is =VLOOKUP(A4,$E$4:$G$8,3,FALSE)*B4. Double click in Cells C4, C5, and C6 to see the formulas. If you copy this formula down, note that A4 and B4 will change to A5 and B5, then A6 and B6, and so on down the column. Note that if your table is on Sheet Two, your absolute range (in this case, $E$4:$G$8) would have to include the sheet name of Sheet Two and would reflect the range of cells your table includes on that sheet and would look something like Sheet2!$A$1:$F$15).

Note the "3" in the formula reflects the column number in the table where the desired information is located. If you change this to a "2", the result would show the information in the "Description" column instead of the "Price" column. This is a simple VLookup formula. Try adding other columns to the table on Sheet Two and adjusting the VLookup so it pulls from different columns.

Using VLOOKUP, HLOOKUP, INDEX, and MATCH in Excel to interrogate data tables Lookup tables are fantastically useful things in Excel. I remember when someone showed me for the first time how to build a data table and perform some simple lookups on it. For the first time, I began to realise just how powerful Excel could be in the right hands. In this article, I'll talk about what a data table is, why you might find it useful to have one, and why and how you might want to interrogate it. We'll end with a trick or two involving some nested formulae, but by the time we get there, it will all make sense. First of all, then, what's a data table? Well, there's one shown below:

You'll notice that some related data is set out in columns, each with a heading in bold at the top. So many other functions in Excel can use those headings intelligently, that I have always made it a habit to put them in. Data tables like this have so many uses it's difficult to know where to start: phone number lists, CD collections, customer lists, the uses are endless. But sooner or later, you're going to want to extract data from such a list, perhaps for a mail merge or to fill in an invoice automatically, say. Probably the best way of learning about the LOOKUP functions is to ask some questions and use formulae to answer them. For instance, look again at the data table above. If I want to know Barbara's age, I can use a command called VLOOKUP. It's called VLOOKUP because it looks up the data in a table, based

on finding the key in a Vertical list. The formula I'd use here is: =VLOOKUP("Barbara",A2:C6,2,FALSE) Of course, this will return the number 23, which is Barbara's age. Let's look briefly at the format of the function. The first argument is the piece of data I want to look up (what I call the 'key') in the first column. (This must always be in the first column, but later on I'll show you how to find values based on a key in other columns instead.) The second argument is the range which contains the table, in this case A2:C6. I'd normally have named this range, but you don't have to. The third argument is the column number I want to return the value from. Looking at the table again, the first column contains names, the second ages and the third locations. Clearly, if I want to find Barbara's location, I'd put a 3 in this argument, but since I want to know her age, I've used 2. The fourth argument, the FALSE, is supposed to be an optional argument, but my advice is ALWAYS to use it. What it means is, "Don't rely on the list of items in the first column of the table being in alphanumeric order - check every one of them until you get an exact match." Leaving it out is like saying, "The first column of the lookup table is definitely in alphanumeric order - if you get past the search phrase in the list and it's not there, don't keep looking, just use the nearest match". This would speed up your sheet if there were a lot of huge data tables in it, but here it's not even worth thinking about. It's good practice always to include "FALSE" just in case it trips you up one day. Well, of course there's an HLOOKUP to match the VLOOKUP, too. You'd use this when your table is oriented left to right, rather than top to bottom. Here is an example of what I mean:

I can use the HLOOKUP function to find what date Debbie's birthday falls in like this: =HLOOKUP("Debbie",F2:J4,3,FALSE) This formula returns April. The arguments work the same way as for VLOOKUP, except of course that the third argument refers to the row number rather than the

column number. On the whole, it's much better to organise your data tables vertically, as in the first example, because a horizontal data table cannot be sorted or filtered by Excel as easily as a vertical one, but there are times when it has to be horizontal for some reason. The MATCH formula appears at first to do something quite unremarkable. Let's have another look at our vertical data table:

MATCH allows me to find the position of an item within a range. For instance, if I want to know how far down in the list of names Charlie is, I can use this formula: =MATCH("Charlie",A2:A6,0) The return from a MATCH function is always a number, in this case the number 3 because Charlie is the third entry in the range A2:A6. The zero at the end there is a bit like the 'FALSE' in VLOOKUP and HLOOKUP - optional but risky to omit. When set at zero, it says: "Make it an exact match". Ninety-nine times in a hundred, that's exactly what I want. INDEX is the opposite of MATCH in a way. It tells you what the nth value in a range is. For instance, who is in position 5 in the list? Easy: =INDEX(A2:A6,5) This returns the name 'Elvis' because he is the fifth item in the range A2:A6. Like INDEX, the MATCH function doesn't seem to do anything out of the ordinary so far. But the real power of these functions only becomes apparent when you combine them. Look at the vertical data table again and consider how you'd find out who lived in Belfast. The two LOOKUP formulas are no use, because the key value is not in the first column. Remember, VLOOKUP can only read values to the right of the key and HLOOKUP can only read values below the key. But, look at it another way. I can break the question down into two smaller ones,

like this: 1. How far down the 'locations' list does 'Belfast' appear? 2. Whose name is in the 'names' list exactly as far down? Put in those terms, it is pretty clear. The first question can be answered, of course, by using a MATCH function: = MATCH("Belfast",C2:C6,0) This will tell us that Belfast is number 4 in the list, so we can put the number 4 into an INDEX formula: =INDEX(A2:A6,4) Of course, this formula will return the name 'Debbie', which answers the original question. But in the same way that original question is made up of two sub questions, so we can turn our two formulas into a single one, like this: =INDEX(A2:A6,MATCH("Belfast",C2:C6,0)) Again, this gives us the answer 'Debbie'. Of course, we've looked so far at simple tables, and it's been far quicker just to look at our table and answer the questions than to sit down and write formulae! However, there will be times when the data tables are huge, or when you want Excel to work things out for itself and get on with things. At times like those, you'll find that the LOOKUP functions of Excel are an invaluable part of your toolkit.

Excel Functions: Financial functions and formulae Excel contains many financial functions and this page includes examples of PMT, PPMT, FV and IPMT. These calculate compound interest, loan repayments and outstanding principal amounts. Many other functions are only available if you load special add-ins after installing the Analysis Toolpak (see Help | Financial Functions). Also included on this page are some formulae with which values can be calculated without using built in functions. Would you like to learn more?

Business Analysis with Microsoft Excel The following example shows the results of various financial calculations based upon both Annual and Monthly values. Note that the cells C4:D8 have all been given names ending with '_A' (annual) or '_M' (monthly). The names have been used in the formulae rather than the cell references.

10 =Amt_A*((1+Rate_A)^Per_A)

compound interest @ 6% over 5 years

11 =(Rate_A*100*Amt_A*((1+Rate_A)^Per_A)) /

a formula for regular loan

(100*(((1+Rate_A)^Per_A)-1))

repayments

12 =PMT(Rate_M,Per_M,Amt_M)

a function for regular loan repayments

13 =PPMT(Rate_A,Now_A,Per_A,Amt_A)

the principal repaid during year 2 of a loan

14 =IPMT(Rate_M,Now_M,Per_M,Amt_M)

the interest element of the current repayment

15 =FV(Rate_M,Per_M,Sav_M)

the value in 60 months of saving £100pm @ 0.5% (or 6%pa)

16

=(Amt_M*((1+Rate_M)^Now_M)) - ((-D12/Rate_M)*(((1+Rate_M)^Now_M)-1))

the amount still outstanding after 24 months. Note that cell D12=monthly repayment

Formulae used in the example

These formulae all assume a steady rate of interest. This is unlikely over a number of years and so the calculations may need to be broken down into several separate elements, each using different rates. [See how ...]