3 Crazy Excel Formulas That Do Amazing Things

complexequations   3 Crazy Excel Formulas That Do Amazing ThingsI’ve always believed that Excel is one of the most powerful software tools out there. It isn’t just the fact that it’s spreadsheet software, which is by its nature powerful anyway. No, Microsoft Excel 2013 itself has such an awesome collection of built-in tools and features that it has beyond any doubt, become one of the most useful tools for so many things. In this article, I plan to show you how powerful formulas and conditional formatting can be, with three pretty useful examples.

We’ve covered a number of different ways to make better use of Excel, such as using it to create your own calendar template, using it as a goals management tool, and other unique ways that you can use it to manage your life. Just read those articles and you’ll see just how powerful Excel can be.

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

Digging Into Excel

With the right information, you can create a system that automatically calculates and recalculates results and reports from that raw data. Today, I’d like to dig a little further under the surface and show you how you can use some of the underlying formulas and other tools to make better use of Excel.

Cool Conditional Formatting With Formulas

One of the tools that I think people just don’t use often enough is Conditional Formatting. With the use of formulas, rules, or just a few really simple settings, you can transform a spreadsheet into an automated dashboard that shows you a lot about the information in the spreadsheet at just a glance.

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

crazyexcel1   3 Crazy Excel Formulas That Do Amazing Things

Under conditional formatting, there are a bunch 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 – doing things like turning a cell red using logical less-than or greater-than formulas.

One of the lesser used – or known about – conditional formatting tools is the icon sets option, which provides you with a great set of icons you can use to turn an Excel data cell into a dashboard display icon.

crazyexcel2   3 Crazy Excel Formulas That Do Amazing Things

I discovered this once I upgraded to Office 2013 and used conditional formatting in Excel 2013. I checked out the icon sets and saw these cool LED indicator lights that I had only really seen before in some of the factory automation displays I’ve programmed. 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.

crazyexcel3   3 Crazy Excel Formulas That Do Amazing Things

When you click on “Edit Rule…”, you 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. In my example, I monitor time spent on different tasks versus my budgeted time. If I’ve gone over half my budget, I want a yellow light to display, and if I’m over budget, I want it to go red.

crazyexcel4   3 Crazy Excel Formulas That Do Amazing Things

As you can see, I’m not really budgeting my time too well. Almost half of my time is spent way over what I’ve budgeted.

crazyexcel5   3 Crazy Excel Formulas That Do Amazing Things

Time to refocus and better manage my time!

Look Up Items With the VLookup Function

Okay, maybe that isn’t crazy enough for you. Maybe you aren’t so excited about simple logical formulas turning lights on and off. Fine – if you’re an old-school Excel fiend and you like using more advanced Excel functions, then I’ve gone another one for you.

You are 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/2013 from the following data?

crazyexcel61   3 Crazy Excel Formulas That Do Amazing Things

In this case, your searching through values on the right, and you want to return the corresponding value on the left – opposite how VLookup normally works. If you read 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 formula would look like this:

“=VLOOKUP(DATE(2013,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)”

What this function means in simple English 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?

crazyexcel7   3 Crazy Excel Formulas That Do Amazing Things

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 two. So, when you type “2″ in the VLookup function, you’re actually referring to Index number 2 in the CHOOSE function. Crazy, eh?

crazyexcel8   3 Crazy Excel Formulas That Do Amazing Things

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 you can do!

Insane Nested Formulas to Parse Strings

As you can see, I’m trying to get a little crazier as we go, because I know there are always some of you out there that go, “…well that’s not crazy at ALL!!” I know, your standards are high and I’m trying to live up to them. If I’ve failed with my last two attempts, I bet I can appeal to your need for crazy with this particular formula.

There may be cases where you either import data into Excel from an outside source that is made up of a string separated by delimiters within the field itself. Once you bring in the data, you want to parse that data out into the individual components.

crazyexcel10   3 Crazy Excel Formulas That Do Amazing Things

Here’s how this is done (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.

“=LEFT(A2,FIND(“;”,A2,1)-1)”

This searches the text string from A2, finds the “;” delimiter symbol, subtracts one for the proper location of the end of that string section, and then grabs the leftmost text to that point. In this case, that’s “Ryan”. Mission accomplished.

But what about the other sections? Well, 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 to extract the street number part of the address.

“=LEFT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2))),1)-1)”

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

“RIGHT(A2,LEN(A2)-FIND(“;”,A2))”

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 past that into a new RIGHT formula with the previous RIGHT formula pasted into itself wherever you see “A2″. Here’s what that looks like.

“(RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),LEN((RIGHT(A2,LEN(A2)-FIND(“;”,A2))))-FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2))))))”

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:

“=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),LEN((RIGHT(A2,LEN(A2)-FIND(“;”,A2))))-FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2)))))),FIND(“;”,(RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),LEN((RIGHT(A2,LEN(A2)-FIND(“;”,A2))))-FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2)))))),1)-1)”

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

crazyexcel9   3 Crazy Excel Formulas That Do Amazing Things

To extract the next section, repeat the above process all over again. Your formulas can get really loopy, but all you’re doing is cutting and pasting long formulas into itself, make long nests that work really well!

Conclusion

So there you have it. Were these formulas and techniques wild enough? Do you have any amazing formula tricks of your own that make up your Excel super-user toolbox? Share your input and feedback in the comments section below!

Image Credit: Website via Shutterstock

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

18 Comments -

0 votes

likefunbutnot

Dude,

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

0 votes

Ryan Dube

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

0 votes

gman

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.

0 votes

Ryan Dube

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. :-)

0 votes

gman

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.

0 votes

Ryan Dube

That’s very true – in most cases you probably could!

0 votes

Endri B

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

0 votes

Ryan Dube

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. :-)

0 votes

Brendan

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))),”")}

0 votes

George7

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.

0 votes

jsquilter

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!

0 votes

Joe Bob

Show me Konami in Excel. That would be impressive.

0 votes

Ryan Dube

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

0 votes

Fred Klein

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….

0 votes

Justin K

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

0 votes

Brad

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!

0 votes

Adrian

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?

Thanks!

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

0 votes

david

can anybody help with this?

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

lines

(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

cheers