On my Contextures Excel Tutorials website, there are a few pages with long lists. To make it easier to find information in those lists, I’ve used DataTables, so people can sort the columns, and search for specific text.
Broken DataTable Features
This week, I noticed that, on one page, the table wasn’t showing its interactive features. The Search Box, the column sorting arrows, and the items per page drop down, were all missing.
It took me a while to find and fix the problem, but it’s working correctly now. If you have a similar problem with DataTables, I hope the notes below help you fix the problem.
Interactive Table – Excel Shortcuts
In the screen shot below, there’s a list with 146 Microsoft Excel keyboard shortcuts.
When it works correctly, the DataTable is has several helpful interactive features:
- Items per page drop down
- Search Box
- Column sorting arrows
However, something was broken, and those features were missing. It took a couple of hours, but I was finally able to get it working again.
Installing DataTables
Way back in 2016, while working on a different long list, for 300 Excel functions, I found the DataTables website, which promised “Advanced tables, instantly”.
- The website also says, “Add advanced interaction controls to your HTML tables the free & easy way”
- And yes, it was free, and might be easy for some people, but it took me a while to get it working on my first page.
In the sections below, I’ve listed the key steps for setting up DataTables, and the problems that I’ve run into, and fixed, along the way.
1) Set Up HTML Table
You can get the full DataTables installation guide for step-by-step details, but here are the key steps:
- First, set up your list in a valid, well-formatted HTML table, with a header and a single body.
- Next, be sure to give that table a name – I used tblkey as the ID for my keyboard shortcuts table.
2) Add Javascript
Next, add the Javascript and CSS files on the HTML page, in the header.
- Tip: The DataTables installation guide has the code you’ll need.
In the screen shot below, you can see the DataTables Javascript in my page header section.
- Note: Your code might look slightly different, depending on version, but this key step was pretty easy.
3) Point to Named Table
Also in the page header code, there’s another key piece of DataTable Javascript.
- It points to the named table where you want to show the interactive features.
- In the screen shot below, I’ve entered a reference to my table, which is named tblkey.
Past Mistakes: When setting up my first DataTable, I omitted this important bit of code. As you’d expect, the interactive features did not work until I finally added that code!
4) DataTable Troubleshooting
And here’s how I found and fixed my most recent problem with DataTables. This process took way longer than it should have!
- First, I checked that the HTML table was named, and it had a table header and single table body
- Next, I verified that the header Javascript referred to that table name correctly
- After that, I carefully checked all the header Javascript code, line by line.
- It was exactly the same as the code on my Excel Functions List page, which has a working DataTable.
5) Finding the Problem
It was a long, frustrating morning, and I didn’t know what else to check.
Finally, I compared the Javascript at the end of the page’s HTML code, line by line, to the code on a different page, which had a working DataTable.
- I discovered that there was an extra script on the problem page (shown below — I turned off Dark mode so it’s easier to read)
- It didn’t seem related to DataTables, but I commented that line out, as a test.
- To my surprise (and delight), the DataTable’s interactive features started working correctly!
Why Did That Solve the Problem?
Commenting that line out didn’t have any negative effect on the page, as far as I can tell.
I have no idea why that script was messing up the interactive features, so maybe I’ll go down that rabbit hole one day.
DataTables Are Worth the Trouble
Despite the minor trouble I’ve had with setting up Datatables, the benefits far outweigh the problems.
I highly recommend using them, on pages where you’ve got a long HTML table. People will be able to sort, search, and control the number of items per page.
That makes it a better experience, and visitors will be more likely to come back to your site again.
__________________