The best Excel formulas to speed up your work

This lack of knowledge reveals a significant skills gap. The Multiverse Skills Intelligence Report 2024 found that 57% of employees have no or only basic Excel skills, despite its widespread use. As Euan Blair, CEO of Multiverse, observes, “Workers are wasting hundreds of hours a year because they lack the skills to handle data effectively.”

The good news? Mastering Excel formulas can boost your productivity, helping you build and manage spreadsheets faster while reducing errors. In this guide, we’ll explore the most useful Excel formulas, grouped by function.

An Excel formula is a set of instructions entered into a cell to perform calculations or actions on your spreadsheet data.

Formulas are incredibly useful shortcuts that let you automate complex or repetitive tasks. For example, manually adding 1,000 financial transactions might take hours, especially if you double-check your work. With the SUM function, you can calculate the total accurately in a few seconds.

Every Microsoft Excel formula begins with an equal sign. This symbol signals to the platform that you’re entering a formula instead of plain text.

A formula’s anatomy may also include some or all of these components:

Let’s say you have a spreadsheet with customer demographic data, and you want to calculate the average income. If this data is listed in column B, your formula would look like this:

=AVERAGE(B2:B120)

If we analyse this formula’s anatomy, AVERAGE is the function, and B2 and B120 are the references.

There’s a reason why Excel is so popular: It’s one of the most versatile tools in the modern workplace.

Data analysis is one of the most common Excel applications. Professionals use this platform to analyse a wide range of numerical and qualitative data. Depending on your role, you might use Excel functions to identify sales trends or measure employee productivity. The software can also transform the results into data visualizations like charts and graphs, making it easy to share findings with colleagues or clients.

Additionally, Excel formulas can help you make more strategic decisions. For instance, the IF function allows you to test two scenarios: one with a TRUE result and the other with a FALSE result. This what-if analysis allows you to explore the potential outcomes of different scenarios, such as decreasing your budget or adjusting pricing.

Excel formulas can also save time in the workplace, leading to significant productivity increases. According to the Multiverse Skills Intelligence Report, employees waste an average of 4.34 hours weekly — or a staggering 25 work days per year — completing data tasks inefficiently. With the right formulas, you can speed up these tasks and use the time you save on more critical activities.

You might assume that you need to become a Data Scientist to use Excel functions, but that’s not the case. With just a day or two of practice, you can learn these basic formulas and quickly upskill.

Excel users often need to perform calculations with numerical values. Instead of reaching for a calculator, you can use these simple Excel functions to get results quickly.

Use the SUM formula to add the values of cells. Source: Microsoft Support(opens new window).

The SUM formula is =SUM(A1:A10). It adds the values in a given range of cells. Use it to quickly tally up numerical data, such as sales and inventory quantities.

The AVERAGE formula is =AVERAGE(A1:A10). As the name suggests, it adds up all the values in a range and calculates the average. For example, you could use it to find the average purchase amount or the average competitor pricing.

To calculate the number of numeric entries, use the COUNT function, which is =COUNT(A1:A10). This formula is handy when you need to count specific data points, such as the number of financial transactions or returns.

If you spend a lot of time working with text in Excel, you know how tedious it can be to edit individual cells. With text formulas, you can manage qualitative data as efficiently as numeric values.

The CONCATENATE function is =CONCAT(A1,B1). It lets you combine text from two or more cells into a single string. Say, for instance, you entered customer street addresses and cities into separate cells. With this function, you can easily combine them for faster shipping or improved data analysis.

You can remove extra spaces from text with the TRIM function, which is =TRIM(A1). This formula is helpful when you accidentally add double spaces before, between, or after words.

Excel formulas also allow you to quickly change the case of text. The UPPER formula is =UPPER(A1), which converts the entire cell to uppercase. For the opposite effect, use the LOWER formula, which is =LOWER(A1).

There are many reasons why you might need to add the current date to a spreadsheet. Maybe you’re working on a financial analysis with time-sensitive data or counting down the days until a deadline. Use one of these formulas to add the date:

These are dynamic formulas, which means they’ll update every time you open or refresh the spreadsheet. That way, you don’t need to change this information manually.

Once you’ve mastered basic Excel formulas, you can learn how to perform complex calculations. These advanced formulas can drastically improve your productivity and strengthen your data analysis skills.

Businesses often create vast Excel spreadsheets with hundreds or even thousands of columns and rows. Manually locating specific data points in this sea of information can be frustrating and overwhelming. Lookup functions solve this issue by allowing you to quickly find data.

