Spreadsheet calculates too slowly

wflabqueen

New Member
Joined
Sep 26, 2014
Messages
5
I have a mid-sized workbook ~14MB that takes far too long to calculate. One sheet contains all a data table with ~32,000 lines and 44 columns. 16 columns have formula and the rest are just text or number, four of which use INDIRECT which I know is a volatile function. One sheet contains all my pivot tables (which are not all built yet) and one sheet is the dashboard (not completed either). I have turned off automatic calculation and set the calculation to use all processors to speed things up which has helped. But I am afraid that when all the pivot tables are built this will calculate even slower. I have VBA code that copies data from another source to populate the data table and shows as linked data in Info.

What I would like to happen is:

A) Add VBA code to recalculate the formulas in the data table and refresh the pivot tables once certain cells are selected and changed.

B) figure out any other way to speed this workbook up.

I have read many posts here to get answers to other questions and can do some VBA code but this is my first post. Any help is welcome.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
Are any of the formulas look ups or using entire rows/columns to calculate? ie. A:A or 1:1
 

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
Replace all volatile functions and your speed will increase significantly. Post the functions you are using if you want further assistance.
 

wflabqueen

New Member
Joined
Sep 26, 2014
Messages
5
Yes, unfortunately. Things were working well until I wanted a way to identify unique values so when I added =1/countifs(range1,criteria1, range2,criteria2) it began to churn slow. The ranges are entire columns.
 

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473

ADVERTISEMENT

the countifs using the entire column will slow you down. Limit that to the last row your data is in. If the length of your data is ever-changing consider using a dynamic named range.
 

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
If the ranges are entire columns then using a combination of Index and match is far superior than using Indirect.

Post a sample of your indirect formula if you like.
 

wflabqueen

New Member
Joined
Sep 26, 2014
Messages
5

ADVERTISEMENT

Column AC =IF([@[Ship To]]="","",SUM(INDIRECT($AC$9&ROW(AB15)&":"&$AC$4&ROW(AB15))))
Column AD =SUM(INDIRECT($AC$11&ROW(AB15)&":"&$AC$10&ROW(AB15)))
Column AE =IF([@[Ship To]]="","",IF(AND([@[Recent Sales]]=0,[@[Previous Sales]]>0),"Lost",IF(AND([@[Recent Sales]]=0,[@[Previous Sales]]=0),"NoSales","")))
Column AF =IF([@[Lost Value]]="","",1/(COUNTIFS([Ship To],[@[Ship To]],[Lost Value],[@[Lost Value]])))

The next set of columns repeats these formulas but for different fields.

Column AQ =INDEX(Customerdata[[#Headers],[January 2013]:[Dec-14]],1,MATCH(99990000000,Customerdata[@[January 2013]:[Dec-14]],1)) - to find the last month of data in the row
Column AR = =1/(COUNTIF([Ship To],[@[Ship To]]))

I need to keep the indirect as I am asking the user to enter the number of months that they want to look at - it takes this value and translates it to the correct column.
 

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
What values do you expect in AC9 and AC4?
 
Last edited:

wflabqueen

New Member
Joined
Sep 26, 2014
Messages
5
AC9 returns a column letter. So the user enters the number of months they want to look at, 1-9, then it takes this number and subtracts it from the current months column and then the CHAR formula gives me a letter. As an example if the current month is September - which is column Y in my table - and they want to look at the previous 3 months - the sum of W,X and Y then AC9 would return W

AC4 is the current month column which would be Y

I hope I am being clear - I read posts that make my head hurt sometimes.
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
This formula is non volatile and will sum data in rows 1:10 matching the header in row 1. Maybe you could work with this?

=SUM(INDEX(1:10,,MATCH("header",1:1,0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,651
Members
414,083
Latest member
Mrsash

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