Links on Super Easy may earn us a commission. Our Editorial Guideline.

[SOLVED] Convert Cross Table to Flat List in Excel

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:

If you already have the PivotTable and PivotChart Wizard function on your top menu, you can skip Step 1 – 3 and head straight to Step 4.

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.


Extra Info:

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.

By Ashley Luo

Ashley loves to write about pets, games, snacks and everything she's interested in. She's a big fan of RPG games including Skyrim, Witcher 3, Dragon Age, Mass Effect and so on. Knowing her way around money-saving tips and life hacks, she's always ready to help people save big on purchases. She also works as a tech writer at drivereasy.com.

Contributor(s):

0 0 votes
Article Rating
guest
0 Comments
Inline Feedbacks
View all comments
Failed to load the Search bar. Please refresh the page and try again.
Click here to reload
0
Would love your thoughts, please comment.x
()
x