Free Microsoft Excel 2013 Quick Reference

Significant loss of functionality


I brought excel files from workplace to home and work. At home, i use version 2007 version while 2003 at workplace.

Just now, when i worked on the file and to save it under "Excel 97-2003 workbook", a message pops up stating "significant loss of functionality". But it does not show me where the loss is. What can i do now to avoid problem when i open the file at workplace tommorrow?

Thanks you in advance for your assistance.

Post your answer or comment

comments powered by Disqus
I have a script which creates 3 workbooks, puts relative data in the workbooks, pivots the data, and saves the files as .XLS. One of the reports keeps experiencing an issue where the pivot table is changed to flat data upon re-opening. I threw the data in a new workbook, ran the script, and when I save I receive an error saying Significant loss of functionality due to a Pivot Table exceeding former limits. The problem is that the Pivot Table doesn't exceed 2003 limits. The data reference for the Pivot Table is a named range which references $A$1:$AF$42. The only other thing worthy of note is that the source worksheet is hidden as part of the script. I would greatly appreciate some help because I am at a loss since the other 2 reports work just fine. Here is the code that creates the Pivot Table.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
"Evaluation", Version:=xlPivotTableVersion12).CreatePivotTable _ 
TableDestination:="Classroom_Evaluation_Trending!R1C1", TableName:= _ 
"PivotTable2", DefaultVersion:=xlPivotTableVersion12 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Site") 
    .Orientation = xlPageField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Trainer") 
    .Orientation = xlPageField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Line of Business") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("WeekEnding") 
    .Orientation = xlColumnField 
    .Position = 1 
End With 
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
"PivotTable2").PivotFields("In Class Score"), "In-Class Score", xlAverage 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("In-Class Score") 
    .NumberFormat = "0.0%" 
End With 
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
"PivotTable2").PivotFields("ABay Score"), "A-Bay Score", xlAverage 
With ActiveSheet.PivotTables("PivotTable2").PivotFields("A-Bay Score") 
    .NumberFormat = "0.0%" 
End With 
With ActiveSheet.PivotTables("PivotTable2").DataPivotField 
    .Orientation = xlRowField 
    .Position = 2 
End With 
With ActiveSheet.PivotTables("PivotTable2") 
    .InGridDropZones = True 
    .RowAxisLayout xlTabularRow 
End With 
ActiveWorkbook.ShowPivotTableFieldList = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi, I am trying to save one of my spreadsheets to an earlier version of Excel.
When saving it gives me an error 'Significant Loss of Functionality'
Followed by : 'Some of the Formulas have more levels of nesting are supported by the selected file format.

It seems to be only one formula in the workbook and the formula is as follows:

=IF(H3=0,H2,IF(H2>0,H3,IF(H3>0,H4,IF(H4>0,H5,IF(H5>0,H6,IF(H6>0,H7,IF(H7>0,H8,IF(H8>0,H9,IF(H9>0,H10 ,IF(H10>0,H11,IF(H11>0,H12,IF(H12>0,H13))))))))))))

Each Cell from H2 to H13 will display the monthly account balance from that particular months spreadsheet.
The Formula (H14) is supposed to show the monthly total $ as the account balance varies from Jan-Dec. I only want the Current month to display when it varies from the previous months balance. (Progressive account total month by month) .

Each cell from H2-H13 displays blank when the Month's balance is 0.

If I save the workbook without changing, I get #VALUE Error in Cell H14.

Thanks in Advance,

I recently purchased 0ffice 2007 (had 2000). I have a spreadsheet I
made in 2000, but every time I save it (in 07) I get a "loss of
functionality" window that comes up.

Is there an easy way to make the entire spreadsheet 07 compatible, by
switching over somehow? Copying and pasting? Something?

I have several sheets I'd like to do this with.

