save excel 2010 with nested formulas into a compatible version for 2007

I have a few locations that are running 2007 version of office.  At the home office we use 2010 or 2013.
We have some excel documents that are using nested formulas that requires 2007 or above to work.
How can I save them as 2007 in 2010 or is there a compatibility pack I can install other than compatibility service pack 1 and 2 for office 2007?

If I save them as 97-2003 then the nested formulas do not work.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What happens if you just open the 2010 file using Excel 2007?  Do the formulas work?

What are the formulas?

Rob HensonFinance AnalystCommented:
Why are you saving as 97-2003 when you say you need compatibility with 2007?

From 2007 onwards the majority of the functions have stayed pretty much the same, the number of nested functions went up to 64 (2003 and prior only had 7). I assume you don't have more than that.

The following link shows restrictions for Excel 2010

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

bbimisAuthor Commented:
functions do not work if ran on 2007 with compat packs installed.
I say 97-2003 because that appears to be the closest option I have for xls.
Rob HensonFinance AnalystCommented:
So, to repeat earlier question, which functions are not working?

Saving as 97-2003 will only make things worse.
bbimisAuthor Commented:
understood that's what I'm trying to save it in 2007 format.
as for what functions are not working. it would be all the functions that work in 2010s version. Can't list them.
Rob HensonFinance AnalystCommented:
I am not aware of any functions that are new to 2010 that wouldn't work in 2007. Please supply some examples of functions that aren't working.
C. Blaise MitsutamaLMS Administrator | Instructional DesignerCommented:
bbimis, there is no special compatibility pack for saving a 2010 file as 2007. There are no significant changes to functions in 2010 that would break if opened in Excel 2007. There may be something else in the way the functions are used or in the capacity updates between 2007 and 2010 that may be causing the problems you're seeing.

It would be useful to see the nested IF statement(s) you're using, the size of the data set(s) the functions are operating on, where the data is stored relative to the Excel file (e.g., is all the data contained in the Excel file itself or is the data located on a server or in a folder), etc.

C. Blaise MitsutamaLMS Administrator | Instructional DesignerCommented:
Also, I note that you state: "I say 97-2003 because that appears to be the closest option I have for xls." Why are you trying to save the files as .xls? Excel 2007 can use .xlsx files. If you are trying to save .xls (Excel 97-2003) files, the nested functions may be breaking because there was a limit of 7 nested statements in Excel 2003. Excel 2007 and 2010 can both have up to 64 nested statements, and both 2007 and 2010 use the file extension .xlsx.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbimisAuthor Commented:
thanks figured it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.