# All articles with 'iferror' Tag

## Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Published on Jul 19, 2013 in Formula Challenges, Posts by Jeff

Welcome back to yet another gripping episode of “When good formulas go GREAT”. We’re working up the singles charts to the number one hit “Formula Challenge 001 – Return everything in string after first block of numbers” By way of a quick refresher, this formula challenge calls for a formula to return a substring from […]

Continue »

## How to create interactive calendar to highlight events & appointments [Tutorial]

Published on Apr 9, 2013 in Charts and Graphs, Learn Excel, VBA Macros

One of the popular uses of Excel is to maintain a list of events, appointments or other calendar related stuff. While Excel shines easily when you want to log this data, it has no quick way to visualize this information. But we can use little creativity, conditional formatting, few formulas & 3 lines of VBA code to create a slick, interactive calendar in Excel. Today, lets understand how to do this.

Continue »

## Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

## Formula Forensics No. 027 – Remove Leading Zeroes

Published on Aug 23, 2012 in Formula Forensics, Huis, Posts by Hui

Today in Formula Forensics we look at how to strip away leading zeroes from strings with this simple array formula

Continue »

## Extracting numbers from text in excel [Case study]

Published on Jun 19, 2012 in Excel Howtos

Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.

Continue »

## Comprehensive Guide to VLOOKUP & Other Lookup Formulas

Published on Mar 30, 2012 in Learn Excel

This week many Excel bloggers are celebrating VLOOKUP week. So I wanted to chip in and give you a comprehensive guide to VLOOKUP & Other lookup formulas. Read on …,

What is VLOOKUP Formula & how to use it?

I often tell my excel school students that learning VLOOKUP formulas will change your basic approach towards data. You will suddenly feel that you have discovered a superman cape in your attic. It is that awesome.

Continue »

## Formula Forensic 014 – Faseeh’s Formula

Published on Mar 1, 2012 in Formula Forensics, Huis, Posts by Hui

“I have a large list which includes blanks, I want to retrieve the list without the blanks”
Faseeh, responded with a neat array formula.

Today Formula Forensics pulls Faseeh’s Formula apart to see what makes it tick.

Continue »

## Convert ISERROR formulas to IFERROR formulas [macro]

Published on Mar 14, 2011 in VBA Macros

Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.

Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”

There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”

I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa.

Continue »

## IFERROR Excel Formula – What is it, syntax, examples and howto

Published on Mar 11, 2011 in Learn Excel

If IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

Continue »

## 6 Tips for Writing Better VLOOKUPs

Published on Nov 2, 2010 in Learn Excel

Ok, you have learned how to write vlookup formulas. You have also seen some pretty interesting examples of it (1, 2).

But how do you write better VLOOKUP formulas?

Here is a list of 6 tips that work wonders with VLOOKUP writing.

Continue »

## Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]

Published on Nov 1, 2010 in Learn Excel

Situation

Sometimes we don’t know what we want. If this happens when I am in a bar, I usually order a cocktail. Just a mix of everything. The same will work in Excel too.

For eg. If you have lots of data, but the value you want to look up needs to change based on whims and fancies of your users, then you can resort to a cocktail. A mix of VLOOKUP with Drop down lists (Data validation)

Read more to find how to solve this.

Continue »

## Excel Formula Errors – Understand and Debug Them

Published on Apr 20, 2009 in Excel Howtos, Learn Excel

In this installment of spreadcheats we will learn how to understand excel formula errors and fix them. The #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, #VALUE! errors and how to fix them.

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.