It's no mere coincidence that Microsoft's Excel application is ubiquitous throughout the corporate, academic, industrial and nonprofit worlds, among others. Some colleges and universities have even started to offer coursework to introduce the program's benefits to its students and prepare them for life after school. If instruction in Excel wasn't one of your rites of passage, however, you may not be aware of all the tasks the application can accomplish for you in the daily course of business.
To help you out, we gathered five Excel efficiency hacks to include in your productivity training.
But first, let's do a quick brush up on basic functions. Consider the SUM function. By typing "=SUM()" into the formula bar and inserting a range of cells within the parentheses, for example "=SUM(A2:A5)," one cell can represent the sum of the values in cells A2, A3, A4 and A5. Try it out. Once you've wrapped your mind around how functions work, you'll be equipped to add these five to your repertoire.
Hack #1: The TODAY function
TODAY is particularly useful for those in human resources or a management role, who may have to see how long an employee has worked at your company before being eligible for benefits such as vested stock or a 401K contribution. By referring to the date the employee was hired, TODAY calculates the days or years employed. You can also use TODAY to see the number of days between two dates. The best part? Every time you open up the spreadsheet, it will update the results of the formula based on the given day.
Hack #2: The IF function
This function is great for many departments, especially those such as accounting where you need to calculate a value based on something being true or false. You can use IF to track dollar amounts within a particular timeframe and trust the numbers to tell you if a sales rep meets a specific goal for earning a bonus (aka, the situation is "true"). The same sort of logic can be applied to other conditions being met, such as meeting a deadline.
Hack #3: The VLOOKUP function
VLOOKUP—meaning vertical lookup—takes a value and compares it against a range of values, finds the highest-matching value within a data set (i.e. a table) and returns a corresponding value. Continuing with the commission example, VLOOKUP could search within your commission structure to find the corresponding annual bonus that should be paid to each salesperson. This function is particularly useful if you have a master spreadsheet with certain important employee information, but another person has been tracking birthdays in another sheet. You can use VLOOKUP to match employee names and pull birthdays into your master list.
Hack #4: The SUMPRODUCT function
As its name implies, SUMPRODUCT adds and multiplies at the same time. Let's say you have a spreadsheet showing different models of cars, the number of cars sold in a particular quarter and the price of each model. Normally, you would multiply the number of cars sold by the price of the relevant models, and then add the results for each model to get your total profit. SUMPRODUCT turns these two steps into one. A small benefit? Perhaps. But for many in the business world, every little hack counts.
Hack #5: The AGGREGATE function
Like a chain of holiday lights, sometimes in Excel when one cell goes out, they all go out. You've probably experienced the extreme frustration of setting up an entire spreadsheet and being unable to complete a calculation because one cell has an error value. This function helps you avoid such frustrations by applying different aggregate functions to a database with the option to ignore hidden rows or error values. Say you're looking at a spreadsheet concerning your employees and two of them are on maternity leave or sabbatical. Because you're not currently counting their sales numbers among your quarterly results, you may hide their rows. AGGREGATE, on the other hand can help you keep your calculations in tact while ignoring these employees' respective rows.
Like these tips, don’t miss our 10 Excel Aha Features all Microsoft Excel Users Should Know video.
