Top 10 Excel Formulas and Functions You should Master

Excel formulas serve as the often-overlooked champions of productivity, effortlessly converting raw data into actionable intelligence with a simple keystroke. Spanning from fundamental arithmetic to elaborate statistical evaluations, Excel’s diverse collection of formulas equips individuals to fully harness the capabilities of their data. 

Regardless of whether you’re an experienced data analyst or a novice to spreadsheet applications, proficiency in Excel formulas reveals a domain characterised by heightened efficiency and accuracy. Need to project sales figures? There’s a formula tailored for that task. Seeking to discern patterns in customer behaviour? Excel provides the tools you need. The significance of Excel formulas extends across various sectors, reshaping how businesses interpret, plan, and flourish in today’s data-centric environment. 

With the right formulas within reach, you’re not just crunching numbers — you’re crafting insights, making informed choices, and propelling your organisation towards triumph.

What is Excel Formula?

In Microsoft Excel, a formula is a structured expression that manipulates values within a specified range of cells. These expressions yield outcomes, even if they encounter errors. Excel’s formulas facilitate various calculations like addition, subtraction, multiplication, and division. Moreover, you can compute averages, determine percentages for cell ranges, manipulate date and time data, and undertake numerous other operations within Excel.

• SUM: Adds up a range of numbers.
• COUNT: Counts the number of cells that contain numbers.
• AVERAGE: Calculates the average of a range of numbers.
• MAX: Finds the highest value in a range of numbers.
• MIN: Finds the lowest value in a range of numbers.
• VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value from another column.
• IF: Performs a logical test and returns different values based on the result.
• HLOOKUP: Works like VLOOKUP but searches horizontally.
• LEFT: Extracts a specified number of characters from the beginning of a text string.
• CONCATENATE: Joins the contents of multiple cells into one cell.
• RIGHT: Extracts a specified number of characters from the end of a text string.
• TODAY: Returns the current date.
• NOW: Returns the current date and time.
• LEN: Calculates the number of characters in a text string.
• COUNTIF: Counts the number of cells that meet a specific condition.
• SUMIF: Adds up the cells that meet a specific condition.
• AVERAGEIF: Calculates the average of the cells that meet a specific condition.
• SUMIFS: Adds up the cells that meet multiple conditions.
• AVERAGEIFS: Calculates the average of the cells that meet multiple conditions.
• DATE: Creates a date based on the specified year, month, and day.

Excel Formulas and Functions

1. SUM

In Excel, every formula begins with the equals sign, =, followed by a specific identifier indicating the desired calculation.

The SUM formula is among the most fundamental formulas in Excel, enabling the calculation of the sum or total of multiple values. To use the SUM formula, input the values you wish to add together using the format, =SUM(value 1, value 2, etc).

The values included in the SUM formula can either be direct numerical entries or references to specific cells within your spreadsheet.

For instance, to calculate the sum of 30 and 80, enter the following formula into a cell: =SUM(30, 80). Upon pressing “Enter,” the cell will display a total: 110.

Similarly, to compute the sum of the values in cells B2 and B11, input the following formula into a cell: =SUM(B2, B11). Upon pressing “Enter,” the cell will display the combined value of the numbers within cells B2 and B11. If either cell is empty, the formula will yield 0.

Additionally, you can determine the total value of a range of numbers in Excel. To calculate the sum of the values in cells B2 through B11, input the following formula into a cell: =SUM(B2:B11). Note that the colon is used to denote the range instead of a comma.

2. IF

The IF formula in Excel is represented by =IF(logical_test, value_if_true, value_if_false). This function enables the insertion of a text value into a cell based on whether a certain condition in the spreadsheet is true or false. For instance, =IF(D2=”Gryffindor”,”10″,”0″) would assign 10 points to cell D2 if it contains the word “Gryffindor.”

There are occasions when you need to determine how frequently a value appears in our spreadsheets. However, there are also instances when you want to identify the cells containing those values and input specific data alongside them.

