All articles in 'Learn Excel' Category

What is the average speed of this road trip? [homework]

Published on Aug 8, 2014 in Excel Challenges, Learn Excel

Its homework time again. This time, lets tackle an interesting & everyday problem. Lets introduce our protagonist of the story – Jack. Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate. And oh yes, Jack is […]

Continue »

Ask me your Excel questions & You could win an eBook

Published on Jul 18, 2014 in Learn Excel
Ask me your Excel questions & You could win an eBook

Time for another round of open-mic weekend.

As you know, Chandoo.org has been running a podcast show for last few months. We have completed 14 episodes as of today. For the 15th episode, lets have an “Ask Chandoo” as the theme.

What you need to do:

Simple. Head over to ask.chandoo.org and submit your questions. You can leave me a voice-mail or post your questions

What happens next?

I will listen (or read) your questions and choose a handful for the session 15 of our podcast.

What you get:

If your question is picked up for the podcast, you will receive the 25 Excel tips e-book.

Continue »

CHOOSE() me, an introduction to Excel CHOOSE function

Published on Jul 16, 2014 in Learn Excel
CHOOSE() me, an introduction to Excel CHOOSE function

Today lets learn about Excel CHOOSE() function.
CHOOSE eh? What does it do?
To understand CHOOSE() and appreciate its uses, lets invent an imaginary boos-subordinate pair.

Jasmine is the boss. She is, well, lets call her peculiar. She likes olives, Tuesdays & color Red. She hates potatoes.

Martin is the faithful butler of Jasmine. He is obedient, quirky and tall. He likes lotuses, Fridays & color blue. He hates potassium.

Enter Jasmine’s scarf problem:

Jasmine likes to wear a different colored scarf every weekday. She likes to wear Red colored scarf on Mondays & Tuesdays. She likes to put on the blue polka dot scarf on Wednesdays. On Thursdays, she wears her olive colored scarf. On Fridays & Saturdays, she prefers the lovely orange blue scarf. Sundays are no scarf days.

No wonder she is peculiar.

Continue »

Here is a simple solution to your forgetful, leaky brian – “Just put it in a spreadsheet”

Published on Jul 14, 2014 in Learn Excel
Here is a simple solution to your forgetful, leaky brian – “Just put it in a spreadsheet”

This is a guest post written by Joel Zaslofsky, author of Experience Curating.

How to Make “Put It in a Spreadsheet” Who You Are (and Not Just What You Do)
It seemed like a crazy question:

Could I use my passion for Excel as motivation to transform my leaky brain from a weakness into a strength?

Sure, I already had a fifteen year love affair going with Excel.

Every other email I sent to my co-workers had an attached Excel spreadsheet. And when I wasn’t using Excel for work purposes, I was using it for grocery list templates or budget tracking.

But I had to discover the answer to my crazy question.

So I told my wife Melinda in January 2012, “Honey, this is the year I put it in a spreadsheet!”

Continue »

Top 10 things we struggle to do in Excel & awesome remedies for them

Top 10 things we struggle to do in Excel & awesome remedies for them

Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.

Continue »

Matching transactions using formulas [Accounting]

Published on Jun 6, 2014 in Excel Howtos, Learn Excel
Matching transactions using formulas [Accounting]

Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.

Now, every month you get a transaction report.

And you want to know which numbers are matching up.

i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,

  • $427.77 on 1st April
  • $152.88 on 2nd April
  • $291.69 on 2nd April

Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).

Continue »

CP010: Averages are Mean – 8 Techniques for making your analysis above average

Published on May 30, 2014 in Chandoo.org Podcast Sessions, Learn Excel
CP010: Averages are Mean – 8 Techniques for making your analysis above average

This is a continuation of Session 9 – Averages are mean

In the earlier episode, we talked about AVERAGE and why it should be avoided. In this session, learn about 8 power analysis techniques that will lift your work above averages.

In this podcast, you will learn,

  • Re-cap – Why avoid averages
  • 8 Techniques for better analysis
  • #1: Start with AVERAGE
  • #2: Moving Averages
  • #3: Weighted Averages
  • #4: Visualize the data
  • Conclusions
Continue »

I struggle doing __________ with Excel. Fill in the blanks…,

Published on May 27, 2014 in Learn Excel

As I am doing a road-trip across parts of southern India (we are in the beautiful temple town of Madurai as you read this post), here is something to keep you busy.

Fill in the blanks & post in comments.

I struggle doing _________________ with Excel.

Continue »

Excel Links – Delay in State migration visualization results edition

Published on May 20, 2014 in excel links

Hello friends,

First a quick update about state migration visualization contest.

We got more than 50 wonderful entries for this contest. I have compiled 50% of the entries. Due to personal reasons (its annual vacation time at our household), I had to delay the rest and resume it after first week of June. So the results will be announced in 2nd week of June. Thanks for understanding and your patience. Watch this space for mind-boggling dashboards in 3 more weeks.

Click “Read more” to get a sneak-peek at 3 random entries & 4 useful Excel links.

Continue »

What is your passion? [weekend poll]

Published on May 2, 2014 in Learn Excel, personal
What is your passion? [weekend poll]

This weekend, lets get passionate ;)

Few days ago, Cheryl, one of our forum members asked this question:

How do you know Excel is your passion? Or is it?

I am searching for my passion, you know that thing that makes my heart sing. I mean I am listening for the pitter-patter in my ticker. So how do I know if Excel is it? Or anything for that matter. I am looking for that thing that will make me turn my tv off. (TV is my crack, I am truly addicted). I thought it was database development and honestly I am not altogether sure that it isn’t. Excel may be a substitute. A more attainable passion.
Give me some insight peeps. Some thoughts, musings, ideas.

As usual, many of our forum members chipped in with words of wisdom. Hopefully Cheryl saw their replies, if she ever managed to turn off that tv.

That gave me an idea for this week’s poll.

What are you passionate about?

Continue »

CP007: aweSUM() – Overview of SUM functions in Excel

Published on May 1, 2014 in Chandoo.org Podcast Sessions, Learn Excel
CP007: aweSUM() – Overview of SUM functions in Excel

In the 7th session of Chandoo.org podcast, lets make you aweSUM().

Imagine for a second that Excel cannot add up numbers. And no it cant subtract them either. What would that look like?

A glorified Notepad. That’s right. Excel’s ability to add up numbers, along with features like formulas, charts, pivot tables & BHATTEXT() are what make it such a lovely software. May be not the BHATTEXT(), but we all agree that Excel is so versatile and useful because it can add up numbers (and perform other calculations) with ease.

But how well do you know the SUM formulas of Excel?

In this podcast, you will learn,

  • Special personal fruit announcement :P
  • + operator
  • Status bar & total rows in tables
  • Auto Sum feature
  • SUM() function
  • SUMIFS function
  • Special cases of SUMIFS function
  • SUBTOTAL & AGGREGATE functions
  • Other summing functions – SUMPRODUCT etc.
Continue »

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 »