David Benaim delves into flexible data entry with visual display options.
Many of us use Excel for data entry and storage, but it has some drawbacks from these angles:
- Excel does not have a form-based interface, although Microsoft Forms (in this blog post) is ideal for this, it is not possible to give the option of form-based data entry or data entry. gate.
- Excel data is not robust. Columns do not have data types which means errors are large and other functionality is not possible in cells such as multiple-select checklists or images
- Excel stores data as it is presented. Therefore, the only layout is a grid view, sometimes it helps to switch views to a gallery, or to Microsoft Planner like a table view, calendar or even views with some groupings and subtotals, but Excel does not allow this.
Microsoft Lists aims to solve these three aspects, in addition to being more integrated than Excel with Power Platform (Power Automate, Power BI and Power Apps). The app was previously called SharePoint Lists, but it has been revamped with the front-end simplified to use even though the back-end is still managed on SharePoint (and some functionality that you need to do from the old interface of SharePoint lists).
This is the fourth and final blog post on Forgotten Office apps, after form and Word replacements: for anything typically PDF, I use Sway and OneNote to replace tasks that are sometimes done on Word (click on the links to read the other blogs I wrote in this series). You can learn how to use the app in this video:
Creators can configure columns in your list with criteria, such as drop down lists (including multiple selection), images, person, date pickers, apply conditional formatting or other features, formulas are possible but it is more complex to do.
Data entries can choose to edit the lists in grid or form mode, the latter can be customized.
Managers can activate alerts when a new one is entered, deleted or modified.
Reviewers can filter, sort, group by category, switch between output views of a normal grid, gallery, calendar view, and soon a Kanban board view (similar to Microsoft planner). to customize your view, then save your favorite views. You can also choose to analyze in Power BI.
Developers can create complex formula columns, create dynamic 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 and configure columns
Office 365 customers can access the lists through the app launcher. This is only available for business customers, not Microsoft 365 consumer customers.
Then click on “New List” and you can choose a blank list, a template to import one from Excel. For an Excel import, you must have used the “Format as table” feature in Excel (most Excel grids do not use this feature).
For each column you add, you first define the column type, give it a name and description, then refine the criteria, the column types are on the left and some criteria are on the right:
At the beginning of November, Microsoft announced that the search columns will arrive soon, they can currently be created using the “more” option, but options built into the user interface will be available 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 these are less relevant for lists anyway.
Once you’ve added your columns, you can click on a column name and choose:
- By which column to sort
- By which to group (if applicable), but note that this disables some other features
- If it is necessary to have totals
- Move or hide columns
- How to format the column, eg alternate (dark, light, dark, etc.) or conditional formatting (eg the word “accepted” should be green, refers to other columns in the list).
Data entry using a grid or form
You can edit in grid view or in form view.
- Use the grid view for bulk data entry. Choice and person fields are searchable (but only when you start typing the first word), even multiple choice ones, date pickers are automatically added to date or time columns. There are some fields that you can move around with the keyboard, although in my opinion it’s much less fluid than typing data quickly with the keyboard in native Excel.
- Use the Form view for a simpler data entry view, you can make simple changes to the form layout by clicking on the Edit icon then “Edit Columns” at the top right, here you can view / Hide some items and move fields up or down (affecting the form only), developers can create more sophisticated forms using Power Apps.
This gif shows how it can be edited in grid view
One area where Excel lacks is notifications, Google Sheets can send you an email alert when something is changed or added, but Excel cannot. With lists, you can choose from âAutomateâ and add an alert when any 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 want to set up more advanced links, you can do that through the Integrate menu using Power Automate.
Like Excel, you can format columns conditionally, based on criteria, or have choice pills where lists automatically assign a different color to different drop-down options.
You can also format the whole table / list by clicking on “Format view”, then:
- Alternating row styles that are similar to Excel’s “Format As Table” function
Conditional formatting (eg put the whole row in blue if the location is “London” etc.), which is unfortunately quite difficult to do in Excel.
Developers can perform more sophisticated formatting by choosing “Edit Current View” from the menu at the top right.
Filters, grouping and sorting
If you click on a column, you can choose to group a column and add subtotals as counts, sums, averages, or other aggregations. Lists do not group the number of dates with grouping and if you have grouped enabled, the edit option in grid view (a very useful option) is disabled.
You can filter or sort by individual columns and you can filter from the menu at the top right.
Switch to Gallery and Calendar views
The remarkable advantage of lists that Excel cannot compete with is the ease of switching views. There are several ways to format the view, but the most flexible is to click on the drop-down menu at the top right and then on âCreate new viewâ, the 3 main views are grid, gallery and calendar view. At Ignite in November 2021, it was announced that a Kanban board view was coming up, similar to Microsoft Planner or Trello.
Microsoft planning board view (coming soon in listings)
Lists vs Excel to collect and store data?
Lists are more effective at enforcing database-level robustness in columns, and they can do some things that Excel can’t:
- Switch between grid, calendar, gallery, and soon the board view
- Enter data through a customizable form as well as a grid view (although Excel Online can be linked to Microsoft Forms)
- New data types such as multiple selections, images, people, or locations
- A cleaner user interface for data management and storage
- A simpler mobile application
- Notifications configured for new items or changes
Overall, though, if you’re using lists, be prepared for a lot more points and clicks than Excel, for entering data, filters, adding columns, changing views, and more. Additionally, Excel provides more sophisticated options for:
- Using formulas
- Combine your master list with others
- Analysis on your dataset without leaving the application
- Data manipulation (although you can link lists to use Excel / Power Query)
- Input and work speed
- Collaboration with people who may be non-users of the Lists
This blog post concludes the four forgotten Office 365 apps that were covered during the year. Starting with Sway, then OneNote (both great substitutes for Word) and more recently Forms.