Let’s revisit Sprung’s example for clarification. Suppose you award 10 points to all individuals belonging to the Gryffindor house. Instead of manually inputting 10s next to each Gryffindor student’s name, you’ll utilise the IF-THEN formula to stipulate: If the student belongs to Gryffindor, they should receive ten points.

The structure of the formula is as follows:

IF(logical_test, value_if_true, value_if_false)

– Logical_Test: This part constitutes the condition evaluated by the “IF” statement. In this context, the condition is D2=”Gryffindor.” Ensure the value of your Logical_Test is enclosed in quotation marks.

– Value_if_True: If the condition is true, indicating that the student belongs to Gryffindor, you want to display this value. Here, you opt for the number 10 to signify that the student received 10 points. Note: Quotation marks should only be used if you desire the result to be text rather than a numerical value.

– Value_if_False: If the condition is false, meaning the student does not belong to Gryffindor, you want the cell to exhibit “0,” indicating 0 points.

3. Percentage

To calculate percentages in Excel, input the cells for which you’re determining the percentage in the format =A1/B1. Once you’ve obtained the decimal value, convert it into a percentage by selecting the cell, navigating to the Home tab, and choosing “Percentage” from the numbers dropdown.

While Excel doesn’t offer a dedicated “formula” for percentages, it facilitates the conversion of cell values into percentages, eliminating the need for manual calculations and data entry.

To convert a cell’s value into a percentage in Excel, access the Home tab. From there, select the desired cell(s), then navigate to the dropdown menu adjacent to Conditional Formatting (initially labelled “General”). Subsequently, opt for “Percentage” from the provided options. This action will transform the value of each selected cell into a percentage.

Remember, if you’re employing other formulas like the division formula (represented as =A1/B1) to generate new values, the results may initially appear as decimals. To address this, highlight the relevant cells either before or after applying the formula and adjust their format to “Percentage” using the options available in the Home tab.

4. Subtraction

To execute the subtraction formula in Excel, input the cells you’re subtracting in the format =SUM(A1, -B1). This employs the SUM formula to subtract a cell by incorporating a negative sign before the cell to be subtracted. For instance, if A1 held the value 10 and B1 held 6, =SUM(A1, -B1) would compute 10 – 6, resulting in a value of 4.

Similar to percentages, Excel doesn’t have a distinct formula specifically for subtraction. However, subtraction can still be achieved using different methods.

• One approach involves using the =SUM formula. To subtract various values from one another, input the cells you wish to subtract in the format =SUM(A1, -B1), with a negative sign (represented by a hyphen) preceding the cell whose value you’re subtracting. Press Enter to obtain the difference between the cells enclosed in the parentheses.

• Another method entails using the format =A1-B1. To subtract multiple values from one another, simply initiate with an equals sign followed by your initial value or cell, then a hyphen, and finally, the value or cell you’re subtracting. Press Enter to retrieve the difference between the two values.

5. Array

An array formula in Excel surrounds a simple formula in brace characters using the format, {=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)}. Pressing ctrl+shift+center  will calculate and return value from multiple ranges rather than just individual cells added to or multiplied by one another.

Calculating the sum, product, or quotient of individual cells is easy — just use the =SUM formula and enter the cells, values, or range of cells you want to perform that arithmetic on. But what about multiple ranges? How do you find the combined value of a large group of cells?

6. COUNT

The Excel COUNT formula, expressed as =COUNT(Start Cell:End Cell), calculates the number of entries within a specified range of cells. For instance, if there are eight cells containing values between A1 and A10, =COUNT(A1:A10) will yield a result of 8.

In large spreadsheets, the COUNT formula proves invaluable for determining the quantity of cells containing data. It’s important to note that this formula doesn’t perform any mathematical operations on the cell values; rather, its sole purpose is to tally the occupied cells within a selected range.

By employing the emphasised formula above, you can effortlessly ascertain the count of active cells within your spreadsheet.

7. AVERAGE

