Replacing an Excel Ledger with a SharePoint List — Sharing, History, and Flow Integration to Put an End to the Broken Ledger

“I opened Case Management Ledger.xlsx and it was read-only. I had no idea who had it open, so I made a copy and updated that — and now nobody knows which one is current.” “I sorted it and the rows shifted, so a note ended up attached to a different case.” We get these consultations frequently from companies running case management, inquiry management, equipment management, and similar ledgers in Excel on a shared folder.

Excel is excellent as a spreadsheet application, but as “the place where a ledger that several people add to and update concurrently lives,” it is structurally unworkable. If you have already deployed Microsoft 365, a purpose-built container for this use already sits in your hands: SharePoint lists (Microsoft Lists). Row-level concurrent editing, change history, typed input, and the path to notification and approval via Power Automate — everything a ledger needs is there.

This article sets out where the limits of an Excel ledger start to bite, how to decide on replacing it with a SharePoint list and how to carry out the migration, the design cautions starting with the 5,000 list view threshold, and how Power Automate integration turns the ledger into a “living ledger.”

1. The Bottom Line First

• The root cause of Excel ledger accidents (overwrites, row misalignment, no idea which version is current) is that the whole file is the unit of editing. A SharePoint list is edited and version-tracked per row (item), which structurally makes this class of accident far less likely.
• A SharePoint list can store up to 30 million items per list, and attachments on a list item can be up to 250 MB per file.¹
• The much-discussed 5,000 limit is the “list view threshold”, not a cap on how many items you can store. With indexed columns and narrowed views, you can operate well beyond 5,000 items.²³
• SharePoint Online lists have version history enabled from the moment they are created (50 versions by default). “Who changed which field, and when” is recorded from day one.⁴
• Microsoft Lists has an import feature that creates a list from an Excel workbook. The real work of migration, though, is unmerging cells, getting to one row per record, cleaning up inconsistent notation, and designing column types — all before you import.⁵
• The SharePoint connector is a standard connector, so within the scope of a Microsoft 365 license you can build notification, approval, and chaser flows starting from “when an item is created or modified.”⁶⁷
• Sheets whose main purpose is aggregation and analysis, and low-frequency ledgers touched by only one or two people, are fine as they are in Excel. Conversely, business data with many relationships, hundreds of thousands of records, or a need for transactional consistency belongs not in a list but in Dataverse or a business system (custom development).

2. Where the Limits of an Excel Ledger Bite

Across the consultations we take on, the points at which an Excel ledger starts to break down are broadly the same.

• Concurrent editing and overwrites. An Excel file on a shared folder becomes read-only if someone else has it open. People who cannot wait make a copy, “Ledger_latest.xlsx” and “Ledger_0715_Tanaka_edits.xlsx” multiply, and nobody knows which is authoritative. Co-authoring in the browser version of Excel does avoid the problem of not being able to open it simultaneously, but the problems below remain.
• Row misalignment and destruction. Pasting while a filter is applied, mis-selecting the range for a sort, inserting a row that shifts only the adjacent columns. The freedom of a spreadsheet is, for a ledger, also the freedom to destroy data. And the only clue as to who broke it and when is the file’s modified timestamp.
• Input rules are not honored. You can decide “status must be one of these five” and “dates are yyyy/mm/dd,” but a cell will take anything. Even with data validation configured, a copy and paste overwrites it easily. Inconsistent notation comes back as the cost of aggregating and searching later.
• Macros become one person’s property. A ledger where transcription or aggregation was automated in VBA turns into a black box the moment its author transfers or leaves. Macro-enabled workbooks also tend to suffer worse damage when a file is corrupted.
• You cannot separate “viewing” from “editing”. You end up handing an editable file even to people who only need to read it, and permissions cannot be separated.

At the same time, some ledgers are fine left in Excel. Those with one or two updaters and low update frequency; summary tables produced and distributed monthly; analysis sheets where formulas, pivots, and charts are the main act; forms where the print layout itself carries meaning. In these, either the “several people updating concurrently” problem does not arise, or it does but Excel’s strengths (calculation and layout) outweigh it. The criteria for deciding on replacement are summarized in the table in Section 7.

3. What a SharePoint List Is — Not a Grid of Cells but a “Database of Rows”

A SharePoint list is a feature of SharePoint included in Microsoft 365, and the same thing can be created and used from the Microsoft Lists app (opening a list in SharePoint directs you to Microsoft Lists — they are the same underlying object).⁸ The biggest difference from Excel is that the unit of editing is the row (item), not the cell.

A few notes on the features that matter in practice.

