All articles in 'Learn Excel' Category

Excel to the Next Level by Mastering Multiple Occurrences

Published on Dec 9, 2014 in Learn Excel
Excel to the Next Level by Mastering Multiple Occurrences

This is a guest post by Sohail Anwar.

August 29, 1994. A day that changed my life forever. Football World Cup? Russia and China de-targeting nuclear weapons against each other? Anniversary of the Woodstock festival?

No, much bigger: Two Undertakers show up at WWE Summerslam for an epic battle. Needless to say: MIND() = BLOWN().

And thus begun one boy’s journey into understanding the phenomenon of Multiple Occurrences.

My journey continued, when just a few years later my grandfather handed me down a precious family heirloom: A few columns of meaningless data that I could take away and analyze in Excel. You may laugh but in the 90’s, every boy only wanted two things 1) Lists of pointless data and …

Continue »

Excel Links – 2014 Holiday Sale Edition

Published on Dec 8, 2014 in excel links
Excel Links – 2014 Holiday Sale Edition

Hello folks, how are you?

I have an announcement for you.

It is almost holiday time. Every year, Chandoo.org celebrates holidays with a 3 day sale on our most popular product – Excel School training program. The sale for this year will be,

10th December to 12th December (Wednesday to Friday)

What is on sale?

You will be able to save $30, $50 or $100 on our most popular courses

  • Excel School + Dashboards program ($30 discount)
  • Excel School + VBA + Dashboards ($50 discount)
  • Excel School + VBA + Power Pivot + Dashboards ($100 discount)

More details & links for Sale page will be available on 10th of December. Stay tuned.

Excel Links

It has been a while since we had a round of Excel links. Here are some cool tips & links for you.

Continue »

Export iPhone contacts to Excel using this free template

Published on Dec 2, 2014 in Excel Howtos, Learn Excel, Templates
Export iPhone contacts to Excel using this free template

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file.

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template that can convert VCF data to Excel worksheet.

Continue »

Open & Save files faster in Excel 2013 [quick tip]

Published on Nov 18, 2014 in Learn Excel
Open & Save files faster in Excel 2013 [quick tip]

Excel 2013, the newest version of Excel as of writing this has many great features like data modeling, improved pivot tables, power view, flash fill etc. But one thing that I find very annoying is the Open & Save experience. Any time we open a file or save a workbook (which happens alot everyday), we must navigate the painful backstage screen to select our favorite folders. You see, Excel 2013 supports cloud saving. So that means, by default it will show your Office 365 or OneDrive or SkyDrive or whatever fancy new name Microsoft has for these things. But I am not so much in to clouding. Heck, I see a passing cloud in the sky and I almost run inside my house. So what to do?

Simple. We can turn off these features.

Continue »

Looking up when data won’t play nice – few more alternatives

Published on Nov 12, 2014 in Learn Excel, VBA Macros
Looking up when data won’t play nice – few more alternatives

Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.

Suitable structure spawns simple solutions

Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.

Structure is everything

So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.

Continue »

Ready to use Excel Dashboard Templates – Official Trailer

Published on Nov 5, 2014 in Charts and Graphs, Learn Excel, Templates

Hello folks,

Its almost ready. Our ready to use Excel dashboard templates are coming to a spreadsheet near you on November 13th.

Here is a teaser preview of the dashboards. Our own official trailer, rated A for awesome.

Watch it below:

http://youtu.be/2-TvjpEKf-o

(see it on our YouTube Channel, alternative link)

Excel Dashboard Templates – What are they?

As the name suggests, these a set of workbooks where you can key in your data, set up how you want the looks & bam, a beautiful, insightful, fun, easy to use dashboard pops right out. All in few minutes.

Here is how these templates can help you:

  • Design awesome dashboards in minutes, not months: Don’t waste hours trying to figure out formulas, chart settings and layouts. Instead focus on what your users want, how your business works and use those insights to tell the ‘Excel Dashboard Template’ what you want. Save time and look awesome.
  • Accommodate change elegantly: People change. Businesses change. What your boss wanted last month is no longer what she wants today. So when someone asks you, “hmm, can you show me YTD data instead of last 13 months”, or “Can I see top 10 KPIs instead of all”, you just smile and tap a few clicks and bingo, the new dashboard will be ready. These templates are designed to handle change (data, requirements, design preferences and insights).
  • Customize everything: Unlike a template set in stone, these are designed to let you customize almost every little aspect of the output, calculations and behavior.
  • Beautiful & Informative: These templates are designed with a delicate balance of beauty & insight. Everything is seamlessly designed, perfectly aligned and precisely calculated so that you & your boss can focus on the insights & decisions. Time tested and well honored visualization principles & guidelines are observed thru-out the reports.
  • Built for starters, built for pros: Whether you are just starting with Excel or a seasoned pro, you can find the templates right up your alley. For advanced users, we have many customizations to whet your appetite. Want to build a custom calculation or include another type of sorting, you can do it by tinkering with ‘calculations’ sheet.
Continue »

CP024: Customize Excel to boost your productivity

Published on Oct 30, 2014 in Chandoo.org Podcast Sessions, Learn Excel
CP024: Customize Excel to boost your productivity

In the 24th session of Chandoo.org podcast, let’s customize Excel so we become productive.

What is in this session?