The VLOOKUP function searches for values in a table by column. To calculate it, enter =VLOOKUP(value you’re searching for, table, col_index, [range_lookup]). For example, you might use this formula to find a customer’s name based on their phone number or locate an employee’s salary by their ID number.

The INDEX function returns the value of a given location in a range of cells. Its formula is =INDEX(array, row_number, col_numb). Use it to retrieve data from a specific place in a table. If you sort products by the number of sales, for instance, you might look up the price of an item from the bottom of the list.

Before using the INDEX function, you must identify the data’s exact data. Enter the MATCH function, which is =MATCH(lookup_value, lookup_array, [match_type]). It finds the location of an item — such as an employee or product — within a range. Together, INDEX and MATCH are two of the most important Excel formulas because they let you pinpoint any data in complex spreadsheets.

A conditional formula lets you analyse data that meets specific criteria. These Excel functions return different results based on whether the condition is true or false.

You can perform logic tests with the IF formula, which is =IF(condition, value_if_true, value_if_false). Say, for instance, you offer an exclusive discount to customers who spend over $500 in a year. If you place a client’s total spend in column B2, your formula will look like this: =IF(B2 > 500, “Discount”, “No Discount”).

Similarly, SUMIF adds cells if they meet certain criteria. Calculate it with =SUMIF(range, criteria, [sum_range]). Suppose you want to calculate the total earnings for Sales Representatives who have met a $200,000 revenue target and earned a $10,000 commission.

If you store their annual revenue in A2 to A13 and the commission amount in the B column for people who meet the target, your formula would be: =SUMIF(A2:A13, “>200000”, B2:B13). This formula will only calculate the total earnings for Representatives who earn over $200,000 in revenue.

Advanced Excel formulas let you perform complex data analysis with the click of a button. Here are a few functions often used to gain business insights from datasets:

For example, you could use the MAX and MIN functions to identify the marketing campaigns with the highest and lowest returns on investment.

Using formulas in Excel can seem intimidating, especially if you’re unfamiliar with this platform. But don’t worry. Our Excel tips will help you create formulas that boost your productivity and lighten your to-do list.

Whether you’re using basic or advanced Excel formulas, you must format them properly to get results. Set yourself up for success with these best practices:

A spreadsheet can change drastically over time as you add or delete data. Using cell references instead of hardcoded numbers allows your formulas to adapt to these changes automatically.

Say, for instance, your annual membership is in column A, and you add a $30 fee. Instead of writing =A2 +30, you could add the fee in column B and write =A2 + B2. That way, the sum will automatically update if you change the membership or fee in the future.

You can also create named ranges to improve readability and accuracy. For example, instead of referring to a column as “=Sheet1!$B$2:$B$12”, you could simplify it by naming the selected range “Earnings.” This easy step can help you interpret spreadsheets faster and with more confidence.

While Excel formulas can perform sophisticated calculations, they’re only accurate if you set them up correctly. You can improve precision with Excel’s built-in tools, such as error checking and formula auditing. Testing formulas on small data sets can also help you catch and fix any issues before scaling them to larger ones.

Professionals can use Excel formulas in many different roles. For instance, Business Data Analysts can apply SUMIF and VLOOKUP to track budgets and identify specific expenses. Similarly, Operations Managers can monitor project timelines and transportation schedules with date functions like TODAY()(opens new window) and DATEDIF(opens new window).

Excel formulas also allow marketers to analyse campaign performance. This might involve using the AVERAGE function to measure engagement levels or COUNTIF to identify email campaigns that achieve specific open rates.

Whether you use Excel every day or only occasionally, learning formulas can open new opportunities for career growth. These functions empower you to manage and analyse data more efficiently, helping you work smarter and make educated decisions.

Sharpen your skills by experimenting with both basic and advanced Excel formulas. As you gain confidence, you can explore more powerful data tools, such as Microsoft Power BI(opens new window) or artificial intelligence-powered(opens new window) analytics software.

While it’s completely possible to learn Excel formulas on your own, a structured program can help you gain more sophisticated data science skills. Multiverse’s free Data and Insights for Business Decisions apprenticeship is the perfect opportunity to level up your skills and master new tools. You’ll learn how to analyse data with AI, predictive analytics, and other advanced methods.

Are you ready to expand your technical skills and advance your career? Apply today(opens new window), and the Multiverse team will get in touch to discuss the next steps.