There are a lot of issues in Excel.
The most annoying one is “Excel cannot create or use the data range too complex”.
I’m sure, by referring above sentence you will not be able to figure out route cause of the problem.
Then, what will you do?
You will google it…
….there will be some workaround and the problem is solved [tempreoraly].
Some other day again same problem and same googling.
But the question is, are you ok with the temporary solution?
…maybe not
So let’s dive in and learn the route cause for the above issue and how to fix it permanently.
The background: Excel cannot create or use the data range too complex
You will most likely face the above problem when you work with filters in a large excel file.
Just imagine, you have an excel workbook with complete Global Sales data for your company (by country, region, product quarter etc..)
You just need Q3 Sales data for North America and for a certain product.
So, you will go ahead and apply a filter for the above criteria.
And then you will select visible cells (Alt+Semi colon) and hit (Ctl+-) or delete rows.
Boom…
Below error message pops up.
“Microsoft excel cannot create or use the data range reference because it is too complex, Try one or more of the following:”
But what’s wrong, why are we getting this error?
In simple terms, your workbook data is completely unorganized or your data is not in a clean sorted order.
Also Read: 51 Revolutionary excel Tips & Tricks to get things done quickly
The Problem:
Your workbook processing/calculation speed will decrease if you have unorganized data.
It’s very similar to having a bookshelf organized by category for easy pickup.
In General, if you have to delete certain row’s within your data, excel first has to perform Search and then it has to perform delete, again the same process for other rows as well.
So, this process will need a lot of resources (fast computing).
The Solution:
The solution for this problem is very simple, you just need to “Sort” your data by A to Z or Z to A.
This will ensure all your data is organized and results in faster processing time.
Also Read: 10 uncommon reasons for Excel formulas not working
Conclusion:
Based on my experience we can solve many problems by using simple sort. It hardly takes few minutes (or seconds) but it will save a lot of your time.
Try this technique from today, you will be amazed to see the results.
Similar to this, do you have any other techniques to improve workbook processing speed?
If so, please comment below.