Best approach for huge data tables (currently using scrolls)

Question

Experts,

I need help in assessing the best approach for huge tables (lets say 500 rows), we want something that is intuitive, visibly appealing and gives more focus to table data. Most pages will have 1 table but there are scenarios where we have multiple tables so we need to account that as well

Currently, we are using scrollbars on table div but the issue is that it adds to another scrollbar (apart from scrollbar on sidebar navigation and content area). Rough sketch of our current approach is below:

For mobile we are using stack-table approach using footable plugin.

Although, one feature in Gmail looks appealing that is,

• when we open a long email thread
• and goto compose section press reply etc to activate that section
• within that previous thread is truncated in "..." if you click that it will show content and update the main scrollbar if you scroll up and down you will notice a nice smooth scroller and keeping the focus on content.

UPDATE: Please consider that table holds financial data, to be precise a Balance Sheet with multiple periods in column. First column is sticky/freeze/fixed rest are horizontally scrollable. Our main concerns are multiple scrollbars, if we can avoid the table scrollbars with loosing usability.

Answer 1

Scrolling through lines and lines of data is laborious. The first question to ask is, "Does the user need to see all of this data, or are they trying to find out information behind the data?"

Work through all of your users and use cases and work out what they are trying to achieve by going through each row and column. At what point would they consider their visit to this page a success? How can your product get the user there quicker and minimise their workload?

If you find that actually there is a need for the table then maybe consider the following things:

Look at the ability to show and hide columns.

In most cases when you are looking at data you rarely need every single column available to gain the information you are looking for. This also allows you to free up some screen estate, where 10 columns is a nice to have, maybe only 1% of your users actually need it. 99% may only need to see 3 columns, so offer to ability to hide the irrelevant data.

Once the user has been able to select the relevant parameters we then need to look again at what they are looking for in particular so we should add a filter.

For example, there are 20 types of fruit in this column but the user only wants to look at the data for apples.

The final change I would make which may require some additional development work would be to really think about what the user is going to do with this data. Do they want to discover insights? If so, create a pivot system much like in Excel which allows them to get a really quick summary of the data. Another thing to consider is whether the user needs to view the data in this format. Can the data be displayed better in a graph?

With regard to rows of data, another thing which could be useful to do could be with the way you structure the data

i.e rolling up multiple items of data into one column where it is practical and still of value to do this. An example is where you might have a customer address spread out into multiple columns:

1. 40 Roader Road
2. Towns
3. City
4. Postcode

If there is no need to have this data broken out for statistical purposes then try pulling it into just one address column which can be expanded on the page to save some screen estate.

Summary:

• Ability to hide/show columns
• Provide a filter for each column
• Add in a customizable pivot table or a summary of the data
• Look at the way data is structured

The most useful bit of advice for this will be to think about what your users want to achieve and then make these things easy and flexible.

Answer 2

In one project in an enterprise environment, I have been dealing with "presenting huge-data tables in web UI" challenge as well. Migrating loads of huge excel-sheets to a (internal) web-platform.

From this experience I would firmly advise not to analyse user workflows for datatables, with the object of reorganising the large dataset into smaller chunks (e.g. master-detail, or summaries, or tabs etcetera). Maybe our skills were lacking, or this organisation is unique, but there turned out to be NO dominant process of reading or using these spreadsheets. There were literally 1000s of different ways of using the spreadsheet, of comparing details between rows/ columns, groups of rows, combinations of rows and columns etcetera etcetera. There was no (easy) way of prioritising these 1000s of workflows, and no way we were going to build different web workflows for each.

What did work in our case was to avoid using tables for data, and instead use other ways of presenting data. We found traditional row-column-data-tables to be terribly inefficient in screen real estate. Some "tricks" we used to reduce width of the total amount of data:

