3 Crazy Excel Formulas That Do Amazing Things

Ryan Dube Updated 01-01-2019

Excel formulas can do almost anything. In this article, you’ll learn how powerful Microsoft Excel formulas and conditional formatting can be, with three useful examples.


Digging Into Microsoft Excel

We’ve covered a number of different ways to make better use of Excel, such as using it to create your own calendar template or using it as a project management tool.

Much of the power lies behind the Excel formulas and rules that you can write to manipulate data and information automatically, regardless of what data you insert into the spreadsheet.

Let’s dig into how you can use formulas and other tools to make better use of Microsoft Excel.

Conditional Formatting With Excel Formulas

One of the tools that people don’t use often enough is Conditional Formatting. If you’re looking for more advanced information on conditional formatting in Microsoft Excel, make sure to check out Sandy’s article on formatting data in Microsoft Excel with conditional formatting.

With the use of Excel formulas, rules, or just a few really simple settings, you can transform a spreadsheet into an automated dashboard.


To get to Conditional Formatting, you just click on the Home tab, and click on the Conditional Formatting toolbar icon.

This is a screen capture demonstrating conditional formatting in excel

Under Conditional Formatting, there are a lot of options. Most of these are beyond the scope of this particular article, but the majority of them are about highlighting, coloring or shading cells based on the data within that cell.

This is probably the most common use of conditional formatting—things like turning a cell red using less-than or greater-than formulas. Learn more about how to use IF statements in Excel.


One of the lesser used conditional formatting tools is the Icon Sets option, which offers a great set of icons you can use to turn an Excel data cell into a dashboard display icon.

This is a screen capture demonstrating conditional formatting icons in a context menu

When you click on Manage Rules, it’ll take you to the Conditional Formatting Rules Manager.

Depending on the data you selected before choosing the icon set, you’ll see the cell indicated in the Manager window, with the icon set you just chose.


This is a screen capture demonstrating the conditional formatting in Excel. This shows the rules manager menu inside of Excel.

When you click on Edit Rule, you’ll see the dialog where the magic happens.

This is where you can create the logical formula and equations that will display the dashboard icon you want.

This example dashboard will show time spent on different tasks versus budgeted time. If you go over half the budget, a yellow light will display. If you’re completely over budget, it’ll go red.


This is a screen capture demonstrating the setting conditional formatting rules in Excel.

As you can see, this dashboard shows that time budgeting isn’t successful.

Almost half of the time is spent way over the budgeted amounts.

This is a screen capture demonstrating Excel's time budgeting dashboard

Time to refocus and better manage your time!

1. Using the VLookup Function

If you’d like to use more advanced Microsoft Excel functions, then here’s another one for you.

You’re probably familiar with the VLookup function, which lets you search through a list for a particular item in one column, and return the data from a different column in the same row as that item.

Unfortunately, the function requires that the item you’re searching for in the list is in the left column, and the data that you’re looking for is on the right, but what if they’re switched?

In the example below, what if I want to find the Task that I performed on 6/25/2018 from the following data?

This is a screen capture demonstrating how to use the vlookup function in excel

In this case, you’re searching through values on the right, and you want to return the corresponding value on the left – opposite the way VLookup normally works.

If you read Microsoft Excel pro-user forums you’ll find a lot of people saying this isn’t possible with VLookup, and that you have to use a combination of Index and Match functions to do this. That’s not entirely true.

You can get VLookup to work this way by nesting a CHOOSE function into it. In this case, the Excel formula would look like this:


What this function means is that you want to find the date 6/25/2013 in the lookup list, and then return the corresponding value from the column index.

In this case, you’ll notice that the column index is “2”, but as you can see the column in the table above is actually 1, right?

This is a screen capture demonstrating the vlookup function in excel

That’s true, but what you’re doing with the “CHOOSE” function is manipulating the two fields.

You’re assigning reference “index” numbers to ranges of data – assigning the dates to index number 1 and the tasks to index number 2.

So, when you type “2” in the VLookup function, you’re actually referring to Index number 2 in the CHOOSE function. Cool, right?

