Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Viewing Workbook Statistics.

Viewing Workbook Statistics

by Allen Wyatt
(last updated October 10, 2015)

6

Excel keeps track of a wide range of statistics about your workbooks. These statistics include such mundane and obvious items as the file name, directory, and title. But you can also find out who last worked on the workbook, what keywords are associated with the workbook, and the total editing time spent on the workbook. (This last statistic is nothing more than the time the workbook has been open.)

If you want to view the statistics for the current workbook, follow these steps if you are using Excel 2007:

  1. Click the Office button, then click Prepare, and finally Properties. Excel displays some of the properties just above the worksheet on the screen.
  2. Click the Document Properties drop-down list and then choose Advanced Properties. Excel displays the workbook's Properties dialog box.
  3. Click on the Statistics tab. The dialog box then displays the statistics for your workbook, as already described.
  4. Click on the Summary tab to see other statistics for your workbook.
  5. Click on OK when you are done reviewing the statistics.
  6. Dismiss the properties above your worksheet by clicking the small X at the upper-right of the properties area.

If you are using Excel 2010 or Excel 2013, the steps are slightly different:

  1. Display the File tab of the ribbon.
  2. Make sure the Info option is selected at the left side of the dialog box.
  3. Click the Properties link near the right side of the dialog box and then click Advanced Properties. Excel displays the Properties dialog box for your workbook.
  4. Click on the Statistics tab. The dialog box then displays the statistics for your workbook, as already described.
  5. Click on the Summary tab to see other statistics for your workbook.
  6. Click on OK when you are done reviewing the statistics.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6290) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Viewing Workbook Statistics.

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

Entering or Importing Times without Colons

Enter a time into a cell and you normally include a colon between the hours and minutes. If you want to skip that pesky ...

Discover More

Make that Chart Quickly!

Need to generate a chart in the fastest possible way? Just use this shortcut key and you'll have one faster than you can ...

Discover More

Preparing Files for a Commercial Printer

Sometimes you may want to send Word documents to a commercial printer for professional mass production. Doing this ...

Discover More

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!

More ExcelTips (ribbon)

Noting When a Workbook was Changed

Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the ...

Discover More

Creating Individual Workbooks

Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based ...

Discover More

Printing Workbook Properties

Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. ...

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 five minus 4?

2017-04-27 17:44:24

Dennis Costello

Beth's Ctrl ~ keyboard shortcut is indeed a piece in the puzzle of finding references to other workbooks where you suspect problems. Other possibilities that you may need to explore are to:
- Use the Edit Links box (on Excel 2007 this is on the Data Ribbon, in the Connections section) to see a list of all the workbooks that this workbook is linked to
- Use Find (Ctrl F) to search for all cells (possibly across all the Worksheets/tabs in this Workbook) that contain the filename of the troublesome external workbook (chances are very good indeed that they'll be formulae and links to that external file); click on the Find All button
- Sort by Formula by clicking on the word Formula on the top of the Find and Replace results.

I find that by following these 3 steps, it's a lot easier to figure out what aspect of the formulae is presenting the problem.

Note that you can also fix some problems related to links to external Workbooks by replacing the formulae containing those references with their corresponding values. This is done with the Break Link button in the Edit Links box. One might wish this function to do the least it can do - for instance, if you have a formula =[other workbook]Sheet1!A1 * 10 and the referenced cell has the value 15, the formula value is of course 150. You might wish in this case that when you Break Link to the other workbook, the formula would be changed to =15 * 10. But that's not what Excel does - it replaces the formula entirely with the corresponding "naked" value 150. This is one of the reasons that most people don't use Break Link except in the most rare of circumstances. But those might be the very circumstances in which Deborah found herself.


2015-10-13 08:08:31

Beth

To view all formulas - Ctrl ~
Use the same to convert back to text.


2015-10-12 18:53:06

Deborah Petty

I have the same issue as Sheldon. In addition, I also get an 'available resources" error message, and then Excel crashes. I searched and found a link to another person's workbook in a formula. I fixed this and it helped, but apparently it was just part of the issue. Is there anyway to find/view all the formulas in a workbook, so I can review for errors/unnecessary links? Thanks in advance for any advice you can provide.


2015-10-12 17:30:22

Geoff hummel

Example that i pRomised yesterday.

Sub statistics()
' VarType constants
'vbEmpty 0 Empty (uninitialized)
'vbNull 1 Null (no valid data)
'vbInteger 2 Integer
'vbLong 3 Long integer
'vbSingle 4 Single-precision floating-point number
'vbDouble 5 Double-precision floating-point number
'vbCurrency 6 Currency value
'vbDate 7 Date value
'vbString 8 String
'vbObject 9 Object
'vbError 10 Error value
'vbBoolean 11 Boolean value
'vbVariant 12 Variant (used only with arrays of variants)
'vbDataObject 13 Data access object
'vbDecimal 14 Decimal value
'vbByte 17 Byte value
'vbUserDefinedType 36 Variants that contain user-defined types
'vbArray 8192 Array

Dim dateCell, usedCell, formulaCell, numericCell, stringCell As Integer
Dim aCell As Range

For Each aCell In ActiveSheet.UsedRange
With aCell
If Not aCell = Empty Then usedCell = usedCell + 1
If IsDate(.Text) Then dateCell = dateCell + 1
If .HasFormula Then formulaCell = formulaCell + 1
If VarType(.Value) = vbString Then stringCell = stringCell + 1
If (VarType(.Value) = vbDouble) _
Or (VarType(.Value) = vbInteger) _
Or (VarType(.Value) = vbLong) _
Or (VarType(.Value) = vbSingle) _
Or (VarType(.Value) = vbDecimal) _
Or (VarType(.Value) = vbLong) _
Then
numericCell = numericCell + 1
End If
End With
Next aCell

MsgBox "Used cells = " & usedCell & vbLf & _
"String cells = " & stringCell & vbLf & _
"Formula cells = " & formulaCell & vbLf & _
"Numeric (Double etc) cells = " & numericCell & vbLf & _
"Date cells = " & dateCell

End Sub


2015-10-11 22:29:11

Geoff hummel

VBA can be used to count such things like:
Number of used cells;
cells with specific formatting; cells with formulas;
etc.

I'll post an example when i get home tomorrow.


2015-10-10 08:08:26

sheldon hopkins

Unfortunately the 'statistics' aren't really useful. You can see most of them right clicking the filename in the "file explorer"

I've been running into operational failures with MSO2007 thru MSO2016, announced by a (paraphrased) somethings wrong with the file. Clicking Edit Anyway doesn't always let the file "run".
I have summerized that I have exceeded the maximum allowable formatted cells. I'm sure Excel keeps a number of file statistics that could help us "KNOW" exactly what the "something wrong" is.
I'd like to be able to access these statistics.


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.