Complete guide to building custom business applications with SharePoint Lists — column types, views, JSON formatting, Power Automate, Power Apps, governance, and enterprise use cases.
SharePoint Lists (and Microsoft Lists) are structured data stores that power IT ticketing, project tracking, asset management, approval workflows, and custom business apps — without code. This guide covers column types, views, JSON formatting, Power Automate automation, Power Apps forms, governance, performance limits, and enterprise use cases.
By Errin O'Connor, Founder & Chief AI Architect, EPC Group
What are SharePoint Lists and how do enterprises use them? SharePoint Lists are structured data collections in Microsoft 365. They serve as lightweight business databases with several key features:
Enterprises use SharePoint Lists to replace shared Excel files, Access databases, and paper forms. This creates governed, automated, and mobile-accessible business applications. Common use cases include:
Unlike spreadsheets, SharePoint Lists support hundreds of concurrent users. They enforce data integrity at the column level and provide a full audit trail of every change.
SharePoint Lists are an important feature in Microsoft 365 that many organizations miss. All organizations with a Microsoft 365 license can use SharePoint Lists. However, most users mainly use SharePoint for document storage. This results in the list feature being underused. Teams often manage key business processes through:
The difference between a SharePoint List and a spreadsheet is significant. A spreadsheet is a flat file with limitations, while a SharePoint List is a structured database.
In contrast, a SharePoint List:
EPC Group has deployed enterprise SharePoint List solutions for Fortune 500 organizations that replaced legacy tracking systems with governed, automated applications — reducing manual effort by 60-80% while improving data quality and compliance auditability.
The most common question EPC Group receives about SharePoint Lists is: "Why not just use Excel?" The answer varies based on the use case.
Excel is ideal for:
In contrast, SharePoint Lists are better suited for:
The decision framework is straightforward. Use a SharePoint List in the following situations:
If you are exploring data, building a one-time model, or working alone, use Excel.
| Capability | SharePoint List | Excel |
|---|---|---|
| Concurrent editing | Hundreds of users, item-level locking | Co-authoring with merge conflicts above ~10 users |
| Data validation | Column-level type enforcement, required fields, choice constraints | Optional validation rules easily bypassed |
| Permissions | Item-level, column-level (via views), role-based | File-level only — all or nothing |
| Version history | Every field change tracked with timestamp and user | File-level versions only, no field-level tracking |
| Workflow automation | Native Power Automate triggers on create/modify/delete | Requires VBA or external tools |
| Scale | 30 million items per list | Degrades above 100K rows, hard limit 1M rows |
| Mobile access | SharePoint mobile app, responsive views | Excel mobile — limited for data entry |
| Reporting | Direct Power BI connector, no export needed | Power BI import or copy/paste |
| Cost | Included in Microsoft 365 — no additional license | Included in Microsoft 365 |
SharePoint Lists and Dataverse (formerly Common Data Service) both store structured data within the Microsoft ecosystem. However, they cater to different levels of application complexity.
The decision depends on three key factors: relational complexity, licensing budget, and integration needs.
EPC Group assists enterprises in making informed platform choices during the solution architecture phase. Many organizations spend months developing complex multi-list SharePoint solutions. These projects often would have been better served by using Dataverse from the beginning.
Additionally, some organizations buy Dataverse licenses when a simple SharePoint List would have been enough.
The right choice depends on:
Single-table tracking application (under 20 columns)
No additional licensing budget available
Simple lookup relationships between lists
Power Automate workflows for basic automation
Standard SharePoint permissions are sufficient
No Dynamics 365 integration required
Multi-table relational data model required
Many-to-many relationships between entities
Business rules, calculated fields, rollup fields at data layer
Integration with Dynamics 365 apps
Security model needs business units and security roles
Application will scale to complex enterprise solution
Choosing the correct column type is the foundation of a well-structured SharePoint List. Each type enforces specific data validation and enables specific formatting and automation capabilities.
Use for: Names, titles, short descriptions
Validation: Max 255 characters, required field option
Enterprise tip: Use for primary identifier columns with unique value enforcement
Use for: Status, category, priority, type
Validation: Restricted to defined options, dropdown or radio
Enterprise tip: Standardize choice values across lists using site columns
Use for: Owner, assigned to, reviewer, approver
Validation: Validates against Azure AD, supports multi-select
Enterprise tip: Use for accountability tracking and automated notifications
Use for: Due date, start date, completion date
Validation: Date picker, optional time, date-only option
Enterprise tip: Index for view performance, use in Power Automate date calculations
Use for: References to other lists (projects, departments)
Validation: Enforces referential integrity to source list
Enterprise tip: Limited to 12 per view — plan lookups carefully in complex solutions
Use for: Taxonomy, classification, tagging
Validation: Term store controlled vocabulary
Enterprise tip: Essential for consistent classification across the tenant
Use for: Formulas based on other columns
Validation: Excel-like formulas, read-only output
Enterprise tip: Cannot reference lookup columns — use Power Automate for complex logic
Use for: Quantities, costs, scores, percentages
Validation: Min/max range, decimal places
Enterprise tip: Use for metrics that feed into Power BI reports
SharePoint List views present data customized for specific roles. A single list can feature multiple views. Each view can display:
This setup enables effective data presentation without duplication.
JSON column formatting enhances views with visual elements. You can add features without coding or deploying solutions. These include:
JSON formatting uses a declarative schema. You describe how the column should appear based on its value, and SharePoint displays it accordingly.
To achieve enterprise standardization, develop a JSON formatting library. This library should include pre-built JSON templates for common patterns such as:
Store these templates in a central SharePoint site. Instruct site owners to use these templates rather than creating their own formats. This approach ensures visual consistency across all lists in the organization. It also makes it easier for new list creators to learn.
Traditional table layout with rows and columns. Best for data-dense lists with many columns. Supports column formatting, grouping, and totals.
Card-based layout showing items as visual tiles. Best for lists with images or when visual scanning is preferred. Customizable with JSON view formatting.
Kanban-style columns based on a choice column. Best for status-driven workflows (To Do, In Progress, Done). Items are moved between columns by drag-and-drop.
Calendar layout using a date column. Best for event tracking, deadline management, and scheduling. Supports day, week, and month views.
Power Automate changes SharePoint Lists from simple data stores into active business process engines. Each action, such as creating, updating, or deleting a list item, can start an automated workflow. This automation can include:
The combination of SharePoint Lists and Power Automate replaces 80% of the scenarios that previously needed custom-developed applications.
Enterprise automation patterns go beyond simple notification emails. EPC Group develops multi-stage approval workflows. These workflows allow a new list item to trigger:
The routing of these approvals is conditional based on the item's attributes.
Escalation flows monitor the age of items. They automatically escalate items that stay unchanged within a set SLA window.
Cross-system integration flows create corresponding records in:
These records are generated when items are created in SharePoint Lists.
Trigger: Item created or status changed to "Pending Approval"
Actions: Route to manager based on department field, send approval email, update status on response, notify requester of decision
Complexity: Medium — handles sequential and parallel approval chains
Trigger: Scheduled (runs every 4 hours)
Actions: Query items where Modified date exceeds SLA threshold, send escalation email to item owner and manager, update Priority to Critical
Complexity: Low — scheduled flow with date calculation and conditional logic
Trigger: Item created or modified
Actions: Map list columns to external system fields, create or update record in Dynamics 365 / ServiceNow / Jira via connector, store external ID in list item
Complexity: High — requires error handling, retry logic, and conflict resolution
Trigger: Item modified (specific field change)
Actions: Compare previous and current values, identify which fields changed, send targeted Teams message or email to affected stakeholders
Complexity: Medium — requires trigger conditions to avoid notification fatigue
SharePoint List governance is essential for keeping list-based solutions efficient, secure, and easy to manage as usage grows. Without proper governance, organizations may face issues such as:
The governance framework should cover key areas like list creation policies, naming conventions, column standardization, permission models, archival schedules, and performance monitoring.
Performance tuning addresses the 5,000 item list view threshold in SharePoint. This limit is often misunderstood. It is important to note that the 5,000 threshold does not mean a list cannot contain more than 5,000 items.
Instead, it means that a single view query cannot return over 5,000 items unless an indexed column is used in the filter.
The solution is to:
When properly indexed and filtered, lists can hold up to 30 million items.
Max items per list: 30 million
Hard platform limit — archive before reaching this
List view threshold: 5,000 items
Index filter columns to query beyond this limit
Lookup columns per view: 12 maximum
Plan lookup columns carefully in complex solutions
Unique permissions per list: 20,000
Item-level permissions degrade performance above 5,000
File attachment per item: 100 MB
Use document libraries for large file storage
Columns per list: Varies by type
64 single-line text, 16 lookup, 255 choice columns max
SharePoint Lists power business applications across every department. These are the most common enterprise use cases EPC Group implements for Fortune 500 organizations.
Replace email-based IT support with a structured ticketing system. Auto-assign tickets based on category, escalate based on SLA, track resolution time, and report on support metrics in Power BI.
Reduced average ticket resolution time by 40% for a 5,000-employee organization
Track project milestones, tasks, owners, due dates, and status with board view for Kanban and gallery view for executive summaries. Power Automate sends weekly status digests to stakeholders.
Consolidated 15 project tracking spreadsheets into one governed list
Track hardware and software assets with assignment history, warranty dates, location, and condition status. Automated alerts for warranty expiration and maintenance schedules.
Eliminated $200K in unnecessary asset purchases through visibility into existing inventory
Checklist-driven onboarding with tasks assigned to HR, IT, facilities, and managers. Automated task creation when new hire is added, progress tracking, and completion reporting.
Reduced onboarding completion time from 3 weeks to 5 days
Track contracts with renewal dates, values, owners, and approval status. Automated 90-day renewal reminders, approval workflows for new contracts, and compliance audit trail.
Prevented $1.2M in auto-renewed contracts that should have been renegotiated
ITIL-aligned change management with risk assessment, approval workflow, implementation tracking, and post-implementation review. Integrated with ServiceNow via Power Automate.
Standardized change management across 12 IT teams with full audit compliance
Enterprise SharePoint implementation, migration, and managed services from EPC Group.
Read moreEnterprise guide to managing projects with SharePoint, Microsoft Lists, and Power Automate workflows.
Read moreConnect SharePoint Lists to Power BI for real-time enterprise reporting and analytics dashboards.
Read moreSharePoint Lists are structured data collections within SharePoint Online and Microsoft 365 that function as lightweight databases for tracking, managing, and automating business information. Unlike spreadsheets, lists provide column-level data validation, permission control, version history, Power Automate workflow integration, and Power Apps custom forms. Enterprises use SharePoint Lists for IT help desk ticketing, project tracking, asset management, employee onboarding checklists, contract management, change request tracking, and compliance audit logs. EPC Group has deployed SharePoint List-based solutions for Fortune 500 organizations that replaced legacy Access databases and shared Excel files with governed, automated, mobile-accessible business applications.
The key differences are: 1) Concurrency — SharePoint Lists support hundreds of simultaneous users editing different items; Excel has co-authoring limits and merge conflicts, 2) Data validation — Lists enforce column-level validation rules, required fields, and choice constraints at the platform level; Excel validation is easily bypassed, 3) Permissions — Lists support item-level permissions and role-based access; Excel is all-or-nothing file access, 4) Automation — Lists integrate natively with Power Automate for workflow automation; Excel requires VBA or external tools, 5) Version history — Lists track every field change with full audit trail; Excel tracks file-level versions only, 6) Scale — Lists support 30 million items per list; Excel degrades above 100K rows. Use Excel for ad hoc analysis. Use SharePoint Lists for structured business processes with multiple users.
SharePoint Lists are included in every Microsoft 365 license at no additional cost and are ideal for departmental business applications with moderate complexity. Dataverse (formerly Common Data Service) is a premium data platform included with Power Apps per-app or per-user licenses and is designed for complex, enterprise-grade applications with relational data models. Key differences: Lists support simple column types and lookup relationships; Dataverse supports full relational modeling with one-to-many and many-to-many relationships. Lists have a 12-lookup-column limit per view; Dataverse has no practical limit. Lists are governed through SharePoint site permissions; Dataverse has its own security model with business units and security roles. Choose Lists for simple tracking apps (under 20 columns, single-table). Choose Dataverse for complex multi-table applications, Dynamics 365 integration, or when you need computed columns, rollup fields, and business rules at the data layer.
SharePoint Lists support these column types: Single line of text, Multiple lines of text (rich text or plain), Choice (dropdown or radio buttons), Number, Currency, Date and time, Yes/No (boolean), Person or Group (user picker from Azure AD), Hyperlink or Picture, Lookup (references another list), Calculated (formula-based on other columns), Managed Metadata (term store taxonomy), Location, Image, and Thumbnail. Each column type enforces data validation at the platform level — a Number column rejects text input, a Person column validates against Azure AD, and a Choice column restricts values to the defined options. For enterprise use, Managed Metadata columns are essential for consistent taxonomy across lists, and Lookup columns create relationships between lists that approach simple relational database functionality.
SharePoint List views control which columns are visible, how items are sorted, grouped, and filtered, and which items appear based on conditions. To create a view: click the view dropdown, select "Create new view," choose a view type (list, compact list, gallery, board), configure visible columns and their order, add sort rules, add filter conditions, and optionally group by one or two columns. Enterprise best practices: 1) Create role-specific views — "My Open Items" filtered to current user, "All Active" for managers, "Overdue" for escalation, 2) Use gallery view for visual card-based display of items with images, 3) Use board view for Kanban-style status tracking, 4) Apply JSON column formatting to add color coding, progress bars, and conditional icons without Power Apps.
JSON column formatting applies custom rendering to list columns without code deployment. In the column header dropdown, select "Format this column" and enter a JSON object that defines how the column should display. You can conditionally change background colors, font colors, icons, and visibility based on field values. Common patterns: traffic light status indicators (green/amber/red based on a choice column), progress bars (a filled bar proportional to a percentage column), due date highlighting (red background when past due), and person cards (showing profile pictures from the Person column). JSON formatting uses the SharePoint column formatting schema, which supports operators ($if, $toString, $abs), references to other columns (@currentField, [$OtherColumn]), and CSS class-based styling. For enterprise standardization, create a JSON formatting library that all site owners reference.
SharePoint Lists integrate with Power Automate through built-in triggers and actions. Common triggers: "When an item is created," "When an item is created or modified," and "When an item is deleted." Common actions: "Create item," "Update item," "Get items," and "Send an HTTP request to SharePoint" for advanced operations. Enterprise automation patterns include: 1) Approval workflows — new items trigger a manager approval flow with email notifications, 2) Escalation — items not updated within 48 hours trigger escalation emails, 3) Cross-list synchronization — changes in one list update related items in another, 4) External system integration — new list items create records in Dynamics 365, ServiceNow, or Jira, 5) Notification — status changes trigger Teams messages to relevant channels. EPC Group designs Power Automate solutions for SharePoint Lists that handle complex multi-stage approval, conditional routing, parallel approvals, and integration with external APIs.
SharePoint Online list limits: 30 million items per list (hard limit), 5,000 item list view threshold (views that return more than 5,000 items without indexed columns will be throttled), 12 lookup columns per view, 20,000 unique permissions per list, 5,000 items per bulk operation, 100 MB file attachment limit per item. To work within the 5,000 item threshold: 1) Index columns used in filters, sorts, and views, 2) Use "Created" or "Modified" date filters to reduce result sets, 3) Create filtered views that return fewer than 5,000 items, 4) Use search-based solutions for lists exceeding 100K items. For enterprise lists approaching millions of items, consider archiving completed items to a secondary list or migrating to Dataverse. EPC Group performance-tunes SharePoint Lists for organizations with high-volume data entry that exceed standard thresholds.
Power Apps can replace the default SharePoint list form with a fully customized experience. To customize: open the list, click "Integrate" > "Power Apps" > "Customize forms." This opens Power Apps Studio with a form connected to your list. You can rearrange fields, add conditional visibility (show fields only when certain conditions are met), add validation rules, add calculated fields, customize the layout for different screen sizes, and add branding. Enterprise patterns: 1) Multi-tab forms that organize 30+ fields into logical sections, 2) Cascading dropdowns where the second dropdown filters based on the first selection, 3) Conditional sections that appear only for specific item types, 4) Embedded approval buttons that trigger Power Automate flows directly from the form, 5) Read-only computed fields that display related data from other lists. Power Apps forms are included in the standard Microsoft 365 license when connected to SharePoint Lists.
EPC Group creates governed SharePoint List solutions. These solutions replace spreadsheets and old databases with automated, mobile-friendly business applications.
Each solution includes:
SharePoint Lists (and Microsoft Lists) are structured data stores. They support IT ticketing, project tracking, asset management, approval workflows, and custom business apps without code.
This guide includes:
SharePoint Lists and Excel serve different purposes. Use Lists for structured, multi-user, workflow-driven data. Use Excel for ad-hoc analysis and personal calculations.
| Dimension | SharePoint Lists | Excel | |---|---|---| | Concurrency | Hundreds of simultaneous editors | Co-authoring limits; merge conflicts | | Data validation | Column-level rules enforced by platform | Validation easily bypassed | | Permissions | Item-level role-based access | All-or-nothing file access | | Automation | Native Power Automate integration | VBA or external tools required | | Version history | Full audit trail per field change | File-level versions only | | Scale | 30 million items per list | Degrades above 100K rows |SharePoint Lists support these column types. Choose the right type — it determines how the column can be filtered, sorted, and used in automation.
Views control how list items are displayed. Create multiple views for different user groups or workflows.
JSON formatting lets you change how columns and rows look without custom development. It is one of the most underused SharePoint Lists features.
SharePoint Lists integrate natively with Power Automate. These five patterns cover most enterprise automation needs.
Large lists require careful configuration to avoid performance problems.
Microsoft Lists and SharePoint Lists belong to the same platform. Microsoft Lists is the branded app available in the Microsoft 365 app launcher. It works with Teams and offers a "My Lists" view that is accessible across all tenants.
The core elements such as data, permissions, and automation are consistent between the two.
SharePoint Lists can hold up to 30 million items. However, the default view threshold is 5,000 items. Exceeding this limit may lead to errors. To fix this issue, you can:
There is no practical limit on the total capacity of lists in an enterprise setting.
Yes, SharePoint Lists are ideal for structured multi-user data. They excel in several areas:
Excel excels in ad-hoc analysis, formulas, and charts. However, you should replace shared Excel workbooks used for tracking, scheduling, or request management with SharePoint Lists.
Use Excel for:
Use Power Automate — no coding needed. You can create a flow that is triggered "when an item is created." Follow these steps:
The entire flow takes about 15–30 minutes to build.
Talk to a SharePoint developer about your IT ticketing, project tracking, or asset management requirements. Call (888) 381-9725 or request a 30-minute discovery call.
AI assistant — not human