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 Office 365’s Power Query to import and transform data from the web into Excel, and how to keep your spreadsheet refreshed for the most updated data. This month, we’ll show you how Power Query can ease the task of flattening a table. Let’s get started!
Flattening a Table
To start, copy and paste the link to this file and the link to this second data file into new browser tabs to download two Excel workbooks and follow along.
The first workbook contains sales information for products during the months January-April. We want to flatten the table so that each piece of information is in a separate row and that the data is sorted alphabetically (A-Z) based on product name – essentially, the opposite function of creating a Pivot Table. Upon refreshing, we also want the flattened table to include new data.
Figure 1: Monthly sales data in a table
To flatten the table, you will need to use Power Query’s Unpivot Columns command. Follow these steps.
- Click into the pivot table.
- From the data tab, go to the Get & Transform Data group and choose From Sheet (if you do not have the latest version of Office 365, you can select Data --> from Table / Range.)
- The Power Query Editor window will open. Click the Transform tab.
- Select the January-April columns by holding down the Shift key.
- In the Any Column group of the Transform tab, click the Unpivot Columns button. This is the step that “flattens” the data!
- Click on the Product column label, and click the dropdown and choose Sort Ascending. This step ensures that your flattened data will be sorted A-Z by product, even when we refresh after adding new data.
- 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 & Load.
- Add a new entry to your source data, for candy sales of 125 in May. After right-clicking on the data and selecting Refresh, you will see that the new row of data has been correctly added to the table (see Figure 2).
- This second Excel file contains the query results.
Figure 2: New entry of candy sales in May appears in the refreshed query
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.
