Archive for March, 2014

VBA Serenity

Published on Mar 31, 2014 in Posts by Jeff, Random, VBA Macros

Greetings and salutations, my fellow VBA-fearin’ congregation. Evangelizin’ Jeff here, spreading the good word about everlastin’ VBA serenity. You may remember me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little spreadsheet. Well today, I’m going to praise the work of a high-yah pow-wah. Our […]

Continue »

Tables, PivotTables, and Macros: music to your ears

Published on Mar 28, 2014 in Posts by Jeff, VBA Macros

Howdy folks. Jeff Weir here again. You may remember me from posts such as What would James Bond have in his Personal Macro Workbook and my now infamous music review. Today – and this truly will be music to some ears – we’re going to concentrate more on the former and less on the latter. […]

Continue »

CP003: Business Intelligence for Masses – Interview with Mike Alexander

Published on Mar 27, 2014 in Podcast Sessions
CP003: Business Intelligence for Masses – Interview with Mike Alexander

In the third session of podcast, we are going to get BI curious. 😉

Not that kind you silly, We are talking about Business Intelligence, Big Data, Power Pivot & other Power BI family members. In this session, I am happy to feature Mike Alexander – Microsoft MVP, Author, Blogger & a good friend. Mike talks about how Excel is shaping the BI (Business Intelligence) revolution with advent of Power BI functionality.

You will learn,

  • Introduction, what Mike is up to these days?
  • What is BI, what does it mean to an average Excel analyst?
  • What BI capabilities Excel has – brief intro to each of them
    • Power Pivot & what it does
    • Power Query & why it is important
    • Power View & how it works (and where it sucks)
    • Power Maps
  • How to learn about these new technologies
    • Recommended Books
    • Websites
    • Courses
    • Live classes
  • Special gift for our listeners
Continue »

Calculating Maximum Change [solutions & discussion]

Published on Mar 26, 2014 in Excel Howtos, Formula Forensics
Calculating Maximum Change [solutions & discussion]

Last Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this problem.

And the best part?

The best part is the variety of solutions & thinking displayed by our community. So if you are one of those 170, puff your chest & pat yourself on the back. Go ahead, I will wait.

Today, lets take a look at some of these awesome formulas and understand how they work. Read on and watch the video you below to gain few awesomeness pounds.

Continue »

Why you should close down Excel completely

Published on Mar 24, 2014 in Humor, Posts by Jeff

Edit: WARNING…This is NOT a post on Excel No. Sorry ’bout that. Well, kinda sorry. To quote John Lydgate: “You can please some of the people all of the time, you can please all of the people some of the time, but you can’t please all of the people all of the time”. If you […]

Continue »

Calculate maximum change [homework]

Published on Mar 21, 2014 in Formula Challenges
Calculate maximum change [homework]

Today, lets see how good your formula skills are.

Calculate maximum change

Can you calculate what is the maximum change in product sales between 2 months for above data?

Continue »

Free Invoice Template using Excel – Download

Published on Mar 19, 2014 in excel apps, Learn Excel
Free Invoice Template using Excel – Download

Anyone running a small business knows the oozing bits of joy when you hear a customer saying, “Can you send me an invoice?”

While creating an invoice is an easy task, if you want something that is professional looking, easy to manage and works well, then you are stuck.

That is where Excel really shines. By using an invoice template, you can quickly create and send invoices.

Today I want to share one such template with you all. Why? Because we are awesome like that.

Continue »

CP002: VTALKUP – 5 Excel lookup functions demystified + 4 Resources for you

Published on Mar 13, 2014 in Podcast Sessions
CP002: VTALKUP – 5 Excel lookup functions demystified + 4 Resources for you

In the second session of Podcast, We will be learning how to use 5 Excel lookup functions.

What is in this session?
In this session, we tackle one of the most important areas of Excel. The lookup functions.

