Microsoft Lists: Flexible data entry with visual view options

Many of us use Excel for data entry and storage however that comes with inconveniences in these angles:

1. Excel doesn't have a Form based interface, although Microsoft Forms (in this blog post) is great for that, its not possible to give the option for form or grid based data entry.
2. Excel’s data isn’t robust. Columns don't have data types meaning the errors are prominent and other features are impossible in cells such as multi select checklists or images
3. Excel’s stores data as it presents it. Hence the only layout is a grid view, its useful sometimes to switch views to a gallery, or Microsoft Planner like board view, a calendar or even have views with certain groupings and subtotals but Excel doesn’t allow this.

Microsoft Lists aims to solve these three aspects, plus it is more integrated than Excel is with the Power Platform (Power Automate, Power BI and Power Apps). The app used to be called SharePoint Lists, but it was revamped with the front end made simpler to use even though the back end is still managed on SharePoint (and certain features you must make do from the old SharePoint Lists interface).

This is the fourth and final blog post about the forgotten Office apps, after Forms as well as Word replacements: For anything people typically PDF, I use Sway and OneNote to replace tasks which are sometimes done on Word (click links to read the other blogs I wrote in this series). You can learn how to use the app in this video:

Overview

Creators can set up columns in your List with criteria, such as dropdowns (including multi select), images, person, date pickers, apply conditional formatting or other features, formulas are possible but its more complex to do so.

Data inputters can choose to edit Lists in either grid or form view, the latter of which can be customised.

Managers can turn on alerts for when a new is input, deleted or amended.

Reviewers can filter, sort, group by category, switch between output views from a normal grid, a gallery, calendar view and coming soon a Kanban board view (similar to Microsoft planner). to customise your view and then save your preferred go to views. You can also choose to analyse in Power BI.

Developers can create complex formula columns, create live links to Excel or Power BI, or integrate with Power Apps (for more complex forms) or Power Automate (for more complex rules and triggers)

Create a List & configure columns

Office 365 customers can navigate to Lists via the app launcher. This is only available to business customers, not consumer customers of Microsoft 365.

Next, click “New List” and you can choose a blank list, template to import one from Excel. For an Excel import you must have used the “Format as table” feature inside Excel (most Excel grids are not using this feature).

For each column you add, you first set the column type, give it a name & description, then fine tune with criteria, the column types are on the left and certain criteria on the right:

In early November, Microsoft announced that lookup columns are coming soon, they can currently be created using the “more” option, but there will options built into the user interface coming soon. These lookup columns can collect information from another Microsoft List, similar to a VLOOKUP or XLOOKUP in Excel. Microsoft Forms has other question types not available here such as likert scales, ranking and NPS but those are less relevant for Lists in any case.

Once you have added your columns you can click on a column name and choose:

• Which column to sort by
• Which to group by (if any), but note this disables certain other features
• Whether to have totals
• Move or hide columns
• How to format the column such as alternating (dark, light, dark light etc.) or conditional formatting (such as the word “accepted” should be green, “rejected” in red, or numbers over 500 etc. or even a combination which refers to other columns in the list).

Data inputs using either grid or form view

You can edit in either grid view or form view.

1. Use grid view for en-masse data entry. Choice & person fields are searchable (but only when you start typing out the first word), even multiple-choice ones, date pickers are automatically added to date or time columns. You can move around some fields with the keyboard although in my opinion its much less slick than rapid keyboard only data entry in native Excel.
2. Use Form view for a simpler data entry view, you can make simple edits to the layout of the form by clicking the edit then “edit columns” icon on the top right, here you can show/hide certain items and move fields up or down (affecting the form only), developers can make more sophisticated forms using Power Apps.

This gif shows how it can be edited in grid view

Turning on alerts

One area where Excel lacks is notifications, Google Sheets can send you email alert when something gets amended or added but Excel cannot. With Lists you can choose from “Automate” and add an alert for when one of these things happen.

If you have a date column, you can also set a reminder when a key date is approaching, or if you wish to set up more advanced links you can do so via the Integrate menu using Power Automate.

Formatting

Like Excel, you can conditional format columns, based on criteria, or have choice pills where Lists auto-assigns a different colour to different drop down options.

You can also format the whole table/list by clicking “Format view” then either:

• Alternating row styles which is similar to Excel’s “Format as table” feature

Conditional formatting (e.g. highlight the whole row blue if location is “London” etc.), which is sadly quite difficult to do in Excel.

Developers can do more sophisticated formatting by choosing “Edit current view” from the menu on the top right.

Filters, grouping & sorting

If you click on a column you can choose to group a column ahd add subtotals as counts, sums, averages or other aggregations. Lists doesn’t band together numbers of dates with grouping and if you have grouping turned on, the edit in grid view option (a very useful options) is disabled.

You can filter or sort from individual columns plus you can filter from the menu on the top right.

Switching to gallery & calendar views

The standout advantage of Lists which Excel doesn’t stand to compete with is the ease of switching views. There are a few ways to format the view but the most flexible is click on the top right drop down then “Create new view”, the main 3 views are grid, gallery & calendar view. At Ignite in Nov 2021, it was announced a Kanban board view was coming which is similar to Microsoft Planner or Trello

Microsoft planner board view (coming soon to Lists)

Lists vs Excel for collecting and storing data?

Lists is better at enforcing database level robustness within columns and it can do certain things that Excel cannot:

• Switch between grid, calendar, gallery & soon board view
• Enter data through a customisable form as well as grid view (although Excel Online can be linked to Microsoft Forms)
• New data types such as multi select choices, images, people or locations
• A cleaner user interface for managing and storing data
• A simpler mobile app
• Notifications set up for new items or changes

Overall though, if you use Lists get prepared for much more point and click than Excel offers, for data entry, filters, adding columns, changing views etc. plus Excel provides more sophisticated options for:

• Using formulas
• Combining your main list with others
• Analysis on your dataset without leaving the app
• Data manipulation (although you can link Lists to make use of Excel/Power Query)
• Speed of data entry and work
• Collaboration with people who may be non-Lists users

This blog post concludes the four forgotten Office 365 apps which have been covered over the course of the year. Staring with Sway, then OneNote (both great substitutes for Word), and more recently Forms.