Excel Functions you need to know

Admittedly, I can’t remember when I started learning Excel. I’m sure it was sometime during high school or early off in college, but I know I learned pretty fast and now I can’t remember my first time typing in a SUM function. I received my Microsoft Excel Expert certification about a year ago now and I’ll be the first to admit that I don’t remember half of what I learned. My point is  that you aren’t going to remember anything you read here unless you practice it and/or use it. To be honest, nobody sits at home practicing Excel, so your best bet is to use it in your job or with school work. If you’re in the business world, this is probably expected, but for anyone else, this may be considered going above and beyond and will earn you some brownie points. So read along and if you have any questions, leave me a comment and I”ll be sure to help you out. Also, there’s dozens of Excel expert sites, so I recommend looking through a couple of them if you’re really stuck as well.

 Easy

1) Sum – Basic but still extremely helpful with large data sets or even small ones for which you don’t want to pull a calculator out for. This is probably the most used function whether as a stand alone function or as part of a nested function (multiple functions combined into one).  The logistics are pretty simple. Plug your numbers into either a row or column and then in your output cell type in “=SUM(” then by clicking with the mouse select your first number. Continue to hold the mouse down and scroll to select all fields you want summed. After you are done let go of the mouse, hit enter and that’s all. Alternatively, if you don’t want to type in the formula you can just select multiple cells and Excel in the bottom right hand corner will show you the average, sum, and count of those selected numbers.

Sum Function, note the auto sum, average, and count feature

Sum Function, note the auto sum, average, and count feature

2) Freeze Pans – While not a function, this feature in Excel is extremely helpful if you’re dealing with a large data set in which you need to be able to see the row or column headers. By freezing the panes you are able to keep these select cells in place while scrolling to vertically or horizontally. For this example, I downloaded an employment dataset from the bls.gov website. As you can see, there is a fair amount of rows and columns in this table. So, for example, say you were trying to look at Maine’s employment in April 2014. You could scroll over to the right but you may lose your place trying to find what row you’re supposed to be focusing on. Here’s the solution. At the top of the page select the “View” tab and you’ll see the “Freeze Panes” button. Now before clicking that you’re going to want to select your key cell. This cell will determine the rows above and the columns to the left that will stay in place. For our example, I’ve selected B5. After selecting freeze panes I’m able to scroll to the right and down to see Maine’s April 2014 employment without having to memorize which row or column I’m looking for.

Select the "View" tab and select Freeze Panes

Select the “View” tab and select Freeze Panes

Freeze Pane in effect

Freeze Pane in effect

Average

3) Vlookup – An easier way to find data in a large dataset is to use a vlookup function. This is one of my most used functions within Excel and most likely will be yours as well. Vlookup is a function that lets you search for a value in a table and return a corresponding value depending on its location within the table. For example, if you wanted to find the April 2014 Employment for Maine (same example as above) here is a simple way to do it using the Vlookup function. First you’ll need to have a search cell, this isn’t always necessary but for our example it’ll make your life easier. A5 is our search cell and will be used in the function as the lookup value. As you can see I typed Maine in this cell. If you’re searching for a different state all you’d have to do is just type it in instead and your result cell will change to the correct result. The rest of the function requires you to input the table references (top left and bottom right cell of the table separated by a colon), then the column number you want the corresponding result to come from. In this case that would be column 14. The last part of the function is to choose either true or false. The true identifier means it’ll look for a close match in terms for the first value in the function. A false identifier means the function searches for the exact match. This sounds complicated but let me explain. If we’re searching for “Maine” and you use a “True” identifier, Excel may come up with the Maryland result. Therefore, I always use the false identifier to avoid any potential snafus.

VLookUp Function

VLookUp Function

 

4) Sum Product – Sum Product can be very useful for specific purposes. It can be used in a variety of ways but the most simple one is for it’s standard purpose, to sum the product of two different ranges. Take a look at the example below. In column D you’ll see the product for each employees weekly pay. The total of all employees weekly pay is summed in cell D8. This results in a total of 2619. This is also the long way to go about things. The Sum Product function will do all those previous calculations with one simple formula. All you have to do is just type in the function, select the two rows (B & C) and then hit enter. Pretty easy formula but very useful and often overlooked!

Sum Product Example

Sum Product Example

Difficult

5) Pivot Tables – Pivot tables make many an Excel user anxious. They’re complicated, have a slew of uses, and many features. For the sake of simplicity I’m just going to provide an example of setting a pivot table up. If you’d like to do more research feel free to visit the Microsoft overview here. We’ll be using the same data set from the BLS example above. The first step is to select the relevant table information, as you can see below that is cells A8:N61. After selecting the cells, click the insert tab and choose Pivot table (top left of the screenshot). This opens up the textbox. Unless you want to move the pivot table to a different place than a new sheet, just click “Ok”.

Pivot Table Creation

Pivot Table Creation

This will lead you to a new sheet with the PivotTable Field List on the right. These are your options for the pivot table, they entail the State and each of the months from the data set. From here it’s really just a bunch of moving fields around to get the desired result. For our example I wanted to see the employment for each state for the month of April 2014. So I selected the State field as well as the April 2014 and put them in their appropriate spots. Obviously we’d want the state on the rows and the employment values down one column for simplicity sake so those are the areas we’d place these two fields.

 

Pivot 2

Below is the final results. This is an easy way to show each of the April 2014 employment values per state. This is basic though. From here you can change how each of the states values are shown and can summarize and analysis to your hears desire. To change the way the values are summarized, right click on the Sum header.

Pivot 3

 

 

I can’t stress enough how useful Excel can be to you in your personal life and in your career. It’s a very effective software that, with a bit of practice, can save you plenty of time. Use it for you budget, your sales report, or just as a catchy chart on that next report.

Advertisements

2 thoughts on “Excel Functions you need to know

  1. Some great tips here, however you overlooked/missed/ignored what (next to VLOOKUP and HLOOKUP) is the most powerful function in excel and one of the easiest to use! IF. Just about anything you can dream of can be accomplished using an IF function some way- whether it be nested with AND’s and OR’s, an additional level of criteria in a VLOOKUP or a million other things. IF can be used to compare things, convert things, alter things and flip things if used correctly. the IF function along with some carefully chosen calcs and string functions has helped me out of many a jam.

    Another thought, pivot tables, I think are not hard, they just aren’t intuitive (which may mean the same for some). Fortunately though, a basic understanding is enough for most of what you will ever do with it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s