All articles in 'Learn Excel' Category

Modeling tiles in a room using Excel Conditional Formatting

Published on Apr 22, 2014 in Charts and Graphs, Learn Excel
Modeling tiles in a room using Excel Conditional Formatting

Last week we learned how to answer questions like, “How many tiles in a room?” using Excel. We learned about CONVERT function and fraction number format settings in Excel.

But why stop at calculation? We can even model a room full of tiles, thanks to Excel’s grid nature.

So today, we will learn how to create a room layout as shown above, using Excel.

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 »

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.

Solution?!?

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 »

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 »

Chandoo.org Podcast: Launching on March 6th

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

Hi folks,

I am super-excited to tell you that Chandoo.org 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 Chandoo.org 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 Chandoo.org 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 »

Robust Dynamic (Cascading) Dropdowns Without VBA

Published on Feb 25, 2014 in Learn Excel, Posts by Jeff

Recently I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like this: …and we saw that users could subsequently change upstream dropdowns in a way that would make downstream choices invalid, like this: In my previous post I used some VBA to clear out any ‘downstream’ choices if […]

Continue »

What should we call our Podcast?

Published on Feb 24, 2014 in Learn Excel
What should we call our Podcast?

Friends and supporters of Chandoo.org,

I am very happy to tell you about another great way to learn Excel & stay awesome

Chandoo.org podcast

That is right. As part our mission to make you awesome in Excel, this year I will be adding a podcast.

Continue »

Quick and easy Gantt chart using Excel [templates]

Published on Feb 18, 2014 in Charts and Graphs, Learn Excel
Quick and easy Gantt chart using Excel [templates]

Gantt charts are a very popular way to visually depict project plans. Today, let us learn how to use Excel to make quick & easy Project Plan Gantt Chart.

This is what we will be creating.

Continue »

What is your favorite Excel book? [open thread]

Published on Feb 7, 2014 in Learn Excel
What is your favorite Excel book? [open thread]

Today lets talk about Excel books. The question is simple.

What is your favorite Excel book? Please share your answers using comments.

My favorite books are..,

Continue »

Right-click from the keyboard, not the mouse.

Published on Jan 21, 2014 in Learn Excel, Posts by Jeff
Right-click from the keyboard, not the mouse.

Well here’s something I never knew until today…I was watching one of Mike ExcelIsFun Girvin’s great YouTube videos from his great Slaying Excel Dragons series – the one on Excel 2010 Keyboard Shortcuts – when he showed how to do the equivalent of a mouse right click using a key on the keyboard that I’d […]

Continue »

Big trouble in little spreadsheet

Published on Jan 17, 2014 in Excel Howtos, hacks, Learn Excel, Posts by Jeff

Howdy folks. Jeff here. I recently gave a presentation on Excel efficiency to a bunch of analysts, in which – among other things – I’d pointed out that if you ever find yourself having to switch calculation to Manual, there’s probably something wrong with your spreadsheet. Here’s the slide:   This prompted one of the […]

Continue »

Bar chart with lower & upper bounds [tutorial]

Published on Jan 8, 2014 in Charts and Graphs, Learn Excel
Bar chart with lower & upper bounds [tutorial]

Bar & Column charts are very useful for comparison. Here is a little trick that can enhance them even more.

Lets say you are looking at sales of various products in a column chart. And you want to know how sales of a given product compare with a lower bound (last year sales) and an upper bound (competition benchmark). By adding these boundary markers, your chart instantly becomes even more meaningful.

Lets learn how to create a column chart with lower & upper bounds in this tutorial.

Continue »

Top 10 things I learned using Excel for a decade

Published on Jan 6, 2014 in Charts and Graphs, Learn Excel
Top 10 things I learned using Excel for a decade

Sometime during the 2nd half of 2013, I finished 10 years of Excel usage. In the last 10 years, I completed my studies, got my first job, married, had kids, visited 15 different countries, quit my job to start a business, bought first car, first house, made dozens of new friends, read 100s of books, wrote a book and learned 1000s of new things. And all along, Excel stayed a true companion. Right from MBA entrance exam preparation in 2003 to making my summer internship project reports in 2005 to planning my wedding expenses in 2007 to getting a promotion in 2009 to planning my kids feeding schedule in 2010 to running a successful business in 2014, Excel helped me in every step.

So today, I want to tell you the top 10 things I learned using Excel in last decade. Grab a hot cup of coffee, buckle your belts and get ready for time travel.

Continue »