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.
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.
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.
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.
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!
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”.
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.
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.
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.