Replacing a multi-user Excel macro file with a SharePoint List (and Power BI integration)

[u/Dust2_Player]

[u/Due-Boot-8540]

How complex is the spreadsheet and what do the macros do? Rather than just migrating to a list, I’d look into ways to break it into smaller lists and probably some automation.

Power Apps might be a good option

[u/Dust2_Player → u/Due-Boot-8540]

To give you an idea of how my current Excel workbook is structured, here is how the data entry works:

• Form 1 (Data entry by an internal department, e.g., Production): Used to create a new row and fill in up to 31 columns (not all fields are mandatory). It includes conditional validation rules, for example, if a field is set to "KO", the user is required to fill in 4 additional fields. This submission populates two separate tabs: one for Department 1 and one for Department 2.
• Form 2: Used to edit or complete the data created in Form 1, so it complete tab for Department 1.
• Form 3 (Data entry by another internal department, e.g., Quality): Used by another department to add even more details specific to their role. It complete tab for Department 2 (27 columns).

Note: Right now, I split the data into two tabs, though I could have kept it in a single one; I did it mainly to make it easier for each department to read.

To give you an example of the volume, for the year 2025, there were 1,988 rows created in each tab.

[u/PimpInTheBox1187]

I live by the motto "Excel is not a datasource"

That sounds like something Poweapps can do, use tabs to separate your fields. Use event handlers to populate other fields based on a fields value.

[u/RumblyBelly]

If you have less then 10k enterys in list use HTML file for live data.

If its calculation havy macro file I would do Automations in Excel that are run with Power Automate

[u/Dust2_Player → u/RumblyBelly]

I replied to the other comment if you want more details, but basically it's around 2K rows per year

[u/whatdoido8383]

Sounds doable. Architect your views to return less than 5000 rows. You'll probably need to use power apps for the form(s).

[u/sam_ibrahim]

Iam here if you would like any assistance