Pivot Table Tutorial: How to flatten a cross tab table

1
9

Youtube video: Flatten crosstab table using pivot table in Excel

When you have a cross tab table you might want to flatten it to make it easier to analyze. Here is a 3 minute video to show you how to do this using Excel’s pivot table wizard to import multiple consolidation ranges. I also show you how to add the pivot table wizard to your Quick Access Toolbar if you’re using the new flavor of Excel (2007 or 2010).

 

In Excel 2003 you would use the pivot table wizard with Multiple consolidation ranges to do this. In Excel 2007 and 2010 the pivot table wizard is not accessible from the Excel ribbon, so you must either add a button to your quick access toolbar or use a keyboard shortcut to bring up the pivot table wizard (press ALT, press D, press P)

 

Excel Pivot Tables: How to flatten a cross tab table
(or how to normalize your data)

 

As you watch the video tutorial on Youtube you may find it helpful to pause, so you can follow along in Excel.

The process I show in the video is also known as normalizing your data. If you find the tutorial helpful, I’d appreciate if you shared this page on Facebook, LinkedIn or Twitter using the buttons below. Thanks!

Related posts:

  1. Results of the Excel Pivot Table survey
  2. Complete our Excel Pivot Table survey for a chance to win $50 of Amazon gift certificates
  3. How do I stop Excel from resetting my custom number format when I update my Pivot Table?
  4. Excel Tutorial: How to use VBA to delete filtered rows (fast!!) … but not delete hidden rows
, ,
FacebookTwitterGoogle+Share
Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

CLICK TO GET FREE UPDATES
Recommended Posts
Showing 9 comments
  • Adel
    Reply

    I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. Personally, if all site owners and bloggers made good content as you did, the web will be a lot more useful than ever before.

    • excelgenius
      Reply

      Adel,
      Nice to know you like my articles, I’ll keep posting more and do my bit to add great value to the web.
      Victor

  • Waldrop
    Reply

    Awesome web log here! Also your own website loads up extremely fast! What host are you currently using? Can I get your affiliate url to your host? I need my web site loaded up as fast as yours :-]

    • excelgenius
      Reply

      Thanks for visiting my website, I hope you find it useful for learning Excel. To answer your question about web host I’m on Blue Host and do not have an affiliate url for them, but recommend looking at http://www.whoishostingthis.com for hosting reviews. All the best, Victor

  • Kelly Hove
    Reply

    Exceptional post – was wondering if you could write a litte more on this topic? Thanks!

    • Victor
      Reply

      Kelly thanks for your kind comment. I’ll write lots more on Pivot Tables in the coming months / years. If you’d like to keep up to date with the latest articles I suggest subscribing to our e-mail newsletter.
      Cheers, Victor

  • Petros
    Reply

    A VBA function for:
    -processing 100s of pivoted tables in one go (e.g. financial statements)
    -removes rows with zeros automatically
    -Results array can be exported to Access, if data rows are over 1.05 Mil

    http://www.spreadsheet1.com/unpivot-data.html

    • Victor
      Reply

      Thanks Petros. Nice VBA solution to solve this problem!

pingbacks / trackbacks

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.