7 Excel Functions to Improve Your Workflow
If you’re like me, you’re probably using Excel, Google Sheets, or some sort of spreadsheet software to manage some sort of data! For me, Excel is good for everything from an outreach tracker to content analysis.
We talked to professionals who use Excel every day and asked what their favorite functions are so you can get to work making your time more efficient!
SUMIF
“When conducting data analysis, it’s not always enough to simply sum values together. Sometimes, values should only be summed based on set criteria.
“This is where SUMIF comes in. SUMIF asks you to set the range you wish to evaluate, establish the criteria, and optionally set a sum_range. Your sum_range is an array of values that are to be added together if your initial range satisfies the criteria. If you omit the sum_range argument, the values from the range argument are summed together instead.
“This gives you a little more processing power when creating insights based on your data and comes with the added advantage of shortening the formula down to something more manageable.”
Paul Sherman
Chief Marketing Officer at Olive
COUNTA
“This function is one of my favorites because it’s simple and one-dimensional yet incredibly effective. COUNTA does one thing: it figures out whether a cell is empty. When you’re working with large data sets, it makes a big difference.
“If you’re unknowingly working with incomplete data, these inaccuracies can skew business decisions and result in major communication issues with stakeholders. Fortunately, this function solves that problem, making it easy to parse through your data set and determine whether there are any gaps automatically. You won’t have to re-organize the data manually, and you can mitigate any potential issues before they have a chance to impact your data negatively.”
Vincent D’Eletto
Founder & CEO at WordAgents
UNIQUE
“UNIQUE is a dynamic array function that extracts a list of unique values from a range or array. UNIQUE serves two purposes: first, it can extract a list of distinct values. This means that if you provide a range, it will create a new list omitting duplicates. This is useful when making sense of survey results, for example. The second argument in the syntax allows you to compare values by row (FALSE) or by column (TRUE).
“Alternatively, UNIQUE can be used to create a list of values that only occur once, which is useful when you’re looking for outliers in data sets. This just requires a slight change in the syntax — simply add a third argument and allocate a binary value (1 or 0) based on whether you’re trying to find truly unique values or all distinct values, respectively.”
Brian Turner
Chief Technology Officer at ConvertBinary
VLOOKUP
“Of all the Excel functions out there, VLOOKUP is the one I use the most, and it’s my favorite. I’ve used VLOOKUP so much in my career that I amusingly say that it saved my career. This function is the best friend of any analyst or any person who works with spreadsheets. It can cross-reference data entities together and join the appropriate data with them.
“It’s always suited for data cleaning that involves multiple sheets across that requires aggregating data points together. It’s even more powerful when combined with other functions like FIND. One could argue that the VLOOKUP function is a must-have for any automation process.”
Francis Angelo Reyes
Founder and Digital Analyst at Lupage Digital
Text-to-Columns
“When we get product names for reviews, sometimes, the names include everything from manufacturer to product name to LOT number. All of that is placed in just one cell in an excel sheet.
“Previously, I used to re-type the information on another cell to separate them, but that led to so many errors. Luckily, one of my employees taught me this Text-to-Columns trick. It has made my life a lot easier since I just have to click the column and choose how to separate the data.”
Chris Spencer
CEO at Picked for Home
GOOGLETRANSLATE
Note: This one is native to Google Sheets, but you can use this add-on for Excel!
“My favorite is GOOGLETRANSLATE, which is very effective as we use it to ensure our non-English sites and pages match up properly.
“We also use it for Google Ads as we want to make sure we target the correct keywords in different languages and that they are not out of context.”
Brett Downes
Founder at HAROhelpers
RegExtract
“This is an example of a RegExtract formula: =REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”).
“The URL will be cut down to just the domain. If you have an URL such as https://www.example.com/category/page/utm=, you will cut that URL to example.com using the formula above.”
Nikolay Krastev
SEO Specialist at Agile Digital Agency
Wrapping Up
Personally, I love VLOOKUP and its cousin, HLOOKUP. Using lookups in conjunction with pivot tables and some conditional formatting, I’ve learned how to cut down my reporting and analysis time.
Spreadsheet software like Excel can be a drag if you’re doing everything manually. Make your life easier with these formulas!
Did we miss your favorite? Let us know!
Originally published at https://www.joinit.org.
