How to speed up excel

[airtabaki]

I 'm a contractor and often I make Excel sheets to handle the specifications of a project. These files have the folowing form:

Nr/ Description work/ unity/ number/ price/ total price


under this heading there are a 5000 rows for instance:

01/ installation / h / 5 / 28€ / 140€
02/ configuration / h / 1 / 28€ / 28€

then there is a further manipulation of data, but limited to simple calculations

My problem: excel copies each formula for each row. After 5000 rows simple calculation become non performing. Is there a method to keep these files well performing with using excel and without manual calculation

 

[GlennUK]

What calculations are you requiring?

Glenn.

 

[airtabaki]

I simplified the system a bit because it brings us to far when I have to explain whole the system, but I have more columns (for instance for each large supplier one) and I calcule each time a total for each colum and a total per supplier per row.
Then there is an onther sheet where I've put some costs which I can't count to the client, but which I want to be spread out over the price per unit of all the items in the first sheet and which I can manipulate a little further

like you can see... the calculation are just adition and multuiples (divisions)

 

[GlennUK]

There are many things that can slow you down, and even though you say that you are just doing addition and multiplication and division, it depends on what objects you are referencing within your formula as to how much processing is taking place.

So, as you haven't included any of your formulae I will have to go through as many things as I can think of that might cause slowness...
Array formulae referencing large ranges
SUMIF covering large ranges
COUNTIF covering large range
Large occurences of INDEX or MATCH or HLOOKUP or VLOOKUP especially is they are referencing large ranges

If this hasn't helped you might want to do a Google search to look for tools that can monitor spreadsheet performance.

Cheers, Glenn.

 

[airtabaki]

Indeed I use systems like SUMIF for large range, but is there a way so I can do the same in a more performant way

 

[GlennUK]

OK then, I'll guess at what you're trying to do ... if you are doing SUMIF on every row to keep a running total for a particular category of data, why don't you instead have a new column that only sums or counts for that particular category, and so a running total could use a simple SUM instead of SUMIF.

Without more information that is all the help I can give you.

Glenn.