This is a screen capture demonstrating vlookup results

So, now the VLookup uses the Date column and returns the data from the Task column, even though Task is on the left.

Now that you know this little tidbit, just imagine what else you can do!

If you’re trying to do other advanced data lookup tasks, be sure to check out Dann’s full article on finding data in Excel using lookup functions.

2. Nested Formula to Parse Strings

Here’s one more crazy Excel formula for you.

There may be cases where you either import data into Microsoft Excel from an outside source consisting of a string of delimited data.

Once you bring in the data, you want to parse that data out into the individual components. Here’s an example of name, address and phone number information delimited by the “;” character.

This is a screen capture demonstrating delimited data

Here’s how you can parse this information using an Excel formula (see if you can mentally follow along with this insanity):

For the first field, to extract the leftmost item (the person’s name), you would simply use a LEFT function in the formula.


Here’s how this logic works:

  • Searches the text string from A2
  • Finds the “;” delimiter symbol
  • Subtracts one for the proper location of the end of that string section
  • Grabs the leftmost text to that point

In this case, the leftmost text is “Ryan”. Mission accomplished.

3. Nested Formula in Excel

But what about the other sections?

There may be easier ways to do this, but since we want to try and create the craziest Nested Excel formula possible (that actually works), we’re going to use a unique approach.

To extract the parts on the right, you need to nest multiple RIGHT functions to grab the section of text up until that first “;” symbol, and perform the LEFT function on it again. Here’s what that looks like for extracting the street number part of the address.


It looks crazy, but it’s not hard to piece together. All I did is took this function:


And inserted it into every place in the LEFT function above where there’s an “A2”.

This correctly extracts the second section of the string.

Each subsequent section of the string needs another nest created. So now you just take the crazy “RIGHT” equation you had created for the last section, and then pass that into a new RIGHT formula with the previous RIGHT formula pasted into itself wherever you see “A2”. Here’s what that looks like.


Then you take THAT formula, and place it into the original LEFT formula wherever there’s an “A2”.

The final mind-bending formula looks like this:


That formula correctly extracts “Portland, ME 04076” out of the original string.

This is a screen capture demonstrating parsed string

To extract the next section, repeat the above process all over again.

Your Excel formulas can get really loopy, but all you’re doing is cutting and pasting long formulas into itself, make long nests that actually work.

Yes, this meets the requirement for “crazy”. But let’s be honest, there is a much simpler way to accomplish the same thing with one function.

Just select the column with the delimited data, and then under the Data menu item, select Text to Columns.

This will bring up a window where you can split the string by any delimiter you want.

This is a screen capture demonstrating splitting text

In a couple of clicks you can do the same thing as that crazy formula above… but where’s the fun in that?

Getting Crazy With Microsoft Excel Formulas

So there you have it. The above formulas prove just how over-the-top a person can get when creating Microsoft Excel formulas to accomplish certain tasks.

Sometimes those Excel formulas aren’t actually the easiest (or best) way to accomplish things. Most programmers will tell you to keep it simple, and that’s as true with Excel formulas as anything else. You can even use in-built features like Power Query. Start by reading our guide to generate your first Microsoft Power Query script.

If you really want to get serious with using Excel, you’ll want to read through our beginner’s guide to using Microsoft Excel The Beginner's Guide to Microsoft Excel Use this beginner’s guide to start your experience with Microsoft Excel. The basic spreadsheet tips here will help you start learning Excel on your own. Read More . It has everything you need to start boosting your productivity with Excel. After that, make sure to consult our essential Excel functions cheat sheet The Essential Microsoft Excel Formulas and Functions Cheat Sheet Download this Excel formulas cheat sheet to take a shortcut through the world's favorite spreadsheet program. Read More .

