Copying from the Task Bar

by Allen Wyatt
(last updated March 30, 2019)

9

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

Removing Unused Styles

Got an older document that has a bunch of unused styles defined in it? You can get rid of those styles easily by using ...

Discover More

Using Graphics to Represent Data Series

You can spice up your bar chart by using a graphic, of your choosing, to construct the bars. This tip shows how easy it ...

Discover More

Clearing All Tab Stops

Tab stops allow you to modify the horizontal position at which text is positioned on a line. If you want to get rid of ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Copying Cells to Fill a Range

Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. ...

Discover More

Relative Worksheet References when Copying

Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it ...

Discover More

Conditionally Deleting Rows

Want to delete a bunch of rows in a worksheet based on the value in a certain cell of each row? There are a couple of ...

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 9 + 5?

2021-10-16 15:13:09

Michel Padawan of excel

thank you Rohn S, MVP 2012-2018, I saw that feature by mistake and came right here to check and I see your comment.


2021-08-21 01:22:29

Voria

@Willy Vanhaelen
I think its not related to the Versions of Excel.
Because I have this feature in my 2019 version of Excel but my friend doesn't have it in his 2021 version.


2021-08-05 06:44:52

Rohn S, MVP 2012-2018

If you right click on the status bar a long laundry list of options is displayed. At the bottom of that list are "Average", "Count", "Numerical Count", "Min", "Max" and "Sum". .Here is the MS info page on the status bar:https://support.microsoft.com/en-us/office/excel-status-bar-options-6055ecd9-e20f-4a7a-a611-4481bd488c55.Imagine that, they don't mention being able to copy the values. I've sent them a note (clicking on "No" at bottom of page) asking them to update the documentation. If a few more people do the same that may get their attention to do it faster. .PS: it really does work, I've seen it work on several excel function pages that I send these suggestion to. A few haven't been acted on <sigh>..These values are displayed on the right side of the status bar, next to the "zoom slider". .If you select some numbers in the sheet the results of these quick calculations are displayed in the status bar. .In my copy of 2010, they are only display values.In Excel 365 hovering over one of the buttons changes it's color, then you can then click on the number you want to copy (that does the copy), then paste the copied value anywhere you want..Apparently the change happened in 2013 or later. .https://excelribbon.tips.net/T006291_Status_Bar_Summing_No_Longer_Availablehttps://treehozz.com/how-do-i-add-the-sum-to-my-status-bar-in-excel.This tip tells how to use VBA to display new calculations in the status barhttps://chandoo.org/wp/customize-excel-status-bar-vba/ This tip describes a feature in 365, "Workbook Statistics". There is a button you can optionally display in the status bar. When you click on it, the statistics summary dialog is displayed. https://office-watch.com/2020/workbook-statistics-finally-in-excel/I suspect it may be a recent change. None of the articles I looked up mention being able to copy these values. This one from from Feb 2021 doesn't mention the featurehttps://www.lifewire.com/description-of-status-bar-3123967


2021-08-05 05:17:03

Willy Vanhaelen

@Voria
On my desktop I have Excel 2019 and the Status Bar behaves like you describe but on my laptop I still have Excel 2007 and this behaves like described in this tip. I don't know from which version on Microsoft changed it.

Another nice change is: deselect some cells in a selection was not possible before. Now in Excel 2019 when you hold down the Ctrl key and click one or more cells in a selection, these cells are deselected.


2021-08-04 04:55:39

Voria

My status bar in excel 2019 is completely alive and usable!
I can see a color change on "mouse over", and by clicking on it, I can copy the data (sum or average ...) and paste it in a cell.
I don't know how it was happened but it is there. Just on my work laptop, not my pc at home.
I don't know how to activate this option on my PC.


2021-06-01 06:27:23

Rohn S, MVP 2012-2018

If you are talking about the bar at the bottom of the Excel application window, that is the "Status Bar".
.
The (default) black bar with the open application buttons is the Windows Task Bar.


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.