How to make your workbook run faster?

[tantcu]

hi guys,

I'm working on the excel workbook contains around 6000 rows and around 100 different columns. The size of the workbook is 6MB. I just come up with the formula SUMPRODUCT to calculate fiscal year data in the new sheet. However, it took like forever to run 6000 rows with that formula. I don't know why it takes so long and super slow. I managed other workbook with more than 20MB but it runs fine to me. When I tried to save the 6MB workbook it froze my screen and took more than 30 minutes with processor's loading at only 40%. Do you know the reason why or any recommendation to make it run better. I really need this formula to work.

Thanks

[shg]

How about a pivot table instead?

[tantcu]

I use the formula to make it as a table and ultimately convert it into a pivot table. Pivot table is my goal on this but before combining different date into year. i can not use a pivot table for 100 columns.

[krivozhnya]

I have similar tasks often and solve it using the macro based on principle below. As a result you get a 600,000 rows which pivot picks up like a charm. Adjust as needed if you need more values
for i = 1 to 6000'first to last row
for j = 1 to 100 ' first to last column
updatedsheet.cells(counter,1) = originalsheet.cells(i,1) ' label e.g. area
updatedsheet.cells(counter,2) = originalsheet.cells(i,1) ' label e.g. date
updatedsheet.cells(counter,2) = originalsheet.cells(i,j) 'value e.g. $
counter = counter + 1
next j
next i