Copying from the Task Bar

Michael can select some cells in a worksheet and the Task Bar (at the bottom of the worksheet) shows some analysis of those selected cells—things like the sum or average of the cells. Michael wonders if there is a way to copy a value from the Task Bar into the Clipboard so that he can paste that value elsewhere in the worksheet.

The short answer is no, there is no way to access the information in the Task Bar. That being said, there are a couple of approaches that you can use to recreate the information so that you can access it.

The first workaround doesn't involve using a macro, but you might consider it a bit of overkill. (Who said you can't shoot a gnat with a bazooka in Excel?) This approach involves the use of the Data Analysis tool. This tool is implemented through the use of an add-in; you can tell if it is enabled on your system by looking at the Data tab of the ribbon where you should see a Data Analysis tool at the right side. If you don't see it, follow these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and later versions display the File tab of the ribbon and then click Options.)
2. At the left side of the dialog box click Add-Ins.
3. At the bottom of the dialog box use the Manage drop-down list to select Excel Add-ins.
4. Click the Go button. Excel displays the Add-Ins dialog box. (See Figure 1.)



Figure 1. The Add-Ins dialog box.

5. Select the check box next to Analysis ToolPak.
6. Click OK.

With the Data Analysis add-in activated, select the cells you want to analyze. You should see your stats on the Task Bar, as normal. Now, display the Data tab of the ribbon and click the Data Analysis tool. Excel displays the Data Analysis dialog box. (See Figure 2.)

Figure 2. The Data Analysis dialog box.

This dialog box does nothing except list a ton of different analyses that you can perform. You want to select the Descriptive Statistics option and then click OK. Excel then displays the Descriptive Statistics dialog box. (See Figure 3.)

Figure 3. The Descriptive Statistics dialog box.

If the cells you want to analyze aren't already noted in the Input Range box, click once in that box and then select the cells in the worksheet. Then, near the bottom of the dialog box click the Summary Statistics check box. When you click on OK, Excel creates a new worksheet that contains all the stats (and more) that might be displayed in the Task Bar. You can then copy any of these back to your worksheet, as desired, and delete the added worksheet when you are done with it.

(See—I told you it might be overkill.)

If you don't mind using a macro, you can create a user-defined function that will return the desired information. The following is one that could be used:

Function Stats(R As Range, Op As String) As Long
    Dim sTemp As String
    Dim c As Range
    Dim sOp As String

    Stats = 0
    On Error GoTo Done
    sOp = UCASE(Op)
    Select Case sOp
        Case "AVG"
            Stats = WorksheetFunction.Average(R)
        Case "CNT"
            For Each c In R
                If Not IsEmpty(c.Value) Then Stats = Stats + 1
            Next c
        Case "MIN"
            Stats = WorksheetFunction.Min(R)
        Case "MAX"
            Stats = WorksheetFunction.Max(R)
        Case "SUM"
            Stats = WorksheetFunction.Sum(R)
        Case Else
            sTemp = "This function requires two parameters. "
            sTemp = sTemp & "The first is the cells you want evaulated. "
            sTemp = sTemp & "The second is the operation you want "
            sTemp = sTemp & "performed. You can use the following "
            sTemp = sTemp & "operations:" & vbCrLf & vbCrLf
            sTemp = sTemp & "  * AVG (average)" & vbCrLf
            sTemp = sTemp & "  * CNT (count of cells with values)" & vbCrLf
            sTemp = sTemp & "  * MIN (minimum)" & vbCrLf
            sTemp = sTemp & "  * MAX (maximum)" & vbCrLf
            sTemp = sTemp & "  * SUM (sum)" & vbCrLf
            MsgBox sTemp
    End Select
Done:
End Function

To use the function, simply enter something similar to the following in a cell:

=Stats(B3:B7, "Min")

By looking through the macro you can figure out the various operation codes that can be passed to the function via the second parameter. The user-defined function seems, in its own way, a bit of an overkill, as well. Why? Because you could simply use regular formulas to get the desired information:

=MIN(B3:B7)

Instead, you might want to create a macro that is tied to a shortcut key. This could then be used with whatever cells are selected in order to stuff the desired information into the Clipboard:

Sub StatClip()
    Dim sTemp As String
    Dim R As Range
    Dim f As Variant
    Dim obj As New DataObject

    Set R = Selection
    Set f = Application.WorksheetFunction

    sTemp = "Address:" & vbTab & R.Address & vbCrLf
    sTemp = sTemp & "Average:" & vbTab & f.Average(R) & vbCrLf
    sTemp = sTemp & "Count: " & vbTab & f.Count(R) & vbCrLf
    sTemp = sTemp & "Min: " & vbTab & f.Min(R) & vbCrLf
    sTemp = sTemp & "Max: " & vbTab & f.Max(R) & vbCrLf
    sTemp = sTemp & "Sum: " & vbTab & f.Sum(R) & vbCrLF

    obj.SetText sTemp
    obj.PutInClipboard
End Sub

Remember that you want to assign this macro to a keyboard shortcut. That way you can invoke it (using the shortcut) while you have some cells selected in your worksheet. If you get an error when you try to run the macro, it is no doubt because you don't have the Microsoft Forms 2.0 Object Library enabled for your project. You'll want, in the VBA Editor, to select Tools | References to enable the requisite library.

When you actually invoke the macro, it will appear as if nothing happens. This is normal; remember that information is being stuffed into the Clipboard. After the macro runs, you need to select a different cell and then press Ctrl+V to paste those Clipboard contents into your worksheet.

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