Corporate users of Microsoft Excel utilize large spreadsheets encompassing many rows and columns. Regardless of whether the spreadsheet is being used for budgeting, cash projections, depreciation, or acquisition analysis, there are usually a few cells containing some critical piece of information. Most users constantly scroll left and right, or up and down, to see the effect of changes on these critical cells.

Excel contains a feature known as the Watch Window. The Watch Window is a floating dialog box that displays the value of your ‘critical cells’ even when those cells are not currently displayed. Using this feature will not only save time, but also carpal tunnel syndrome.

To display the Watch Window in Excel 2007, 2010, or 2013 click on the Watch Window icon contained on the Formulas ribbon. [For Excel 2003 select Tools → Formula Auditing → Show Watch Window on the menu bar.]


This will display the Watch Window.


Click Add Watch on the menu bar. This displays a pop-up allowing you to either type a cell (or range) address or by clicking the icon, select the cell (or range) with your mouse.


When the correct cell (or range) is shown in the pop-up, click the Add button.


The Watch Window will then display the book name, sheet name, optional range name, cell (or range) address, current value of the cell, and the formula contained in the cell.


You can continue to add more additional items to the Watch Window by clicking on Add Watch on the menu bar.

The Watch Window will remain ‘in your face’ as you move around the spreadsheet. However, you also have the option of docking the Watch Window below the ribbon by dragging the window up towards the ribbon until it snaps into place.


You can turn off the display of the Watch Window by either clicking the Watch Window icon on the Formula ribbon or by clicking the ‘x’ in the upper right corner of the Watch Window.

Copyright 2014 – VBA Consultants Ltd

Automatically Shade Excel Rows

Before laser printers became common place (yes there was life before laser printers), people working with computer generated reports were used to green-bar paper. While the paper comes in various sizes, the most common is 14 7/8 x 11. The paper is in a carton of five thousand continuous sheets. The left and right sides of the paper have sprocket holes that align with the sprockets on an impact printer. The sprockets actually pull the paper past the print head as opposed to a laser jet’s method of feeding the paper in to the print mechanism. If you pay a little extra you can get the paper with perforations by the sprocket holes so that they can be cleanly removed from the report.

It was called green-bar paper because it had alternating sections of light green coloring imprinted on it. Depending on the point size of the report, each section might contain from three to five rows of print. These alternating colored sections allow the reader of the report to ‘line things up’ as the reader’s eyes travel across the report.

The rise of the laser jet heralded in the demise of the impact printers and with it green-bar paper. However, people still need to read landscape reports with compressed print that might squeeze the same amount of information onto a smaller page. If that is your situation, this post will allow you to mimic green-bar paper using two Excel functions along with the Conditional Formatting command.

ROW Function
The ROW function returns the row number of the current cursor location. As seen in the following screen shot, entering =ROW() in cell E2 returns 2, the row of the cursor when the formula was entered. This function by itself is not such a big deal. You could tell that the formula is in row 2 just by moving your eyes a few inches to the left. This function is the most useful when nested inside another function, or in some VBA code.


MOD Function
The MOD function returns the modulus of one number being divided by another – in plain English, the Remainder. If you enter the values and MOD function as shown in the next screen shot, the MOD function returns the value 1. Six goes into twenty-five four times with one left over.


A formula such as =MOD(ROW(),2) will return a zero whenever the row number is evenly divisible by two, i.e. an even number. Knowing this, you can shade every other row of a selected area using the Conditional Formatting command.

Select a section of the worksheet, or the entire worksheet, and then make the following choices from the Home tab of the Excel ribbon. You enter the formula in the New Format Rule dialog box and then click on the Format button to pick the fill color (i.e. shading). If you are printing on a black and white laser, very light gray will probably work best. If you are printing on a color laser, you can let your imagination run wild.


After clicking the OK button a couple of times, you should see a result similar to the following.


What if you want to change the shading to every third row, or every fifth row? You have to edit the rule for the selected cells using the following options from the same Conditional Formatting icon on the ribbon. The key is that you want to edit the rule, not delete the old rule and create a new rule. You also want to remember to change the formula to reflect how often you want the shading applied.


