Login to online classes

Tag: OFFSET()

VLOOKUP(), MATCH() and INDEX() – explained in plain English

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, INDEX and MATCH worksheet formulas can transform you from normal excel user to a data processing beast. Today, […]

Speed up your Excel Formulas [10 Practical Tips]

Speed up Excel Formulas - 10 Practical Tips

Excel formulas acting slow? Today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.

1. Use tables to hold the data
2. Use named ranges & named formulas
3. Use Dynamic Array formulas
4. Sort your data
5. Use manual calculation mode

… and more. Read on to learn these top 10 tips & ideas to improve performance of your excel formulas.

Excel Basics: How to add drop down list to validate data

Excel dropdown list without duplicate entries

Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to […]

Relative References in Excel Tables

Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.

While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.

An odd lookup problem [Formulas]

Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.

And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).

So how would you write the lookup formula?

CP055: “Yes, I am back” edition (and a bonus Excel tip)

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

Ladies & gentlemen, its time we revived the much loved Chandoo.org podcast. In the 55th episode, I do a lousy imitation of Arnold Schwarzenegger’s famous “I will be back” and tell you why there was such a long gap between episodes, my plans for reviving our podcast and more.

What is in this session?

In this podcast,

  • Why there was such a long gap between last and this episode
  • What next?
  • How to extract every 6th item from a list?