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.
Next month we will show you how to use Power Query to easily flatten an Excel table or create a table from a flat file.
Want to improve your accounting Excel skillset? Enroll in Becker’s Microsoft® Excel Fundamentals + Data Analytics Certificate and learn essential Excel functions for accounting, from fundamentals all the way to in-depth applications for data analytics.
Keep visiting the Becker career blog for more helpful Excel tips.
