Monday, August 12, 2013

Essential Microsoft Excel functions

I hate my job, but I've learned things. Over the past decade, I've spent a lot of time working with Microsoft Excel. It is an extremely useful software package for professional and personal use.

Personal and professional: It can do lots of things
The spreadsheet program is good for keeping track of anything from performing simple math to creating databases. Pulling out Excel to do some addition, add up my school grades, and tracking expenses is something I do very often. A calculator is just slow and not advanced enough for doing these things. Work would probably be impossible without Excel too. I regularly manipulate and keep track of hundreds of database entries using this program. Having seen Microsoft Word used for storing data tables, I have learned to greatly appreciate the layout and functions of Excel.

It can do a lot more things
It can do a lot more things than what I described above. At work, I not only make my own spreadsheets, but I also receive and have to process those created by others. Excel is very simple to get started on: the only real syntax you need to do the bare basics are math operators like addition and subtraction signs. Many spreadsheets that I've gone through stick to those. The more advanced ones are created by a handful of people in the company and passed down.

There are, however, much more advanced built-in functions. I probably know only a very small number of them, which is strangely more than quite a few people I've met. Macros, advanced coding, long functions, and whatever else are foreign to me. Regardless, I know a few that are should be pretty useful for a bunch of applications. Here's a list of the ones I use frequently. Just Google the function for a detailed guide from Microsoft on how to fill the fields out properly and for more details.

IF()
This is the most basic function I've known for a long time. I got introduced to programming in junior year of high school and the IF function was always essential to practically everything. What this does is do whatever you tell it to if something is true or false.

I usually fill it out by entering the evaluation criteria, which is normally a cell value with an operator (>, <, =) and value, what to do if true, and what to do if false. By putting another IF in place of what to do if false, I can usually loop a bunch of conditions together.

The AND() and NOT() functions are usually very useful when entering the evaluation criteria.

VLOOKUP()
This is a great one for having Excel lookup a value and return a value from a large collection of data. I use it regularly to match one table or list against another. It's also very simple to use once you get the hang of it.

Regular use on my part involves entering a cell or exact value, the table array I'm looking it up in, the column in that array I want returned in the cell, and "0" to make sure it's a near exact match.

Recently, I read about wildcard operators ("*", "?") that make it so that an exact match isn't required for a value to be returned. Anything that can't be found usually returns a "#N/A" error.

COUNTA()
When I enter this formula and a selection of rows or columns, I can usually find how many cells are not empty. Essentially, it tells me the number of data entries in a list -- doesn't always work perfectly if cells are accidentally filled in with garbage.

COUNTIF()
Similar to the IF and COUNTA functions, except this one only counts how many cells meet a certain criteria. For example, sometimes I want to find how many times a number greater than 5 occurs in a list of 100 cells. Instead of manually counting and sorting, I can pop this thing in and get an immediate, automatically calculated number.

SUMIF()
The SUMIF function is similar to the COUNTIF function except that it sums all the numbers instead of just counting the occurrences.

Others and Final Words
Some of the other functions I use less often are MIN(), MAX(), LEFT(), RIGHT(), and INDIRECT(). I find that being good at Excel functions is partly related to just knowing that functions exist. Punching the names into Google will usually tell you about how to fill them in properly.

No comments:

Post a Comment