(I'm using Excel 2007.) I get this warning from the Compatibility Checker when I save a particular workbook in .xls format:

"This workbook contains data in cells outside of the row and column limit of the selected file format..."

It's clearly wrong, since none of my data goes anywhere near the limits of IV65536. It says there are 60 occurrences, which I'm sure relate to the 60 drop down data validation lists. If I select one of those cells and hit save, I can save the file without this warning, but if another cell is selected, the warning appears.

Some users of this file use Excel 2003, some use 2007. Should I uncheck the "Check compatibility when saving this workbook" box? I guess it shouldn't matter since I won't be going outside the limits and I don't plan to use any 2007-specific formulas. Unchecking it should stop this warning from appearing for the 2007 users of this file, right?

Anyone got any information about why this happens? Just a bug? Ignore it? Check that box and forget about it?

I'm getting a warning message when I try to save a particular .xls file in Excel 2007. It's clear to me that the message is erroneous, but our customers will find it very confusing. If anyone can shed light on what causes the message or how to work around it, I'd be very grateful.

I open this particular Excel 97-2003 workbook in Excel 2007, and it shows that it's running in compatibility mode. So far so good. But then when I try to save it with another name but still file type Excel 97-2003 workbook (*.xls), the compatibility checker comes up and complains as follows:

--- start of compatibility checker message ---

The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format.

Significant loss of functionality

This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 256 (IV) columns by 65,536 rows will not be saved. Formula references to data in this region will return a #REF! error.

--- end of compatiblity checker message ---

Just after the message are hyperlinks for "Find" and "Help". If I click "Find", it goes to cell F42, which is well within the limit of 256 columns and 65,536 rows. If I click "Help", it goes to some generic help which is not useful.

The error message doesn't make sense anyway, because I'm in compatibility mode, having loaded an Excel 97-2003 workbook, so the original file couldn't possibly have any data outside the limits, and I haven't changed anything. Even if I did, compatibility mode shouldn't let me go outside the limits.

I get the same message if I just do Prepare->Run Compatibility Checker. The problem happens quite consistently with this workbook.

I've opened a support request with Microsoft about this, but if anyone has seen this before and has a suggestion, I'd love to hear it. Thanks.

When i do conditional formatting using a specific farmula and save my document, it gives me a warning and after I close the file and then reopen it. Conditional Shading is allover the column where i applied conditional formatting. I have to go and again apply the conditional formatting.


Please see the attachment for the warning it gives me when i save the file. I am trying to save in default file format i.e. excel workbook.

Compatibility Report for FALL 08 DATA FINAL.xlsx
Run on 5/19/2009 17:33

The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format.

Significant loss of functionality # of occurrences

Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all of the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting. 142

One or more cells in this workbook contain a conditional formatting type that is not supported in earlier versions of Excel, such as data bars, color scales, or icon sets. 3

Minor loss of fidelity

Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available. 25

I have a file developed in Excel 2003 that I brought up in Excel 2007, to
confirm functionality. Everything worked fine, but when I went to save the
file the compatibility checker reported "minor loss of fidelity" due to the

"Some formulas in this workbook are linked to other workbooks that are
closed. When these formulas are recalculated in earlier versions of Excel
without opening the linked workbooks, characters beyond the 255-character
limit cannot be returned.
Location: Defined Names
Number of Occurences: 15"

I did at one point copy some functionality from another workbook into this
one, but have checked formulas, defined names, and VBA code for outside
references, and see none. I moved the file to another computer with Excel
2003, then ran and saved the file with no mention of outside links. I don't
know what else to do to resolve the problem (assuming there is one). Anyone
have any ideas? TIA

Is there a function I can used to return the # of significant digits of a
cell value.
ie. If cell A2 = 1.042, I want cell B2 to equal 3


I have developed a .NET addin for excel and have noticed that once it
is installed I lose the Undo menu option completely.

>From reading previous posts, I am guessing that because my addin
intercepts excel events, then this is triggering the lose of the undo

Is there anything I can do about this? The undo command is very
powerful and my users are not exactly thrilled about the loss of this

Any comments would be most appreciated.


Syntax of Functions - GNU `make'

The text written for each argument is processed by substitution of variables and function calls to produce the argument value, which is the text on which the function acts.

I have the following vb code and I need to add a couple of functions:

Sub prop()
Dim rng1 As Range, rng2 As Range, c As Range
Set rng1 = Intersect(ActiveSheet.UsedRange, Range("A:G,I:O"))
If Not rng1 Is Nothing Then
For Each c In rng1
c.Value = WorksheetFunction.Proper(c.Value)
Next c
End If
Set rng2 = Intersect(ActiveSheet.UsedRange, [Q:IV])
If Not rng2 Is Nothing Then
For Each c In rng2
c.Value = WorksheetFunction.Proper(c.Value)
Next c
End If
End Sub

1. I need to delete the text (without quotes) "non" and "select..." from column P.

2. I need to replace all instances of "P.O." "P. O." and "p o" with PO in columns E and N.

3. I need to replace all (instances of more than one space) with one space " " In the whole sheet.

In all instances where column I contains "US" I need to limit column J to five characters.

I know I can do some of these with find and replace, but I do it daily on a new sheet.


I've written a VBA program to open a series of files,fix them, and save and
close them. I'd like to do this without having to sit in front of the
computer, but before it saves the file it opens up a window telling me that
there will be a "minor loss of fidelity" if it's saved. The original files
were written in Excel 2003 and I'm now using Excel 2007, and the colors are
the problem. But I don't care; I want to run the program and just save them
as is. Is there any way I can turn off the "minor loss of fidelity" warning?

I have a workbook that was originally created in Excel 2003. It has multiple
links to other workbooks. When I go to save the data, I receive a message
that states there is a minor loss of fidelity. "Some formulas are linked to
other workbooks that are closed.....Characters beyond the 255 character limit
cannot be returned." When I click on Find in the Compatibility Checker, it
shows 5 cells as the problem. The cells contain If statements. I don't
understand what the problem is though. The formulas only contain 126
characters. Can anyone help me? Thanks so much!!

=IF('H:EvaluationsTest 2008-2009[questions.xls]Sheet1'!A20,
'H:EvaluationsTest 2008-2009[questions.xls]Sheet1'!A2, "")

How do correct a loss of fidelity error when saving excel work book?

I had a sheet with combinations of Function keys, Ctrl, Alt & Shift and what
they do as keyboard shortcuts...but I've lost it and don't remember how I got
it the first time.
I want to obtain another sheet like that.

I often need explanations of functions and their formats. The old "fx" icon
and the resulting list of functions was terriffic.

I can't find any way to get that list of functions.

The new help just finds the word 'function' anywhere and that's not helpful

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane. unctions

Document was originally created in Excel 2003. Basic file with a few
forumlas. When we save in 2007 we get an MS error "loss of fidelity"

Any ideas?


Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF( D6=0,SUM(D712),IF(D5=0,SUM(D612),SUM(D512))) )))))

Any help would be greatly appreciated

Thanks ...

--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from =-

Does anyone know where to get a list of all the functions contained in the
add-ins, especially the Analysis Toolpak and VBA Analysis Toolpak. I've
looked in Excel help and have found a list of functions by category, but no
summary of which ones appear in those toolpaks.

If anyone knows of a helpfile / website / whatever that provides a simple
list detailing which functions require which add-ins to be installed it would
really help me.

Many thanks in advance, Neil

Far from madding crowd and fascism of function - Opinion -

The headlines were grabbed by the Archbishop of Canterbury's attack upon US foreign policy. But the deeper point, widely missed, was his attack upon Western modernity in general ...

I have a workbook with multiple pivot tables on a page. The rest of the page has other content. When I hit refresh on the pivot table, it is causing a loss of formatting in the pivot table itself in some ways, but also in other cells on the worksheet. Things like cell borders, shading, font size, etc. Also, the loss of formatting is inconsistant. For example:

The page title loses all formatting
Pivot Table 1 loses cell borders.
Pivot Table 2 loses cell borders and changes all shading from green to grey.
Yet changed font colors in certain fields remain totally unaffected.

Does anyone have any ideas why it could be doing this? I need to find out before I go much farther. I have completed this workbook once already and am recreating due to other bugs that developed.


In Excel 2000 loss of network connection with an open excel workbook
creates problems then saving the workbook, even to the local drive of
the pc. (Loss of connection on this occasion was from pc left on
overnight, server connected to did a reboot from a windows update)

Has this been resolved in later versions of excel?

Hi, I'm just wondering if there is a way I can cut and paste the results of functions that are outputted from Excel instead of the functions themselves?

say A1 was function =(2+2)

Is there a way I can cut and paste it in to A2 as 4 instead of =(2+2) ?


Seeing a problem that could possibly be an excel glitch in the days360

I'm running O2k3sp2 and have tried this on several machines including those
outside of our company build environment.

When attempting to calculate the number of days using this function between
the dates 2/27/2006 and 3/27/2006, I return the number 30 (which is correct
based on days360). Though when entering the dates 2/28/2006 and 3/28/2006 I
return 28 days. My understanding is that this function should always return
30 days in a month, no matter which month is chosen. Can anyone enlighten me
as to why this is happening? The result has been a significant loss of funds
within our securities team in regard to interest accrual.


No luck finding an answer? You could always try Google.