Analyzing the Fortune 500 using Excel

Explaining the Fortune 500 Headquarters spreadsheet assignment for class. In this exercise you will use SUMIF, COUNTIF, AVERAGEIF to summarize the Fortune 500 by state. This exercise also covers the RIGHT(), Text-to-columns, named ranges, and VLOOKUP.

Note that if you are using Office 2019 or Office 365 you can use the built in MINIFS() and MAXIFS function and do NOT need to use the array formulas that I use (see about 13:44).

Timestamps:
00:00 Introdution
00:50 Using RIGHT() to split off state abbreviation
02:33 Using text-to-columns to split state and its abbreviation
03:50 Using TRIM() function to remove excess spaces
05:30 Splitting out the city, state, and zip from the combined address field
06:23 Using the CTRL + SHIFT + down arrow to select all 500 companies
06:09 Cutting-and-pasting the entire column
07:31 Using "paste values" to paste the state and zip (it replaces the formula with the values)
09:53 Creating named ranges for state, revenue, profit, and employees
12:09 Using SUMIF and COUNTIF to count employees in that state
13:44 MINIFS and MAXIFS (I used array formulas). Sometimes called CSE formulas for CTRL + SHIFT + ENTER
16:02 Using filters to find states with no Fortune 500 headquarters
17:15 Using conditional formatting to find the top 10

You can read more about the Fortune 500 at:
HQ Data from:
(NOTE: The dataset has financial and employee data for FY 2015)

Extended data from:
(This dataset includes latitude & longitude coordinates for mapping)

Fortune magazine's methodology here:

#excel #exceltutorial