by Allen Wyatt
(last updated July 18, 2020)
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:
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 Office 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Normally a macro is only calculated when you specifically tell Excel to calculate it. Some macros need to be calculated ...Discover More
You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's ...Discover More
When you copy information from one worksheet to another using a macro, you might not get exactly what you want. This tip ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.