Help:Creating tables

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.

This page documents various ways to create tables.

Tables and visual editor

Some table operations are easier in the visual editor (VE). Some rare actions, such as adding colored backgrounds, cannot 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. 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:Example – replace username Example with yours. A short sandbox will work much faster with VE than trying to do major VE work within a long article page.

Insert blank row/column

Click on a column or row header. Then click on the arrow. From the popup menu click on “Insert above/below".

Move/delete row/column

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 above/below" or "Delete". If the arrow does not show, it may be due to a template style. You can alternate between source editing and VE editing to temporarily remove the problem style. You can do this without having to publish until the task is completed.

In the freeware LibreOffice Calc, select the column or row. Then click and hold Alt. 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 to table

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 Excel2Wiki 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 right off the page (not the wikitext) into Excel2Wiki. Excel2Wiki will compress the wikitext. And it will do it correctly for tables with row headers too (see also Help:Table#Row headers). Tab2wiki will not compress the wikitext at all if this is checked: "First element in a row is a header".

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|{{

Or use Template:Table alignment to align whole columns.

If you need to first strip all styling, linking, flags, templates, etc, from the column paste it into excel2wiki. Copy the wikitext back to a sandbox. It will now be a plain text table with one column. Copy that column.

Copy/delete 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 webpage to VE

You can copy and paste some tables from a web page directly into the 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.

Web to Excel2Wiki 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. Click "convert". Copy and paste the table wikitext into the wikitext editor. Save. Do further editing in VE.

List to Excel2Wiki 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.

List. Remove text block

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. Spreadsheet & VE

Note: For more info see Help:Sortable tables. See the section on putting a table in initial alphabetical order.

You can move rows around in VE, but it would be slow to do an initial sort of a country list that way. 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 first, and copy the wikitext.

Copy table. PDF to VE

See also: c:Commons:PDF to image files

Upload PDF to a 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 the visual editor, or if that does not work, into a blank 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 first, and copy the wikitext.

Sum/average column

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.

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 Excel2Wiki, or the MediaWiki Tables Generator.

Sometimes you can drag and drop an unopened .csv file directly into the visual editor. Or copy the unopened file and paste it into VE. Then the table shows up.

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.

Separate after 3 digits

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

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. Use ctrl-click on numbers you don't want changed.

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 Excel2Wiki first, and copy the wikitext.

Sometimes rates need multiple decimal places to separate the locations from each other when the rate column is sorted. For example: List of countries by firearm-related death rate. Here is how to round off to 3 decimal places without changing the single "0" to 0.000. You need to use custom cell formatting in Calc:

1. Select the column you wish to format.

2. Right-click.

3. Format Cells...

4. Under Format Code enter 0.000;-0.000;0;@

5. OK.

What exactly is happening with this?: 0.000;-0.000;0;@

The first part 0.000 is the format with three decimal places for positive numbers.

The second part -0.000 is the format with three decimal places for negative numbers (you probably don't have those, but you cannot skip the negative number part in such formatting strings).

The third part 0 is what to display in place of single zeros

The fourth part @ is what is displayed when it's not a number at all and it means that it will be displayed as is. E.g., if you have a cell with text 'word' in it, it will be displayed as 'word'.

Add link brackets to text in each cell in a column

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 Excel2Wiki. Do not check the box for "First element in a row is a header" or the table will only be partially compressed. You can add row headers back later if desired. 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]]

Making data cells lighter

Note: Afterwards, you can use {{mw-datatable}} to make the data cells white if further lightening is desired.

Data cells should normally have plain unbolded text, and a lighter background. In the table below the data cell wikitext is on the same line as the row header wikitext. This causes the data cell backgrounds to be the same shade of gray as the column and row headers. It also makes the data cell text bold. See how to fix this after the table.

Wikitext

{| class=wikitable
|+ The table's caption
! Column header 1
! Column header 2
! Column header 3
|-
! Row header 1 || Cell 2 || Cell 3
|-
! Row header A || Cell B || Cell C
|}

Produces

The table's caption
Column header 1	Column header 2	Column header 3
Row header 1	Cell 2	Cell 3
Row header A	Cell B	Cell C

The data cell wikitext needs to be on a separate line from the row header cell for that row. See the next table.

To make this happen use Excel2Wiki. Copy the table right off the page (not the wikitext) and paste it into Excel2Wiki. Remove the table caption text for now. Check the following boxes:

format first row as header
format first column as header

Check or uncheck "sortable" depending on your needs. Then click "Convert". Copy the wikitext results back to the wiki. You must use class=wikitable to get the background shading.

Excel2Wiki fixes the problem by putting the row header cells on separate lines in the wikitext.

Wikitext

{| class=wikitable
|+ The table's caption
! Column header 1
! Column header 2
! Column header 3
|-
! Row header 1 
| Cell 2  || Cell 3
|-
! Row header A 
| Cell B  || Cell C 
|}

Produces

The table's caption
Column header 1	Column header 2	Column header 3
Row header 1	Cell 2	Cell 3
Row header A	Cell B	Cell C

Another way to fix the problem is to copy the table wikitext to a text editor such as freeware NoteTab Light. The following method works for tables with flag templates. In the table wikitext do a mass replace of }}|| to }}^P|. ^P is the NoteTab Light code for a line break. That puts the row header cells on a separate line in the wikitext. And you don't lose your flags, templates, classes, scopes, styling, etc.. And you maintain most of the compressed wikitext format.

Complex references

See WP:Verifiability (WP:V) and WP:ANNOTATION. It can be difficult to find the source data just from a link to the entry page of a reference. Please provide a path to the specific source data used in a table. Otherwise WP:V says the unsourced material can be deleted from the article.

WP:V: "Readers must be able to check that any of the information within Wikipedia articles is not just made up. ... Any material that needs an inline citation but does not have one may be removed. ... The cited source must clearly support the material as presented in the article."