Archive for May, 2011

How Would You Visualize Product Sales Data? [Excel Challenges #2]

Published on May 30, 2011 in Charts and Graphs, Excel Challenges
How Would You Visualize Product Sales Data? [Excel Challenges #2]

We have a new Excel Challenge folks!

I know our friends in US are away celebrating Memorial Day weekend. But that should not leave rest of us from fun. So, we have a new Excel Challenge. This time, you need to make a chart, to visualize product sales data.

And what more, one lucky person could get an Amazon Kindle Reading Device (Wi-fi version) by making the chart.

Continue »

Advanced Sumproduct Queries

Published on May 26, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Advanced Sumproduct Queries

How often have you had a simple table where you want to lookup a value or add up some values meeting a criteria?

This post looks at a simple way to do a 2D Lookup or 2D Sum of a values from within a 2D range with multiple criteria, without complex Sum, Offset, Index, Match, Sumifs or other functions, simply using Sumproduct

Continue »

Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]

Published on May 24, 2011 in Financial Modeling, Learn Excel
Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]

You take an apartment on rent at $1000 per month and the owner puts an escalation clause saying 10% increment each 3 years. How do you model this in excel? In this tutorial we understand how escalations at certain frequency can be implemented using the mod function in excel. What is the mod() function Simply […]

Continue »

Use Copy & Paste to Preserve References to Tables [Quick Tip]

Published on May 23, 2011 in Learn Excel
Use Copy & Paste to Preserve References to Tables [Quick Tip]

With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural references. That means, you can write easy to understand formulas like this,

But, there is a problem. When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

Well, there is a simple workaround for this problem

Continue »

VBA Classes Registration Closing in a Few Hours – Join Now!

Published on May 20, 2011 in Charts and Graphs, VBA Macros
VBA Classes Registration Closing in a Few Hours – Join Now!

I have a quick announcement for you.

Registrations for our VBA Class will be closing in a few hours

As you may know, we have opened registrations for our first batch of Online VBA classes on May 9th. This program is aimed at beginners & intermediate level VBA users. The aim of this course is to make you awesome in VBA. We will be closing registrations for this program in few more hours (exactly at 11:59 PM, Pacific time, on 20th May 2011)

Click here to join our VBA Class now.

Continue »

Data Tables and Monte Carlo Simulations (Update)

Published on May 19, 2011 in Huis, Posts by Hui, simulation
Data Tables and Monte Carlo Simulations (Update)

Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide, has had some housekeeping carried out.

Continue »

Count-down Timer App in VBA to Remind you about the VBAClasses Closing Time!!!

Published on May 18, 2011 in products, VBA Macros
Count-down Timer App in VBA to Remind you about the VBAClasses Closing Time!!!

Here is a cool count-down timer application made in VBA to remind you about our VBAClasses registration closing time!
Read the rest of this post to learn how this particular app is built.

If you want to learn VBA, then click here to join our class.

Continue »

Lost Excel Functions

Published on May 16, 2011 in Excel Howtos, Huis, Posts by Hui
Lost Excel Functions

– Cue, alluring music.
Just like Jack and the crew of Oceanic Airlines flight 815, Excel has a number of LOST functions.
This post will look at some of Excel’s Undocumented, Unloved and Rarely used Functions

Continue »

Introduction to Programming – Demo Lesson from our VBA Class

Published on May 13, 2011 in VBA Macros

We have a challenge in our VBA class. Many of the students who join our VBA program have no formal programming background. They may have written a few simple programs long time ago, but most of them lack basic understanding of programming. Teaching VBA can be difficult if we do not address this problem.

So, we have added a lesson on “Introduction to Programming”. In this lesson, our aim is to introduce programming to non-programers.

Since many of you are considering to join our VBA classes, it is appropriate that we give this introduction to programming lesson as a demo lesson. Please watch it below.

Continue »

Using Solver to Assign Items to Buckets

Published on May 11, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Using Solver to Assign Items to Buckets

Ok, You’ve just finished the mother of all Spreadsheets. Not only does it solve your companies budget, it tracks production, reports variances, makes a mean cup of coffee and some say, “it May Even Solve World Peace ?”.
But the boss walks in and asks “I have a list of items which I need to distribute evenly into colored buckets”
I had never thought of that option, Maybe I can constrain my model and let it work it out for me ?
Solver I need your help !

Continue »

VBA Classes are Open now. Please join today!

Published on May 9, 2011 in products, VBA Macros
VBA Classes are Open now. Please join today!

Dear Readers & Friends,

I am very happy to announce that our first batch of VBA Classes is open for your consideration. Please read this short post to understand the benefits of this program and how you can join. Click here to join our class, if you are ready.

What is this VBA Class?

VBA Class is a structured and comprehensive online training program for learning Microsoft Excel VBA (Macros). It is full of real world examples & useful theory.

The aim of VBA Classes is to make a beginner an expert in VBA.

What are the benefits of this class?

Oh so many! Learning VBA one ups your Excel mojo. You will suddenly start saying “Yes” several work opportunities & challenges. Your boss might fall in love with you. You realize the potential to automate large chunks of your work and save time & money.

Continue »

5 things you should know about VBA Classes + a Demo Lesson

Published on May 6, 2011 in products, VBA Macros

Today, I want to quickly share a few things about the upcoming VBA Classes.

I have been running online training programs since Jan, 2010. I have trained more than 900 students till date. Still, whenever I am launching a new program, I could feel that familiar sense of eagerness, tension and tremendous enthusiasm building up. I feel eager because I want to meet you, teach you and learn from you. I feel tensed because I want to do it right. I feel enthusiastic because these training programs give me a lot of new ideas and open-up new possibilities.

Continue »

Dummy Data – How to use the Random Functions

Published on May 4, 2011 in excel apps, Excel Howtos, Huis, Learn Excel, Posts by Hui
Dummy Data – How to use the Random Functions

Don’t know which way to go ?
Can’t make up your mind between alternatives?

Using collected or known data is the best when developing Excel models, but from time to time this may not be available when you are developing your model. This post will look at some options for setting up Dummy Data using Excels Random number functions.

Continue »

80% Discount on PUP & PowerPivot Contest – Hurry up!

Published on May 3, 2011 in excel links
80% Discount on PUP & PowerPivot Contest – Hurry up!

Since I am busy with background work on VBA Classes launching next week, today I want to give you two quick updates.

1. John Walkenbach is offering 80% discount on his Power Utility Pack Excel add-in, only today (3rd May) between 11AM-1PM EST.
2. You can win an XBOX 360 + Kinect by participating in PowerPivot Nerdtastic Quiz on their facebook page.

Read more to get the details about these two.

Continue »

Online VBA Classes by Chandoo.org – Details & Dates

Published on May 2, 2011 in products, VBA Macros

Few days ago, I have asked you, Do you want to learn Excel VBA online? and many of you said YES. So we are starting the program, on next Monday – 9th of May.

VBA Classes is a 12 week online training program that will explain various Excel VBA concepts to you in an easy to understand format. Just like Excel School, we will keep this fun, exciting, interactive and useful. We will learn from each other as much as we learn from this course. To help you understand how this course works, we made a small video. You can watch it and get other details about the program in this post.

Continue »