• think "card" not "row" to present a single record of data. (I am not advocating to get rid of tables all together, but it helps to think outside the row-and-column mindset)
• put data of 1 "record" on multiple lines, e.g. business unit, operating company, department.
• get rid of all column header labels: for most text fields (e.g. department name), the data makes it clear what the data is. For other fields (e.g. sales, profit, margin), provide a mouse-over to explain what the number is. And there are other and better ways to do sorting and filtering.
• cut-off long texts and use ... with a mouse-over to reveal details
• replace statuses (like employee yes/no) with icons, with (again) mouse-over to explain.
• combine info into one "string", Combine first name, middle name, last name, nickname into one string.
• round numbers (e.g. $10K)
• use expand/ collapse on each card (but stay on the same page) to reveal more details.

This works for us: the audience use the data on a daily basis, so they will fairly quickly lose the need to look up what icon x means, or what the 3rd amount from the top is. In other environments (mobile, infrequent use), other solutions may be more suitable.

BTW: in the end, we will still end with some data-tables (old user habits die hard, and tables are familiar), and we will not be able to eliminate horizontal scrolling altogether.

Answer 3

A simple solution: Make the table full width, give the table section a vertical scrollbar (and horizontal if necessary) and separate multiple tables over different tabs. Something like this (sorry for the bad drawing):

Answer 4

Best way to show this is pagination, show users only few relevant records , let's say 50 per page. It will look good and also recommended when there are large number of rows and client do not want to see all the rows at once.

Answer 5

I disagree with table pagination here; your data target seems to be meaningful only with relative data and paginating it makes sense only in cohesive sections.

So first thing I would do is divide the data in functional sections and enable changing table through your navigation.

Then, in my opinion, the table should fill the page vertically and the only scrollbar should be handled by the browser. This should solve the big table issue; 500 rows isn't that much; load them dynamically if needed.

The multiples tables per page requirement is a different beast: If it's just related tables maybe you should review the requirement and handle that through your navigation (subsections - each table in a page). If it's to be used for reference and comparisons(2 tables): handle a special layout that splits horizontally and hides the navigation bar.

If space is still a problem (horizontally) you could try to handle the historical data through color and signs (2/3 levels each for + and -), relative to the frozen year. The user is probably interested in finding anomalies in the data or performance year over year, so at first what draws attention is difference/variability (or lack thereof) relative to another data set (ask the users if they want the difference of each column from the previous or always the first column, etc). Then enable the user to see the actual data by clicking/tapping the row.

Answer 6

With data grids you basically have two options:

1) Use pagination, or

2) Use virtual scrolling.

Both approaches will help you show large data sets in the same table, without causing your web browser to timeout.

Answer 7

I would try to remove some of the columns to make horizontal scrolling less likely. Knowing what the user intends to do with the data is the key; not every bit of data needs to be a column in order to achieve the goal.

Here's an example of a request I got for our internal application:

Dept head: "Add a column showing the upload date and time for any attached documents."

Me: "What will you do with that?"

Dept head: "We need it to help decide which items to approve."

Me: "How will you use it to make that decision?"

Dept head: "People need to get the uploads done in time for us to meet approval deadline."

Me: "What happens if they don't get the upload done in time?"

Dept head: "They don't get approved."

Me: "What time do you do approvals?"

Dept head: "10 a.m."

Me: "How far in advance should they have uploads done?"

Dept. head: "We need an hour to get everything lined up.

Me: "So you don't need to see date and time of every upload, you really just need to know who uploaded something after 9 a.m. today, right?"

Dept head: "Yes, that's what we would use that column for."

Me: "Instead of having to see all that information for every line item and check the time on each one, how about if we flag the line items where an upload occurred after 9 a.m. today, and clear it at midnight? Then you could just skip those without having to check the date and time?"

Dept head: "That would be even better!"

Boom. Extra column averted and task made easier for the user. Now on to the next column...

Answer 8

Do users also edit the data in these tables? If yes, would be interested to know how you handled :

• columns that should have numerical values only = did you limit keyboard entries so that letters could not be entered? Or did you enable entering letters and then display an error?

• how did you show validation errors in table cells? Especially if there was more than one error at a time (if validation was performed based on the value entered in more than one cell).