Unexplained Slowness

Do you have many conditional formatting, formats, array formulas?

Originally Posted by Pepe Le Mokko

Do you have many conditional formatting, formats, array formulas?

Yes, some, and no.

Seriously, I have about three columns with conditional formatting, and about eight columns in which the text is a color other than black. I don't have any array formulas.

The spreadsheet is a Table and I always have AutoFilters on, if those make a difference.

The workbook has many other worksheets, but most of them have no connection to the main one, and I haven't touched them in a while. They do include pivot tables that are connected to the main table, but I haven't touched them either; could they be refreshing themselves in the background?

One issue with Conditional Formatting is that the "areas" can get split up when you add and delete rows and columns. Over time, this can become a horrendous overhead with lots and lots of (repeated) Conditional Format conditions.

Another common problem is that formatting and Conditional Formatting inadvertently gets copied down the whole worksheet to the rest of the rows. Obviously, it's not visible until data is present in the cells ... but it requires processing and calculating.

Ideally, raw data (input), analysis and presentation should be kept separate. Clearly, with 7600 rows and 57 columns, you can't look at all that data visually, so you need to analyse it and present it in manageable chunks ... so, Pivot Tables, Charts, Dashboards, etc.

Regards, TMS

If the table is a real Data Table (Insert Table command) and not just a range with the Data Filters applied, apply the conditional formatting to the cells of the table. This way the Conditional Formatting will increase to include new rows in the table and not include rows not in the table. This should help to eliminate a bunch of repeated overlapping formatting.

If you add columns to the table, you can either expand the Applied To range or enter =Tablename. If you use the Table name like =Table1 the range will expand to include the whole table without having to enter cell references manually.