add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Entering a Formula into a Cell
Excel offers two options for entering a formula into a cell.
© Typing a formula
Experienced Excel users prefer to type formulas directly into a cell, in the formula bar. This is a good technique for skilled users who are familiar with the syntax of formulas (where to place commas and parentheses, the order of arguments, and more). If you are not sure of the syntax of the formula, type = and the name of the function, and then press Ctrl+Shift+A. You will now see the syntax of the function.
When typing a function in the formula bar, a yellow Tip box pops up, which contains the function’s syntactic structure.
New
In 2002
© Entering data into the function’s edit boxes
The edit boxes guide you in handling the syntax of functions.
Shortcuts for opening the Formula Palette
Ctrl+A – opens the Formula Palette immediately after typing the name of the function. For example, type =SUM, and press Ctrl+A to open the Formula Palette.
Shift +F3 – opens the Paste Function dialog box. Alternatively, use the Paste Function icon (fx) on the toolbar.
Tip – Enter a formula more quickly by changing the names of the sheets
When you enter a formula that links cells in different sheets in the workbook and you do not use names for the references, the formulas are long and often take up several rows. They are also difficult to enter and edit.
If you rename the sheets with shorter names, such as 1, 2 or 3, the formulas will be shorter. When you are done entering/editing, change the names of the sheets back to meaningful names.
Copying a Formula, Relative and Absolute Reference
Relative reference
When a formula is copied, the relative reference is used. Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula. For example, in Cell A1, type the number 100. In Cell B1, type the formula =A1. Cell B1 is one column to the right of Cell A1. When the formula is copied from Cell B1 to Cell B10, the distance between the reference and the cell containing the formula remains one column. The formula in Cell B10 is =A10.
Absolute reference
Use the previous example, and select Cell B1. In the formula bar, select the reference A1, and press F4. The result is =$A$1.
Copy the contents of Cell B1 to Cell B10. Notice that the formula does not change; the formula reference remains constant as =$A$1.
The F4 key
This is an important shortcut. The F4 shortcut, which deals with relative and absolute references, has four states. Select Cell B1, and then select the formula bar after the = sign. Press F4 several times. Notice how the formula changes each time you press F4.
State 1 – absolute reference to the column and row, =$A$1.
State 2 – relative reference (column) and absolute reference (row), =A$1.
State 3 – absolute reference (column) and relative reference (row), =$A1.
State 4 – relative reference to the column and row, =A1.
Maintaining a relative reference when pasting a formula
In many cases, you copy a formula from cell to cell when you want to avoid changing the reference of the cell containing the formula. In this case, use F4 to change the formula to absolute, copy it and paste it; then use F4 to change the original formula back to relative.
Using F4 twice is annoying, but there is a way to avoid this repetitive task. Copy and paste the formula from the formula bar back to the formula bar, instead of from cell to cell.
For example: Cell A1 contains the formula =A$1. Select the text from the formula bar (that is, select the formula) and press Ctrl+C (copy). Leave the formula bar by hitting Esc or by clicking Enter or Cancel (click the or the X to the left of fx on the formula bar). Select another cell in the sheet and press Ctrl+V.
Tip – Copy and paste a range of cells containing formulas without changing the relative reference
Select a range of cells containing formulas. Press Ctrl+H to replace the = sign with the # sign. After pasting the cells in a different location, replace the # sign with the = sign.
Nesting Formulas
Nesting is the term for a formula within a formula.
For example:
In Cell A1, type the number 100. In Cell A2, type the number 200.
In Cell B1, enter the formula =SUM(A1:A2) (the result = 300).
In Cell B2, enter the formula =A2-B1 (the result = -100).
In Cell C1, enter the formula =IF(A1>A2,B1,B2).
When the formula in Cell C1 is calculated, the result depends on the values in Cells A1:B2.
In this case, the result in Cell C1 is –100.
In the figure above, note the formula in Cell D1. The IF formula links two formulas.
Excel’s method of nesting formulas is not particularly easy if you are interested in using complex formulas.
For example: For Cell E1, type =IF in the formula bar, and press Ctrl+A.
In the first edit box for the IF formula, select the relevant cells to enter the formula A1>A2.
Notice the Name box to the left of the formula bar. The Name box has changed to Paste Function. Click the little arrow on the right to open the list of formulas, and select the formula SUM. In the first line of the SUM formula, select Cell A1. In the second line of the SUM formula, select Cell A2. Click OK.
The IF box has disappeared. In the formula bar, click between the two right-hand parentheses and type a comma (,); this brings back the IF formula palette. Then type the formula A2-B1, and click OK.
This is clearly a tedious process. Chances are you will not manage to nest complex formulas by this method without lots of practice.
Copying and pasting a formula within a formula
This technique is easy. Copy and paste one formula into another by copying the first formula from the formula bar. Then select a new cell, click on the formula bar, and paste using Ctrl+V. See Copying a Formula, Relative and Absolute Reference; the technique is similar.
For example:
In Cell D1, enter the formula =SUM(A:A)
In Cell E1, enter the formula =SUM(B:B)
In Cell F1, enter the formula =SUM(D1+E1)
Copy and paste all the formulas into a single formula in a single cell, instead of three formulas in three cells. For Cell D1, select in the formula bar SUM(A:A) without the = sign. Press Ctrl+C and click the X to exit edit mode for the cell. In the formula bar for Cell F1, select the reference D1, and press Ctrl+V.
Perform the operation again to copy the formula (without the = sign) from Cell E1 to Cell F1, and paste over the reference E1. The result is a single formula: =SUM(SUM(A:A)+SUM(B:B)).
Adding Statistical Formulas
Analysis ToolPak, Analysis ToolPak-VBA
When Excel is installed on your computer, not all functions are automatically installed. The Analysis ToolPak contains about 100 functions. You must install this add-in if you want to use any of its functions.
You should also install the Analysis ToolPak-VBA add-in. This add-in includes VBA functions that are useful in developing applications in the VBA programming language.
Install both add-ins before beginning to work in Excel.
Installing the add-ins
1. From the Tools menu, select Add-ins.
2. Select both add-ins, Analysis ToolPak and Analysis ToolPak-VBA.
3. Click OK.
Now check to see if the functions included in Analysis ToolPak have been installed. Press Shift+F3 (Paste Function) and select All. In Select Function, notice the functions whose names are printed in lower-case letters. You have installed these functions by installing the Analysis ToolPak.
Array Formula
Use an array formula to create a formula that performs complex calculations.
For example: there are names defined for three ranges:
Range A4:A12 Part_Number
Range B4:B12 Quantity
Range C4:C12 Price
In Cell D16 is the array formula ={(SUM(Quantity*Price)}.
The formula returns the result of the Quantity range times the Price range.
Tip – To enter an array formula, you must hold down Ctrl+Shift while hitting enter.
For example:
Cell D19 contains the formula ={SUM(IF(Part_Number=C19,Price*Quantity,0)}.
The formula returns the result of the Quantity times the Price for part number A663.
Use the following technique to enter an array formula:
1. In any cell, enter the formula =SUM(Price*Quantity). Define Names before beginning to enter the formula.
2. Press these three keys simultaneously: Ctrl+Shift+Enter.
3. The array formula is created when these three keys are pressed simultaneously. In order to identify the array formula, brackets ({}) are inserted around the formula.
4. If you forget to hold down Ctrl+Shift while pressing Enter, the formula will evaluate to the VALUE! ERROR. If this happens, then press F2 (Edit Cell), or use the mouse to select any character in the formula bar.
The technical side of array formulas
An array saves calculations in the temporary memory, which are used later in the calculation of the total. The ability to save results in the temporary memory allows you to perform complex calculations such as the one displayed.
Using array formulas to create links with a change of direction
Select a range of cells in the sheet, and press Ctrl+C. Select the last cell in the sheet, right-click, and select Paste Special.
In the Paste Special dialog box, there is a Paste link option. This option allows you to paste link formulas into cells. The Paste Special dialog box also has a Transpose option, which pastes the data in the opposite direction (that is, horizontally to vertically, or vice versa).
Problem
You cannot select the Transpose option together with the Paste link option. In other words, you cannot create a link while changing the direction of the paste operation.
Solution
Use the TRANSPOSE function together with the array formula technique to create links that change direction.
First, measure the number of cells in the selected columns and rows. When selecting the range, notice the Name box.
Select range A2:B6, which has the name Range defined for it. The size of the range is 4Rx2C – that is, four columns by two rows.
Starting with Cell A8, select a range whose size is 2Rx4C – that is, the same size in the opposite direction.
1. Enter the formula =TRANSPOSE, and press Ctrl+A.
2. Press F3, paste the name RANGE, and press Ctrl+Shift+Enter.
Creating a link between cells in a workbook
Create a link between two workbooks. In Cell A1 in one workbook, enter a formula that links to Cell A1 in another open workbook.
There are two ways to create links between cells. The first method is between sheets in an active workbook, and the second is between a sheet in one workbook and a sheet in another workbook. Here are two methods for setting up a link between workbooks. The second method is rarely used, but easier.
Method 1 Starting in the destination workbook:
In Cell A1, type the = sign. From the Window menu, select another workbook, select Cell B1 in one of its sheets, and press Enter.
Method 2 Starting in the source workbook:
Go to the source workbook. Copy Cell B1, and press Ctrl+Tab (to move between open workbooks). Select Cell A1, right-click, and from the shortcut menu, select Paste Special. Click Paste link.
Auditing
Auditing and tracing precedents (cells that contain formulas) or dependent cells (cells that are referenced by formulas) can be a big headache for the Excel user.
Using Names (see Relative reference and Absolute reference) is very helpful in reducing the time spent auditing and evaluating precedent cells. This section will show you ways to audit and evaluate formulas.
In Excel 2002, the relevant toolbar is called Auditing formulas. In Excel 97 and 2000, the toolbar is called Auditing.
Auditing formulas toolbar
Select one of the toolbars, and right-click. From the shortcut menu, select Auditing formulas.
In Excel 97 and 2000, the Auditing toolbar does not appear in the list of toolbars. Select one of the toolbars, right-click, and from the shortcut menu, select Customize. Select the Toolbars tab, and select the Auditing toolbar (or from the Excel menu, select Tools, Auditing formulas).
Moving to a precedent cell / moving to a dependent cell
Moving to a precedent cell
Select Cell A1 and click the Trace precedents icon on the Auditing Formulas toolbar. Double-click the dotted arrow, and in the Go to dialog box, select the reference to which Cell A1 is linked. Click OK. See the figure below.
Moving to a dependent cell
Select Cell A1 (without a formula), and click the Trace dependents icon on the Auditing formulas toolbar. Double-click the dotted arrow, and select the cell being referenced. Click OK.
Moving between linked cells in a sheet
Select a cell with a formula that is linked in the same sheet (for example, =B10). Click the Trace precedents icon on the Auditing formulas toolbar. Double-click the blue arrow between the linked cells to move between the precedent cell and the dependent cell.
Keyboard shortcuts to precedent and dependent cells
Precedent cell – Ctrl+[
Select all precedent cells – Ctrl+Shift+{
Press F2 or double-click a cell that contains links within the sheet. The precedent cells are indicated in blue.
Tip – Double-click a cell that contains links to mark all precedent cells in the current worksheet
From Tools, select Options, Edit.
Clear the Edit directly in cell option, and click OK.
Stepping into the Formula
New
In 2002
Step into the formula’s calculations, one step at a time.
From the Tools menu, select Audit formulas, Evaluate formula, or from the Audit formulas toolbar, click Evaluate formula.
The Evaluate formula dialog box allows you to move between the calculations in a formula. Click Step In to view a calculation that is part of this formula.
Handling Errors
Entering formulas into cells in Excel sheets is not foolproof. It is impossible to completely avoid mistakes, but there are ways to keep them to a minimum.
The method described in Nesting Formulas (see above) will help you to create formulas inside other formulas. Combining the IF formula with the ISERROR formula returns a calculation whose result is TRUE, if the result of a calculation returns an error.
For example:
In Cell A1, type the number 100.
In Cell B1, type the formula =A1/A2. The calculation returns an error - #DIV/0!
The error is created when you try to divide a number in a cell by 0 (A2).
To avoid displaying an error in a cell:
1. In Cell C1, enter the formula =ISERROR(B1). The formula returns the result TRUE. That is, Cell B1 contains an error in the calculation of the formula (the formula ISERROR is located in the category Information in the Paste Function dialog box).
2. In Cell D1, enter an IF formula =IF(C1,0,B1).
3. Combine the formulas into one nested formula. From Cell C1, copy the formula (without the = sign), and paste it into Cell D1 instead of C1.
4. From Cell B1, copy the formula (without the = sign), and paste it twice – instead of the digit 0 and instead of B1.
The result – one nested formula: =IF(ISERROR(A1/A2),0,A1/A2).
Marking Cells Containing Errors
Mark cells containing errors so that they can appear in color, be easily identified, or be deleted.
Technique 1
1. Press F5, or from the Edit menu, select Go To…
2. In the Go To dialog box, click Special…
3. Select Formulas, Errors.
4. Click OK.
Technique 2
Use conditional formatting to mark and/or color cells containing errors.
1. Make A1 be the active cell. Press Ctrl+A to select the sheet.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula Is.
4. In the formula box, type the formula =IsError(A1). Now click Format, and select the desired properties.
5. Click OK.
Tracing errors
New
In 2002
In Cell A1, type the number 100.
In Cell B1, type the formula =A1/A2. The result of the calculation returns the error #!DIV/0!.
Select Cell B1 with the error, and click the Error checking icon (the first icon on the left on the Auditing formulas toolbar.
Smart tag
New
In 2002
The smart tag for handling errors is created automatically in the cell whose
calculation returned an error. Open the shortcut menu by clicking the small
Smart tag arrow.
Through the shortcut menu, you can get an explanation of the type of error in the formula, edit the formula, ignore errors, and view the evaluation steps by selecting Show Calculation Steps…
Circular References
A circular reference is created if you enter a formula into a cell and the cell is its own precedent.
The Circular Reference toolbar (shown on next page) indicates the reference of the cell containing the circular reference. If you glance at the formula or the Status bar (at the bottom of the sheet), you can pinpoint the source of the error and correct it.
Iteration
You can use a circular reference intentionally by using the Iteration box. Iteration enables circular calculation and offers a method to control it.
For example: Value-added tax (VAT) gross-upformula
1. In Cell B1, type the formula =B3-B2 (the result of this calculation is a total without VAT).
2. In Cell A2, type the VAT percent (%) 0.17.
3. In Cell B2, type the formula =A2*B1 (calculates the VAT amount). Click OK to dismiss the circular reference warning.
4. In Cell B3, type the amount 100.
A circular reference is created in Cell B1. The calculation of the amount without VAT is conditioned upon the calculation of VAT in Cell B2.
Select the Iteration box to perform a circular calculation.
From the Tools menu, select Options. In the Calculation tab, select the Iteration box and click OK.
Excel allows you to define the number of iterations (number of times the formula is calculated) until the exact calculation is performed.
Links Between Workbooks
Creating links between workbook sheets
© Links are created by formulas.
© Links are created by moving sheets with Names from one workbook to another. When you move or copy a sheet by selecting its tab, right-clicking, and then selecting Move or copy from the shortcut menu, the sheet with Names that you defined is moved from the active workbook to a new workbook. This results in the creation of unwanted links in the new workbook.
© Broken links are created when the location of the precedent workbook is changed on the hard disk. When you save a workbook that is linked to another workbook and then move or copy the linked workbook to another folder on the hard disk, the first workbook still saves the original reference on the hard disk.
Tip – quick creation of link formulas
Use Paste special to create links automatically. Copy a range of cells with data, select a cell in another sheet or workbook, right-click, and select Paste special. Then click Paste links.
Canceling the message about creating links when opening a workbook
1. From the Tools menu, select Options, and then select the Edit tab.
2. On the Edit tab, clear the selection of the Ask to update automatic links box.
The Edit Links Dialog box
From the Edit menu, select Links.
Excel 2002 offers new solutions for handling the problem of links between files.
New
In 2002
Startup Prompt
New
In 2002
Click the Startup Prompt button, and define the method of opening linked files.
Break Links
Select the type of link you want to break, and click Break links. The links between workbooks are broken, and the linked formulas are deleted.
Caution
Double-check and make a backup copy of the workbook before breaking the links.
Changing a source
Changing a source allows you to change the location of the workbook on the hard disk or to exchange the location of one workbook for another.
1. Select the workbook in the old location.
2. Click Change source.
3. Select the workbook that should be linked in the folder you have opened.
4. Click OK. The location of all linked formulas will change.
Changing links and deleting unwanted links
Stage 1 – Check to see if there are any links that were created by Names
1. Select a cell in the sheet.
2. Press F3, and click Paste list.
3. Check the list, and identify the Names you want to delete.
4. Press Ctrl+F3.
5. Select the Name to be deleted, and click Delete.
Stage 2 – Check the link in the sheet and find the links that can be deleted
1. Press Ctrl+F.
2. In the Find box, type a square bracket ([). Every externally linked formula has a square bracket.
3. Start the search for formulas one cell at a time.
4. Delete unwanted and unnecessary formulas (do this with care).
Stage 3 – Check the data consolidate dialog
1. From the data menu, select Consolidate. This dialog remembers the last consolidation.
2. If any items in the All References box point to the undesired link, highlight them and click Delete.
Stage 4 – Check the link box
1. From the Edit menu, select Links.
2. Select the name of the workbook whose links you want to delete.
3. Click Change source.
4. In the folder, select the workbook you are currently working in (that is, link the workbook to itself).
5. Click OK.
Stage 5 – Save the workbook under a new name
If a linked workbook exists and it is not mentioned in the Links box, there is a solution. Save the workbook that has links under the name of the workbook to which it is linked (according to the Links box). The links are automatically deleted (the new workbook is linked to itself). Save the workbook again under whatever name you wish.
Deleting Formulas, Saving Calculation Results
Using formulas does not mean you must keep them after performing calculations with them. Saving a large number of formulas has several disadvantages: it requires a large amount of memory; workbooks have a large file size; links are problematic; and more.
It is vital to delete formulas without deleting the necessary calculated values, whether to solve some of the problems mentioned above or to send reports to other people who should not see the calculation methods that were used.
Deleting formulas, maintaining calculated values
1. Copy the cells with the formulas.
2. Right-click, and from the shortcut menu, select Paste special.
3. In Paste, select Values and click OK.
Tip – Quickly delete the formula without deleting the calculation result
Select a cell containing a formula, press F2 and then press F9.
Deleting formulas and pasting values using a single line of code and a keyboard shortcut
Record a macro adding a Shortcut Key and save the macro in a Personal workbook. Enter the following code to the macro you created:
Selection.Formulas=Selection.Value
Selecting Cells with Formulas
Use the Go To dialog box to select formulas in a sheet to delete, to protect (see below), or to color.
1. Press F5, or from the Edit menu, select Go to.
2. In the Go to dialog box, click Special.
3. Select Formulas.
4. Click OK.
Only cells that contain formulas are selected in the sheet.
Identifying and Formatting Cells with Formulas
Excel does not provide a formula that identifies formulas. VBA has a function called HasFormula. The solution is to create a custom function (see explanation at end of chapter) to identify a cell containing a formula.
Function FormulaInCell(Cell) As Boolean
FormulaInCell = Cell.HasFormula
End Function
Use the technique described below to combine the Get.Cell formula with conditional formatting to format cells containing formulas. After creating the formula FormulaInCell, combine it with Conditional Formatting.
Combining the Get.Cell formula and conditional formatting to format a cell with a formula
Excel includes the macro language XLM. This macro language has a function called Get.Cell. Use this function to identify a formula in a cell by combining it with conditional formatting. The technique is described below.
1. Select a cell in the sheet, and press Ctrl+F3.
2. In the Define Name dialog box, type the name FormulaInCell.
3. Type the formula =GET.CELL(48,INDIRECT(“rc”,FALSE)) in the Reference field.
4. Select all the cells in the sheet by pressing Ctrl+A.
5. From the Format menu, select Conditional formatting.
6. In Condition 1, select Formula is.
7. In the formula box, type =FormulaInCell.
8. Click Format.
9. From the Font tab, select the color yellow, and click OK.
10. Click OK.
Protecting Formulas in Cells
Protecting a cell or a group of cells prevents writing, editing or deleting the cell, or damaging the formula. Two conditions must be met to protect a cell: the cell must be locked, and the sheet must be protected.
Locking, the first condition
Select a cell in the sheet and press Ctrl+1. In the Format Cells dialog box, select Protection. Select the Locked option.
Protecting a sheet, the second condition
From the Tools menu, select Protection, Protect sheet, and click OK (password is optional).
Protecting formulas
Protecting formulas requires isolating cells with formulas from the rest of the cells in the sheet, locking them, and then protecting the sheet.
Stage 1 – Canceling the locked format of all the cells in the sheet
1. Select all the cells in the sheet by pressing Ctrl+A.
2. Press Ctrl+1.
3. Select the Protection tab.
4. Clear the selection of the locked option.
5. Click OK.
Stage 2 – Selecting cells with formulas
1. Press F5.
2. Click Special.
3. Select the Formulas option.
4. Click OK.
Stage 3 – Locking cells with formulas
1. Press Ctrl+1.
2. Select the Protection tab.
3. Select the Locked option.
4. Click OK.
Stage 4 – Protecting the sheet
1. From the Tools menu, select Protection, Protect sheet.
2. Click OK (password is optional).
The Protect Sheet dialog box in Excel 2002 allows you to select various topics for protection.
In Allow all users of this worksheet to… in the Protect Sheet dialog box, select the desired options.
New
In 2002
Displaying a Formula in a Cell and Printing Formulas
Displaying formula syntax
The result of calculating a formula (value) appears on the screen and can be printed by clicking the Print icon. However, you can usually view the syntax of a formula only by selecting the cell. To see all formulas on the sheet, press Ctrl+~ (the ~ sign is located to the left of the number 1 on the keyboard), or from the Tools menu, select Options. Then select the View tab, and select the Formulas option.
To return to the normal view, press Ctrl+~ again.
Printing the formula syntax
Use the usual method of printing in Excel to print from the sheet while formulas are displayed using Ctrl+~.
Using VBA to Create and Add Functions to the Function Wizard
When you work in Excel, there will be times when you need to create a complex custom formula.
Take, for example, a formula to calculate the net salary from a gross salary. This is a very complex calculation, as this type of formula is made up of fixed pieces of data, including income tax schedule, social security laws, tax credits and additional credits and deductions.
Another example would be a formula for calculating sales commission that varies depending on the level of sales, formulas for converting currency, conversion of weights and measures (a Convert function already exists in Excel), or a conversion of text. See Chapter 16, Importing Text Files.
VBA formulas can be used for several reasons, one of which is to create custom functions for your personal use. You can add these functions to the Function Wizard.
Custom functions, example and explanation
Create a function called Add to total two numbers in different cells. The function has two argument boxes.
Formula structure:
© Function name = Add
© The function arguments are placed in parentheses. The above example has two argument boxes.
© The last piece of the formula, As Integer, defines the value of the variables as a whole number. See the explanation in the Variables section of Chapter 28, Other VBA Techniques.
© Function calculation – where the function receives the results of the calculation and returns the result.
Location to write and save function:
We advise saving the functions in an active workbook or personal workbook. See the Storing a macro in a personal workbook section of Chapter 26, Running a Macro.
Activation of function:
1. Type numbers into cells A1:A2.
2. Select cell A3 in the same sheet.
3. Press Shift+F3 (opens the Paste Function dialog box).
4. Select the User Defined category.
5. Select the Add function.
6. In the first argument box, select cell A1.
7. In the second argument box, select cell A2.
8. Click OK.
Using a function to calculate accrued income tax from gross salary
This function will calculate the tax payable based on gross salary, marginal tax schedule and credit point schedule.
The function has a single edit box – gross salary (income).
Schedules – the values are defined using a constant variable, Const, which cannot be changed when the function is operating. The constant variables are loaded into memory cells when the function is operated.
Case – like IF, the function allows you to check a large number of instances.