Related topics: Microsoft Excel, Microsoft Office 2016, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. CindyH
    October 7, 2019 at 2:32 am

    Instead of Vlookup, setting up relationships is so much easier. No more formulas!

    January 25, 2019 at 12:29 pm


  3. Dominic Richens
    November 7, 2018 at 4:05 am

    ...or export to CSV and use python in half the time.

  4. Dominic Richens
    November 7, 2018 at 4:04 am

    ...or export to CSV and use python or perl in half the time

  5. Gabriel Goldberg
    November 6, 2018 at 4:43 pm

    Plenty to digest, great. Small mistake, I think:
    You can get VLookup to work this way by nesting a CHOOSE function into it. In this case, the Excel formula would look like this:


    What this function means is that you want to find the date 6/25/2013 in the lookup list, and then return the corresponding value from the column index.

    Date in formula should be 2013, yes?

  6. Timothy O'Neal
    September 12, 2017 at 3:40 am

    If you want crazy, then please answer my query.Iis there a way to do the following?
    input on 1 worksheet two or three fields (last name, first name, company name)
    then have a FIND/CHOOSE/whatever go through the rest of the workbook (12 pages or so)
    then either GO TO any row that has all three and have a NEXT to move on if needed
    OR return the page and row # where all three are
    and if there is more than one instance of all three provide the other pages and rows
    Basically I am looking for a way to improve upon the CTRL F that my company uses to search for last name only or first name only, but IF the list has multiple pages and has multiple "Ruiz" or "Johnny" or "Smith" then it takes forever to click next, next, next and IDENTIFY that 1 Ruiz or Johnny as being on my list.
    The main problem is that these lists on the worksheets in the workbook all come from different companies that use subcontractors and often format their pages differently from each other, such that one will have two separate columns for first and last name while another uses one column for both (eg: last, first) and they are often in different columns. Some in A, some in D, some in F, you get the picture.
    SO I need a SUPER DUPER FIND function with some boolean thrown in. If you could at least point me in the right direction, I would appreciate it.

    • Jose
      October 18, 2018 at 10:46 pm

      I am a little late, but there's a way to make the VLOOKUP work like a search engine and when you type the name or any part of it (multiple keywords separated by a space), it will give you a list of all the istances with that keyword (regardless of which column they are located). If you'd like to know more, let me know, so I can send you a sample.

  7. Naveen
    July 27, 2017 at 5:15 am

    this is a insane way of doing the simple task making it more complicated. Imagine you have big piece of text, may be with 50 comma separated fields, what would be your effort to create formulas for those fields.

  8. Madeline
    April 26, 2017 at 11:06 pm

    If some one desires to be updated with most recent technologies after
    that he must be go to see this site and be up to date daily.


  9. Jimbob
    January 4, 2017 at 11:55 am

    Whoever wrote this article is a dork

  10. Martin
    December 11, 2016 at 7:59 pm

    Oh yea, why make it easy and simple when there is always a complicated solution.. :D

  11. haze78
    November 18, 2016 at 10:58 pm

    Only when you master excel VBA you release its true power. It is just ridicolous what can one do with it.

    • god
      July 10, 2017 at 2:44 pm

      Like spell, perhaps

      • Haze78
        July 11, 2017 at 9:20 pm

        Not native, anyway, my spelling is kind of offtopic, how many languages do you speak?

  12. Tony
    October 16, 2016 at 8:41 pm

    Why would you not just use text to columns with the delimiter set to ";"? I get that you started talking about super crazy but I'm all about efficiency.

  13. Excel Guru
    September 9, 2016 at 3:53 pm

    This is nothing. Not Impressed!

    • Klaas Vaak
      October 5, 2016 at 11:06 am

      Then wow us with a really ggod one, please.

    • Klaas Vaak
      October 5, 2016 at 11:07 am

      Then wow us with a really good one.

  14. Prathet
    September 8, 2016 at 6:30 am

    Wow. crazy formulas. I love it! THANKS.

  15. WK
    June 30, 2016 at 11:13 pm

    Or, for that crazy data importing, download LibreOffice portable (google it), and open the file using LO Calc, save it to a .xlsx file, and open it in excel. MS excel's text file importing and exporting is terrible, but LO Calc does it really well. Just make sure you use the right tool for each job.

  16. jeetu DON
    May 17, 2016 at 1:20 pm

    nice ijo9jijjjjjjjjjjj

  17. Wayne
    April 15, 2016 at 5:28 pm

    Excel can parse data multiple ways.

    If you have to use a formula, consider this formula:


    Where +2 and +3 represent the 2nd and 3rd occurrence.

    • Greg
      May 11, 2016 at 2:09 pm

      This isn't quite accurate because the num_chars part of your MID formula doesn't return the correct value. Using the string given in the article, FIND(";",A2, FIND(";", A2)+3) will return "16", so the MID would become =MID(A2, 17, 16) 16 is the position of the 2nd semicolon.

      This returns "Portland, ME 040" instead of "Portland, ME 04076"

  18. theSplund
    February 9, 2016 at 3:54 pm

    rather than Lookup/Choose, a more powerful, and less processor intensive, option is the Index/Match

  19. JADD
    January 24, 2016 at 6:21 pm


  20. Anonymous
    October 16, 2015 at 11:59 am

    instead you could only use function in excel itself
    text to coloum

    • ashfaq
      December 14, 2015 at 12:13 pm

      Yes correct :D

    • Preshen
      January 8, 2016 at 8:50 am

      Was thinking the same thing

    • Anonymous
      March 30, 2016 at 11:53 am


    • dani
      April 12, 2016 at 3:56 pm

      if u want any help in excel working , im available at there on facebook,

  21. debangshu sekhar pal
    May 23, 2015 at 12:36 pm

    find fill_{{{{_++++ c++ admin

  22. debangshu sekhar pal
    May 23, 2015 at 12:34 pm

    find fill_+++++c++ jumla java admin

  23. david
    November 21, 2013 at 3:54 pm

    can anybody help with this?

    i am looking for a code to sort a problem out and am wondering if this is possible


    (b) price (H-N) will have prices from different shops

    i will fill in (H-N) with the prices

    how to get excel to put the lowest price in (B)

    this code will go in (g) if possible

    now (H-N) have different colors, in the background not the text
    can the color go as well but not just for (b) but also for (c-d-e-f)

    and last but not least can the name of the shop go to (f)

    any help is welcome

    hope this makes sense to someone out there


  24. Adrian
    October 10, 2013 at 6:56 pm

    hey ryan!

    Thanks for the awesome excel guides . Sorry cz i'm pretty new in excel here.
    But i was just wondering , for the first guide - icon sets , since the conditional formatting rules of the elapsed time (E2 - E8) is based on each rows' estimated time (B2-B8) individually respective to their rows . So do i have to input the formulas individually for each cell one by one ? or ...
    Is there a faster way?


    Sorry for troubling. I've been trying to figure this out for hours! :(

  25. Brad
    August 23, 2013 at 5:01 am

    Hey Ryan,

    You got to love the conditional formatting icon sets in Excel right. I use these all the time when I'm building dashboards.

    I did find a really cool time saving shortcut key today. Use the = symbol when you are on a cell and select a range. You can then hit F9 and it will display a concatenated version of the range and place into the cell you are on. You can then take out the parentheses and the = sign and depending on your bi reporting software, you can use the concatenated value in the cell as a filter for your bi report. Saved me hours today.

    Anyway, cheers!

    Great post!

  26. Justin K
    July 21, 2013 at 11:47 pm

    Thanks, but no thanks! :) Too complicated for my overworked brain!

  27. Fred Klein
    July 19, 2013 at 12:07 am

    My favorite is CTRL *
    It selects the contiguous region around the cursor!

    Another related one is CTRL . (That's a decimal point!)
    This will cause the selected cell to move around the selected region clockwise for each CTRL . ! You can inspect a LARGE region in seconds.

    Then there's ... select a cell with a formula. Then F2 then F9 and the computed value replaces the formula!

    And the list goes on....

  28. Joe Bob
    July 18, 2013 at 8:01 pm

    Show me Konami in Excel. That would be impressive.

    • Ryan Dube
      July 18, 2013 at 8:09 pm

      Joe - That's actually a brilliant idea! Challenge accepted. Watch for an upcoming article. :-)

  29. jsquilter
    July 18, 2013 at 6:17 pm

    The other way to spread text data out into columns is under the Data tab. Select the data you want to move into columns, and click on Text to Columns. It will come up with the Convert Text to Columns Wizard, and you can choose Delimited or Fixed Width. If you keep it in Fixed Width, you can move the arrows around to change the break lines if you want two or more items in a single column. I use it all the time!

  30. George7
    July 18, 2013 at 10:00 am

    I would better suggest, instead of using the "Insane Nested Formulas to Parse Strings" method, to just open the imported file on a Notepad and just replace every ";" character to ",", therefore Excel will automatically parse each component to separate column. Of course this method is without using Excel to do what you need.

  31. Endri B
    July 18, 2013 at 3:18 am

    Thanks Ryan for the "Insane Nested Formulas to Parse Strings", I did't know excel could do that :)

    • Ryan Dube
      July 18, 2013 at 5:24 am

      Thanks Endri. It's probably not the prettiest or most elegant way to do it - but I wanted to see how far I could push Excel to process a super-insane formula. :-)

    • Brendan
      November 20, 2013 at 9:34 pm

      That's not a super insane formula...

      This is,

      {=IFERROR(IF(OR((LEFT(VLOOKUP(Required_Live!A4148,'Years Of Coverage'!A:P,COLUMN(),FALSE),2))=(LEFT(VLOOKUP(Required_Live!A4148,'Application Data'!A:O,{4,5,6,7,8,9,10,11,12,13,14,15},FALSE),2)),(LEFT(VLOOKUP(Required_Live!A4148,'Years Of Coverage'!A:P,COLUMN(),FALSE),2))=(LEFT(VLOOKUP(Required_Live!A4148,Districts!A:O,{4,5,6,7,8,9,10,11,12,13,14,15},FALSE),2)),(LEFT(VLOOKUP(Required_Live!A4148,'Years Of Coverage'!A:P,COLUMN(),FALSE),2))=(LEFT(VLOOKUP(Required_Live!A4148,'New Data'!A:V,{4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22},FALSE),2)),(LEFT(VLOOKUP(Required_Live!A4148,'Years Of Coverage'!A:P,COLUMN(),FALSE),2))=(LEFT(VLOOKUP(Required_Live!A4148,Digitized!A:AG,{4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33},FALSE),2))),"",IF(VLOOKUP(Required_Live!A4148,'Years Of Coverage'!A:P,COLUMN(),FALSE)=0,"",VLOOKUP(Required_Live!A4148,'Years Of Coverage'!A:P,COLUMN(),FALSE))),"")}

  32. gman
    July 18, 2013 at 1:19 am

    I strongly recommend against using the formula method to parse your strings.

    There is an in-built excel function which does everything you described.
    Under Data>Data Tools> Text to Columns

    This is much much faster and doesn't eat up your computer resources.

    • Ryan Dube
      July 18, 2013 at 2:19 am

      This requires a delimiter. You could actually do the same thing by importing data from outside sources - a text file for example, and you could import a large volume of data and parse it that way.

      The point here, however, is if you have an unusual input stream. My example didn't show this clearly, but let's say you have data with one section delimited by "-" and the next by "+" in an alternating manner. This would break both the text-to-columns tool and the import/delimited file. However, you could still edit my crazy script to use alternating delimiters in parsing the data. :-)

      • gman
        July 18, 2013 at 3:51 am

        haha I see your point,

        But you know, I still think using nested formulas should be a last resort. Say your example of the unsual input stream. I could just do a find-replace to change all "+" signs to "-", then use text-to-column.

        • Ryan Dube
          July 18, 2013 at 5:23 am

          That's very true - in most cases you probably could!

  33. likefunbutnot
    July 18, 2013 at 12:44 am


    Regular Expressions. That's a crap-ton of work for parsing a bunch of text.

    • Ryan Dube
      July 18, 2013 at 2:12 am

      Sure is - but it was fun to come up with the expression. :-)