The Excel COUNT function, written as =COUNT(Start Cell:End Cell), calculates the number of entries within a specified range of cells. For example, if there are eight cells with values between A1 and A10, =COUNT(A1:A10) will produce a result of 8.

In extensive spreadsheets, the COUNT function is indispensable for determining the number of cells containing data. It’s crucial to understand that this function doesn’t conduct any mathematical operations on the cell values. Its primary purpose is to count the occupied cells within a designated range.

By utilising the highlighted formula, you can effortlessly determine the count of active cells within your spreadsheet.

8. SUMIF

The SUMIF formula in Excel, represented as =SUMIF(range, criteria, [sum range]), adds values within a specified range meeting a criterion.

Whether calculating profits from leads in specific area codes or summing salaries above a threshold, manual calculations are time-consuming. But with SUMIF, it’s easier to add up cells meeting specific criteria, like salary thresholds.

Formula: =SUMIF(range, criteria, [sum_range])

Range: The tested range is based on your criteria.

Criteria: Determines which cells in Criteria_range1 will be added.

[Sum_range]: Optionally adds another range of cells to the first Range entered. This field is optional.

9. TRIM

The Excel TRIM formula, written as =TRIM(text), serves to eliminate any leading or trailing spaces within the specified text. For instance, if a cell like A2 contains the name ” Steve Peterson” with undesired spaces before the first name, applying =TRIM(A2) would yield “Steve Peterson” without any extraneous spaces in a separate cell.

Modern workplace efficiency often relies on email and file sharing. However, productivity can be hindered when receiving documents with irregular spacing. These unexpected spaces not only impede data searches but also impact calculations involving numerical columns.

Instead of manually correcting spacing inconsistencies, the TRIM function proves invaluable. It efficiently removes excess spaces from data, preserving single spaces between words.

The syntax is as follows:

=TRIM(text)

Where ‘text’ represents the text or cell containing the data requiring space removal. 

10. RANDOMIZE

An insightful analogy draws parallels between Excel’s RANDOMIZE formula and shuffling a deck of cards. In this analogy, the deck represents a column, while each card, totalling 52 in a standard deck, corresponds to a row. Steve McDonnell elucidates, stating that to shuffle the deck digitally, one can generate a new column of data, filling each cell with a random number and subsequently sorting the workbook based on this random number field.

In marketing applications, this functionality proves valuable for scenarios such as assigning random numbers to contact lists. For instance, in experimenting with a new email campaign where selection criteria must remain blind, random number assignment becomes essential. By associating numbers with contacts, one can implement rules like, “Contacts with a generated number of 6 or above qualify for inclusion in the new campaign.”

The formula at play here is RAND(), which generates a random number between 0 and 1.

To execute this process, begin with a single column containing contacts. Adjacent to this column, input “RAND()” (excluding the quotation marks) in the first row adjacent to the top contact, extending downwards.

For more control over the range of assigned numbers, RANDBETWEEN can be utilised. For example, if one desires numbers between 1 and 10, the formula would be set accordingly.

Also read:  15 Excel Tips You should Definitely Master in 2024

Winding It Up

In the marketing arena, utilising Excel is nearly unavoidable. Nevertheless, navigating its complexities doesn’t need to be overwhelming. As the saying goes, practice breeds proficiency. The more you acquaint yourself with these formulas, shortcuts, and tips, the more seamless your workflow becomes. Excel formulas serve as a cornerstone, providing a versatile toolbox for data analysis, process optimisation, and actionable insights.

Whether it’s employing VLOOKUP for data retrieval or harnessing SUMIFS for conditional summation, comprehending these functions empowers marketers to efficiently extract valuable insights. Embrace the potential of Excel and hone your expertise to enhance your marketing strategies. Ready to excel in Excel? Take the plunge on the Accelerator Program in Business Analytics and Data Science or Advanced Certification Program in Science & Analytics Powered by The University of Chicago. The University of Chicago and unlock the transformative capabilities of its formulas!