Excel is a very useful tool in data analysis, and it’s always a good thing to master as many skills as you can to save time and effort at work. One common question is: how can I convert a cross table (a.k.a. two-dimensional table) to a flat list (a.k.a. one-dimensional list) in Excel? Although flat list is the ideal form for analyzing, the data we get in daily life usually appear as cross tables so it’s necessary to know how to do the conversion.
Cross table & flat list
If you’re not familiar with cross tables and flat lists, here’s what they look like:
Cross Table/Two-dimensional Table: a common form to record data
Flat List/One-dimensional List: the ideal form for data analysis
Can you find out the difference between these two forms? In the lower list, you can see that the metric for year (shown as the first row in the upper cross table) has been converted to a single column, which means it’s no longer a metric but a list of data.
How to convert a cross table to a flat list
To convert a cross table to a flat list, you can perform the following procedure:
1) In the top menu, select File.
Then click Options in the left pane.
2) In the left pane, select Customize Ribbon; on the right panel, under Choose commands from, select Commands Not in the Ribbon, and find PivotTable and PivotChart Wizard in the list below.
3) Expand the Data category and click New Group. When the New Group (Custom) item is created, click to highlight PivotTable and PivotChart Wizard in the left list and click Add >>. Now the PivotTable and PivotChart Wizard function should’ve been added to Data > New Group (Custom).
When complete, don’t forget to click OK to save the changes.
4) Go back to the Excel window, and click PivotTable and PivotChart Wizard on the Data tab.
5) In the pop-up window, select Multiple consolidation ranges and PivotTable. Then, click Next >.
6) Click Next >.
7) Under Range:, select all the data you need and click Add. If you succeed, the range you selected should appear in the All ranges section. Once complete, click Next >.
8) Select New worksheet and then click Finish.
9) In the new PivotTable, deselect the Row and Column options.
10) Double-click the number next to Grand Total.
11) There you go! A one-dimensional list should be created in a new sheet.
So this is the end of this short tutorial. Hopefully it helped you solve your problem! If you have any further questions or ideas, please feel free to leave a comment below.
Want to get a leg up in your career?
Try Udacity – an e-learning platform for tech-focused learners!
It’s never been too late for you to learn new tech skills. No matter if it’s about programming, data analysis, or other fields. All you need is a little passion, a little perseverance, and a little professional guidance. Udacity will do the last part for you.
For any penny-wise learners, Driver Easy Coupons provides the latest deals, coupons, and promo codes that actually work. Before heading to the checkout, why not have a look at the coupon page for Udacity and save yourself a few bucks?
What If the Promo Code Doesn’t Work?
Ensure you’ve entered the promo code and your purchase meets all restrictions, such as minimum spend, regional-use, new customers only, etc. If the code still doesn’t work, it could be an expired or inaccurate coupon. While our goal is to provide only valid coupons, unfortunately, we can’t guarantee that once in a while a non-working or expired coupon will appear on our website.
Be sure to confirm any coupon has been applied before you complete your purchase.