everydayExcel Newsletter Header

Advanced Pivot Table and MSQuery

Last week, we conducted a one-day customised in-house training for a group of advanced Excel users who are already conversant in Pivot Table and VLOOKUP. To qualify them as advanced Excel users, we sent them 2 Excel tests that they were required to complete before they could sign up for the training. Through the profiling at the start of the course (which we do for all our courses), we found that the users' main problem was in spending too much time on recreating and refreshing the monthly reports that they have to prepare for their bosses. And that happened to be the key reason of the users for attending that customized course organized by their HR department.

We concluded immediately after the profiling that they should learn MSQuery, which all of them had never heard before. MSQuery, a function combined with PIVOT TABLE, can help them update their PIVOT TABLES automatically without the need to copy and paste the raw data from one worksheet to another and re-create PIVOT TABLES that are used regularly, for instance on a weekly or monthly basis. Furthermore, it allows them to separate the raw data from their PIVOT TABLES, making the reporting file smaller.

Another reason why MSQUERY is recommended has to do with the users' need to combine sales forecasts from multiple sources together. MSQUERY enables easy consolidation. Users do not have to do any copy and paste from multiples files into one worksheet to prepare their reports any more.

Another helpful topic we covered was grouping dates within PIVOT TABLE by month, quarters and years so that they could present their reports in one click. And we must mention the PIVOT TABLE sorting function by performance - these functions are real time savers Not only were the users able to save time, I am confident that their bosses also would be able to receive those weekly and monthly reports faster.

The training has led us to launch this One-Day MSQUERY and PIVOT TABLE Course as a public run. The date for this training is planned on 24 Aug 2011. If you wish to take this course, please take the 2 Excel tests and see if you qualify for this course. You have to send us the test results and also answer the question "What do you hope to learn from this course"? This is a pre-requisite for the course. As this is an advanced Excel course, no step-by-step notes (which are provided for all the other Excel courses) would be provided. This One-Day MSQUERY and PIVOT TABLE Course is priced at $400 per pax.


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.