Need to Improve Speed of Excel 365 Document

**Steve Bown** · 06-01-2023, 09:09 AM

Hi all,

I have just joined this forum so apologies if I am not sending this request to the correct audience.

I need help improving the speed of an Excel 365 document please.

I have a central Excel document (365 Apps) with multiple data queries that extract data from hundreds of separate files saved in a folder.

These files all contain the same 2 worksheets named Product Data and Critical Path.

In my central Excel document, I also have a separate worksheet that combines all of the Project Data and Critical Path information from each query in to a single view where each row represents a separate project and each column contains the project data and the corresponding critical path details in 1 consolidated view.

I then have a final worksheet which shows each project on a separate row and 365 columns to show a rolling 12 month calendar.

In this calendar view I have a formula with over 20 arguments that references the combined data to return a number if a particular task in the critical path is required on each day for each project.

This formula is repeated across all 365 columns and down 350 rows (12,750 cells).

I have conditional formatting to colour each cell in the calendar view to visually see when these tasks are required so I can manage workload.

Finally, I have a couple of basic formulas to count how many of each of the tasks are required on each day.

The long repeated formula in the calendar view is causing the file size to be over 20Mb and the file takes over 1 minute to open or save.

I have tried to add the arguments used in the long formula as multiple separate conditional formatting rules and removed the formula from the calendar view and this helps to increase the speed of the file greatly but by doing this I then can?t count the number of times each task appears in each date column.

I have found VBA code that will count cells with simple conditional formatting but this doesn?t work for complex conditional formatting.

Does anyone have any suggestions please?

Thank you

**FlameRetired** · 06-01-2023, 11:00 AM

Welcome to the forum Steve Brown.

Please see instructions for uploading a sample workbook in the 'gold' banner at the top of this page.
It is almost always easier to understand what is asked for if a sample Excel workbook is provided (not pics/screenshots).

Thank you ahead of time, and once again welcome.

**Steve Bown** · 06-02-2023, 06:37 AM

Hi FlameRetired,

I have stripped out a lot of data to get my attachment down to the required maximum file size so hopefully this is now visible?

The file I have attached is the version I have used with the conditional formatting which is much faster.

I did have a formula in cells Q3:Q7 on the Workload View worksheet which did a count of the cells in the rest of the column below it when I used the large formula in the calendar view instead of the conditional formatting but I need to find a way to count the number of cells in each column that contain each colour based on the formatting rules.

Thanks and regards

**Dal123** · 06-02-2023, 06:53 AM

I've been working on something similar for the past 5 months; a pricing database in Excel. The problem is that Excel is not a database, it can behave like one, but where a database will reference a small piece of data as the reference to that entire piece of info (primary key). Excel has improved greatly in this area over the past 30 years, however it is not a database. Conditional formatting... complex formulae take it's toll. Some formula's reduce overhead & only calculate if a change has been made e.g. index & match combined but we are limited with what we can do.

**torachan** · 06-02-2023, 09:37 AM

@Steve Brown, I hope I have translated your requirement properly - however try the attached there is a small macro on the orange refresh button - there are now no formula in range "Q3:NQ8"