✦Template Printable Ideas Insights

State & Territory Abbrevs for Excel

By

When working with geographical data in Excel, having a list of state and territory abbreviations can be invaluable. It helps streamline your data entry, makes your spreadsheets more readable, and is particularly useful when you're dealing with addresses or location-based information. So, let's delve into creating and using a list of state and territory abbreviations in Excel.

Before we begin, it's essential to understand the importance of standardizing abbreviations. While some may be universally accepted, like 'NY' for New York, others might vary. For instance, the abbreviation for North Carolina could be 'NC' or 'N.C.', depending on the style guide you're following. Therefore, we'll stick to the most commonly accepted abbreviations for consistency.

Creating a List of Abbreviations

Creating a list of state and territory abbreviations in Excel is a straightforward process. Here's how you can do it:

List of All 50 US State Abbreviations
List of All 50 US State Abbreviations
the advanced excel chart sheet is shown in green and has instructions on how to use it
the advanced excel chart sheet is shown in green and has instructions on how to use it
the numbers and times for different countries are displayed
the numbers and times for different countries are displayed

1. In a new Excel worksheet, create two columns. Label the first column 'State/Territory' and the second column 'Abbreviation'.

Manually Entering Abbreviations

You can manually enter the full state/territory name in the first column and its corresponding abbreviation in the second column, like so:

the basic excel formats for each type of document, including numbers and symbols on it
the basic excel formats for each type of document, including numbers and symbols on it
the excel formats chart for each class
the excel formats chart for each class
an image of the names and abbreviations of different languages in english, spanish, and german
an image of the names and abbreviations of different languages in english, spanish, and german

- Full State/Territory: Alabama
- Abbreviation: AL
- ...

Using a Drodown List

Alternatively, you can use a dropdown list to enter the abbreviations, which can save time and reduce errors. Here's how you can create one:

1. In the 'State/Territory' column, type the full names of all the states and territories in the U.S., starting from A (Alabama) to Z (Z broni zm Magazine).

Microsoft Excel Cheat Sheet | Essential Formulas, Shortcuts & Productivity Tips
Microsoft Excel Cheat Sheet | Essential Formulas, Shortcuts & Productivity Tips
Excel formulas
Excel formulas
the excel shortcuts list is shown with green and black numbers on it, which are
the excel shortcuts list is shown with green and black numbers on it, which are
Microsoft Excel Shortcuts| Data Analysis Tools| Tips and Tricks Spreadsheets|Excel Tutorial Formulas
Microsoft Excel Shortcuts| Data Analysis Tools| Tips and Tricks Spreadsheets|Excel Tutorial Formulas
list of state and territory abbreviations excel
list of state and territory abbreviations excel
the advanced excel features list is shown in green and blue, with text below it
the advanced excel features list is shown in green and blue, with text below it

2. Select the 'Abbreviation' column, then click on 'Data' in the ribbon, and select 'Data Validation'.

3. In the 'Settings' tab, under 'Allow', select 'List', and in the 'Source' field, enter '=$A$2:$A$60' (assuming you've entered all your state/territory names from A2 to A60).

Using the List in Your Spreadsheet

Now that you have your list, you can use it in your spreadsheet as a reference. Here's how you can do it:

the excel functions quick reference chart is shown in green and white, with numbers on each side
the excel functions quick reference chart is shown in green and white, with numbers on each side
a table that has different types of text on it, including the words excel formulas
a table that has different types of text on it, including the words excel formulas
an image of a list of the countries with flags on each side and numbers in different languages
an image of a list of the countries with flags on each side and numbers in different languages

1. In your primary data table, include a column for the state/territory abbreviation.

Using VLOOKUP

You can use the VLOOKUP function to pull the abbreviation from your list based on the full state/territory name. Here's how:

1. In the cell where you want the abbreviation to appear, type '=VLOOKUP(' and drag to select the cell containing the full state/territory name.

2. Type a comma, then type '2', which refers to the column number where the abbreviations are located in your list.

3. Type a comma again, then 'FALSE'. This means the match should be exact and not approximate.

4. Close the formula with a closing parenthesis. The abbreviation should now appear in the cell.

Using INDEX and MATCH

Another way to pull the abbreviation is by using INDEX and MATCH together. The advantage of this method is that it's less prone to errors and can handle approximate matches. Here's how:

1. In the cell where you want the abbreviation to appear, type '=INDEX(' and drag to select the range containing the abbreviations.

2. Type a comma, then type 'MATCH(' and drag to select the cell containing the full state/territory name.

3. Close the formula with a closing parenthesis. The abbreviation should now appear in the cell.

Using a list of state and territory abbreviations in Excel not only saves you time but also ensures consistency in your data. Whether you're entering addresses, sales data, or any other location-based information, having this list at your disposal can make your work more efficient and your spreadsheets more organized.