• Column types. With a choice column, status can only take the values you defined. A person column is tied to organizational accounts, so inconsistent renderings of an assignee’s name disappear. A date column can only take a date, which makes deadline evaluation and sorting reliable. A lookup column lets you build simple relationships that reference values in another list.⁹¹⁰
• Views. You can share the same data in purpose-specific presentations: “my items,” “due this month,” “incomplete only.” The practice of copying sheets in Excel and distributing them per person becomes unnecessary.
• Version history. For each item, when, by whom, and which column was changed is recorded, and you can revert to an earlier version. In SharePoint Online this is enabled by default from the moment the list is created.⁴ “Someone deleted it” and “I want to know the previous value” get answered by a standard feature.
• Alerts and rules. Simple rules that send a notification when a value changes can be configured on the list side alone.⁵ Elaborate notifications and conditional branching are Power Automate’s job (Section 6).
• Teams and mobile. Lists can be added as a tab in a Teams channel, and the same list can be worked with from desktop, web, and mobile.⁵ That makes it practical to update inquiry status from a phone out in the field.

4. The Practicalities of Migration — Clean Up → Design Columns → Import → Views → Switch Over

Migrating from an Excel ledger proceeds in the following order. The first two steps (cleaning up and column design) decide success or failure far more than the tool operations do.

(1) Cleaning Up the Existing Data Comes First

An Excel ledger will always contain structures that were only permitted because it was a spreadsheet. A list has a flat structure of one row = one item, so you tidy up on the Excel side first.

• Unmerge cells (merged cells cannot be brought into a list)
• Get to one row per record (resolve multiple cases in one cell, and single records spanning multiple rows)
• Get to a single header row (rename columns where there is a two-tier header)
• Standardize inconsistent notation (“In progress / in-progress / WIP” → align to the choice column’s candidate values)
• Delete intermediate subtotal rows and decorative blank rows
• Decide, for formula cells, whether to “keep them as values” or “reproduce them on the aggregation side after migration”

This cleanup is a migration task and at the same time a stocktake of the ledger’s design. It is normal to turn up a great many instances of “nobody has ever filled this column in” and “these two columns meant the same thing.”

(2) Designing Column Types

Decide which list column type each column of the cleaned-up Excel becomes. The standard choices are as follows.

Microsoft Lists can create a list by importing data from an Excel workbook, so an existing ledger can be your starting point.⁵ In practice, though, we recommend not leaving everything to the import’s automatic detection but deciding column types first and verifying them during the import (or designing an empty list first and pouring the data in afterwards). Choice and person columns in particular are the columns that determine the ledger’s quality, so if you start operating with them left as text columns, half the point of migrating is lost.

Also, for ledgers of more than a few thousand rows, do not plan on entrusting everything to a single import. From the moment the data lands, the 5,000-item threshold design covered in Section 5 becomes your first problem, and the larger the import, the heavier the column-type verification and the rework if it fails. The safe approach is to create an empty list with the column design settled, prepare the indexed columns and narrowed views that address the threshold, and then load the data in sensibly sized batches (rehearsing once with a subset of the real data).

(3) Views and Permissions

Set the default view to the shape of day-to-day work — something like “incomplete only, ascending by due date” — and provide the all-items view separately. This is also preparation for the 5,000-item threshold discussed below. For permissions, start by separating “people who can edit” from “people who can view only” at the list level, and limit fine-grained item-level permissions to cases where they are genuinely necessary (changing permissions once item counts grow is constrained; see Section 5).

(4) Parallel Running and Switching Over

Ideally you set a migration date and switch over all at once, but in reality there will be people who “carry on writing in Excel for a while.” If you do run a parallel period, declare that “the list is authoritative and the Excel file is a reference copy,” and make the Excel file read-only — that is the safe route. Let dual operation drift on and neither one ends up authoritative.

5. Limits and Design Cautions — 5,000, Attachments, and “What Excel Has That Lists Don’t”

Understanding the 5,000 List View Threshold Correctly

SharePoint Online has a default threshold of 5,000 on the number of items a single view (display, sort, filter, group, and similar operations) can handle at once. Exceed it and you get the error “The number of items in this list exceeds the list view threshold,” or failures when sorting or filtering a column.²³

The important point here is that this is not a cap on how many items you can store. The list itself can hold up to 30 million items.¹ What is restricted is “a query that sweeps a range of more than 5,000 items at once,” and the countermeasures are documented officially.

• Index the columns you filter on. Create column indexes on the columns that serve as axes for sorting and filtering (date received, status, assignee, and so on). Note, however, that indexes can only be added or removed while the list is under 20,000 items, so design this before the list grows large.³
• Keep every view under 5,000 items. Build filters on indexed columns into the default view — “this fiscal year,” “incomplete only,” “assignee = me.”³

