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.
Last month, we showed you how to use Power Query to transform data into a flat table, where each piece of information lives in a separate row. Even better, when we added a new entry to our table, a simple refresh ensured that the new entry was incorporated into our flat file. This month, we will show you how to use Power Query to transform a flat file into a tabular format.
To start, copy and paste this link into a new browser tab to download the Excel workbook and follow along.
Each row of the table contains the product sold, the month in which it was sold, and the number of units sold during the month.
Figure 1: A flat file
Our goal is to use the Power Query tool to rearrange this data into a table format, as you can see in this workbook link.
Figure 2: Sales in a table format
To create this table format, proceed as follows in the first workbook:
1. Select the data in the range C1:E36 and press Ctrl+ T to convert the selected range to an Excel table.
2. From the Data tab, in the Get & Transform Data Group, click From Table/Range (this option may also be listed as “From Sheet,” depending on how updated your Office 365 is.)
3. The Power Query Editor window will open. Hold down Shift to select both the Attribute and Value columns.
4. In the Any Column group on the Transform tab, click the Pivot Column button.
5. The Pivot Column dialog box will open. Leave the default settings as is and click OK.
6. Click the Home table in the Power Query Editor window.
7. Select the product column. Then, click the A-Z button in the Sort group on the Home tab.
8. In the Power Query Editor window’s Home tab, click the Close & Load button in the Close group. Alternatively, open the File menu and choose Close and Load.
9. Add a new entry to your source data for 25 units of soda in January. Then, click into the table and choose Refresh.
As shown in Figure 2, your data is now summarized in a table format, with the Product column sorted in alphabetical order. And of course, our new data is now incorporated in the table.
Next month, we will show you how Power Query can be used to transform messy data.
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.
