5 Essential Excel Formulas to Save You Hours Every Week

Excel is a powerful tool, but with the right formulas, it becomes a game-changer for productivity. In this post, we’ll explore five must-know Excel formulas that can automate calculations, clean data, and make your life easier. Get ready to boost your efficiency and reduce manual work!

1. XLOOKUP: The All-in-One Lookup Formula

The XLOOKUP formula is a more flexible replacement for VLOOKUP and HLOOKUP. Use it to quickly find values in a table or range.

Example Use Case: Retrieve a customer’s phone number based on their name in a list.

Formula: =XLOOKUP(A10,B:B,F:F)

Lookup Value (A10): This is the cell containing the value we want to search for, which serves as the basis for retrieving information.

Lookup Array (B:B): This is the column where the lookup value can be found.

Return Array (F:F): This is the column containing the information we want to retrieve, such as a customer number, based on the criteria we established (e.g., customer name).

Pro Tip: Combine XLOOKUP with IFERROR to handle missing data gracefully.

To manage errors, you can include a custom message using IFERROR. For example, instead of displaying a messy #N/A, you can specify text like "Not Active Customer" or any message you prefer.

If the customer is not included in the list, their telephone number cannot be retrieved. Adding custom text helps make the error message clearer and more user-friendly.

Formula: =XLOOKUP(K2,B:B,F:F,"Not Active Customer")

2. TEXT: Format Numbers or Dates with Ease

The TEXT formula allows you to display numbers, dates, and times in a specific format.

Example Use Case: Convert a date into a custom format like "January 2025".

Formula: =TEXT(B1, "MMMM YYYY")

Value (Column B 'Added'): This is the value we want to transfer.

Format Text: Enter your custom format in quotation marks (e.g., the name of the month and year, or just the month).

Pro Tip: Combine this with CONCAT to create dynamic text like "Date Added: January 2025".

Formula: =CONCAT("Date Added: ", TEXT([@Added],"MMMM YYYY"))

3. IF + AND/OR: Automate Decision Making

These formulas allow you to apply logic to your data, making decisions automatically.

Example Use Case: Flag invoices as "Overdue" if today’s date is past the due date.

Formula: =IF([@[Invoice Due Date]]<TODAY(),"Overdue","On Time")

This means that if the selected date (C2 'Invoice Due Date') is earlier than today, the status will automatically be marked as "Overdue." Conversely, if the date is later than today, the status will be automatically set to "On Time."

Pro Tip: Use AND or OR for more complex conditions, like overdue invoices only if the balance is greater than zero.

Formula:

=IF(AND([@[Invoice Due Date]]<TODAY(),[@[Account Balance]]>0),"Overdue","On Time")

Logical1: This condition checks if the Invoice Due Date (D2) is earlier than today.

Logical2: This condition checks if the Account Balance (C2) is greater than £0.

4. UNIQUE: Instantly Remove Duplicates

The UNIQUE formula creates a list of distinct values from a range.

Example Use Case: Extract a list of unique product codes from sales data.

Formula: =UNIQUE(A2:A10)

Pro Tip: Combine UNIQUE with SORT to organise the data alphabetically.

Formula: =SORT(UNIQUE(A2:A10),,1)

The '1' in this formula represents the sort order, which is ascending. To sort in descending order, use '-1' instead.

5. LEN + TRIM: Clean Up Messy Data

These formulas are essential for cleaning data with extra spaces or inconsistent formatting.

Example Use Case: Remove unwanted spaces and check the length of a text string.

Formulas:

Remove spaces: =TRIM(A2)

Count characters: =LEN(A2)

Pro Tip: Use these before applying other formulas to ensure your data is clean and reliable.

These five formulas are just the tip of the iceberg when it comes to Excel's capabilities. By mastering them, you’ll save time, minimise errors, and unlock the full potential of your spreadsheets.

Which of these formulas do you use most often? Let us know in the comments, or share your favorite Excel tip with us!