For a small or mid-sized company’s case or inquiry ledger, a few hundred to a few thousand entries a year is typical, so this looks irrelevant for the time being — but a few years of accumulation gets you there. Create a default view filtered by fiscal year plus indexed columns from the start and you will not have to scramble later. Note also that once item counts exceed 100,000, other constraints on scale appear, such as being unable to break permission inheritance at the list or folder level.¹ If operations in the hundreds of thousands of records are visible from the outset, the decision is to choose a database (Dataverse or SQL) rather than a list (Section 7).

Handling Attachments — Attachment Column or Document Library?

You can attach files to list items, up to 250 MB per file.¹ In practice, though, two things need care.

• List version history records changes to columns, but changes to attachments are not versioned.⁴
• When manipulating attachments from Power Automate, the SharePoint connector handles attachments up to 90 MB.⁶

For files that themselves need version control or co-authoring — quotations, reports — a configuration where you store them in a document library and link them to the list by URL or reference number, rather than as list attachments, is easier to work with. Treat the attachment column as suited to light evidence subordinate to the item, such as “one scanned FAX page.”

What Excel Has That Lists Don’t

The thing we always check in a migration consultation is how much you depend on “things that were possible because it was Excel.”

• Free-form layout. Merged cells, two-tier headers, and tables where color alone carries meaning cannot be reproduced. Meaning moves into columns; presentation moves into views.
• Complex formulas across cells. Lists do have calculated columns, but they are no substitute for a complex web of calculations referencing an entire sheet, or for pivots. Split the roles so that aggregation and analysis are done by connecting the list’s data to Excel or Power BI. There are official procedures both for connecting to a list from Power BI Desktop to build a report and for creating a semantic model directly from a list in the Power BI service and refreshing it on a schedule.¹¹¹²
• Print layout. If the ledger doubled as the print form, the list’s screen is not a printable document. If printing is a requirement, you need a design that pours exported data into an Excel template, or a separately built reporting layer.

In short, the correct shape of the replacement is to reorganize around a division of labor: “input and sharing in the list, calculation and layout in Excel/Power BI.”

6. Power Automate Integration Makes the Ledger Move

The biggest dividend of moving to a SharePoint list is integration with Power Automate. The SharePoint connector is a standard connector, so you can build cloud flows within the scope of any Microsoft 365 license that includes standard connectors.⁶ Lists come with triggers such as “when an item is created” and “when an item is created or modified,” so registering or updating the ledger can itself be the starting point of a flow.⁶⁷

flowchart TD
    Excel[Existing Excel ledger] -- Clean up, design columns, migrate --> List[SharePoint list<br/>Case and inquiry ledger]
    Forms[Microsoft Forms<br/>Intake for requests] -- Transcribe responses --> List
    List -- Item created/modified trigger --> Flow[Power Automate<br/>Cloud flow]
    Flow --> Notify[Notify the assignee via Teams/email]
    Flow --> Approve[Route into an approval flow]
    Sched[Scheduled run] --> Remind[Chase overdue items]
    List -.-> Sched
    List -- Connect/export --> BI[Excel aggregation, Power BI reports]

There are three representative patterns.

• Notification. Send a Teams notification to the relevant channel when a new inquiry is registered; email the person when the assignee column is set. The ledger changes from something you “go and look at” to something that “tells you.”
• Approval. When the status changes to “approval requested,” start an approval flow and write the result, approver, and timestamp back into columns of the list. This configuration, where the approval trail lines up on the same row of the ledger, is covered in detail in the separate article “Building an Approval Workflow in Power Automate — Digitizing Paper and Email-Based Approval Requests.”¹³
• Chasers and stocktakes. On a schedule each morning, extract items that are “overdue and incomplete” and send each assignee a consolidated notification. Reminder flows using SharePoint are documented officially as a standard scenario.⁷ The design of scheduled runs including business-day checks and month-end processing is set out in “Designing Scheduled Flows in Power Automate — Month-End Processing, Business-Day Checks, and Reminders in Practice.”

There is just one design caution. If a flow using the “when an item is created or modified” trigger updates the list itself, that update can start the same flow again, producing an infinite loop. Rather than discarding the case in a later conditional branch, the standard approach is to write “only start when the status has a particular value” on the trigger itself, as a trigger condition. Updates that do not satisfy the condition produce no run at all.¹⁴ A trigger condition only stops the loop, however, when it is paired with the fact that the flow’s own write-back makes the condition false. If your flow starts “when the status is ‘approval requested’,” then part of the design is that the last thing it does is always update the status to some other value such as “in progress” or “approved.” If the status is still “approval requested” after the write-back, that update makes the condition true again and approval requests and notifications go out repeatedly. If you genuinely need a write-back that does not change the column used in the condition, add a “processed” flag column and include it in the trigger condition.

