everydayExcel Newsletter Header

Re-arrange data for Pivot Table Reporting

A lot of Excel users like to present monthly data in columns from Jan to Dec, just like in table shown below, because it is easy to update the table as new data becomes available.

This layout works fine if there is no need to analyse the data further. But most of time, we have to analyse the data in different dimensions and thus would make use of Pivot Table to do that. The problem is, data arranged in this way is not a good source for Pivot Table. It has to be re-arranged, in the way presented below.

For a few rows of data, I would have resorted to simple copy and paste. But if the rows are in hundreds or thousands, then it is not going to be feasible to do manual copy and paste. It is time consuming and very prone to errors. Creating a macro is one possibe solution but it takes time to write one. In such situations, I recommend using formulas. In this case, INDEX formula will be able to get the job done is less than 15 mins, regardless of the number of rows that have to be re-arranged.

To find out more, click on this Columns to Rows link now!

Ask us an Excel question and get your question answered by our team of Excel experts

Click on this link to post your question.

Upcoming Courses

With the newly introduced PIC bonus, you are now paid (not paying) 60% of the course fees for sending your staff to our courses. Check out now in this IRAS PIC Link.

Hidden Secrets in Data Analysis with Excel 2007/2010 - 21 & 22 Jan 2016 (confirmed)

Business Analytics Reporting with PowerPoint 2010 - 3 & 4 Mar 2016 (confirmed)

The Hidden Key to Excel Reporting and Charting - 10 & 11 Mar 2016 (confirmed)

Unleash the Power of Excel Power Pivot for Data Insights - 21 & 22 Mar 2016 (confirmed)

Learning the Magic of Macros 2007 (Excel VBA) - 25 & 26 Feb 2016 (confirmed)

Breakthrough Performance for HR in Excel 2007/2010 - 17 & 18 Mar 2016 (confirmed)

Dynamic Real-Time Forecasting with Excel 2007/2010 - 16 & 17 Jun 2016 (confirmed)

For the details of the courses, please refer to our Excel courses page.

Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.

Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.

Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"


Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.