Copying from the Task Bar

by Allen Wyatt
(last updated March 30, 2019)

3

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.)
  5. Figure 1. The Add-Ins dialog box.

  6. Select the check box next to Analysis ToolPak.
  7. 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 Excel in Office 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

Stopping Text from Jumping Around

Do you struggle with getting your graphics and surrounding text to appear just the way you want it? Here are some ...

Discover More

Errors when Copying References to External Cells

If you copy a cell that contains a reference to external data, do you get an error? It could be due to the complexity of ...

Discover More

Quickly Changing Font Sizes

A quick little shortcut can help you easily step through different font sizes for whatever text you've selected. Word ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Deleting Rows before a Cutoff Date

If you are using Excel to work with data that is date-centered, you may want to delete some of the data before a specific ...

Discover More

Moving Cells Using the Mouse

Want to easily move information from one cell to another? A quick way to do it is to simply drag and drop using the mouse.

Discover More

Undoing Actions in Only the Active Workbook

When you undo actions within Excel, those steps you undo may affect the multiple workbooks in which you've been working. ...

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}] 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 eight minus 6?

2019-04-02 16:10:33

Thomas Papavasileiou

In case you would like to see stat results that respond to any modifications (Active results), you can use a macro that displays the stats, based on a formula.
The relevant macro was posted by Mr.Excel (see https://www.youtube.com/watch?v=cWZ8nieutjk) and here is the code.

Sub quickstats_as_formulas()

ma = Selection.Address
ms = "For range" & vbTab & ma & vbCr
ms = ms & "Average: " & vbTab & "=average(" & ma & ")" & vbCr
ms = ms & "Count: " & vbTab & "=count(" & ma & ")" & vbCr
ms = ms & "CountA: " & vbTab & "=counta(" & ma & ")" & vbCr
ms = ms & "Min: " & vbTab & "=min(" & ma & ")" & vbCr
ms = ms & "Max: " & vbTab & "=max(" & ma & ")" & vbCr
ms = ms & "Sum: " & vbTab & "=sum(" & ma & ")" & vbCr

Dim dataobj As New MSForms.DataObject
dataobj.SetText ms
dataobj.PutInClipboard

End Sub

Any error cells within the selection will result in some errors. if you want to check the existence of any error cells and prohibit the further execution of the macro, you can add the following code lines at the top of the code:

On Error Resume Next
fe = 0
ce = 0
With Selection
fe = .SpecialCells(xlCellTypeFormulas, 16).Count 'Formula errors
ce = .SpecialCells(xlCellTypeConstants, 16).Count 'Constant errors
End With
If fe > 0 Or ce > 0 Then
txt = "Selection contains " & fe + ce & " errors." & vbCr
txt = txt & "Macro ends!"
ttl = "Correct the errors and rerun..."
MsgBox txt, vbOKOnly + vbCritical, ttl
Exit Sub
End If
On Error GoTo 0


2019-04-01 03:28:58

Gerhard Seljehammer

Allow me to recommend ASAP Utilities. There is a function that lets you copy from the task bar with a click of the mouse. It has also a ton of other very useful functions.
I have no connection to the people behind ASAP Utilities, but I am a dedicated user for years. It has saved me lots of time.

Best regards
Gerhard


2019-03-30 13:19:16

Willy Vanhaelen

If your selection has hidden rows and/or columns, those macros will not produce a result corresponding to what is displayed in the Status Bar(at the bottom of the worksheet) which only takes into account the visible cells while the WorksheetFunction takes all cells of your selection.

To fix this in the second macro replace:
Set R = Selection
with
Set R = Selection.SpecialCells(xlCellTypeVisible)

In the first macro add this line somewhere before Select Case:
Set R = R.SpecialCells(xlCellTypeVisible)

As for the formula, better use =SUBTOTAL( 5,B3:B7) instead of=MIN(B3:B7)


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.