Getting a 'formula too long' error when trying to save.

Shiremaid

Board Regular
Joined
Jun 18, 2013
Messages
73
"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid this limitation save the workbook in the Excel binary Workbook format"

So I have a workbook with about 40 tabs.
I went in and on one tab inserted a copied row (from the same tab). There are some longer formulas in the rows, but even the longest formula is only a little over a hundred characters. I changed my view to see the formulas in the cells instead of the results and don't see any on the tab that don't match what I put in. I didn't do anything to the other tabs.

This has happened to me before and I just closed without saving and remade my change with no problem. However, I would really prefer to prevent the error from occurring at all as I am not the only one using this workbook and sometimes we need to make multiple changes at a time.

Does anyone know how or why this is happening?
This is the beta test of a "database" and I would really like to know if I can prevent this error from coming back in the rebuild to go live with our projects
(Also, I know Excel is not a database program and we have a real one coming but can't wait until it's arrived to have something at least minimally functional)

Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
make a copy
make a copy of the copy
delete all formulas in tab1
save
if saves ok the problem is not in tab1
continue and test all 40 tabs
say problem in in tab7
start deleting row by row
etc etc
you will home in on the issue
 

Shiremaid

Board Regular
Joined
Jun 18, 2013
Messages
73
Thanks for the suggestion oldbrewer.
However, my problem isn't getting rid of the error after if happens, it's trying to figure out how it's happening in the first place.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
first step is to find out all you can about the error - where it is - what sort of formula..........
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213

ADVERTISEMENT

you get passed the restricted amount of if-formula?
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
if it saves ok after deleting tab1 the problem is in tab 1
if it doesnt, delete tab 2 also - - - and so on....
 

Shiremaid

Board Regular
Joined
Jun 18, 2013
Messages
73

ADVERTISEMENT

you get passed the restricted amount of if-formula?
- What is the restricted amount of if formula?

I tried to find out where the error had happened, unfortunately I have also been having a problem with the spreadsheet crashing and it did so during the process. And as I said in my original post, the error seems to happen randomly so when I re-opened the document and made the exact same change as I had before, it saved fine with no error.
So I am wondering if there were any known problems that could cause excel to randomly think a formula is too long or to randomly change one so that it is too long.
 

thuhoang

New Member
Joined
Jan 8, 2021
Messages
2
Office Version
  1. 365
For me after trying to delete the conditional formatting, running Macro, saving in binary and older Excel version 2003 and prior (having about 1/5 of data capacity than new Excel version 2019) did not work for me, I have to recreate the file by copying and pasting each individual tab to the new file to detect and eliminate the tab with "over 8192 characters in formula", which i believe is some kind of coding error in Excel to misread text as formula. Hope it helps!
 

Forum statistics

Threads
1,141,300
Messages
5,705,577
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top