Another Run Of Our Hidden Secrets In Data Analysis with Excel 2007 Course

We have received some request to have another run of the above-mentioned course because they missed the date or they were not able to make it. So at their request, we have decided to organise another run of this course in Excel 2007 version. It is scheduled for 23 & 24 Sep 2010 at our newly renovated training room.

Learn How To Be INDIRECT - 13 Aug 2010

One of our participants from the finance department of a well-known American retailer owns a workbook containing the budget numbers of the divisions in the company. Just like many others, the budget numbers are organised with each worksheet storing the budget numbers of a division. She has to spent 1.5 hour and use a macro to upload the budget numbers into the Hyperion Financial Management (HFM) System, which then takes another 1.5 hour to process the budget numbers before the consolidated numbers can be viewed in the system.

During our course, she asked if there was a faster way to do this in MS Excel, and a few formulas were recommended to her. One of them is the INDIRECT formula which is used to convert a text formatted range into a valid Excel range. INDIRECT looks like a pretty useless formula, doesn't it?

If you have attended one of our courses, you will know that the value of the formula is not determined by this definition. Using the INDIRECT formula, we can present the worksheet name and the range separately in two different cells and have them combined together using the INDIRECT formula to become a valid range. With this approach, she is able to consolidate all the divisions' budget numbers in the different worksheets into one in a breeze without using Copy and Paste, and then use the pivot table to present the consolidated numbers. This set up allows her to consolidate the number in less than 0.5 hour, less than 1/6 of the time she currently spend using HFM!

Wish to know how this is done? Sign up for our Dynamic Real-TIme Forecasting with Excel course and be amazed by our solution. The pre-requisite for this course is a strong understanding in VLOOKUP formula and Pivot Table. If you are not ready, attend our foundational Excel course which will show you how we apply the functions and formulas to real-life business problems.

Excel Competition

We realise that some people might have thought that the contribution for our Excel competition we are planning at the end of the year is about creating complex formulas. On the contrary, the competition is meant to showcase how simple functions can be used to significantly cut short the amount of time you spent on copying and pasting. Here is an example to explain:

Attached is an Excel file containing the data for product categories and the SKUs in each product category. Develop a solution to populate the blank cells below with the respective product category.