Seeing the Difference on the Status Bar

Written by Allen Wyatt (last updated July 18, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


3

Mike frequently needs to see the difference between two cell values. He can enter a simple "=A1 - B1" formula into a spare cell, but it would be so much easier if Mike could just select the two cells of interest and see the difference, as with sums, averages, etc., in the Status Bar.

Excel allows you to customize what appears on the Status Bar (right-click the Status Bar and choose the statistic you want displayed there), but it does not allow you to display the difference between two cells. This makes a bit of sense, if you think about it—the statistics available for display on the Status Bar are based on however many cells you have selected, and the "difference" works with only two cells.

So, the next logical choice is to either do a formula (as Mike suggests) or use a macro. If you want to go the macro route, the following is a nice, simple approach:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim v As Variant
    Dim c As Range
    Dim sTemp As String

    If Target.Count = 2 Then
        v = 0
        sTemp = ""
        For Each c In Target
            If IsNumeric(c) Then
                v = c - v
            Else
                sTemp = " (non-numeric values in selected range"
                sTemp = sTemp & "; result may be meaningless)"
            End If
        Next c
        Application.DisplayStatusBar = True
        Application.StatusBar = "Diff: " & Abs(v) & sTemp
    Else
        Application.StatusBar = False
    End If
End Sub

The macro is an event handler, designed to reside in the code window for a specific worksheet. (Right-click the worksheet's tab and choose View Code from the resulting Context menu.) To use the macro, just select two cells. The result is shown at the left side of the Status Bar. Note that the macro will also indicate in the Status Bar if either of the cells you selected contains something other than a numeric value.

If you want to create a more complex version of the macro (and use it as an Excel add-in), then you might enjoy this article on the Chandoo website:

https://chandoo.org/wp/customize-excel-status-bar-vba/

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7433) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Overlining Characters

Want to add an overline above a character or two in your document? There are several ways you can try, as described in ...

Discover More

Page Numbers in VBA

When you print a larger worksheet, Excel breaks the printout across several pages. You may want to know, before you ...

Discover More

Shortcut Key for Non-Breaking Space

Most of the time you'll use regular spaces between words in a document, but there may be times you want to use a special ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Copying a Set Range from Multiple Worksheets to a New Worksheet

Want to create a summary worksheet that pulls a single row of data from each worksheet in the workbook? Here are a couple ...

Discover More

Jumping to the Start of the Next Data Entry Row

Want a quick way to jump to the end of your data entry area in a worksheet? The macro in this tip makes quick work of the ...

Discover More

Using R1C1 Formula References in a Macro

Besides the regular way of displaying formulas, Excel can also display them using what is called R1C1 format. If you are ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2020-07-22 12:09:34

Phil W

@J.Wooley

Re "Sorry, but your method only works if the two cells are vertically adjacent."

It does work on two selected cells either in a row or a column. I agree it does not work on non-contiguous selected cells. Below is a modification that handles any two selected cells, contiguous or not.

In playing around with referencing cells within a selection, I noticed that how you select the cells makes a difference, even if the two cells are contiguous.

For example, if I click Cell A1 and drag to cell B1, the selection address is $A$1:$B$1. However if I click on cell B1 first and then select cell A1 by pressing the Ctrl key and clicking on A1, I get a different selection address: $B$1,$A$1.

This is important as the sign of the difference could be affected by how you select the two cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Displays the difference between two selected cells on the status bar.
' Modified 7/22/2020 to handle two non-contiguous cells

Dim c As Range
Dim sTemp As String
Dim Count As Integer
Dim Values(2) As Variant

If Target.Cells.Count = 2 Then
sTemp = ""

For Each c In Target
Count = Count + 1
If IsNumeric(c) Then
Values(Count) = c
Else
Values(Count) = 0
sTemp = " (non-numeric values in selected range"
sTemp = sTemp & "; result may be meaningless)"
End If
Next c
Application.DisplayStatusBar = True
Application.StatusBar = "Diff: " & Values(1) - Values(2) & sTemp
Else
Application.StatusBar = False
End If
End Sub


2020-07-21 11:17:19

J. Woolley

@Phil W
Sorry, but your method only works if the two cells are vertically adjacent. Here is another way to display a signed difference. This method applies to all sheets in the workbook, so the three event procedures must be located in the code window of the ThisWorkbook document module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Selection.CountLarge = 2 Then
Call Workbook_SheetSelectionChange(Sh, Selection)
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.StatusBar = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim v As Variant, c As Range
If Target.CountLarge = 2 Then
v = 0
For Each c In Target
If IsNumeric(c) Then
v = c - v
Else
v = -v
End If
Next c
Application.DisplayStatusBar = True
Application.StatusBar = "Difference: " & -v
Else
Application.StatusBar = False
End If
End Sub

(see Figure 1 below) is a screen shot of the code window.

Figure 1. Figure 1


2020-07-20 08:01:39

Phil W

If you want to display a signed difference between two values, and assuming you are always subtracting the second value in the selected range from the first one, here is a variation on the macro. This would more closely match the formula mentioned in Allen's comments.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Diff As Variant
Dim sTemp As String

If Target.Count = 2 Then
Diff = 0
sTemp = ""
If Not IsNumeric(Target(1)) Or Not IsNumeric(Target(2)) Then
sTemp = " (non-numeric values in selected range"
sTemp = sTemp & "; result may be meaningless)"
End If

If IsNumeric(Target(1)) Then ' Get first value in selected range
Diff = Target(1)
End If
If IsNumeric(Target(2)) Then ' Subtract the second value from the first value in selected range
Diff = Diff - Target(2)
End If

Application.DisplayStatusBar = True
Application.StatusBar = "Diff: " & Diff & sTemp
Else
Application.StatusBar = False
End If
End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.