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.

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.

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

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.

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 into the compact format. 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 a column, or whole table, paste it into excel2wiki. Copy the wikitext back to a sandbox. It will now be a plain-text table.

Or copy the column or table into LibreOffice Calc: Edit menu > Paste Special > Paste Unformatted Text. Copy what you need back to a sandbox.

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.

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.

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.

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.

If starting with a state/country and value list, do the same. But once in VE, you will need to add double bars || between the state/country and value. For example, if there is colon (:) between the 2, then do a search-and-replace in the wikitext source editor.

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.

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

Easy method before flags and links are added: Sort table the way you want on a wiki page (ascending, descending, or alphabetical). Then copy the table directly off the page, and paste into a VE window. The table will show up in that 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.

See also: c:Commons:PDF to image files

Upload the PDF to a free online PDF-to-Excel site. For example; here. Then when done 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.

Or you can extract the table pages from the PDF before uploading the PDF to the Excel converter. For example; here and here. You can extract a group of pages, or a single page. Remove/crop part of a single page here. Download the extracted PDF. Upload it to the PDF to Excel converter. Download the Excel file.

Open the Excel file in Calc. Then 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.

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.

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.

In Calc you may want to copy (as unformatted text) to a new sheet first. New > edit > paste special > paste unformatted text. This prevents any tooltip text from showing up in VE. Also, what looks like spaces after numbers may be left-aligned cells. Select cell, row, column, or table and then click Ctrl+R or Ctrl-L to right or left align. Paste into VE. Use find-and-replace in wikitext editing to convert tabs to spaces, convert double spaces to single spaces, and remove any cell alignment. Use {{table alignment}} instead.

Change rates per million to rates per 100,000. Or the reverse. Basically, you are dividing or multiplying all the numbers by 10. You can use free Google Sheets online, or freeware LibreOffice Calc. Some adapted table excerpts below from United States drug overdose death rates and totals over time#Comparisons to other countries in Europe. Section title may change.

Drug-induced mortality rates per million

Location	Year	Females	Males	Total
Austria	2022	18	65	42
Belgium	2020	7	34	21

Drug-induced mortality rates per 100,000

Location	Year	Females	Males	Total
Austria	2022	1.8	6.5	4.2
Belgium	2020	0.7	3.4	2.1

Note that for the visual editor to work correctly you will need to put nowiki tags (<nowiki> </nowiki>) around the table templates (if any) just above the table. Remove them when you are done.

Then in the visual editor select and copy just the data cells, but not the years (Ctrl+c). Then paste them (Ctrl+v) into a blank sheet in one of the above spreadsheets. The range will be the same in all 3: A1:C2 for the above example. The basic formulas are: =A1:C2/10 and =A1:C2*10 (all numbers in the range are divided or multiplied by 10). Don't forget the equal sign (=).

Then click the column head above a blank column to select that column. Then paste the formula in the fx spot above the spreadsheet. Then to get the adjusted data cells to show up:

• In LibreOffice Calc click Ctrl + Shift + Enter.
• In Google Sheets click Ctrl + Shift + Enter, and then Enter by itself.

Select and copy the adjusted data cells via Ctrl+c. Then using the visual editor select the old numbers in the wikitable. Paste the new numbers over them via Ctrl+v.

Option 2: If you aren't trying to save your templates (flags, etc.), then you can start in the spreadsheet with all the columns (both data and text columns).

Select the data cells (only) in a column (or columns) that you want to multiply or divide. Copy the range of those data cells. Select blank data cells in a blank column. Paste the previously copied range to the formula spot above that column. Be sure there is an equal sign (=) in front. Add the division or multiplication number (*100 or /100). Execute it as previously described depending on what spreadsheet software you are using. Copy the spreadsheet to the Visual Editor for the easiest editing.

To keep the resulting values in the spreadsheet, but without the formulas, select that column. Then copy it. Select a blank column. Then "paste special" (twice). Then "values only". Then you can delete the unnecessary columns without risk of losing the adjusted values.

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 (zero to get whole numbers). You can also choose from the example list. Use ctrl-click on numbers you don't want changed. Leading zeros are not necessary for whole numbers (so enter zero there).

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. Paste there first if you want compact table wikitext.

If you paste a table into Calc in order to round off to whole dollars, you may notice that the paste alone rounds off some entries. Those are numbers with trailing zeros. The zero is not needed.

Rounding can not occur in cells that also have any text characters such as letters for tooltips or for additional notes below the table. With some choices Calc will remove them. They are often not necessary since the latest numbers are often estimated, provisional, etc.. That can be noted on the Wikipedia page so readers can go to the source if needed for latest update.

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'.

Countries use commas, periods, and spaces to separate every third digit in numbers. See: Thousands separator.

LibreOffice Calc: Select column head. Then: format menu > cells > number > thousands separator > OK. 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 and locales > general > 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.

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.

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.

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.

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

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.

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

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.

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."

• Help:Tables for general information about using tables. It also has many links in its "See also" and "External links" sections. Please don't duplicate links here.