5 Boring Excel Functions That Are Secretly Brilliant

Hi ,

Some Excel functions have exciting names like XLOOKUP, FILTER, and LET. Others sound like they belong in a maths textbook.

Functions like ABS, SIGN, REPT, TRUNC, and CELL don't get much attention, but each one solves a surprisingly common problem. Better still, they're available in virtually every version of Excel, so you don't need Microsoft 365 to use them.

Let's look at five underrated Excel functions that can make your formulas smarter, your reports easier to read, and your spreadsheets more reliable.

1. ABS: Ignore the Sign and Focus on the Size

What Does ABS Do?

The ABS function returns the absolute value of a number by removing its plus or minus sign.

For example:

returns:

On its own, that might not seem very exciting. But used in the right situation, ABS can prevent some seriously misleading calculations.

Example 1: Calculating Year-on-Year Percentage Change

A common formula for calculating percentage change is:

This works perfectly for revenue figures that are always positive.

The problem comes when the prior-year figure is negative, such as expenses or losses.

Suppose travel costs increased from -$1,200 to -$1,450.

The dollar change is:

If you divide by the negative prior-year amount, Excel returns a positive percentage, suggesting performance improved when, in reality, costs increased.

The solution is to make the denominator positive:

Now the percentage correctly reflects whether the change was favourable or adverse:

Example 2: Checking Invoice Tolerances

Imagine a business allows supplier invoices to vary by up to 10% from the purchase order amount.

You don't care whether the invoice is higher or lower. You only care about the size of the difference.

ABS makes this simple:

Instead of testing for both positive and negative variances, ABS converts everything into a positive number so you only need one test:

2. SIGN: The Secret to OR Logic in SUMPRODUCT

What Does SIGN Do?

The SIGN function returns only three possible results:

At first glance, it doesn't seem particularly useful. But it becomes incredibly powerful when building logical tests.

Example: Avoiding Double Counting with SUMPRODUCT

Suppose you want to calculate total sales for books that:

A common approach is:

Because Excel treats TRUE as 1 and FALSE as 0, adding the tests together works well until both conditions are TRUE.

In that case:

When used inside SUMPRODUCT, those rows get counted twice.

SIGN fixes the problem:

The result:

Every qualifying row is included once and only once.

This is a brilliant technique whenever you're creating OR logic with SUMPRODUCT.

3. REPT: Create Charts Inside Cells

What Does REPT Do?

The REPT function repeats text a specified number of times.

For example:

returns:

Simple, but surprisingly powerful.

Example: Build Mini Charts in Cells

Suppose you have sales figures by region.

Instead of inserting a chart, you can create a visual directly inside the worksheet:

The result is an in-cell bar chart that grows and shrinks with your data.

These mini charts are great for:

You aren't limited to block characters either. Stars, circles, and shaded symbols all work well.

Click here for the full REPT function tutorial and learn how to build the dashboard above.

REPT is one of those functions that can make a report feel far more interactive without using a single chart object.

4. TRUNC: Remove Decimals Without Rounding

What Does TRUNC Do?

The TRUNC function removes decimal places without rounding.

Example 1: Split Dollars and Cents

Given a price of:

The formula:

returns:

The cents can then be calculated as:

which returns:

TRUNC vs INT

For positive numbers, TRUNC and INT return the same result.

The difference appears with negative values.

Consider:

TRUNC returns:

INT returns:

This is because:

When negative numbers are possible, this distinction becomes very important.

Example 2: Calculate Whole Items You Can Afford

Suppose you have:

The calculation:

returns:

But you can't buy one-third of a licence.

TRUNC gives you the number of whole licences:

which returns:

To calculate the money left over:

Or even better:

This same pattern works for:

5. CELL: Ask Questions About Your Worksheet

What Does CELL Do?

Unlike most functions, CELL doesn't perform calculations.

Instead, it returns information about a cell.

The syntax is:

For example:

returns:

You can also ask:

which returns:

Or:

which returns the cell's contents.

Example: Display the Current Worksheet Name Automatically

One of the most practical uses of CELL is creating dynamic worksheet titles.

The formula:

extracts the current sheet name from the workbook's full file path.

This is incredibly useful for:

Rename the sheet and the title updates automatically.

One thing to remember: the workbook must be saved first. Until the file has a name, CELL("filename") has nothing to return.

Final Thoughts and Next Steps

None of these functions are particularly impressive on their own.

The magic happens when you know when to use them and how to combine them.

This is the real power of Excel. It isn't about knowing one giant formula. It's about having lots of small techniques you can combine to solve problems elegantly.

Want to get better at writing formulas like these? My Advanced Excel Formulas course will help you stop guessing and start building clean, confident formulas you can trust.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub