View source for Help:Creating tables

{{short description|How to create tables}}
{{Table help}}
{{Wikipedia how to}}
{{Wiki markup}}
This page documents various ways to create tables.

== Tables and visual editor ==
{{anchor|Tables and visual editor|Table and VE}}
{{See also-text|[[mw: Help:VisualEditor/User guide#Editing tables]]|[[Help:Introduction to tables with VisualEditor]]|[[commons:Commons:Convert tables and charts to wiki code or image files|Commons:Convert tables and charts to wiki code or image files]].}}

Some table operations are easier in the [[VisualEditor|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 [[Wikipedia:Subpages|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 [[WP:template style|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 {{Keypress|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:Commons:Convert tables and charts to wiki code or image files|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 {{Keypress|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 {{Keypress|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 [https://excel2wiki.toolforge.org 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 [https://excel2wiki.toolforge.org Excel2Wiki]. Excel2Wiki will compress the wikitext. And it will do it correctly for tables with row headers too (see also [[Help:Table#Row headers]]).  [https://magnustools.toolforge.org/tab2wiki.php Tab2wiki] will not compress the wikitext at all if this is checked: "First element in a row is a header".

{{A 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 <code><nowiki>{{</nowiki></code> with
:<code><nowiki>style=text-align:left|{{</nowiki></code>
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 [https://excel2wiki.toolforge.org 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 [https://excel2wiki.toolforge.org 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.

=== 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 [[Monospaced font|fixed-width font]] such as [[Courier New]]. For example; freeware [https://www.notetab.com/notetab-light 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.''

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 [https://documentation.libreoffice.org/en/english-documentation 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 {{Keypress|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 [https://excel2wiki.toolforge.org Excel2Wiki] first, and copy the wikitext.

=== Copy table. PDF to VE ===
:''See also: [[c:Commons:PDF to image files]]''
Upload the PDF to a [https://www.google.com/search?q=pdf+to+excel free online PDF-to-Excel site]. For example; [https://www.pdftoexcel.com 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; [https://www.ilovepdf.com/split_pdf here] and [https://pdfresizer.com here]. You can extract a group of pages, or a single page. Remove/crop part of a single page [https://pdfresizer.com 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 {{Keypress|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 {{Keypress|ALT}} key). Rows are similarly moved (with the {{Keypress|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 [https://excel2wiki.toolforge.org 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 {{tl|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 ==
{{see also|Wikipedia:Tools#Importing (converting) content to Wikipedia (MediaWiki) format}}
{{Hatnote|See also the visual editor section above, and the spreadsheet info there.}}
To convert from [[spreadsheet]]s (such as those produced by [[Gnumeric]], [[Microsoft Excel]], or [[LibreOffice]]/[[OpenOffice.org]]/[[StarOffice]] Calc), you can use [https://excel2wiki.toolforge.org Excel2Wiki], or the [http://www.tablesgenerator.com/mediawiki_tables 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 [https://bambots.brucemyers.com/csv2wikitable.php CSV to Wikitable] converter. Another version is [http://mlei.net/shared/tool/csv-wiki.htm here]. It has a useful auto-detect function for the delimiter.

The [http://area23.brightbyte.de/csv2wp.php CSV Converter] converts [[comma-separated values]] (CSV) format to table wikitext or to HTML. See ([[:de:Benutzer:Duesentrieb/csv2wp (en)|documentation]]). You may use this to import tables from both spreadsheets and [[database]]s (such as [[MySQL]], [[PostgreSQL]], [[SQLite]], [[FileMaker]], [[Microsoft SQL Server]] & [[Microsoft Access|Access]], [[Oracle Database|Oracle]], [[IBM Db2|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 [[Arabic digit|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 <code>[ \t]+$</code> 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 ([[MS Notepad|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 {{Pf|Formatnum}} and template {{Tl|Formatnum}} (see {{Section link|Help:Magic_words|Formatting}}), and familiarizing yourself with [[Wikipedia:Manual of Style/Dates and numbers]].

== Rates per million or per 100,000 ==
{{Hatnote|'''Note.''' This is basically how to multiply or divide in a spreadsheet.}}
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 '''[https://workspace.google.com/products/sheets 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.

<div style="display:inline-table; vertical-align:top;">
{| class="wikitable sortable" style=text-align:right;
|+ Drug-induced mortality rates per million 
!Location
!Year
!Females
!Males
!Total
|-
|{{flagg|uspeft|pref=Healthcare in|pref2=Health in|Austria}}
|2022
|18
|65
|42
|-
|{{flagg|uspeft|pref=Healthcare in|pref2=Health in|Belgium}}
|2020
|7
|34
|21
|}
</div>

<div style="display:inline-table; vertical-align:top;">
{| class="wikitable sortable" style=text-align:right;
|+ Drug-induced mortality rates per 100,000 
!Location
!Year
!Females
!Males
!Total
|-
|{{flagg|uspeft|pref=Healthcare in|pref2=Health in|Austria}}
|2022
|1.8
|6.5
|4.2
|-
|{{flagg|uspeft|pref=Healthcare in|pref2=Health in|Belgium}}
|2020
|0.7
|3.4
|2.1
|}
</div>

Note that for the visual editor to work correctly you will need to put nowiki tags ('''{{nowrap|<nowiki><nowiki> </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 ('''{{Keypress|Ctrl|c}}'''). Then paste them ('''{{Keypress|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 {{Keypress|Ctrl|c}}. Then using the visual editor select the old numbers in the wikitable. Paste the new numbers over them via {{Keypress|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.

== 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 [https://excel2wiki.toolforge.org 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 <code>0.000;-0.000;0;@</code>
:5. OK.

What exactly is happening with this?: <code>0.000;-0.000;0;@</code><br>
:The first part <code>0.000</code> is the format with three decimal places for positive numbers.
:The second part <code>-0.000</code> 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 <code>0</code> is what to display in place of single zeros 
:The fourth part <code>@</code> 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'.

==Making data cells lighter==
'''''Note:''' Afterwards, you can use {{t|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. 

<div style="display:inline-grid; border: 2px solid; padding: 3px;">
'''Wikitext'''
<syntaxhighlight lang=wikitext highlight=7,9>
{| 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
|}
</syntaxhighlight></div>

<div style="display:inline-grid; border: 2px solid; padding: 3px;">
'''Produces'''
{| 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
|}
</div>

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 [https://excel2wiki.toolforge.org 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 <code>class=wikitable</code> to get the background shading.

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

<div style="display:inline-grid; border: 2px solid; padding: 3px;">
'''Wikitext'''
<syntaxhighlight lang=wikitext highlight=7,8,10,11>
{| 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 
|}
</syntaxhighlight></div>

<div style="display:inline-grid; border: 2px solid; padding: 3px;">
'''Produces'''
{| 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 
|}
</div>

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 <code>}}||</code> to <code>}}^P|</code>. <code>^P</code> 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."

==See also==
*[[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.

[[Category:Wikipedia tables]]