Each of us use Excel in our own way. And yet, we all end up using the same Excel. That’s not fair. Shouldn’t the Excel of an accountant be different from Excel of a teacher?

In this podcast, lets understand some of the powerful & useful ways to customize Excel so that we can do our work better. Tune in only if you are serious about productivity.

You can get Excel Customization Handbook free. Listen to the podcast for instructions.

In this podcast, you will learn,

  • Announcements
  • Why customize Excel?
  • Customization options:
    • Excel Options
    • Quick Access Toolbar
    • Excel Ribbon
    • File menu / back stage view
    • Themes, styles & templates
    • Personal Macros
  • Closing thoughts & Bonus give away instructions
Continue »

The ultimate VLOOKUP trick – Multi-condition Lookup

Published on Oct 28, 2014 in Learn Excel
The ultimate VLOOKUP trick – Multi-condition Lookup

This is a guest post by Sohail Anwar.

Let’s not bore you with an intro. You are about to learn a VLOOKUP trick that Lucifer himself would not want you to know. It’s so absurdly powerful that it was developed in a lab and had to be tested on Rocky’s arch nemesis Ivan Drago.

Presenting the Multiple criteria VLOOKUP!

…boring…pass, we’ve seen it.

Oh, have you? Not like this you haven’t. This will change the way you work with Excel.

Let me start with an easy example. Here’s some data and we would love to know what Bb and Dd is.

Continue »

Back after a while & 3 announcements

Published on Oct 27, 2014 in Learn Excel
Back after a while & 3 announcements

Hello awesome folks. It has been a while since I posted on Chandoo.org. And there is a reason for that. As you may know, recently (on October 12th) a category 3 cyclone (hurricane) passed thru our city devastating trees, power lines, cellular towers, old houses & roads on its way. This means our family was left without power, water, telephone and internet for almost 10 days. Early last week we got power & water. Then slowly the internet started working too. (more on this here)

I am swimming thru heaps of email & backlog work. Thanks to everyone who emailed me with kind thoughts, prayers and love. I can’t tell you how thankful I am for having you in my life.

I am really glad to be back online, sharing my stories, knowledge & tips with you all.

As it has been a while, I want to share a few quick announcements first.

Continue »

ABC Inventory Analysis using Excel

Published on Oct 1, 2014 in Charts and Graphs, Learn Excel
ABC Inventory Analysis using Excel

ABC analysis is a popular technique to understand and categorize inventories. Imagine you are handling inventory at a plant that manufactures high-end super expensive cars. Each car requires several parts (4,693 to be exact) to assemble. Some of these parts are very costly (say few thousand dollars per part), while others are cheap (50 cents per part). So how do you make sure that your inventory tracking efforts are optimized so that you waste less time on 50 cent parts & spend more time on costly ones?

This is where ABC analysis helps.

We group the parts in to 3 classes.

  • Class A: High cost items. Very tight control & tracking.
  • Class B: Medium cost items. Tight control & moderate tracking.
  • Class C: Low cost items. No or little control & tracking.

Given a list of items (part numbers, unit costs & number of units needed for assembly), how do we automatically figure which class each item belongs to?

And how do we generate above ABC analysis chart from it?

Continue »

CP020: Top 10 time saving strategies for business analysts

Published on Sep 18, 2014 in Chandoo.org Podcast Sessions, Learn Excel
CP020: Top 10 time saving strategies for business analysts

In the 20th session of Chandoo.org podcast, lets save some time.

What is in this session?

We all want to save time and stay productive. The obvious answer seems like using keyboard shortcuts. But they can only get you so far. So what about the real productive strategies? That is what we address in this podcast.

In this podcast, you will learn,

  • Announcements
  • 5 key areas of business analyst work – tracking, analysis, reporting, data management & modeling
  • Time saving strategies for tracking
  • for analysis
  • for reporting
  • for data management
  • for modeling
  • Conclusions
Continue »

Please help me design our new course – “50 ways to analyze your data”

Published on Sep 10, 2014 in Analytics, Learn Excel
Please help me design our new course – “50 ways to analyze your data”

Hi friends & readers of Chandoo.org,

I have an exciting and awesome new training program for you. 50 ways to analyze your data is aimed to give you critical data analysis & charting skills necessary for your success as an analyst (or manager). I want to make sure that this course offers you best content & value. I need your help in designing it. Please take a few minutes to read this short post & share your feedback.

Continue »

3D Max Formula for Excel

Published on Sep 9, 2014 in Excel Howtos, Learn Excel
3D Max Formula for Excel

We all know about the MAX formula. But do you know about 3D Max?

Sounds intriguing? Read on.

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

Continue »

Teach me (and rest of our community) something this Teachers’ Day

Published on Sep 5, 2014 in Learn Excel
Teach me (and rest of our community) something this Teachers’ Day

Today is teachers’ day in India. I owe everything I learned to my teachers. Their powerful, insightful & inspirational lectures make me a better person.

Today I want to recruit more teachers. Because you never learn enough.

So why don’t you come on board and teach me (and rest of website visitors) something?

All you have to do is post a comment with an Excel tip, technique, idea or experience with us. It can be related to anything, from formulas to solver, charts to power pivot, VBA to keyboard shortcuts. Anything goes.

Lets go. I am all ears.

Continue »

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 »