Guidance needed to manage data effectively

[Tiny_t]

Hi, it's been a while since I found myself posting on the forum.

I currently manage a bunch of sheets and workbooks for our team, I use Office 365 and SharePoint to help automate and link all the sheets which is working great.
I do have some power queries for some of the sheets and a query linked to our system's DB for additional data, All working fine.

Most of the data is static with new datasets added on either a daily or monthly basis, other data sheets are updated on the go by the sales team as they go about the day.

All the data is processed using Pivot tables to analyze the data and charts to create a dynamic dashboard in my "Master sheet"

I am however starting to become concerned about resource usage as the datasets are becoming bigger and bigger and was wondering if someone here might be able to shed some light or guidance on doing things more efficiently.

My question is thus will it be more resource efficient to use multiple sheets on SharePoint and use Power queries and Linked sheets (my current method) or is better to set up an MYSQL database for the more static data and link the tables from the SQL data and keep the more dynamic data linked as sheets?

 

[bebo021999]

I can't provide feedback on your file since I can't see it. However, based on the statistical data, it appears that the number of cells containing formulas is quite significant. Are you using VBA? If not, you should consider using VBA, as it can make your file lighter and more efficient.

 

[Tiny_t]

unfortunately due to sensitive data in the sheet, I cannot share it, but I did use VBA on some of the tasks however most of the users that access the sheet use the web version to open and extract their data and I found that VBA scrips on the web version of 365 do not work resulting in mixed reporting. most formulae are just for the data to be pulled from the linked sheets.