Help:Table/Advanced
 | This help page is a how-to guide. It explains concepts or processes used by the Wikipedia community. It is not one of Wikipedia's policies or guidelines, and may reflect varying levels of consensus. |
 | Are you new here? If so, welcome! There is a simplified version of this page at Help:Wikitable. |
A table is an arrangement of columns and rows that organizes and positions data or images. Tables can be created on Wikipedia pages using special wikitext syntax, and many different styles and tricks can be used to customise them.
Tables can be used as formatting instrument, but consider using a multi column list instead.
Note: Go here for a tool to fully expand/collapse the table of contents.
Tables and the visual editor (VE)
Some table operations are easier in the visual editor. Some rare actions, such as adding colored backgrounds, can't be done in the visual editor.
The visual editor does not work on talk pages. It does work on your user page (Special:MyPage), and its subpages. But if you have a long user page VE may take a long time to load or publish. VE will load very fast in short or empty pages. So to get VE work done efficiently user sandboxes help a lot: Special:MyPage/Sandbox, Special:MyPage/Sandbox2, Special:MyPage/Sandbox3, etc.. Share the link in discussions or when asking for help. You can save sandboxes on a specific topic if used in discussions. Otherwise reuse them. To find all your sandboxes and subpages: Special:PrefixIndex/User: – click link, add user name to the spot labeled "Display pages with prefix:".
Insert blank row or column
Click on a column or row header. Then click on the arrow. From the popup menu click on "Insert".
Move or delete columns and rows
In VE click on a data cell, or its column or row headers. Then click on the arrow that shows up near the header. From the popup menu click on "Move" or "Delete". If the table is using {{static row numbers}} template you will temporarily need to remove the template and its subtemplates (if used). You can alternate between source editing and VE editing to do so. Without having to publish until the task is completed.
In freeware LibreOffice Calc select the column or row. Then click and hold the Alt key. Then drag the column or row where you want it. You can drag from anywhere in the selected row or column except for the row number or column letter.
Copy column from one table to another
This is useful for many things. For example; for quickly updating country lists, or adding/updating a rank column, or copying a list of full names for states or countries. See Help:Sortable tables about rank columns and row numbers. See also: Commons:Convert tables and charts to wiki code or image files. See the section on updating List of countries by incarceration rate.
Launch the visual editor. In the column you are copying click the header cell or whatever top cell you want. Then shift-click a cell farther down or at the end of the column. This will select the column down to that cell. Then click "copy" from the edit menu of your browser (or Ctrl+C). In some browsers you can do this from the popup context menu. Then click any blank spot on the page to deselect the column.
Go to the column you want to replace or fill in (in this table or another one). Be sure visual editor has been launched. If you are copying the whole column including the header cell select just the header cell for that column by clicking on it. Then click "paste" from the edit menu of your browser (or Ctrl+V). You can paste into a blank column the same way. Just select the header cell. Then from your browser: edit menu > paste.
Or you can select part of a column and paste into it. For example; if you don't want to replace the header cell.
All of the above will sometimes work when copying to or from a column in a spreadsheet (such as freeware LibreOffice Calc). If there is a problem, then paste that spreadsheet column into tab2wiki first, and copy the wikitext.
After combining the columns into one table, you may notice that VE created a mishmash of compressed and uncompressed wikitext. If this is a problem, it can be fixed by pasting the table into tab2wiki or Excel2Wiki. Excel2Wiki will compress the wikitext correctly for tables with row headers too (see also #Row headers).
Note: The visual editor will copy templates (as in a column of country/state/province names with flag templates), but it will not copy inline styling such as styling used to left align those names. That can be added back all at once in any text editor. For example; if the flag template is the only template in the table, then replace {{ with
style=text-align:left|{{
Copy or delete multiple rows
Click on the top corner cell of the area you want to select. Then shift-click on the opposite top corner cell in the same row. Then go straight down and shift-click on the bottom corner cell on the same side of the area you want to select. You will now have a rectangle or square selected of the table.
Click on "copy" or "cut" as needed from the edit menu of your browser. "Delete" will not work for multiple rows and columns.
Click on the top left corner cell of the area you want to paste into in a table. Then click "paste" from the edit menu of your browser. It can take from a few seconds to up to a minute for very large areas. You might want to do this work in a sandbox first. Then save and edit further before putting anything in an article.
"Cut" will empty the selected cells. It does not delete them. Once empty though it is very easy to delete all the empty cells, rows, and columns in the source editor. Switch over to it by clicking the arrow at the top right of the editing window.
It may be even faster to use the visual editor to copy the parts you want from the table into a new blank table.
Copy table from web page to the visual editor
You can copy and paste some tables from a web page directly into the visual editor (VE). Not all tables work. Use an empty sandbox to do this most quickly. Save it, and edit further before pasting it into an article.
Select the table on the web page. Then click "copy" from the edit menu of your browser. In some browsers you can do this from the popup context menu. Launch visual editor on any page. Then paste the table into the page.
If that does not work, click on the insert menu, and then "table". It usually has the first header in the table selected. Then click on "paste" from the edit menu of your browser. It may take awhile for the table to show up. It can take 3 seconds, or up to a minute for very large tables.
Copy web table to Excel2Wiki to wikitext editor to VE
For copying web page tables that can't be copied directly into the visual editor (as described in the previous section): Try copying the table into Excel2Wiki, or tab2wiki. Click "convert". Copy and paste the table wikitext into the wikitext editor. Save. Do further editing in VE.
Copy list (not table) to Excel2Wiki to wikitext to VE
Paste any list (text and/or numbers) into Excel2Wiki. Click "convert". Copy and paste the table wikitext into the wikitext editor. Save. Do further editing in VE. For example; add more columns, or copy the column and paste it into other tables.
Remove block of text from list before table conversion
Before pasting a list into Excel2Wiki (see above) you may want to remove a block of text. Some text editors can do this when text is set to a fixed-width font such as Courier New. For example; freeware NoteTab Light: Modify menu > Block > Cut. Fill in popup form with starting column, block width, block rows. Click OK and that rectangle of text is removed. This saves a lot of time.
Sort alphabetically/numerically with spreadsheet & VE
- Note: For more info see Help:Sortable tables. See the section on putting a table in initial alphabetical order.
Many things can be done in spreadsheet programs that can not be done on wiki. Select and copy a table right off of a page (do not go into the wikitext or the HTML). Paste the table into a spreadsheet program such as freeware LibreOffice Calc (see free guide), or another spreadsheet program. See List of spreadsheet software.
In Calc click on any cell in the column you want sorted, and then click on one of the sort options in the data menu at the top of the Calc window. Click on "ascending" or "descending" to sort alphabetically or numerically depending on the column contents. Click on "sort" for more options.
To move the header row(s) back up to the top: Select the entire row(s). To select the entire row(s) click on the Calc numbered columns located on the far left of any sheet. Press and hold ALT key. Left click and hold any cell within that section (row numbers won't work). Using the mouse drag and drop the source row(s) to its destination. Release the mouse button.
To avoid this problem when doing an alphabetical sort, select all of the table except the header rows. To do so with multiple columns click the top left non-column-header cell, and then shift-click the bottom right cell. When you click on "ascending" or "descending" in the data menu the table will be sorted alphabetically. That is the default.
Paste that sorted table (or just the selected columns of interest) directly into the visual editor. If that doesn't work then paste into a new visual editor table where the first header cell has been selected. It may take up to a minute. If there is a problem, then paste into Excel2Wiki or tab2wiki first, and copy the wikitext.
Copy table from PDF to visual editor
- See also: c:Commons:PDF to image files
Upload PDF to free online PDF-to-Excel site. For example; here. Download the Excel file. Open it in freeware LibreOffice Calc or another spreadsheet program. If you just want one table from a long Excel page, you can select that table from the Calc page. Then copy the table to a new page in Calc.
Edit and move columns and rows in Calc. To drag a column first select it by clicking its header number. Then press and hold the ALT key. Then click a data cell, and drag the column to a new location. Or right click and delete the selected column (no need for ALT key). Rows are similarly moved (with the ALT key pressed), or deleted. Sort as described in the previous section.
Copy the table to a wiki sandbox. In Calc select the table. Copy directly from it, and then paste into a new visual editor table where the first header cell has been selected. It may take up to a minute. If there is a problem, then paste into tab2wiki first, and copy the wikitext.
Commas/periods to separate every third digit in numbers
Countries use commas, periods, and spaces to separate every third digit in numbers. See: Thousands separator.
Let's say you have column(s) of numbers in a table and you want to replace spaces with commas or periods. To do mass find-and-replace you will need to first get rid of trailing spaces (if any). So your numbers don't end up with commas at the end. Copy just the number columns to a sandbox or a new section of a page. See relevant sections of Help:Table to do so. In source mode editing use "search and replace" to delete trailing spaces. Check the box for "Treat search string as a regular expression". Replace [ \t]+$ with nothing. Then uncheck the regex box, and replace the spaces inside the numbers with commas. Then paste the text and number columns next to each other again.
If you have a simple list (not in a table) and you want to replace spaces with commas or periods, you can paste the list into a text editor (Notepad for example). Use replace (from edit menu in Notepad). See previous sections to learn how to copy that corrected list into a table.
If you are already in a spreadsheet use find-and-replace after selecting the column or row in question.
If there is no separator between every third digit, then paste the list, row, or column into a spreadsheet (LibreOffice Calc for example). To do that click the first column head in the blank spreadsheet. This will select that column. Then click paste from the edit menu of Calc. From the popup box click "fixed width". Uncheck everything else. Click OK. This will paste it into the first column. In Calc save it in the default .ods format. This will allow the next step.
If there are multiple columns select the top left data cell (not the header cell or this will not work). Then Shift-click on the bottom right data cell. This will select all the data cells. Don't select any text or date cells. Or deselect just the text or date cells by ctrl-click.
Click "number format" from the format menu. Then click "thousands separator" from the submenu. This will add commas or periods depending on the default language you have selected in Calc for the locale setting. (tools menu > options > language settings > languages > formats > locale settings). For example; with English you might select USA, UK, South Africa, or one of the many other English speaking countries that are listed for English. Copy that column to a Wikipedia table via VE.
Consider also a parser function {{Formatnum}} and template {{Formatnum}} (see Help:Magic words § Formatting), and familiarizing yourself with Wikipedia:Manual of Style/Dates and numbers.
Round off numbers (zero or one decimal places, etc.)
With dollars and cents, the data is usually more useful to readers when rounded off to no decimal places (so no decimal point needed). With percent of GDP (gross domestic product) one decimal place (one digit after the decimal point) is often needed to distinguish between some countries.
It is easy to do in a spreadsheet. For example; in LibreOffice Calc select the column by clicking the very top of its column. Then go to the Format menu > Cells. A popup will show up. Click the "Number" category. Choose the number of decimal places. You can also choose from the example list.
That popup page is also where you choose a thousands separator or not. Choosing from the example list will also check or uncheck that box. When done copy that table or column to a Wikipedia table via VE. If there is a problem, then paste into tab2wiki first, and copy the wikitext.
Summing or averaging a list of numbers
Launch VE. In a table select a column, or part of a column of numbers. To do so click the top cell you want. Then shift-click a cell farther down or at the end of the column. This will select the column down to that cell. If this is not possible, you may need to first comment out, or temporarily delete some table templates such as those for {{Static row numbers}}. Use a sandbox.
Upon selection in VE you will immediately see a popup box with a sum total of the selected cells, and an average.
Add link brackets to text in each cell in a column
- Note: User:PrimeHunter (Talk) provided the code. Ask him for help.
- Note: In editing preferences check the box: "enable the editing toolbar. This is sometimes called the '2010 wikitext editor'."
List of countries by intentional homicide rate has a main table with a region column and a subregion column. Here below is a fast way to link the regions and subregions in those columns. For example, after updating the whole table from the source. See example in this sandbox.
The following assumes the syntax is a whole table row in one source line starting with a pipe and with double pipe between cells. It does not work with partially compressed table wikitext either (such as for tables with row headers). A table with any non-compressed wikitext can be completely compressed by pasting the table into Tab2wiki or Excel2Wiki. Do not check the box for "format first column as header" or "First element in a row is a header". You can add row headers back later if desired. Tab2wiki and Excel2Wiki will also remove any templates and styling. This is necessary for adding links since the link brackets surround whatever is in the cell.
Click on the wikitext source editing link. Click on "Advanced" in the editing toolbar. Then click on the search and replace icon on the right. Put a check in the box called "Treat search string as a regular expression." Fill in the "Search for" box with:
^(\|(.*?\|\|){m} *)(.*?)( *\|\|| *\n)
Change "m" to the number of the column minus 1. 2nd column from the left would be "1". 3rd column from the left would be "2". The row number column created by Template:Static row numbers is not counted in these calculations. Fill in the "replace with" box with:
$1[[$3]]$4
Then click "Replace all". The "m" number may be different if a table uses rowspan or colspan.
If you want links for the first column the following will work with both compact and long table wikitext. Fill in the "Search for" box with:
(\|-.*\n\|\s*)([^\|\n]*)
Make sure there is no space on the end.
Fill in the "replace with" box with:
$1[[$2]]
Convert rows to columns and columns to rows
Sometimes there is a need to transpose columns and rows (move rows to columns, and columns to rows). For simple tables, this can be done via the "transpose rows and columns" function of Copy & Paste Excel-to-Wiki, or via the "transpose" feature of a third-party spreadsheet program such as Microsoft Excel, the free web-based Google Sheets, or the free downloadable software LibreOffice Calc.
To transpose the table with a third-party spreadsheet program, copy the published table on the Wikipedia page and paste it into a new blank document in your spreadsheet program. While the pasted cells are still selected in the spreadsheet, copy them again by right-clicking and choosing "Copy" from the context menu. Open a new blank spreadsheet spreadsheet, click in the upper-left cell, right click on it, and choose "Paste Special". In Microsoft Excel, check the "Transpose" box at the bottom of the dialogue and hit Okay. In Google Sheets, choose "Transposed" from the sub-menu. in LibreOffice Calc, choose "Transpose" from the sub-menu. Perform any required editing of the transposed table, and copy the new table directly from the spreadsheet program into visual editor, or into one of the wikitable converters previously mentioned, such as toolforge:excel2wiki or tab2wiki.
For more complicated operations, such as consolidating multiple rows with the same header into a single column, you can use the "pivot table" feature of an external spreadsheet program. For example; the data for the overdose rates table by state for United States drug overdose death rates and totals over time comes from a csv file and is converted to wikitable format via one of the previously mentioned csv converters, but the year headers in the left table below need to become the column headers in the right table (truncated).
| Year | State | Rate |
|---|---|---|
| 2019 | AL | 16.3 |
| 2019 | AK | 17.8 |
| 2019 | AZ | 26.8 |
| 2019 | AR | 13.5 |
| 2019 | CA | 15 |
| 2019 | CO | 18 |
| 2019 | CT | 34.7 |
| 2019 | DE | 48 |
| 2019 | FL | 25.5 |
| 2019 | GA | 13.1 |
| 2019 | HI | 15.9 |
| 2019 | ID | 15.1 |
| 2019 | IL | 21.9 |
| 2019 | IN | 26.6 |
| 2019 | IA | 11.5 |
| 2019 | KS | 14.3 |
| 2019 | KY | 32.5 |
| 2019 | LA | 28.3 |
| 2019 | ME | 29.9 |
| 2019 | MD | 38.2 |
| 2019 | MA | 32.1 |
| 2019 | MI | 24.4 |
| 2019 | MN | 14.2 |
| 2019 | MS | 13.6 |
| 2019 | MO | 26.9 |
| 2019 | MT | 14.1 |
| 2019 | NE | 8.7 |
| 2019 | NV | 20.1 |
| 2019 | NH | 32 |
| 2019 | NJ | 31.7 |
| 2019 | NM | 30.2 |
| 2019 | NY | 18.2 |
| 2019 | NC | 22.3 |
| 2019 | ND | 11.4 |
| 2019 | OH | 38.3 |
| 2019 | OK | 16.7 |
| 2019 | OR | 14 |
| 2019 | PA | 35.6 |
| 2019 | RI | 29.5 |
| 2019 | SC | 22.7 |
| 2019 | SD | 10.5 |
| 2019 | TN | 31.2 |
| 2019 | TX | 10.8 |
| 2019 | UT | 18.9 |
| 2019 | VT | 23.8 |
| 2019 | VA | 18.3 |
| 2019 | WA | 15.8 |
| 2019 | WV | 52.8 |
| 2019 | WI | 21.1 |
| 2019 | WY | 14.1 |
| 2018 | AL | 16.6 |
| 2018 | AK | 14.6 |
| 2018 | AZ | 23.8 |
| 2018 | AR | 15.7 |
| 2018 | CA | 12.8 |
| 2018 | CO | 16.8 |
| 2018 | CT | 30.7 |
| 2018 | DE | 43.8 |
| 2018 | FL | 22.8 |
| 2018 | GA | 13.2 |
| 2018 | HI | 14.3 |
| 2018 | ID | 14.6 |
| 2018 | IL | 21.3 |
| 2018 | IN | 25.6 |
| 2018 | IA | 9.6 |
| 2018 | KS | 12.4 |
| 2018 | KY | 30.9 |
| 2018 | LA | 25.4 |
| 2018 | ME | 27.9 |
| 2018 | MD | 37.2 |
| 2018 | MA | 32.8 |
| 2018 | MI | 26.6 |
| 2018 | MN | 11.5 |
| 2018 | MS | 10.8 |
| 2018 | MO | 27.5 |
| 2018 | MT | 12.2 |
| 2018 | NE | 7.4 |
| 2018 | NV | 21.2 |
| 2018 | NH | 35.8 |
| 2018 | NJ | 33.1 |
| 2018 | NM | 26.7 |
| 2018 | NY | 18.4 |
| 2018 | NC | 22.4 |
| 2018 | ND | 10.2 |
| 2018 | OH | 35.9 |
| 2018 | OK | 18.4 |
| 2018 | OR | 12.6 |
| 2018 | PA | 36.1 |
| 2018 | RI | 30.1 |
| 2018 | SC | 22.6 |
| 2018 | SD | 6.9 |
| 2018 | TN | 27.5 |
| 2018 | TX | 10.4 |
| 2018 | UT | 21.2 |
| 2018 | VT | 26.6 |
| 2018 | VA | 17.1 |
| 2018 | WA | 14.8 |
| 2018 | WV | 51.5 |
| 2018 | WI | 19.2 |
| 2018 | WY | 11.1 |
| 2017 | AL | 18 |
| 2017 | AK | 20.2 |
| 2017 | AZ | 22.2 |
| 2017 | AR | 15.5 |
| 2017 | CA | 11.7 |
| 2017 | CO | 17.6 |
| 2017 | CT | 30.9 |
| 2017 | DE | 37 |
| 2017 | FL | 25.1 |
| 2017 | GA | 14.7 |
| 2017 | HI | 13.8 |
| 2017 | ID | 14.4 |
| 2017 | IL | 21.6 |
| 2017 | IN | 29.4 |
| 2017 | IA | 11.5 |
| 2017 | KS | 11.8 |
| 2017 | KY | 37.2 |
| 2017 | LA | 24.5 |
| 2017 | ME | 34.4 |
| 2017 | MD | 36.3 |
| 2017 | MA | 31.8 |
| 2017 | MI | 27.8 |
| 2017 | MN | 13.3 |
| 2017 | MS | 12.2 |
| 2017 | MO | 23.4 |
| 2017 | MT | 11.7 |
| 2017 | NE | 8.1 |
| 2017 | NV | 21.6 |
| 2017 | NH | 37 |
| 2017 | NJ | 30 |
| 2017 | NM | 24.8 |
| 2017 | NY | 19.4 |
| 2017 | NC | 24.1 |
| 2017 | ND | 9.2 |
| 2017 | OH | 46.3 |
| 2017 | OK | 20.1 |
| 2017 | OR | 12.4 |
| 2017 | PA | 44.3 |
| 2017 | RI | 31 |
| 2017 | SC | 20.5 |
| 2017 | SD | 8.5 |
| 2017 | TN | 26.6 |
| 2017 | TX | 10.5 |
| 2017 | UT | 22.3 |
| 2017 | VT | 23.2 |
| 2017 | VA | 17.9 |
| 2017 | WA | 15.2 |
| 2017 | WV | 57.8 |
| 2017 | WI | 21.2 |
| 2017 | WY | 12.2 |
| State | 2017 | 2018 | 2019 |
|---|---|---|---|
| AL | 18 | 16.6 | 16.3 |
| AK | 20.2 | 14.6 | 17.8 |
| AZ | 22.2 | 23.8 | 26.8 |
To re-arrange the table using pivot tables, copy and paste the published Wikitable into a spreadsheet program such as freeware LibreOffice Calc.
In LibreOffice Calc, first make sure the headers are in the top row. Then select a data cell. Then click on the "Pivot Table" command from the Insert menu. Click OK in the popup box. In the next dialog box drag "Year" to the "Column Fields" box, and drag "State" to the "Row Fields" box. Drag "Rate" to the "Data Fields" box. Click OK. The table will convert to the new format with the years as column headers. It is easier to do than to describe. For help see: LibreOffice: Pivot Tables and LibreOffice Help: Pivot Table.
If further editing is required, first copy and paste that table to a new Calc spreadsheet, to prevent complications with the existing interactive pivot table. If necessary convert the state or country abbreviations to full names. See relevant sections here on Help:Table.
When done editing, copy the new table (if small) directly from the Calc page into visual editor, or into one of the wikitable converters previously mentioned, such as Copy & Paste Excel-to-Wiki or tab2wiki. Then use VE to delete the summation column and row.
Picking selected dates from massive .csv files
COVID-19 pandemic deaths has a few tables by year of cumulative deaths by country on the first of each month. The source is a massive WHO (World Health Organization) csv file. Download here. You could convert it to a massive wikitable as described elsewhere, and delete the hundreds of unwanted date columns over several hours.
Or you could open the csv file in a spreadsheet such as freeware LibreOffice Calc. Then delete all columns except for "Country", "Date reported", and "Cumulative deaths" columns (select, right-click column head, delete). Save as .ods file. Then use the autofilter function to select just the dates of interest from a checklist. Click anywhere in the table. Then: Data menu > AutoFilter. Dropdown menus will show up on all column heads. In the "Date reported" dropdown menu clear the "All" box by clicking it. This unchecks all the dates. Then check the dates you want. In this case all the first of the month dates for the desired year. Click OK. Save the file. See video. See: "Applying AutoFilter" in Calc help.
In order to completely remove all the unwanted data you must copy and paste that table to a new Calc ods file. A simple paste works (edit menu > paste). This greatly reduces the file size. See: "Only Copy Visible Cells" in Calc help.
On this smaller file use the "Pivot Table" method described in the previous section to put the dates as column heads. Select a data cell. Then click on the "Pivot Table" command from the Insert menu. Click OK in the popup box. In the next dialog box drag "Date reported" to the "Column Fields" box, and drag "Country" to the "Row Fields" box. Drag "Cumulative deaths" to the "Data Fields" box. Click OK. Copy and paste that table to a new Calc ods file to prevent Pivot complications, and to do further editing.
Add thousands separator as needed as described in another section. Sort latest month. Save it. Then copy that Calc table to tab2wiki, or to the VisualEditor (if small). For more info go here.
This method can be used for creating or updating many Wikipedia country tables that need only the latest data, or selected dates, from large multi-year data file sources. Just scrolling back and forth through one huge data file could take hours otherwise.
List of countries by intentional homicide rate has a country table that uses a source with multiple years. But the table only shows data for the latest available year for each country. Some more complex transposing needs to be done with the source spreadsheet. For more info:
- Talk:List of countries by intentional homicide rate/Excel instructions.
- Talk:List of countries by intentional homicide rate/LibreOffice Calc instructions
See also this Help:Table section:
Separating counts and rates to 2 columns
| Location | Year | Type | Value |
|---|---|---|---|
| Afghanistan | 2021 | Counts | 1613 |
| Afghanistan | 2021 | Rates | 4.0 |
| Albania | 2021 | Counts | 66 |
| Albania | 2021 | Rates | 2.3 |
See List of countries by intentional homicide rate. Its data reference in table form comes with counts and rates alternating in the same column. See it here. That page explains how to put them in 2 columns.
Copy the table to a spreadsheet such as freeware LibreOffice Calc. Select a single cell with "Counts" in it. Then: Data menu > More Filters > Standard Filter > Filter Criteria. Enter "Counts" in Value spot. Click OK. Only rows with "Counts" in them will be kept. Change column head of "Value" column to "Counts". Delete column that only contains the word "Counts". Add a thousands separator to count numbers. Copy and paste to new Calc file to get accurate row numbering. Note the number of rows. Copy to a sandbox.
Start over and do the same except for "Rates." Number of rows should be the same as for counts table. Copy to a sandbox. Using VE insert a blank column to the right of the rates column. Copy the counts column and paste it into that blank column.
Convert spreadsheet/database tables to wikitables
To convert from spreadsheets (such as those produced by Gnumeric, Microsoft Excel, or LibreOffice/OpenOffice.org/StarOffice Calc), you can use the Copy & Paste Excel-to-Wiki converter, tab2wiki, or the MediaWiki Tables Generator.
You can save spreadsheets as .csv and use the CSV to Wikitable converter. Another version is here. It has a useful auto-detect function for the delimiter.
The CSV Converter converts comma-separated values (CSV) format to table wikitext or to HTML. See (documentation). You may use this to import tables from both spreadsheets and databases (such as MySQL, PostgreSQL, SQLite, FileMaker, Microsoft SQL Server & Access, Oracle, DB2, etc.).
Sometimes you can paste a table from a spreadsheet directly into the visual editor. Some formats may work better than others. In LibreOffice Calc, for example, you may need to convert a file to the .ods format for better luck. File menu > save as.
Tables with sticky headers
- Note: See most recent Village Pump discussion. And other archived discussions: 1. 2. 3. 4. 5. See Phab:T42763. See also: Meta: Community Wishlist Survey 2022/Reading/floating table headers and meta:Community Wishlist Survey 2021/Reading/Enable sticky table headers.
A simple solution uses {{sticky header}}. It has been fixed, and now works in Vector 2022 also. And it works with multi-row headers. It only works with sortable tables. It works with {{static row numbers}}. But it does not work with this subtemplate: {{static row numbers table}} (go there to see what to replace it with). It breaks this subtemplate: {{static row numbers arrows}}. The separate sort row disappears. Use {{sort row}} instead.
In desktop view it works in all browsers tested so far: Firefox, Edge, Chrome, and Opera. On cell phones it works in both Android and iOS phones. In some mobile cases it does not work if the table extends past the edge of the screen.
A scrolling table in the sense of the vertical scrollbar for the whole page. When you scroll the page the table headers stay visible when the table goes beyond the top of the screen.
{{sticky header}} just above the table wikitext.
class=sticky-header to the top line of the table wikitext along with class=sortable. For example:
{{sticky header}}
{| class="wikitable sortable sticky-header"
For the old template {{import style}} see Global Search at Toolforge. Search for "{{Import style|sticky}}" - in quotes. To get transclusion count and list of articles.
See list of articles transcluding {{sticky header}}. See transclusion count.
See Template:sticky header for examples, more info, and specialized cases such as when table subsections have headers. See Template talk:Sticky header for discussion about how to improve it.
Sortable tables
See Help:Sortable tables for details of how to make sortable tables. Tables can be made sortable by adding class=sortable to the top line of the table wikitext. Individual columns can then be excluded from sorting with class=unsortable in the header cell.
Note: data-sort-type=VALUE must go in the header cell with the sorting icon. So it will not work with {{sorting row}}. See alternative at Help:Sortable tables#Sorting row template.
Numerical and year sorting problems
Collapsible tables
Classes can also be used to collapse tables, so they are hidden by default. Use the class mw-collapsible to enable collapsing behaviour. Collapsible tables can be sortable, too, by also including the sortable class (). By default, a collapsible table begins expanded. To change this, add the additional class mw-collapsed. Alternatively, you can add autocollapse, instead of mw-collapsed, which will automatically collapse the table if two or more collapsible elements are present on the page.
Note: Previous versions of this article recommended the collapsible class, but mw-collapsible is now preferred. It is a feature of the MediaWiki software, not a local customization, and can be used to make any element collapsible, not just tables. mw-collapsible also does not require a header row in the table, as collapsible did. Tables will show the "[hide]" / "[show]" controls in the first row of the table (whether or not it is a header row), unless a table caption is present.()
Example with a header row
Wiki source
{| class="wikitable mw-collapsible mw-collapsed"
|-
! Header
|-
| Content that starts hidden
|-
| More hidden content
|}
Rendered result
| Header |
|---|
| Content that starts hidden |
| More hidden content |
Examples without a header row
Wiki source
{| class="wikitable mw-collapsible"
|-
| First row is always visible
|-
| Content that starts visible
|}
Rendered result
| First row is always visible |
| Content that starts visible |
Wiki source
{| class="wikitable mw-collapsible mw-collapsed"
|-
| First row is always visible
|-
| Other rows start hidden
|}
Rendered result
| First row is always visible |
| Other rows start hidden |
Tables with captions
Tables with captions will collapse to the table caption. The header will not be visible when collapsed. Use {{nowrap}} to keep the caption from being fit into a tiny vertical column when the table is collapsed.
Wiki source
{| class="wikitable mw-collapsible"
|+{{nowrap|Somewhat long table caption}}
|-
! Header
|-
| Content starts visible
|-
| More visible content
|}
Rendered result.
Click "hide" to see
| Header |
|---|
| Content starts visible |
| More visible content |
Rendered result
(without {{nowrap}}.
Click "hide" to see
| Header |
|---|
| Content starts visible |
| More visible content |
Starting off collapsed:
Wiki source
{| class="wikitable mw-collapsible mw-collapsed"
|+{{nowrap|Somewhat long table caption}}
|-
! Header
|-
| Content starts hidden
|-
| More hidden content
|}
Rendered result.
Click "hide" to see
| Header |
|---|
| Content starts hidden |
| More hidden content |
Rendered result
(without {{nowrap}}.
Click "hide" to see
| Header |
|---|
| Content starts hidden |
| More hidden content |
Sorting and collapsing
It is possible to collapse a sortable table. To do so, you need to use this code:
class="wikitable sortable mw-collapsible"
For example:
Wiki source
{| class="wikitable sortable mw-collapsible"
|+ Sortable and collapsible table
|-
! scope=col | Alphabetic
! scope=col | Numeric
! scope=col | Date
! scope=col class=unsortable | Unsortable
|-
| d || 20 || 2008-11-24 || This
|-
| b || 8 || 2004-03-01 || column
|-
| a || 6 || 1979-07-23 || cannot
|-
| c || 4.2 || 1492-12-08 || be
|-
| e || 0 || 1601-08-13 || sorted.
|}
Rendered result
| Alphabetic | Numeric | Date | Unsortable |
|---|---|---|---|
| d | 20 | 2008-11-24 | This |
| b | 8 | 2004-03-01 | column |
| a | 6 | 1979-07-23 | cannot |
| c | 4.2 | 1492-12-08 | be |
| e | 0 | 1601-08-13 | sorted. |
If you want the table to default to collapsed state, use the code
{| class="wikitable sortable mw-collapsible mw-collapsed"
in place of
{| class="wikitable sortable mw-collapsible"
mw-datatable – row hover highlighting. White background
- Note:
class=mw-datatableis no longer working by itself. See T287997 and this discussion.
To make it work now requires adding {{mw-datatable}} just above the table wikitext. As before class=mw-datatable goes in the top line of the table wikitext. See: Wikipedia:TemplateStyles for more info.
class=mw-datatable allows for row highlighting. This makes it easier to follow the data and info across a row, especially in wider tables. It also makes the background of the non-header cells white. It does not override existing background colors set for cells or rows.
See list of articles transcluding {{mw-datatable}}, or transclusion count.
Scroll your cursor over the mw-datatable tables below to see the light blue hover banding.
class=mw-datatable produces a white background for data cells; while class=wikitable produces a gray background for data cells:
Wiki source
{{mw-datatable}}
{| class=mw-datatable
|-
! 1 !! 2 !! 3
|-
| 1-1 || 2-1 || 3-1
|-
| 1-2 || 2-2 || 3-2
|}
Rendered result
| 1 | 2 | 3 |
|---|---|---|
| 1-1 | 2-1 | 3-1 |
| 1-2 | 2-2 | 3-2 |
Wiki source
{| class=wikitable
|-
! 1 !! 2 !! 3
|-
| 1-1 || 2-1 || 3-1
|-
| 1-2 || 2-2 || 3-2
|}
Rendered result
| 1 | 2 | 3 |
|---|---|---|
| 1-1 | 2-1 | 3-1 |
| 1-2 | 2-2 | 3-2 |
class=mw-datatable can be used in combination with class class=wikitable
Wiki source
{{mw-datatable}}
{| class="wikitable mw-datatable"
|-
! 1 !! 2 !! 3
|-
| 1-1 || 2-1 || 3-1
|-
| 1-2 || 2-2 || 3-2
|}
Rendered result
| 1 | 2 | 3 |
|---|---|---|
| 1-1 | 2-1 | 3-1 |
| 1-2 | 2-2 | 3-2 |
{{import-blanktable}} and class=tpl-blanktable do the same thing, but are not recommended because class=mw-datatable has been around much longer.
See list of articles transcluding {{import-blanktable}}. See transclusion count
See also
- Help:Introduction to tables with Wiki Markup: an introduction to tables.
- Help:Basic table markup: the fundamentals of table wiki markup.
- Help:Sortable tables: information about sortable tables.
- Help:Tables and locations
- Wikipedia:Advanced table formatting: advanced topics.
- Wikipedia:Manual of Style/Tables: the Manual of Style indicates best practices for tables.
- Wikipedia:Table dos and don'ts: a quick guide to the Manual of Style for tables.
- mw:Help:Tables
- User:Dcljr/Tables: table tutorials.
- Table (HTML): tags used for HTML tables.
- Category:Wikipedia tables
Templates
- Category:Table templates: all the templates that effect or use tables.
- Category:Multi-column templates: templates for using columns without tables.
- Category:Chart, diagram and graph templates
- Category:Chart, diagram and graph formatting and function templates
- Category:Wikipedia template editors
- {{List to table}}: template and its maintenance category: Category:Articles requiring tables
- {{Horizontal TOC}}: good for country lists in table format.
- {{Table}}: template for specifying table CSS classes such as "wikitable" and "collapsible"
- {{Aligned table}}: for specifying tables as a template, allowing inclusion in other templates and areas where table syntax is problematic.
- Examples:
- {{Chess diagram}}: chess board template.
- {{Goban}}: Go board template.
Notes
External links
- VBA-Macro for EXCEL tableconversion, published in German Wikipedia project (English translation included)
- html2wiki - HTML tables to wiki converter at WMF Labs
- excel2wiki - copy/paste Excel-to-Wiki converter at WMF Labs
- Tables Generator, a WYSIWYG table generator for Mediawiki markup
- Wikitable Editor, a visual table editor in wiki code
- HTML-WikiConverter, various versions and languages
- pywikipediabot, can convert HTML tables to wiki
- Table of CSS color names and HEX codes
- Phabricator request for floating table headers
- tabulate - Python module for converting data structures to wiki table markup
- wikitables - Python module for reading wiki table markup
- H63: Using the scope attribute to associate header cells and data cells in data tables | Techniques for WCAG 2.0.
Wikimedia sister projects
Editing Wikitext/Tables at Wikibooks- Editing Wikitext/Tables Ready to Use at Wikibooks
- mw:Help:Tables: MediaWiki help page on tables.
- m:Wiki markup tables: Meta-Wiki information on tables.
- m:Help:Sorting: Meta-Wiki information on sortable tables.
- m:Table background colors: MediaWiki background colors table.
- Commons:Chart and graph resources: Chart and graph resources at Commons
- Commons:Convert tables and charts to wiki code or image files: includes information on converting table markup.
- Commons:Template:SVG Chart. Convert list/table to SVG line chart.
- Commons. Convert US list/table to state-by-state SVG map.
