The instructions for some of the tips you see featured in ExcelTips vary depending on the version of Excel you are using. If you are a relative newcomer to Excel, you may not know exactly how to determine which version you are using.
- If you want to save the workbook in the older format, you need to use only colors that are defined in the older versions of Excel. To see these, click on the Office button, pick Excel Options from the bottom of the frame, and go to the Save tab. Click on the Colors. Button, and you'll see the palate available for the older versions.
- Under Office Updates, the exact version number and build number are listed. To find out whether your version of Office is 32-bit or 64-bit, click “About Word”. The version and build number are listed at the top of the About dialog box along with either “32-bit” or “64-bit”. Click “OK” to close the dialog box.
- Create a new Excel/Word file with the same name as your lost Excel/Word file or find your present, saved-over Excel/Word file. Right-click on the Excel or Word file and select 'Properties'. Go to 'Previous Versions', find and select the latest version or the version you want back, click 'Restore'.
There are a couple of ways you can figure out which version you are using. The first is to watch Excel as you start the program. Depending on the speed of your system, you may notice the version in the splash screen that appears as Excel starts up. (I say that this depends on the speed of your system because I've seen some systems that are so fast, the splash screen is gone before anyone can fully see everything that is on it.)
Once you have started Excel, there is a better way to figure out your version: Choose About Microsoft Excel from the Help menu. Excel displays the About Microsoft Excel dialog box. (See Figure 1.)
To check the Version from Excel 2016 start Excel an click on File / Account. On the right side you see the office main version. In the scrrenshot it is 2016. Below you find the exact version. In the screenshot it is 1707 (Build 8326.2096) nfo Open Save As Histop Print.
Figure 1. The About Microsoft Excel dialog box.
Note that near the top of the dialog box you can see the version number you are using. When you are through reviewing the information, click OK to close the dialog box.
You can also use a single-line macro that will show you your Excel version:
The macro pops up a dialog box that shows a number, such as 8.0, 9.0, 10.0, etc. The versions for which this tip was created are Excel 97 (8.0), Excel 2000 (9.0), Excel 2002 (10.0), and Excel 2003 (11.0). (If you get a larger number, you are using a later version of Excel and should really follow the link in the following paragraph to the tip applicable to your version of Excel.)
Last updated on January 28, 2019
Posted by Felix Zumstein - Comments
Most Excel workbooks contain errors which in some cases lead to unpleasant “surprises”. Spreadsheet errors come in many different flavors: Some of them are easy to spot but others are much more subtle: When you forget to update an external data source for example or when you copy a formula from the cell above instead of from the cell to the left. Or you end up counting some cells twice etc. etc.
Since there are so many different errors, this blog post is concentrating on formula errors (as they are easy to find) and will leave other types of errors for future blog posts.
Table of Contents
![Excel Excel](/uploads/1/1/7/6/117649109/668761979.jpg)
What are formula errors?
As the name says, formula errors are caused by formulas or functions that return an error. Here is an overview:
#DIV/0!
: A number is divided by 0 or an empty cell.#N/A
: A value is not available to a formula or function. E.g. VLOOKUP doesn’t find a match.#NAME?
: Some text is not recognized in a formula. E.g. you use a named range with a typo.#NULL!
: Intersection of two areas that don’t intersect.#NUM!
: A formula or function contains invalid numeric values. E.g. ifIRR
can’t find a result.#REF!
: A cell reference is not valid. E.g. you deleted a cell that is used in another formula.#VALUE!
: Can occur if a formula contains cells with different data types. For example if you are adding two cells and one is a number and one is a letter.
Excel offers a few built-in ways to find errors in formulas, let’s go through them one by one:
Go To Special
On your ribbon’s Home tab, go to
Find & Select
> Go To Special...
(or via Ctrl-G
and Alt-S
):then select
Formulas
and check Errors
:When you click
OK
, Excel will format cells with errors on your active sheet in gray:Error Checking
You can also loop through the errors in a more convenient way (rather than just highlighting them as we did in the previous section): Go to the
Formulas
tab in your ribbon and click on Error Checking
in the section Formula Auditing
. This opens the following pop up from where you can click Next
to get to the next error:Inquire add-in
In the more recent versions of Excel, Microsoft has included the Inquire add-in. If you don’t see an
Inquire
tab in your ribbon, go to File
> Options
> Add-ins
. Then, at the bottom under Manage
, select COM Add-ins
and click on Go...
. In the pop-up check the box next to Inquire
. The tab in the ribbon should now show up.Once the
Inquire
tab is available in the ribbon, click on Workbook Analysis
and you will get an extensive analysis of the contents of your workbook. As an example, you can also list your formula errors:Automated error checking
By default, Excel shows you errors in formulas (and quite a few more) by highlighting the cell with a green triangle in the upper left corner of the cell. Select the cell and click on the trace error button that appears. This will explain the error as well as suggest help on it. If the error is expected, you can also ignore it:
To control which errors are marked with this green triangle, go to
File
> Options
> Formulas
:Peer review
How To Find Excel Version
An effective way to reduce errors and a good complement to automatic error checking are peer reviews. Peer reviews are standard practice in software development (i.e. a colleague looks at your changes before they will find their way into the code base).
For Excel, this has been a difficult task for the longest time as there were no good solutions for version controlling and peer reviewing Excel workbooks.
xltrail, a solution similar to GitHub or SharePoint, makes the task of peer reviewing changes in an Excel workbook trivial: It allows you to see what changed between two versions of the file and makes changes that may have happened in hidden sheets or columns visible:
For example, the above screenshot shows how deleting one column (in red) introduced a lot of
#REF!
errors that can easily be caught in a peer review process.![Excel Excel](/uploads/1/1/7/6/117649109/665179246.jpg)
Conclusion
How To Find Excel Version 2016
We have looked at a few different ways of how to spot formula errors in Microsoft Excel. Let us know in the comments below which method is your preferred one or if you use another technique to spot these type of errors.