If you want to widen the intake further, a configuration where Microsoft Forms receives the input and a flow transcribes it into the list is effective (see “Building an Intake for Requests with Microsoft Forms and Power Automate”). Also, everything within the scope of this article can be built with standard connectors, but reaching for Dataverse or SQL takes you into premium license territory.¹⁵ The dividing line is set out in “Power Automate Licensing and the Boundary Between Standard and Premium Connectors.”

7. Decision Table — Stay in Excel / Move to a List / Build a Business App or Database

As a rule of thumb, we take the view that a list is enough for as long as the thing can be called a “ledger,” and that starting to make a list stand in for a business system is a red flag. Once lookup columns proliferate, flows tangle around each other, and views created to work around the threshold start multiplying, you have a problem that should be solved with a database and a business application. The virtue of lists is that they let you operate safely, on standard features with little additional cost, for the several years it takes to get there.

8. Summary

The problem with an Excel ledger on a shared folder is structural, not a matter of how careful the person handling it is. As long as the whole file is the unit of editing, overwrites, row misalignment, and uncertainty about which version is current will keep recurring. Move to a SharePoint list and editing becomes row-level, input becomes typed, changes are retained in version history, and permissions can be split by role.

The real substance of migration is “cleaning the data” and “designing column types” far more than the import operation itself. And once you understand that the 5,000 threshold is a limit on views, and prepare indexed columns and narrowed views from the start, you can use it with confidence for a long time. Decide the division of labor between attachments and document libraries, and leave calculation and layout to Excel/Power BI. A ledger that achieves this division of labor grows into a “living ledger” that runs notification, approval, and chasers automatically from Power Automate triggers.

And when the list starts to hurt, that is the sign to move on to Dataverse or a business system. How much to do with standard features, and where to start solving things with development — we take on consultations that start with exactly that boundary.

Related Articles

• Automating Business Processes with Power Automate — Cloud Flows, Desktop Flows, and Robust Error Handling
• Building an Approval Workflow in Power Automate — Digitizing Paper and Email-Based Approval Requests
• Building an Intake for Requests with Microsoft Forms and Power Automate
• Designing Scheduled Flows in Power Automate — Month-End Processing, Business-Day Checks, and Reminders in Practice
• Power Automate Licensing and the Boundary Between Standard and Premium Connectors

Related Consulting Areas

KomuraSoft LLC handles everything from design consultations on turning Excel ledgers into SharePoint lists and integrating them with Power Automate, through to custom development of the business databases and business applications that a list cannot contain.

• Technical Consulting & Design Review
• Windows App Development
• Contact

References

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These topic pages place the article in a broader service and decision context.

Where This Topic Connects

This article connects naturally to the following service pages.

Frequently Asked Questions

Common questions about the topic of this article.

Can a SharePoint list really only hold 5,000 items?

No. The 5,000 figure is the list view threshold — the default limit on the number of items a single view (display, sort, filter) can handle at once. The list itself can store up to 30 million items. To operate beyond 5,000 items, index the columns you filter on and make views that are always narrowed to fewer than 5,000 items (this fiscal year, by assignee, and so on) the defaults. Because adding an index is restricted once a list exceeds 20,000 items, it is important to design this before the list grows.

Can I create a SharePoint list directly from an Excel file?

Yes. Microsoft Lists can create a list by importing data from an Excel workbook, so an existing ledger can be your starting point. Before you do, though, you need to unmerge cells, restructure the data to one row per record, standardize the header row, and clean up inconsistent notation. Column types such as choice, person, and date columns are also the heart of what makes a list valuable, so rather than leaving them to the import, we recommend deciding your column design first and importing afterwards.

When is it fine to stay with an Excel ledger?

If only one or two people update it and they do so infrequently, if it is really an aggregation or analysis worksheet rather than a ledger, or if complex formulas, pivots, or print layouts are the main point, then staying with Excel is usually no problem. Conversely, a ledger where several people add and update entries day to day, where you manage state (in progress, complete, and so on), where you want to trace change history and who did what, or where you want to feed notifications and approvals, is well suited to a SharePoint list.

What can Power Automate do once the ledger is a SharePoint list?

The SharePoint connector is a standard connector, so within the scope of a Microsoft 365 license you can start cloud flows from triggers such as "when an item is created" and "when an item is created or modified." Typical examples are a Teams notification for a new entry, an approval flow triggered by a state change, and periodic chasers for overdue items. The accumulated data can also be referenced from Excel or Power BI for aggregation and reporting, so the ledger becomes a hub for notification, approval, and reporting.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and investigations into failures that are difficult to reproduce.