You will learn,

  • Why lookup functions are necessary
  • 5 Important lookup functions in Excel – VLOOKUP, HLOOKUP, LOOKUP, MATCH & INDEX
  • When & how to use each of these 5 functions?
  • Extreme scenarios:
    • What happens when the value you are looking up is not there?
    • What if too many items match the lookup value?
    • What if you have too many conditions in the lookup criteria?
  • Using IFERROR function
  • Re-cap of the new powers you acquired
  • 4 Resources for you to learn lookup functions better
Continue »

You can move formula help box with your mouse!!! [quick tip]

Published on Mar 12, 2014 in Learn Excel
You can move formula help box with your mouse!!! [quick tip]

One of the most useful features of Excel is formula help box. You know the little yellow box that appears as soon as you start typing a formula in a cell. I use this all the time to understand what the syntax of a particular function is, what parameters to pass etc.

Although I love it, sometimes it does get in the way when writing formulas. Because the help box sits on top of my data, often I find it hard to know which cell to link to.


Simple. Use your mouse to move away the help box wherever you want.

Continue »

6 Best charts to show % progress against goal

Published on Mar 10, 2014 in Charts and Graphs, Learn Excel
6 Best charts to show % progress against goal

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.

So today, lets talk about best charts to show % progress against a goal.

Continue »

CP 001: Podcast First Episode – Introduction, What to expect, Show formalities & Special gift

Published on Mar 6, 2014 in Podcast Sessions
CP 001: Podcast First Episode – Introduction, What to expect, Show formalities & Special gift Podcast is here….

Friends, fans & supporters of,

I am so happy to add another dimension to our website. podcast is finally here. You can listen to the inaugural episode by using the audio player above.

Continue »

Excel links – What is your next Excel book edition?

Published on Mar 5, 2014 in excel links
Excel links – What is your next Excel book edition?

Outside of a dog, a book is a man’s best friend. Inside of a dog it’s too dark to read. -Groucho Marx

We don’t have a dog (yet), but we have a lot of books. Anytime I am in a bookshop, I feel like a mosquito in fat-camp. So you can imagine me smacking my lips when I saw your suggestions for favorite Excel books recently. There were more than 50 different books recommended by our readers. Of course I cannot buy all the 50 in one go. That would make Jo (my wife) angry. So I bought one that most of you recommended.

Ctrl + Shift + Enter by Mike Girvin

It is a book about array formulas, advanced formulas and data analysis techniques. I am eagerly waiting for the delivery to devour it. Once I am done with it, I will post a review here.

What about you? What Excel book are you reading now? Please share in comments.

Continue » Podcast: Launching on March 6th

Published on Mar 4, 2014 in Learn Excel Podcast: Launching on March 6th

Hi folks,

I am super-excited to tell you that Podcast will be launching on 6th of March (Thursday). First episode is already recorded and I am getting ready for the big launch.

Name of the podcast

Last week I asked you what should we call our podcast. And I got more than 250 suggestions (150+ thru comments another 100 thru email). While the suggestions ranged from funny (chanpoo, addict.xls) to word plays (Excelent cast, Lord of the cells, Speaking Cells) to outright brilliant (XL.AM remember AM radio?). Some of the suggestions were crazy too: Word on the sheet, YouChandooIt, Didasko.xlsx – greek word “to impart knowledge”.

Thanks everyone for the ideas and suggestions.

A majority of you suggested that we call it Podcast. This keeps the name generic, sticks to our brand and sets us apart. So that is what we will call it.

The full name of the show is Podcast – Become Awesome in Data Analysis, Charting, Dashboards & VBA using Excel

And our first episode will be launched on Thursday, 6th of March.

Continue »

Handle Volatile Functions like they are dynamite

Published on Mar 3, 2014 in Learn Excel, Posts by Jeff
Handle Volatile Functions like they are dynamite

If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.

In fact, you *might* want to consider getting out of the habit of using these functions at all if there are alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that!

So what the heck does volatile actually mean? And why should you care? Let’s find out, shall we?

Continue »