Creating Tables in WordPress from Excel

Video versionA video overview of Excel-based wpDataTables

WordPress table based on Excel file examplewpDataTables is deeply integrated with Excel

Please note: From version 5.1 you are able to create Excel tables linked to an external source from any domain! Click here to read more about using the URL for generating linked Excel tables.

Microsoft Excel is a leading software program for working with spreadsheets. Most likely, no other software is as commonly used for tables; and .XLS and .XLSX formats are also so common for storing and exchanging table data that they could also be considered standard. Consequently, wpDataTables could not be looked upon as a fully-functional tool if it wouldn’t support this format as well.

You can download the Excel file used in this tutorial by this link.

See the resulting table first, and the step-by-step tutorial to create it below.

Please note: Once you create a table from an Excel file, changes in this file, like:

  • Changing the column’s name,
  • Changing the column’s order,
  • Deleting the column, and
  • Adding a new column.

are highly not recommended, because the table in the wpDataTable plugin will not work, at the moment. If you make any of these changes, please recreate the table again.

When working with Excel-based tables, please note:

  1. Formulas and merged cells are not supported,
  2. By default, the data is read from the data source every time the page loads, so when your tables are large (more than 2.000 – 3.000 cells) the pages with large tables will load slowly, or even crash after reaching a certain size.However, with the new caching option that was implemented in wpDataTables 5.0, this issue is resolved for large tables linked to an existing data source.
  3. They generally work slower than CSV-files, or even Google Spreadsheets; so if your table is large, and you don’t want to use the new caching option, we can advise porting the table to a different format, or – even better – to a MySQL table (you can use “Create a table by importing data from data source” to import your Excel file to MySQL).
  4. Formatting applied in the original file (font families, color, size, fill color, etc.) will not be copied over to the wpDataTable, but you can make whatever adjustment you may need in WpDataTables.
  5. Tables linked to an existing Excel file are not editable, nor are they saved in the database.
  6. Generated tables cannot be replaced with another Excel file. Each new file has to be a new wpDataTable.

Instructions to create the table

First, prepare an Excel file with your data. It doesn’t have to necessarily be prepared in MS Excel, it can be prepared in other software – e.g. OpenOffice or Libre Office. The necessary thing is to save it in an .XLS or .XLSX format.

Excel table example

Create a wpDataTable and upload the Excel file

Once the .XLS or .XLSX file is prepared, you need to create a wpDataTable in your WordPress admin which will pull the data from it.

Create data table from source

Go to WordPress admin, and open wpDataTables -> Create a table
1. Click on Create a table linked to an existing data source.

linked-excel-2nd-step

2. Provide a name for the new wpDataTable in the “Table title” input to help you identify it.
3. Select “Excel file” as the Input data source type.
4. The WordPressMedia Library will automatically be selected as the File location and it is used when you’re creating a table by uploading an Excel file.
5. Click on the Browse button to open the WordPress media library.

6. Upload the Excel file that you prepared (or select one of the Excel files you previously uploaded into your Media Library); then click Use selected file.

7. Click “Save Changes” so wpDataTable will read the column structure and save it.

Note: Your Excel file should be on the same domain as WordPress.

URL from any domain

Creating a table linked to an Excel file by providing its URL is pretty much the same as it is for creating a table by uploading an Excel file. The only difference is that you would choose the URL from any domain option instead of the WordPress Media Library option under File location when creating the table.

file-from-any-url

That is it, everything else is the same as when you’re creating a table linked to an uploaded Excel file.

Please note: The file needs to be accessible online in order to create a table from it.

(Optional) - Define additional table and column settings

When the table structure is read from the Excel file, you might wish to define additional settings for the table (responsiveness, table tools, layout, etc.) and for individual columns (column data type, filter, change the displayed name). You can read this documentation section for all the details related to different table features, and this documentation section for descriptions of different column features.

In this example table, we defined the filter type for the “Amount Purchased column as “Number range“.

To achieve that, you will need to open Column settings.
There are 2 ways to do so:

1. By clicking on the Column settings button

2. Or, by clicking on the Column List button on top of the table.
In this dialog you can quickly rename, reorder, or toggle visibility for columns.

Now, you can click on the Filtering tab, and for the filter type select Number range for the “Amount purchased” column.
Also, we will define the value that will be set as a default predefined filter value on page load.
Let’s set that, for example from 4 to 18.

Now that we’ve configured our table, click on the Save Changes button.

You can dynamically filter the table by using placeholders as predefined filtering values.

Insert the wpDataTable in your page or post

wpDataTable shortcode

When you’ve finished preparing your Excel-based wpDataTable, you can insert it in the post or page.

Create a post or page, or open one of the posts/pages you already have for editing.

Place the cursor in the post or page where you want to insert the table, click the “Insert a wpDataTable” button, and pick the table that you created in previous steps:

If you prefer, you can just copy & paste the generated wpDataTable shortcode.
When you save the post or page and open it in the front-end, you will see the wpDataTable generated from your Excel file. Now you have Excel in WordPress.

If you are using Gutenberg editor, Elementor, or WPBakery page builder, you can check out the section for Adding wpDataTables shortcodes on the page.