After clicking the OK, Apply, OK buttons, you should see a result similar to the following.


Although the screen shots in this post are from Excel 2010, the procedure will be similar in Excel 2003, 2007, and 2013.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

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.


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.


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.


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.


Note: The keyboard technique also works for LibreOffice Calc.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Using Excel Sparklines

Starting with its 2010 version Microsoft Excel has a feature called sparklines. Excel sparklines are small one-cell mini-charts that allow the user to quickly see:

  1. trends over time, such as sales by quarter
  2. the relationship between various components, such as sales by location for quarter 1, or
  3. win-lose scenarios similar to a comparison between fair market value for an investment compared to its purchase price.

[Read more…]

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.


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


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.


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.


This will display the following dialog box.


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


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

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.


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


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.


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.


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.


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.


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


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

Excel Page Layout View

As its name implies, Microsoft Excel’s Normal view is the view that you normally see when working in Excel. The following is a screen shot of the normal view.


Excel has also offered the Page Break Preview view. This view will show you how your rows and columns will be split across pages when the spreadsheet is printed. Notice the faint ‘Page x of y’ in the middle of each printed page. Also, the dialog box instructing you as to how you can relocate the page breaks will always appear unless you check the ‘Do not show this dialog box again’ check box.


Starting with Excel version 2007, Microsoft has also included a Page Layout View option. This view is similar to Page Break Preview, but more powerful. As you can see from the next screen shot that in addition to showing where your rows and columns will be split across pages, this view contains horizontal and vertical rulers for setting the margins.


This view also lets you directly enter a header (or footer) without having to go through the Page Setup dialog box. When you click the ‘Click to add header (or footer)’ area of the view, two things happen. First, the text disappears so that you can add your header (or footer). Second, the ‘Header and Footer Tools Design’ tab is added to the ribbon.


This toolbar lets you add some header (or footer) elements and change some of the other options. The following screen shot shows the effect of clicking on the File Path icon while the mouse cursor is in the center section of the header.


Once you get used to the Page Layout view, you may find it easier to use than the ‘old’ Page Setup dialog box.

Copyright 2014 – VBA Consultants Ltd

Excel’s Conditional Formatting

Microsoft Excel’s Conditional Formatting allows you to create rules whereby the format of one or more cells changes based on the data in your worksheet. You would want to do this so that you can focus on the items that need review or correction. Examples could be employees with excess overtime, departments with high absenteeism or injuries, products with a high number of returns, or salesmen failing to make sales goals. Starting with the 2007 version Excel has not only made it easier to apply conditional formatting, but it also has made more formatting options available.

[Read more…]

Color Excel Tabs

Microsoft Excel uses a default coloring scheme for its spreadsheet tabs. The default color is fine for most applications. However, in an application such as shown in the following screen shot, tabs with very similar names make it harder to quickly locate a specific tab.


In a workbook like that shown above you may want to consider applying a color to a group of ‘related’ tabs. In this example we could actually come up with two different groupings:

  • Actual – Budget – Forecast
  • Income – Expenses

In a multi-year workbook you could even consider grouping the tabs by year.

An application that we recently designed for a client had thirty-three sheets with four groupings. In addition to coloring the tabs we also colored the listing of the sheet names on the index tab to reinforce the groupings.

To change the color of one or more tabs, select the tabs, and then right-click with your mouse. By moving your mouse to Tab Color in the short-cut menu you will be able to choose from a myriad of colors as seen in the next screen shot.


If you choose More Colors… from this menu you are presented with another dialog box where you can choose your color by moving the mouse or entering R(ed) G(reen) B(lue) numerical values.



The following screen shot shows how the original workbook might look after applying one color to the ‘Income’ tabs and another color to the ‘Expenses’ tabs.


With this color arrangement the user of the workbook can quickly focus on specific sheets, regardless of the actual order of the sheets in the workbook.

View VBA Consultants profile on LinkedIn

Copyright 2013 – VBA Consultants Ltd

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.


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.


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.


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