April 17, 2014 By

Displaying Excel Formulas

Normally when you enter a formula into Microsoft Excel you want to see the result of the formula, not the formula itself. However, a situation may arise where you need to see the formulas in a spreadsheet and not the answers to the formulas. This need usually occurs when you have to ‘debug’ a problem spreadsheet.

The easiest way to look at a formula is to move the cell pointer to a cell with a formula and look at the formula bar as shown in the next screen shot.

ShowingFormulas0

However, this method is only effective if you have a spreadsheet with very few formulas. When you are working with a large spreadsheet it may be difficult to to find each formula. The following spreadsheet, while not large, has a lot of formulas. In fact, only three of the cells displaying numbers are actual data entry cells. Everything else is a formula.

ShowingFormulas1

The easiest way to display formulas in Excel is to hold the CTRL key and press `. {The key to the left of the 1 key on the keyboard.} This toggles the display between showing formulas and showing results. Starting with the 2007 version of Excel Microsoft added an icon to show formulas. It is logically placed (surprisingly) on the Formulas tab on the ribbon. This icon is also a toggle: alternating between displaying formualas and results.

ShowingFormulas2

Using either method on the demonstration loan comparison spreadsheet displays all of the formulas, including an extra one in cell A10 that was not displayed due to the formatting applied to that cell.

ShowingFormulas3

Note: The keyboard technique also works for LibreOffice Calc.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Filed Under: Excel, LibreOffice Calc, Spreadsheet Tagged With: , , , , ,
February 21, 2014 By

Fixing and/or Removing Excel Links

Linking Formulas are one of the most useful features of Microsoft Excel. These formulas allow you to share or ‘re-use’ data contained in one workbook with another workbook. The following is a textbook example of a linking formula: =[Book2]Sheet1!A3. In English this translates to “Show me the text and/or value that is being displayed in cell A3 on Sheet1 in the Excel file named Book2.” In a network environment the name of the file can include the full path name to the file, I:\HR\Budget\2014\[Book2]Sheet1!A3, for example.

What happens, however, if someone moves the file to a new location, renames the file or any portion of the path, or worse, deletes the file? The short answer is that Excel displays an error message that your linking formula no longer works. How you fix the problem depends on what was done.

Starting with the 2007 version Excel installs with automatic updating of linking formulas turned off by default. If you get the following error message when opening a workbook containing linking formulas you will know the setting for you machine.

Links_SecurityWarning

Clicking on the Options button will display the following dialog box.

Links_SecurityWarning2

Obviously you have to check the lower radio button and then the OK button if you want to use linking formulas.

Assuming that you have automatic updating turned on, the following dialog box will appear whenever there is a problem with a linking formula.

Links_BrokenLinkWarning

If you click the Continue button the workbook will open normally, but the linking formula will not update. The linking formula will remain intact. You may want to choose this option if you are temporarily disconnected from the network. Clicking the Edit Links button will display the normal Excel File Open dialog box. You can then point to the new location for the file, or the new file name. The linking formula will be adjusted to the new location and/or name, retaining the same cell address.

In the worse case scenario, the file with the source data has been irretrievably deleted. In this situation you want to break the link, otherwise Excel will persist in displaying the previous error message each time that the workbook containing the linking formula is opened. To begin the process of breaking the link click the Edit Links icon on the Data tab of the ribbon.

Links_OnRibbon

This will display the following dialog box.

Links_EditLinks

Click the Break Link button, which displays the following warning.

Links_BreakingLinksWarning

As the warning states, this will convert the formula to its current value, similar to using Paste Special==>Values. Should you need the linking formula you would then have to re-create it once the source workbook is re-created.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Filed Under: Excel, Spreadsheet Tagged With: , , ,
February 7, 2014 By

Expand Excel Formula Bar

Microsoft Excel’s formula bar is the area of the spreadsheet window that is above column letter headings, to the right of the current cell location indicator. It is the area highlighted in yellow in the following screen shot.

FormulaBar1

The formula bar in the above figure does not show anything because cell C6 does not contain anything (i.e. it is a blank cell). The formula bar will display the contents of a cell whenever the cell contains something. This ‘something’ is going to be either text, a value, or a formula. The formula bar in the next screen shot shows that cell C6 contains the SUM function adding a few cells. [People new to spreadsheet programs should be aware that cell C6 will display the answer to the formula, but that the cell actually contains a formula.]

FormulaBar2

What happens if a formula is so long that it takes up more space than the formula bar? In Excel 2003, the formula bar would automatically expand to display the entire formula as seen in the next screen shot. Notice that column letter headings are not obscured. This behavior could cause problems for a user trying to trouble-shoot a formula.

FormulaBar2b

Starting with Excel 2007 the formula bar will normally display one line of the formula bar, even if the formula extends to multiple lines. How does a user know that the entire formula is not being displayed? By the up/down arrowheads now being displayed toward the right end of the formula bar as seen in the next screen shot. Using the up/down arrowheads allows a user to scroll through a formula one line at a time.

FormulaBar3

The end most icon (that looks like a ‘v’) allows you to expand the formula bar to three lines as seen in the next screen shot. The ‘v’ icon flips 180 degrees to resemble the carat symbol (^) when the formula bar is expanded. The carat also moves above the up/down arrowheads. The formula bar does not expand beyond three lines. Clicking the carat returns the formula bar to the normal one line display. The expansion/contraction of the formula bar can also be achieved via the following keyboard shortcut: CTRL+SHIFT+U.

FormulaBar4

What if you have a formula longer than three lines? Rather than using the method from the last paragraph, you can manually resize the formula bar.

Move the mouse to the bottom of the formula bar until the mouse pointer becomes a double-headed arrow.

FormulaBar5

Then drag with the mouse to make the formula bar as large as you need.

FormulaBar5b

Being able to change the size of the formula bar is a vast improvement over Excel 2003. Not only does the formula bar expand, but the column letter headings continue to be displayed. This makes for much easier formula verification.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Filed Under: Excel, Spreadsheet Tagged With: ,
October 30, 2013 By

Stop Excel’s #DIV/0!

#DIV/0! is not a comic book version of someone cursing. It’s the error message that you receive in an Excel worksheet whenever you attempt to divide by zero. If you remember grade school math class, zero divided by any number is zero. However, unless you were born on Gallifrey, dividing any number by zero is a mathematical impossibility. Since Excel cannot solve the formula, it returns #DIV/0! as shown in the following screen shot.

DivideZero_ErrorCondition

As you can see from this image, the #DIV/0! error cascades throughout the spreadsheet. Cells D8 and D10 return the same error message because one of the cells that is being summed and averaged contains the error condition. The error in cell D10 causes cell D12 to return an error. Although you cannot prevent a zero from appearing as a divisor in a formula, you can prevent the error condition from cascading by wrapping all of your division formulas inside of the IF function.

The basic structure of the IF function is: =IF(condition to test,return if true,return if false). In this example we want to test whether the numbers in column C equal zero. If they do, return the number zero. If they do not, perform the original division formula. As shown in the following screen shot, the division error messages have disappeared and the formulas in D8,D10, and D12 return an answer.

DivideZero_WithIFZero

Notice that cells D5 and D6 both return zero, but for different reasons. One way to stop the error messages but yet not return the number zero is to enter some text as the true result from IF function. In the following example the IF function has been changed to display a dash (-) whenever the divisor is zero.

DivideZero_WithIFDash

WARNING – although the SUM function in cell D8 returned the same answer in both of the two previous screen shots, the AVERAGE funtion in cell D10, and the formula in cell D12 have different answers, as highlighted in the red box. This is caused by the way Excel functions work. The SUM function treats text as a zero. Thus summing a cell containing a zero or a cell containing text results in the same answer. However, the AVERAGE function ignores any cells containing non-numerical data. Therefore the first IF example is averaging five cells, two of which contain zero, while the second IF example is averaging four cells, one of which contains zero. Depending on your application this could have a significant impact. [In these instances you may want to use the AVERAGEA function. This function treats text as a zero, similar to the SUM function.]

View VBA Consultants profile on LinkedIn

Copyright 2013 – VBA Consultants Ltd

Filed Under: Excel, Spreadsheet Tagged With: , , , , , ,
September 4, 2013 By

Here We Go Round The Formula

It is 4 PM on Friday. You are already in weekend mode when you boss calls.

He is going to be working on the budget over the weekend and he wants you to give him some rations. He is technologically impaired, so you create the quick spreadsheet that appears in the next figure and email it to him.

Rounding1

The phone rings at 4:15 PM. Your boss likes the quick turn around, but would like you to make Col C only four decimal places. You highlight cells C5:C9 and change the number format to four decimal places, save the changes and email the revised spreadsheet (as shown in the next figure) to your boss.

Rounding2

You glance at your watch. It is 4:30 PM. You have made it through the end of the week. The phone rings. You grimace when you hear the boss’ voice. First he asks you if you know basic addition. Then he questions whether you should begin cleaning out your desk . Apparently the numbers in Col C do not add to the total.

What happened?

The answer is that many Excel users mistakenly believe that changing the format of a number instructs Excel to round a number. They do not know or understand that when Excel uses a cell in a formula it uses the calculated value of the cell, not its displayed value. In this example the SUM(C4:C6) function is adding all of the decimals in those cells whether they are displayed or not.

To correct this problem you need to place your formula within the ROUND function. The syntax for this function is ROUND(x,n) where x is the cell address, number, or formula that you wish to round, and n is the number of decimal places to round to. This number should be the same as the number of digits being used for your cell formatting. The next figure shows the corrected spreadsheet with the formulas being rounded to four decimal places. The amounts now total to the displayed sum and you have kept your job.

Rounding3

You should be aware that Excel (at least going back to the 2003 version) has three different round functions: ROUND, ROUNDUP, and ROUNDDOWN. ROUND uses a mathematical approach to rounding: anything five or greater gets rounded to the next larger number, while anything four or less gets rounded to the next smaller
number. As the names of the other two functions imply, ROUNDUP always rounds to the next larger number while ROUNDDOWN always rounds down to the next smaller number.

This figure shows the results of the three functions for both positive and negative numbers.

Rounding4

Filed Under: Excel, Spreadsheet Tagged With: , , ,