Written by Allen Wyatt (last updated February 19, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
Figure 1. The Add-Ins dialog box.
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 Microsoft 365.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When entering data in a worksheet, you may only want to add information to the cells in a particular range. You can ...
Discover MoreWhen entering information in a worksheet, it is common to also note a date or time corresponding to the entry. There are ...
Discover MoreThe radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments