View source for Help:Tables and locations

{{Table help}}
{{wikipedia how-to}}
This page documents various ways to use a table to display information related to physical locations (e.g. countries or US states).

== 2 or 3-letter country codes to full names ==

Some source data tables only use the codes. Wrap them in brackets <code><nowiki>{{ABC}}</nowiki></code> to create full-name country links. To do so 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:

<syntaxhighlight lang="ragel" inline>(\|-.*\n\|\s*)([^\|\n]*)</syntaxhighlight>

Make sure there is no space on the end. 

Fill in the "replace with" box with:

<syntaxhighlight lang=wikitext inline>$1{{$2}}</syntaxhighlight>

Save the page. Copy it to [https://excel2wiki.toolforge.org Excel2Wiki] to strip all the templates out, and keep the full names. Copy to a sandbox. Or skip excel2wiki and copy to a spreadsheet such as LibreOffice Calc: '''Edit menu > paste special > paste unformatted text.''' Alphabetize by the full names: '''Data menu > Sort ascending.''' This is necessary because alphabetization of codes is different from full names. Copy the table back to a sandbox via VE. 

There is another way to convert the codes to full names. After putting the table in a sandbox use VE to copy just the code column as previously described at {{Section link||Copy column from one table to another}}. Copy that list to a converter such as [https://www.tracemyip.org/tools/country-code-list-alpha-2-alhpa-3-converter this one]. Since it is a column list pick "new-line separated" from the first dropdown menu. And be sure to pick the correct direction in the second dropdown menu. Since the converted column is going to be pasted back into the table with the exact same order and length pick "include invalid", "original order", and "capitalize country names". Copy the list of results and put it into a separate one-column table as described in the previous section. Then copy that column and use it to replace the code column as described here: {{Section link||Help:Tables|Copy column from one table to another}}. The table will need to be alphabetized again since the codes alphabetize differently versus the full names.

== Convert US state abbreviations to full names ==

If you have a list of all 50 US state abbreviations, then you can copy the full names from the show/hide boxes below. See [[Help:Table/Advanced#Copy column to table]]. Add or remove the [[District of Columbia]] (D.C.) as necessary. Make sure the two lists are in identical order with the same number of rows. Be sure the lists are in matching alphabetical order (whether by abbreviations or full names). You can work in [[Special:MyPage/Sandbox|your sandbox]]. Open both tables below to see highlighted differences in alphabetization. In the end the full names will need to be in alphabetical order. See: [[Help:Table/Advanced#Sort. Spreadsheet & VE]].

Example: You may have a sandbox or spreadsheet of state data using state abbreviations. Open this section for editing via the visual editor (VE). Delete or move the D.C. row if necessary, but do not save the changes. Select and copy ({{Keypress|Ctrl|C}}) the full state names. You can usually paste them directly into the sandbox or spreadsheet over the abbreviations. Use {{Keypress|Ctrl|V}} with the visual editor in the sandbox. Then alphabetize the full names manually or in a spreadsheet. If spreadsheet, copy it directly to VE. If that doesn't work, copy to [https://excel2wiki.toolforge.org Excel2wiki] first.

Some state lists will include the 5 permanently inhabited [[US territories]]: '''AS''' = [[American Samoa]]. '''GU''' = [[Guam]]. '''MP''' = [[Northern Mariana Islands]]. '''PR''' = [[Puerto Rico]]. '''VI''' = [[U.S. Virgin Islands]]. You will have to remove them temporarily. Or fill in their names first, and paste the other full names around them. 

<div style="display:inline-table; vertical-align:top;">
{| class="wikitable mw-collapsible mw-collapsed"
|+ style="border:1px solid; padding:0 3px"| All 50 states and D.C. {{nowrap|Alphabetical order by full names}}
|-
!Full name
! [[List of U.S. state and territory abbreviations#Table|2-letter USPS]]
|-
|Alabama
| style=background:yellow |AL
|-
|Alaska
| style=background:yellow |AK
|-
|Arizona
| style=background:yellow |AZ
|-
|Arkansas
| style=background:yellow |AR
|-
|California
|CA
|-
|Colorado
|CO
|-
|Connecticut
|CT
|-
|Delaware
| style=background:yellow |DE
|-
|District of Columbia
| style=background:yellow |DC
|-
|Florida
|FL
|-
|Georgia
|GA
|-
|Hawaii
|HI
|-
|Idaho
|ID
|-
|Illinois
|IL
|-
|Indiana
|IN
|-
|Iowa
| style=background:yellow |IA
|-
|Kansas
|KS
|-
|Kentucky
|KY
|-
|Louisiana
|LA
|-
|Maine
|ME
|-
|Maryland
| style=background:yellow |MD
|-
|Massachusetts
| style=background:yellow |MA
|-
|Michigan
|MI
|-
|Minnesota
|MN
|-
|Mississippi
|MS
|-
|Missouri
| style=background:yellow |MO
|-
|Montana
|MT
|-
|Nebraska
|NE
|-
|Nevada
| style=background:yellow |NV
|-
|New Hampshire
|NH
|-
|New Jersey
|NJ
|-
|New Mexico
|NM
|-
|New York
| style=background:yellow |NY
|-
|North Carolina
| style=background:yellow |NC
|-
|North Dakota
| style=background:yellow |ND
|-
|Ohio
|OH
|-
|Oklahoma
|OK
|-
|Oregon
|OR
|-
|Pennsylvania
|PA
|-
|Rhode Island
|RI
|-
|South Carolina
|SC
|-
|South Dakota
|SD
|-
|Tennessee
|TN
|-
|Texas
|TX
|-
|Utah
|UT
|-
|Vermont
| style=background:yellow |VT
|-
|Virginia
| style=background:yellow |VA
|-
|Washington
|WA
|-
|West Virginia
|WV
|-
|Wisconsin
| style=background:yellow |WI
|-
|Wyoming
|WY
|}
</div>

<div style="display:inline-table; vertical-align:top;">
{| class="wikitable mw-collapsible mw-collapsed"
|+ style="border:1px solid; padding:0 3px;"| All 50 states and D.C. {{nowrap|Alphabetical order by abbreviations}}
|-
!Full name 
! [[List of U.S. state and territory abbreviations#Table|2-letter USPS]]
|-
|Alaska
| style=background:yellow |AK
|-
|Alabama
| style=background:yellow |AL
|-
|Arkansas
| style=background:yellow |AR
|-
|Arizona
| style=background:yellow |AZ
|-
|California
|CA
|-
|Colorado
|CO
|-
|Connecticut
|CT
|-
| District of Columbia
| style=background:yellow |DC
|-
|Delaware
| style=background:yellow |DE
|-
|Florida
|FL
|-
|Georgia
|GA
|-
|Hawaii
|HI
|-
|Iowa
| style=background:yellow |IA
|-
|Idaho
|ID
|-
|Illinois
|IL
|-
|Indiana
|IN
|-
|Kansas
|KS
|-
|Kentucky
|KY
|-
|Louisiana
|LA
|-
|Massachusetts
| style=background:yellow |MA
|-
|Maryland
| style=background:yellow |MD
|-
|Maine
|ME
|-
|Michigan
|MI
|-
|Minnesota
|MN
|-
|Missouri
| style=background:yellow |MO
|-
|Mississippi
|MS
|-
|Montana
|MT
|-
|North Carolina
| style=background:yellow |NC
|-
|North Dakota
| style=background:yellow |ND
|-
|Nebraska
|NE
|-
|New Hampshire
|NH
|-
|New Jersey
|NJ
|-
|New Mexico
|NM
|-
|Nevada
| style=background:yellow |NV
|-
|New York
| style=background:yellow |NY
|-
|Ohio
|OH
|-
|Oklahoma
|OK
|-
|Oregon
|OR
|-
|Pennsylvania
|PA
|-
|Rhode Island
|RI
|-
|South Carolina
|SC
|-
|South Dakota
|SD
|-
|Tennessee
|TN
|-
|Texas
|TX
|-
|Utah
|UT
|-
|Virginia
| style=background:yellow |VA
|-
|Vermont
| style=background:yellow |VT
|-
|Washington
|WA
|-
|Wisconsin
| style=background:yellow |WI
|-
|West Virginia
|WV
|-
|Wyoming
|WY
|}
</div>
<!-- See: [[User:Qwerfjkl/scripts/convertAbbreviations]]. It is not currently working (see [[User talk:Qwerfjkl/scripts/convertAbbreviations#Does not work in Vector 2010 and 2022. Or in Monobook|talk]]). This [[user script]] changes them all at once, via a "Convert abbreviations" link in the sidebar or tools menu. It shows up only during wikitext source editing. -->

== Horizontal table of contents for a table of states or countries ==

See '''[[Template:Flatlist]].''' Example below with all 50 states (no Washington, DC). It can be added.
{| class=wikitable
|+ Contents
|
{{flatlist|
*[[#Alabama|Alabama]]
*[[#Alaska|Alaska]]
*[[#Arizona|Arizona]]
*[[#Arkansas|Arkansas]]
*[[#California|California]]
*[[#Colorado|Colorado]]
*[[#Connecticut|Connecticut]]
*[[#Delaware|Delaware]]
*[[#Florida|Florida]]
*[[#Georgia|Georgia]]
*[[#Hawaii|Hawaii]]
*[[#Idaho|Idaho]]
*[[#Illinois|Illinois]]
*[[#Indiana|Indiana]]
*[[#Iowa|Iowa]]
*[[#Kansas|Kansas]]
*[[#Kentucky|Kentucky]]
*[[#Louisiana|Louisiana]]
*[[#Maine|Maine]]
*[[#Maryland|Maryland]]
*[[#Massachusetts|Massachusetts]]
*[[#Michigan|Michigan]]
*[[#Minnesota|Minnesota]]
*[[#Mississippi|Mississippi]]
*[[#Missouri|Missouri]]
*[[#Montana|Montana]]
*[[#Nebraska|Nebraska]]
*[[#Nevada|Nevada]]
*[[#New Hampshire|New Hampshire]]
*[[#New Jersey|New Jersey]]
*[[#New Mexico|New Mexico]]
*[[#New York|New York]]
*[[#North Carolina|North Carolina]]
*[[#North Dakota|North Dakota]]
*[[#Ohio|Ohio]]
*[[#Oklahoma|Oklahoma]]
*[[#Oregon|Oregon]]
*[[#Pennsylvania|Pennsylvania]]
*[[#Rhode Island|Rhode Island]]
*[[#South Carolina|South Carolina]]
*[[#South Dakota|South Dakota]]
*[[#Tennessee|Tennessee]]
*[[#Texas|Texas]]
*[[#Utah|Utah]]
*[[#Vermont|Vermont]]
*[[#Virginia|Virginia]]
*[[#Washington|Washington]]
*[[#West Virginia|West Virginia]]
*[[#Wisconsin|Wisconsin]]
*[[#Wyoming|Wyoming]]}}
|}

A flat list (without all 50 states) is in the article linked below. The list is in the map caption: 
*[[List of temples in the United States (LDS Church)#Contents]]

Clicking the state on the map there will also work. Map does not have to be there, but it is convenient. The flat list and map there are using internal links to the section links within the table. 

Another method is the '''id=''' anchor method used at the link below, and discussed in the next section.
*[[Legality of cannabis by U.S. jurisdiction#By state]]

It is also possible to put the map and list next to each other. And they can wrap. Narrow browser window to see.
<div style="display:inline-grid; vertical-align:top;">
'''Table of contents. Click states on map, or in list.'''
{{Template:USA image map in page|width=400px}}
</div>
<div style="display:inline-grid; vertical-align:top;">
{| class=wikitable style=max-width:400px;
|+ Contents
|
{{flatlist|
*[[#Alabama|Alabama]]
*[[#Alaska|Alaska]]
*[[#Arizona|Arizona]]
*[[#Arkansas|Arkansas]]
*[[#California|California]]
*[[#Colorado|Colorado]]
*[[#Connecticut|Connecticut]]
*[[#Delaware|Delaware]]
*[[#Florida|Florida]]
*[[#Georgia|Georgia]]
*[[#Hawaii|Hawaii]]
*[[#Idaho|Idaho]]
*[[#Illinois|Illinois]]
*[[#Indiana|Indiana]]
*[[#Iowa|Iowa]]
*[[#Kansas|Kansas]]
*[[#Kentucky|Kentucky]]
*[[#Louisiana|Louisiana]]
*[[#Maine|Maine]]
*[[#Maryland|Maryland]]
*[[#Massachusetts|Massachusetts]]
*[[#Michigan|Michigan]]
*[[#Minnesota|Minnesota]]
*[[#Mississippi|Mississippi]]
*[[#Missouri|Missouri]]
*[[#Montana|Montana]]
*[[#Nebraska|Nebraska]]
*[[#Nevada|Nevada]]
*[[#New Hampshire|New Hampshire]]
*[[#New Jersey|New Jersey]]
*[[#New Mexico|New Mexico]]
*[[#New York|New York]]
*[[#North Carolina|North Carolina]]
*[[#North Dakota|North Dakota]]
*[[#Ohio|Ohio]]
*[[#Oklahoma|Oklahoma]]
*[[#Oregon|Oregon]]
*[[#Pennsylvania|Pennsylvania]]
*[[#Rhode Island|Rhode Island]]
*[[#South Carolina|South Carolina]]
*[[#South Dakota|South Dakota]]
*[[#Tennessee|Tennessee]]
*[[#Texas|Texas]]
*[[#Utah|Utah]]
*[[#Vermont|Vermont]]
*[[#Virginia|Virginia]]
*[[#Washington|Washington]]
*[[#West Virginia|West Virginia]]
*[[#Wisconsin|Wisconsin]]
*[[#Wyoming|Wyoming]]}}
|}
</div>

== Section link or map link to a row anchor ==
{{hatnote|See also: [[Template:Australia Labelled Map]] for links outside the page it is on.}}

Note that this method is problematic for people using larger text sizes in their browser. A clickable image map (such as the US map in the previous section) is a better choice: 
*[[Template:USA image map in page]]

To enable a [[Help:Link#Section linking (anchors)|section link]]<nowiki />'s [[HTML element#Anchors|anchor]] (or a [[Wikipedia:Blank maps|map link]]'s anchor), referencing a specific row within a table, an <code>'''id="{{var|section link anchor name}}"'''</code> parameter needs to be added to the row start <code>'''|-'''</code> or '''<code><nowiki><tr></nowiki></code>''':
 <code><nowiki>|-</nowiki> id="{{var|section link anchor name}}"</code>

 <code><nowiki><tr</nowiki> id="{{var|section link anchor name}}"></code>
Note that each {{var|section link anchor name}} must be different from every other in the page (this includes heading names), to create valid XHTML and allow proper linking.

;Example of a map link to a row
{{hatnote|See also: [[Template:USA image map in page]] and where it is transcluded.}}
When a country label, containing a link, is clicked on the map (the link coded, for example, as <code>'''<nowiki>[[#New South Wales|New South Wales]]</nowiki>'''</code> that references the anchor (within the table), coded as <code>'''|-&nbsp;id="New South Wales"'''</code>) the page scrolls so that the selected row of the table is at, or within a few lines, of the top of the browser view. Here, we use the template family {{t|Image label begin}}, {{t|Image label}}, and {{t|Image label end}} to lay out such a table for us:

{{Image label begin|image=Australia location map recolored.png|alt=Australia map|width={{{width|}}}|float={{{float|}}}|font-size=85%}}
{{Image label|x=0.82|y=0.55|scale={{{width|}}}|text=[[#Australian Capital Territory|Australian<br/>Capital<br/>Territory]]}}
{{Image label|x=0.72|y=0.465|scale={{{width|}}}|text=[[#New South Wales|New<br/>South<br/>Wales]]}}
{{Image label|x=0.47|y=0.24|scale={{{width|}}}|text=[[#Northern Territory|Northern<br/>Territory]]}}
{{Image label|x=0.6525|y=0.3|scale={{{width|}}}|text=[[#Queensland|Queensland]]}}
{{Image label|x=0.495|y=0.41|scale={{{width|}}}|text=[[#South Australia|South<br/>Australia]]}}
{{Image label|x=0.70|y=0.685|scale={{{width|}}}|text=[[#Tasmania|Tasmania]]}}
{{Image label|x=0.6775|y=0.58|scale={{{width|}}}|text=[[#Victoria|Victoria]]}}
{{Image label|x=0.26|y=0.34|scale={{{width|}}}|text=[[#Western Australia|Western<br/>Australia]]}}
{{Image label end}}

<div style="display:inline-grid; vertical-align:top;">
{| class=wikitable 
|+ [[Australia]]
! State or territory
|- id="Australian Capital Territory"
| [[Australian Capital Territory|Capital Territory]]
|- id="New South Wales"
| [[New South Wales]]
|- id="Northern Territory"
| [[Northern Territory]]
|- id="Queensland"
| [[Queensland]]
|- id="South Australia"
| [[South Australia]]
|- id="Tasmania"
| [[Tasmania]]
|- id="Victoria"
| [[Victoria (state)|Victoria]]
|- id="Western Australia"
| [[Western Australia]]
|}</div>

<div style="display:inline-grid; vertical-align:top;">
<syntaxhighlight lang=wikitext>
{| class=wikitable 
|+ [[Australia]]
! State or territory
|- id="Australian Capital Territory"
| [[Australian Capital Territory|Capital Territory]]
|- id="New South Wales"
| [[New South Wales]]
|- id="Northern Territory"
| [[Northern Territory]]
|- id="Queensland"
| [[Queensland]]
|- id="South Australia"
| [[South Australia]]
|- id="Tasmania"
| [[Tasmania]]
|- id="Victoria"
| [[Victoria (state)|Victoria]]
|- id="Western Australia"
| [[Western Australia]]
|}
</syntaxhighlight>
</div>

== Glossary flat list for table header abbreviations ==
Glossary is in alphabetical order for easier lookup. And it wraps as the screen narrows. 
{{flatlist|
* '''Bei''' (Beijing)
* '''Ber''' (Berlin)
* '''Boc''' (Boca Raton)
* '''Can''' (Canada)
* '''Cha''' (Charleston)
* '''Cin''' (Cincinnati)
* '''Doh''' (Doha)
* '''Dub''' (Dubai)
* '''Ind''' (Indian Wells)
* '''Mad''' (Madrid)
* '''Mia''' (Miami)
* '''Mos''' (Moscow)
* '''Phi''' (Philadelphia)
* '''Rom''' (Rome)
* '''San''' (San Diego)
* '''Tok''' (Tokyo)
* '''Wuh''' (Wuhan)
* '''Zur''' (Zürich)
}}

{{mw-datatable}}{{sort under}}{{table alignment}}{{sticky header}}
{| class="wikitable sortable mw-datatable sort-under-center defaultcenter col1left sticky-header" style="font-size:90%"
|+ Tennis titles
|-
! Player
! Titles
! Boc
! Dub
! Doh
! Ind
! Mia
! Cha
! Mad
! Ber
! Rom
! Can
! San
! Cin
! Phi
! Mos
! Tok
! Wuh
! Zur
! Bei
! Years
|-
| {{sort|Williams.|{{flagicon|USA}} [[Serena Williams]]}}
|23
| -
| -
| -
|'''<u>2</u>'''
|'''<u>8</u>'''
|1
|2
| -
|'''<u>4</u>'''
|3
| -
|'''<u>2</u>'''
| -
| -
| -
| -
| -
|1
|1999–2016
|-
| {{sort|Hingis.|{{flagicon|SUI}} [[Martina Hingis]]}}
|17
| - || - || -
|1
|2
|'''<u>2</u>'''
| -
|1
|2
|2
| -
| -
| -
|1
|'''<u>5</u>'''
| -
|1
| -
|1997–2007
|}

Wikitext for glossary flat list for table header abbreviations: 
<syntaxhighlight lang=wikitext>
{{flatlist|
* '''Bei''' (Beijing)
* '''Ber''' (Berlin)
* '''Boc''' (Boca Raton)
* '''Can''' (Canada)
* '''Cha''' (Charleston)
* '''Cin''' (Cincinnati)
* '''Doh''' (Doha)
* '''Dub''' (Dubai)
* '''Ind''' (Indian Wells)
* '''Mad''' (Madrid)
* '''Mia''' (Miami)
* '''Mos''' (Moscow)
* '''Phi''' (Philadelphia)
* '''Rom''' (Rome)
* '''San''' (San Diego)
* '''Tok''' (Tokyo)
* '''Wuh''' (Wuhan)
* '''Zur''' (Zürich)
}}
</syntaxhighlight>

== Add flags. Link countries, states, etc. in tables ==
:'''''This is fast. It works with compact or long table wikitext.''' Single or double bars between cells.''
:'''''Note:''' [[User:PrimeHunter]] ([[User talk:PrimeHunter|Talk]]) provided the code. Ask him for help, or ask at [[WP:VPT]].''
:'''''Note:''' In [[Special:Preferences#mw-prefsection-editing|editing preferences]] check the box: "enable the editing toolbar. This is sometimes called the '2010 wikitext editor'."''
Make sure the countries, states, provinces, or cities, etc. are in the first column. The regular expression wraps all the text in the first paragraph in the first cell of each row with the {{tlx|flaglist}} template. {{tlx|flaglist|Country name}} So do this before adding any styling to the first column. If there is other info besides the location name in those first cells, separate it with a blank line. See  [[User:Timeshifter/Sandbox231|example table]].

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:

<syntaxhighlight lang=wikitext inline>(\|-.*\n\|\s*)([^\|\n]*)</syntaxhighlight>

Make sure there is no space on the end.

Fill in the "replace with" box with:

<syntaxhighlight lang=wikitext inline>$1{{flaglist|$2}}</syntaxhighlight>

Other flag template names can be used too. If you want '''links without flags''' replace with:

<syntaxhighlight lang=wikitext inline>$1[[$2]]</syntaxhighlight>

Then click "Replace all". Nearly all countries, states, etc. will be linked.

'''Create links without flags first.''' If there are red links create redirects. This will also take care of all the red links in the flag lists. Then create another table with flag links. If there are any country/state links without flags open the whole page in wikitext source mode. Go to the bottom of the page and look for red-linked [[:Category:Country data templates|country data templates]]. Redirect them to the correct country data templates. In the edit summary of such redirects, add something like this: "Please do not delete this redirect. It is sometimes used in updates of {{elc|NAME OF ARTICLE WITH TABLE}}." The country data template redirects may show up temporarily for a day or two here:
*[[Wikipedia:Database reports/Transclusions of non-existent templates]]
And the corresponding flags may take a day or two to show up. Be patient. Template redirects can take time to come into effect. After that happens you may need to correct the alphabetization of a few countries. 

If it is a US state list, change '[[Georgia]]' to '[[Georgia (U.S. state)]]'. Otherwise 'Georgia' will be treated as [[Georgia (country)|the country]] by the flag templates. Use this:
:<code><nowiki>{{flaglist|Georgia (U.S. state)|name=Georgia}}</nowiki></code>

{{tl|flaglist}} does not work with abbreviated country or state names. But if you intend to use a specialized article name (as in the next section) then it is OK to use abbreviated country or state names in this section, and then go on to the next section. 

=== Adding specialized country/state links ===

For example:
*[[List of U.S. states and territories by incarceration and correctional supervision rate]]
*[[List of countries by incarceration rate]]

Once you have a table with {{tl|flaglist}}, ''and/or other flag template such as {{tl|flag}},'' it is easy to convert the links to specialized links. So instead of '''[[Oklahoma]]''' you would have '''[[Incarceration in Oklahoma]]''', or if that article does not exist, then '''[[Crime in Oklahoma]]'''. 

Following the [[principle of least astonishment]] articles with specialized country and state links (and so on) should indicate just before the table what those links will be. This is especially important where some links have asterisks for the specialized links, meaning the other links are just standard links. See examples in the previous list links, and in the list links in the following sections.

The following works with full country or US state names. Also with abbreviated (3-letter) country names, and abbreviated (2-letter) US state names. 

Use a global find-and-replace as previously described, but without regular expressions. Replace:

<code><nowiki>{{flag|</nowiki></code>
:or
<code><nowiki>{{flaglist|</nowiki></code>

with

<code><nowiki>{{flagg|us*eft|pref=Incarceration in|pref2=Crime in|</nowiki></code>

The bar after flag or flaglist is necessary to avoid problems in case you have to replace 2 flag templates. 

The {{tl|flagg}} template looks for the articles. If it finds one of the two choices it adds the link and an asterisk after the location name. If it does not find either article, it just adds the standard link.

Substitute your preferred topics in the '''pref=''' and '''pref2=''' spots.

<code><nowiki>{{flagg|us*eft|</nowiki></code> usually left aligns the text of that column (due to the [[template:flagg|"t" at the end of the code]]). For some unknown reason that is not always true. In that case each cell in that column will need <code><nowiki>style=text-align:left</nowiki></code> in it. Or use the whole column code described here: [[#Column alignment]] and here: {{tl|Table alignment}}. See [[User:Timeshifter/Sandbox206|example table]]. 

[[Washington, D.C.]] - Have to force the full name to be shown: 
:<code><nowiki>{{flagg|us*eft|pref=Crime in|Washington, D.C.|name=Washington, D.C.}}</nowiki></code>

This particular use of {{tl|flagg}} parameters is '''[[mw:Help:Extension:ParserFunctions#ifexist_limits|expensive]]''' and can only be used on a few hundred links per article. As a rule of thumb, only use one big country table per article. Other country tables in the same article should use standard links.

In [[List of countries by incarceration rate]] the {{tl|flagg}} template creates a link to [[Incarceration in United States]]. That link is a redirect to [[Incarceration in the United States]]. Add "the" as necessary to create redirect pages. Bots and admins will rapidly delete redirects that are not linked from within Wikipedia. So if you need to create redirects be sure to use them right away in an article table. Or keep them in a sandbox, or the article talk page, so that they are in use even if removed later during table changes. See [[User:Timeshifter/Sandbox94|examples]]. See [[User:Timeshifter/Sandbox158|list]].

{{tl|Flagg}} removes the names of the countries behind territories, subnational areas, etc.. This makes for more compact tables. Other flag templates (such as {{tl|flaglist}}) do not do that. But that template can not create specialized country links.

See [https://global-search.toolforge.org Global Search] at Toolforge. Search for <br>
<nowiki>"{{flagg|us*eft"</nowiki> - in quotes. To get transclusion count and list of articles.

The {{tl|flagg}} template does not work if '''colspan''' is used before the country/state name. {{tl|flag+link}} works. See: [[Template talk:Flagg#Does not work with colspan]].

=== Without asterisks after location names ===

When all the locations in a table list have specialized articles, then there is no need for the asterisks to distinguish those specialized location articles.

Replace <code><nowiki>{{flagg|us*eft</nowiki></code> with <code><nowiki>{{flagg|uspeft</nowiki></code> to remove the asterisks, and lower the [https://www.mediawiki.org/wiki/Help:Extension:ParserFunctions#ifexist_limits expensive server load].

See [https://global-search.toolforge.org Global Search] at Toolforge. Search for <br>
<nowiki>"{{flagg|uspeft"</nowiki> - in quotes. To get transclusion count and list of articles.

=== Without asterisks. Only one preference ===

The above section will work too, and the location names line up better. 

See [https://en.wikipedia.org/w/index.php?title=List_of_countries_by_hospital_beds&oldid=1141044027 this version] of [[List of countries by hospital beds]].

Replace:

<code><nowiki>{{flaglist</nowiki></code>

with

<code><nowiki>{{flag+link|Health in</nowiki></code>

== 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 [[:toolforge:excel2wiki| 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, 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 "Transpose" 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 [https://excel2wiki.toolforge.org Excel2Wiki].

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. The year headers in the left table below need to become the column headers in the right table.

<div style="display:inline-grid;">
<div style="height: 10em; overflow: auto; border: 2px solid lime; padding:0 2em;">
{| class=wikitable
!scope=col| Year
!scope=col| State
!scope=col| Rate
|-
!scope=row| 2019
|AL
|16.3
|-
!scope=row| 2019
|AK
|17.8
|-
!scope=row| 2019
|AZ
|26.8
|-
!scope=row| 2018
|AL
|16.6
|-
!scope=row| 2018
|AK
|14.6
|-
!scope=row| 2018
|AZ
|23.8
|-
!scope=row| 2017
|AL
|18
|-
!scope=row| 2017
|AK
|20.2
|-
!scope=row| 2017
|AZ
|22.2
|}
</div></div>

<div style="display:inline-grid;">
{| class="wikitable sortable" style="text-align: right;"
!scope=col| State
!scope=col| 2017
!scope=col| 2018
!scope=col| 2019
|-
!scope=row| AL
| 18
| 16.6
| 16.3
|-
!scope=row| AK
| 20.2
| 14.6
| 17.8
|-
!scope=row| AZ
| 22.2
| 23.8
| 26.8
|}
</div>

To re-arrange the table using pivot tables, copy and paste the table into a spreadsheet program such as freeware [[LibreOffice Calc]].

In LibreOffice Calc, first make sure the headers are in the top row, and that they are [https://help.libreoffice.org/latest/en-US/text/scalc/guide/line_fix.html not sticky (frozen)]. Then select all from the edit menu. 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. In the options menu decide whether you want the rows or columns totaled. Click OK. The table will convert to the new format with the years as column headers. To avoid problems copy the table to a new sheet before further editing. See: "[https://help.libreoffice.org/latest/en-US/text/scalc/guide/cellcopy.html Only Copy Visible Cells]" in Calc help. For more help see: [https://elearn.ellak.gr/mod/page/view.php?id=3015 LibreOffice: Pivot Tables] and [https://help.libreoffice.org/latest/en-US/text/scalc/guide/datapilot.html?DbPAR=CALC LibreOffice Help: Pivot Table]. If necessary, convert state or country abbreviations to full names. 

When done editing, copy the new table (if small) directly from the Calc page into visual editor, or into [https://excel2wiki.toolforge.org Excel2Wiki] first, and then into VE. Then use VE to delete the summation column and row if necessary.

== Pick 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 [https://covid19.who.int/WHO-COVID-19-global-data.csv 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 [https://www.youtube.com/watch?v=aVUFTQ-Ux7k video]. See: "[https://help.libreoffice.org/latest/en-US/text/scalc/guide/autofilter.html 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 [[OpenDocument technical specification#Documents|ods file]]. A simple paste works (edit menu > paste). This greatly reduces the file size. See: "[https://help.libreoffice.org/latest/en-US/text/scalc/guide/cellcopy.html 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 all from the edit menu. 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. See: [[Help:Creating tables#Separate after 3 digits]]. Sort latest month. Save it. Then copy that Calc table to [https://excel2wiki.toolforge.org Excel2Wiki], or directly to the VisualEditor (if small). For more info go [[User:Timeshifter/Sandbox173|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.

== Data from latest available year ==

[[List of countries by firearm-related death rate]] has both a yearly rate table, and a rate table showing only the latest available year. The latest-year format allows for easier sorting, ranking, and comparison of countries. Due to the limits of screen width the yearly table is missing some countries because their latest available year is too far back in time. For latest-year instructions see:
*[[Talk:List of countries by firearm-related death rate/LibreOffice Calc instructions for latest available year]]

[[List of countries by intentional homicide rate]] has a table that only shows data for the latest available year. It has 2 data columns (counts and rates). See updating instructions:
*[[Talk:List of countries by intentional homicide rate/LibreOffice Calc instructions]]
*[[Talk:List of countries by intentional homicide rate/Excel instructions]]

== Separate counts and rates to 2 columns ==
Rates are per 100,000 inhabitants. 
<div style="display:inline-grid; vertical-align:top;">
{| class="wikitable sortable" 
|+ Homicide rates and counts
! Location !! Year !! Type !! Value
|-
| Afghanistan || 2021 || Counts || 1613
|-
| Afghanistan || 2021 || Rates || 4.0
|-
| Albania || 2021 || Counts || 66
|-
| Albania || 2021 || Rates || 2.3
|}
</div>
<div style="display:inline-grid; vertical-align:top;">
{| class="wikitable sortable" 
|+ Homicide rates and counts
! Location !! Year !! Rates !! Counts
|-
| Afghanistan || 2021 || 4.0 || 1613
|-
| Albania || 2021 || 2.3 || 66
|}
</div>

See [[List of countries by intentional homicide rate]]. Its reference dataset comes with counts and rates alternating in the same column. See example [[User:Timeshifter/Sandbox223#Counts and rates in one column|here]]. That example is after only the latest available year is showing in the spreadsheet. See previous section above for links to detailed instructions.

In LibreOffice Calc select the column head for the column containing the word "counts". 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. Delete column that only contains the word "Counts". Copy and paste to new Calc file to get accurate row numbering, and small file size. Note the number of rows. 

Start over and do the same except for "Rates." Hopefully, the number of rows are the same as for the counts sheet. You now have 2 sheets. One for counts and one for rates. Copy the counts column to the rates sheet.

== Automated tables updated daily by bots ==

{{Hatnote|'''Note:''' Contact [[User:Tol]] ([[User talk:Tol|talk]]) for help with automated tables.}}
{{see also|Category:Automatically updated COVID-19 pandemic table templates}}
Some automated tables:
*[[Template:COVID-19 pandemic data]]. Scrolling. Used here: [[Portal:COVID-19]].
*[[Template:COVID-19 vaccination data]]. Scrolling. Used here:
**[[Deployment of COVID-19 vaccines]]
The next 2 templates are almost the same:
*[[Template:COVID-19 pandemic death rates]]. Scrolling. Used here:
**[[COVID-19 pandemic by country and territory]]
*[[Template:COVID-19 pandemic death rates by country]]. Scrolling. Used here:
**[[COVID-19 pandemic death rates by country]].

==See also==
*[[Help:Tables]] for general information about using tables. It also has many links in its "See also"  and "External links" sections. Rather than duplicating them here.

{{Wikipedia help pages}}
{{Wikipedia technical help}}

[[Category:Wikipedia tables|Locations]]
[[Category:Wikipedia how-to]]
[[Category:Wikipedia text help]]