Welcome to another entry in our series of Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can make your accounting and finance work a bit easier.
Often, business analysts need an easy way to import data from a text file, database, the web, or other data source into Excel. The business analyst will often need to shape or transform this data, and will usually want the imported data to include changes in the source data. In our next few articles, we will introduce the amazing capabilities of Excel’s amazing Power Query (available to those of you who have Excel 2016 or a newer version.) Power Query allows analysts to efficiently import, reshape, and transform data. As shown in Figure 1, Power Query is available from the Get & Transform group on the Data Tab. Copy and paste this link into a new browser tab to walk through an interactive example of the Power Query function in Excel.
Figure 1: Get & Transform options
Clicking on “Get Data” brings up different options for data sources, shown in Figure 2. Figure 2 also shows the source options available if you choose “From Other Sources.” In this month’s article, we will choose “From Web” to import data on the United States’ largest cities into Excel. We want to import each city’s name and state, separated by a comma in a single cell, and import each city’s population into a second column. Since populations change, we want to ensure that we can refresh our output and that our transformed population data will automatically update to incorporate changes in the website.
Figure 2: Other Get & Transform data sources
This link contains information about the population of America’s 100 largest cities.
To import this data, we proceed as follows:
1. From the Data tab, choose “From Web” (or choose “From Web” from “Other Sources”) and enter the source URL, which contains populations of the largest US cities, into the “From Web” dialog box and click OK.
Figure 3: We enter the source URL
2. We will now see the Tables contained in this website, as shown in Figure 4.
Figure 4: Available website tables shown on “Navigator” tab
3. After clicking on and looking through these Tables, we see that Table 2 contains the needed data, so we select Table 2. Since we need to transform the data, we click “Transform Data” and we see the website’s data. A subset of the data is shown in Figure 5.
Figure 5: Data on US cities’ populations
4. As we only need the data from Columns 2-4, we need to select the other columns and remove them. Select the “2019 Rank” column and click “Remove Columns” to remove the first column. Use the Shift key to select all columns to the right of “2019 estimate” and repeat the process to remove those columns.
5. Select the “City” and “State” columns and choose “Merge Columns” from the “Transform” tab on the ribbon. Select the “comma” option under the “Separator” dropdown and click OK.
6. Right click on the first column and after selecting “Rename,” change the name of the column to “City and State.” Your data should now look like Figure 6.
Figure 6: Transformed US population data
7. Now click “Close and Load” and save the file.
8. In the future, if you right click in the transformed data and hit refresh, Excel will go to the website and refresh the data in the format you have created.
Learn more with Excel CPE
Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!
Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:
- Excel: Technical Analysis Trading Strategies
- Excel: Enterprise Risk Management
- Excel: Magic with Excel
- Excel: Solve Hard Problems in Corporate Finance
- Excel Metrics: Best Practices
- Python for Excel Users: A Gentle Introduction
These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!
Unlock unlimited CPE with a Prime Subscription
Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry!
With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits.
