Technology advances faster than the typical user can keep up. New applications, tools, and products are constantly entering the market, and even the technology we are familiar with receives wave after wave of updates and changes. After being out of the daily grind for a few years myself, it seems like many things have passed me by. Sometimes, we just need a little extra help to make technology listen to what we’re trying to tell it.

I am self-taught in Excel; however, I have picked up a few tricks I use that make my day-to-day a little easier. It is surprising how much time can be saved on a daily basis when we know what we are trying to search for in Excel, or what functions to use to cut out manual work. If you find yourself with repetitive sets of manual work, I am hoping this article helps you. For example, let’s say you are tasked with pulling a small set of data and organizing it each month. What outcome are you looking for with that data? Do you need totals? Do you need a sortable list with all the details included? Do you need a graph?

I am going to share some of the most popular functions I have found that help break down that manual work and speed up your daily processing. Most functions have numerous purposes and variations.

The IF function

The most common function is adding an ‘IF’ statement to a normal function to make it exponentially more efficient. Think back to primary school, we all remember the “If, then” statement. “If” this is true, “then” what? This same logic applies to the IF function and the IF variations in Excel. In essence, if it’s true a word, phrase, or number is equal to, less than, or more than your requested value, then what? This ‘what’ is where all the magic and flexibility happen.

An example of a typical IF formula structure would be {=IF(A1=’value’, if true then?, if false then?)}. This same principle works with the IFS function, combining multiple ‘if true then?’ statements. An IFS function would work as {=IFS(logic=’value’, if true then?, logic=’value’, if true then?, logic=’value’, if true then?)} and so on. Once the function meets the first ‘true’ condition, then that will be the resulting value. Amazingly, these statements don’t have to be equal to, =, you can also use greater than, >, less than, <, or even words by enclosing them in quotations, “value”.

Adding IF to common functions to curate data

When applying this logic to other popular functions such as SUM, COUNT, and AVERAGE, you have the option of getting the required results without the hassle of filtering or moving any data. I have found that these IF variations become more and more useful the larger the data set. The goal is to start with all available data and have these helpful functions pull out the summary information for you.

Let’s use the most common function, SUM, as an example. The SUM function works to add together a range of data, let’s say {=SUM(A1:A3)}, A1:A3 being our range to add together. SUM plus IF, SUMIF, uses that same range but adds an IF true, then SUM statement. Allowing you to choose to only add together cells that meet your criteria and logic.

SUMIFS works similarly, except that there is one SUM range, but multiple ‘if true?’ criteria options with specific criteria ranges. This allows you to answer statements such as, “I’d like to SUM A1:A3 if B1:B3 is 1, or C1:C3 is 2”, and populate the SUM of your given range while utilizing your entire data set from multiple columns. These same principals work for COUNTIF, COUNTIFS, AVERAGEIF, and AVERAGEIFS as well. Totals are thankfully the easiest data set to get, the best part being, once you start your function in the formula bar you can click the ‘choose function’ (fx) button to get specifics on what information the formula is looking for.

Taking advantage of smartsheets

Where this information most comes in handy is when you use the same data set repeatedly. Never be afraid to create new sheets! The sheets tabs at the bottom of the Excel documents are your best friend. If you have the data, you can paste it into one sheet and have another sheet with all your totals, this will keep you from having to replicate this same process repeatedly and keep you from inadvertently changing information. This is where automation comes in handy.

The idea is to create a worksheet (we call it a smartsheet here in Data Analytics) to do all the work for you. Ideally, you would import your data and save it in a copy of your “smartsheet,” reusing the smartsheet as a template. You don’t have to stop with just a data sheet and a totals sheet. Excel can also add graphs and other visuals based on your raw or organized data and can track it over time from one worksheet to another with the right formula.

While working at my CUSO, I have modified these reusable smartsheets for monthly processing. Whereas before these smartsheets were created, the same information could be found by selecting the data and viewing counts and totals at the bottom of the page or selecting the text and creating pivot tables, now all that information will pull directly into a report sheet. This saves me a minimum of 15 minutes per report, multiply that by 7 monthly service reports and that’s almost two hours and tons of hassle saved. The report can look the same as if it were created in PowerPoint or Word simply by changing the view and adding visually appealing elements.

Try them yourself!

Automation via Excel is a great opportunity to experiment with formulas and reuse them in different and interesting ways without needing to research the problem repeatedly. Some of the most helpful functions I have found are listed below. While this isn’t a detailed how-to guide, I encourage you to search for what you want and rely on the Microsoft Support pages for creative and helpful ways to use these functions in your data automation journey.

Whether the data is being used for marketing, mailing lists, or management reports, Excel can be utilized to process it and make it visually interesting for your needs, you just need to know how to “talk” to it.

Some Common Functions:

=SUM(), =SUMIF(), =SUMIFs()

=COUNT(), =COUNTIF(), =COUNTIFs()

=AVERAGE(), =AVERAGEIF(), AVERAGEIFs()

=TEXT()

=TODAY()

More Advance Functions: (Best when used with other functions, or ‘nested’)

=EOMONTH()

=EDATE()

=VLOOKUP()

=INDEX()