5 Excel Tools

Generally, you should use software tools that have been well tested and provide robust results, but there are times when you just need to do your own thing. Maybe the software doesn’t give you the information you need and you need to dig into the details deeper. Or you need to do some ad hoc “what ifs” that Excel…well… excels at.

In my prior work life, some of the canned reports in the software were insufficient for the job and the only way to get at the solution was to use Excel. Excel is very powerful but keep in mind you can easily make mistakes. But despite how easy it was to “break” the spreadsheet, I needed to use Excel because the software just did not spit out information for 150 projects in a usable fashion. If I wanted a nice clean report, I had to run a PDF report for each project. If I  wanted information for all 150 projects simultaneously, I had to download the raw data in a flat file form.

You can use Excel for more than summing and filtering. You can change flat files into nice reports that enable your managers to choose their information. Fancy Excel tools such as macros are not required but it does require some creative problem solving. Following are the five Excel tools that I basically use over and over, and they are tools that anyone can learn.

[divider]My Five Go To Tools[/divider]

1) IF function: Aside from SUMming and SUBTOTALing, the IF function is probably my most used function. This is an extremely useful function and I use it a lot with the ISERROR function. Oftentimes my IF will be a nested IF function, with multiple IF questions within a single formula. To arise at these formulas, I first start off small to test a small “IF” situation. Once I’m satisfied that the small IF statement works, I add upon it to build up the formula. So, my formula may not be (IF a, do b, otherwise do c). It could be (If a, then (if b, do c or if not b do d), otherwise (if e, do f or if not e do g)). Huh? Yes, it can get crazy. Each part gets tested: I test the a part, the b part, the c part and so on.

2) VLOOKUP or HLOOKUP function: Most people probably use this so I won’t go into much more detail. The only thing I want to say is to test out the TRUE/FALSE portion. There are 2 other functions for the “Excel masters” that can replace the VLOOKUP and HLOOKUP: MATCH and INDEX. I believe they are commonly used together. Now I just started using them so I’m not an expert…yet. The main benefit of using them is that you don’t have to have the first column or row be the lookup value; your lookup value can be anywhere in the array. This ability can come in handy in saving space in your file.

3) CELL(“type”, ref): This function is really handy to determine whether a number is really a number (“v”) or text (“l”).

4) Conditional formatting: This tool can make information pop out. Say you did a download of 150 projects and you made a pivot table of that information. You can apply conditional formatting to make each project “Grand total” stand out in the sea of numbers. I probably will need to do a post explaining how to do this but I do this all the time. But one caveat: conditional formatting can create large files!

5) Pivot Tables: This tool is so useful in slicing and dicing data that I can no longer do without it. You can download the data, create a pivot table, and then BOOM, you have your report. If you need to, you can apply conditional formatting to make certain areas pop out. If you are not using pivot tables, you really should look into learning how to use this tool. It will save you TIME and you can change the layout in an instant.

And there you go, my 5 Excel tools that I use. I combine them and come up with great solutions that make me look like a genius.