Searching in spreadsheets — analyzing UX patterns
In the past months, I spent a lot of time examining UX patterns in different data analytics tools. I find it very interesting how spreadsheets like Microsoft Excel defined the way we interact with data. Is there room for improving the UX of these tools or have we already achieved perfection? In this blog post, I’d like to summarize my findings on search functions concentrating on filtering, sorting, and quick search.
Why should we include search functions in tables?
Long story short, it’s more efficient and saves time. Thanks to computers we don’t have to go through a large amount of data manually anymore, we have smart systems that work for us. (Do you remember dictionaries? How much longer did it take to write your homework back then?)
The three main types of search functions we use simultaneously are filtering, sorting, and searching.
Filtering is useful for hiding irrelevant information that pollutes your data. It can help in relieving cognitive overload: A greater number of options can make it more difficult to make a decision.
Sorting in alphabetical or numerical order only reorganizes the data, it does not hide anything. It helps to structure information in a relevant way, spotting patterns or inconsistencies.
Search is often treated as a filter in the sense that it can hide results. In spreadsheets, however, it might be more useful to just highlight a piece of data. If we’re looking for something specific (e.g. a name or an email address) the fastest way is to search for it.
Filtering
The goal of filtering data is to exclude information that you don’t need, to reduce noise in your dataset. This is the most complex search function from a UX perspective because filtering options depend on the type of data itself.
I found two main UX patterns: the first one applies the filter from the column headers, the other from a dedicated menu. Let’s see how they work, and some pros and cons!
Apply filter from the column header
Where: Microsoft Excel, Google Sheets
Just to try a more complex scenario, I’ll combine two filters. In my example, I wanted to search for all graphic designers who were born before 1920.
In Sheets, filtering can be turned on for any column from the toolbar.
Pros
- Start from the context — since you apply filters right from the column headers, it’s easy to connect the function with the data itself
- Compact dropdown menu — you can apply or edit filters without losing the context of the table
Cons
- Too many options — even though the dropdown menu hides some options and uses dividers to create categories, there’s still a lot of information presented
- Lack of an overview — If you have more columns than the with of your screen you have to scroll. It can be difficult to filter in multiple columns at the same time because you need to scroll or reorganize them.
- Hard to clear filters — there’s no clear button, you either turn off filtering altogether or clear your filters manually
- Combining filters — You can only combine filters with ‘and’ statements. What happens if I want to filter for graphic designers or people born before 1920?
Apply filters from a menu
Where: Airtable, Smartsheet
With the rise of lightweight online databases like Airtable, we can see a lot of innovation happening to spreadsheets. The biggest advantage is that you can easily connect data across multiple sheets and view it in various ways, not only as a table.
When it comes to filtering, these tools tend to have a dedicated filter menu, that serves as an overview. First, you have to choose a column to filter from a dropdown list, then you’re presented with contextual filter options.
Pros
- Contextual filter options — Since tools like Airtable have column types, they can limit the filtering options. E.g. You get different options to filter a column with text-based data than a column with numbers or dates. Since Excel or Google sheets allow full customization, they have to present all these filtering options at once.
- Clear overview on all applied filters — you can always open the popup and see all your applied filters in one place. It’s also very easy to change an ‘and’ to an ‘or’ condition.
Cons
- Hard to find the right column — choosing the column name from a dropdown is more work because you have to remember its name. Most people automatically right-click on the column header, because that’s how they learned it in Excel. Probably because of these reasons the interaction is available from the column header as well. If you click on the column and select filtering from there, the filter menu will be prefilled.
- The filter menu can interfere with your workflow — In Smartsheet, the filters appear in a modal. This is problematic because it prevents you from interacting with the rest of the table. What if you don’t remember the name of the column? Or if you apply a filter that doesn’t bring you any results? Before you hit Apply, you wouldn’t know….
So, which pattern is better? It depends of course, but I do see more advantages in a dedicated menu, where you can see everything regarding filtering. If(!) that menu doesn’t block you from using the table, and you can access it from the column headers as well.
Sorting
The sort function has one job: to reorder information in alphabetical or numerical order. The list can be ascending or descending, and it should be easy to toggle in between them. Simple, right? Well, after looking around a bit, it seems like UX designers cannot agree on a single pattern.
There are more advanced options to consider as well:
- Would you like to sort only one column or the whole table?
- Do you need to sort multiple columns after each other?
- What if your column has a header you need to be fixed on the top?
Google Sheets provides two ways. Starting from the column gives you a quick and simple option to sort the whole sheet. You choose from A to Z / Z to A (I like the simple microcopy there) and that’s it. If you’d like to sort the column only, you can do that from a popup from the menu. You can’t disable or revert your sorting in either case.
Airtable and Smartsheet sorting functions are available from the column menu (Airtable also has it in the top navigation). In both cases, it opens a separate menu. Airtable’s solution provides a quick way to toggle between ascending and descending order, lets you keep the list sorted, or remove it anytime. The other tools don’t make it easy to remove sorting and revert to the original table.
I’d like to mention one more common pattern that feels like the quickest interaction to get the job done. In many cases, clicking on an icon in the column header will sort the table. Clicking for the second time will change the order, and finally clicking for the third time makes the sorting disappear. You can see an example here.
Searching
Last but not least, let’s talk about quick search. This option is the best when you’re looking for something specific, and you want to find it fast. Based on the usability interviews I had, people rarely need this function in tables, but it can make those moments much more pleasant.
We know the Cmd/Ctrl + F function in your Chrome browser well. It opens a search bar in the top right corner, and it highlights the keywords you’ve searched for on any page. It’s important that this is not a filter: a lot of search bars (e.g. the Google search) are filters in reality.
The quick search works in Google Sheets and Airtable the same way, making it super easy to jump in between cells. It’s very important to sow the number of results, and to highlight the cells properly.
Final thoughts
Even though these search options are powerful, they do require some previous knowledge. That is why it’s very important to be mindful of the people who use your table. It could be, that implementing only one of the above-mentioned functions is enough for them.
I’m only scratching the surface with this topic, but I already realized that there are huge differences in how people go about analyzing data. Most of us use filtering, sorting, and searching to some extent in our day-to-day lives, and they save us a ton of effort.
Separating these functionalities makes it possible to use them at the same time, creating powerful data analytics tools. If you include all of them in your data table, I’m sure people will find their needles in their haystacks.
Further reading
A great article with great examples — https://medium.com/swlh/find-a-needle-in-the-hay-best-practices-for-filtering-ui-design-fd51f556160f
Dan Brown, Eight Principles of Information Architecture — https://asistdl.onlinelibrary.wiley.com/doi/pdf/10.1002/bult.2010.1720360609
A very detailed breakdown on table elements and how to design them — https://www.smashingmagazine.com/2019/02/complex-web-tables/
