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.

--

--

--

Marketing Manager at JoinIt.org. Big fan of snacks. Like, in general.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Researching the practice of forecasting in the companies of the petrochemical and oil products…

Data Storytelling — How to Choose the Right Chart or Graph for Your Data

Analyzing 50 years of tennis

Let me introduce you to neural networks

Audio WEADS

Data Governance Postmortem

Feature Engineering For Algorithmic And Machine Learning Trading

Getting started with PowerBI — Prepare data for analysis and Model data in Power BI.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Taryn Hefner

Taryn Hefner

Marketing Manager at JoinIt.org. Big fan of snacks. Like, in general.

More from Medium

Top 6 Data Trends for 2022

Automating Tableau Prep from Command line

Takeaways from 2021 Melee Head-to-Head Data by ETossed

And you, are you also a CitiBiker?