Friday, January 22, 2021
Home Formulas & Functions

Formulas & Functions

Virtually everything business users do with Excel involves worksheet formulas and functions. And this category concentrates on that topic.

This category also includes what Microsoft calls “Names”—which many of us call “Range Names.” More accurately, however, “Names” are named formulas.

Check tags for information about specific functions.

Excel's range names offer great power and flexibility. And they're not hard to use. Here's an introduction to the power that Excel provides.

Introducing the Power of Excel Range Names

Many Excel users seldom use range names. This fact always surprises me because Excel names are powerful, flexible, and easy to use. Excel offers two...
Most loans and many investments are annuities, which are payments made at fixed intervals over time. Here's how to use Excel to calculate any of the five key unknowns for any annuity.

Excel’s Five Annuity Functions

“Help!” the message said. “I know the payment, interest rate, and current balance of a loan, and I need to calculate the number of...
Does Excel's INDEX worksheet function work for you only some of the time? Here are ways to learn why your INDEX function isn't working as you expect.

How to Debug INDEX in Excel

"I'm not a very experienced Excel user. Why does = INDEX(...) sometimes work and sometimes not.... :-)?" -- Suzan G. Probably the best way to...
With the help of three functions most Excel users seldom use -- INT, MOD, and CEILING -- you can round Excel time values to any increment you want.

How to Round Time in Excel to Any Increment

Several years ago, a reader asked whether it's possible to use the ROUND function to round times quarter- or half-hour increments. I gave him a...
INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster. Here are six examples.

How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can’t Do

In this three-part series, I'm showing you how to use INDEX-MATCH. In Part 1, I showed you how to use the INDEX function. In Part...
The Rule of 72 is a guesstimate of how long it will take an investment at a specific interest rate to double in value. But how accurate is it? Ask Excel.

Excel and the Rule of 72

I recently learned that my son—who's an avid amateur investor—had never heard of the Rule of 72. The rule of 72, I texted him, says...
Mortgage lenders give you many options for your interest rate and amortization period. This workbook helps you to choose from among them.

Compare Loan Payment Options in Excel

When you’re comparing your payment options for mortgage loans, your choices can be overwhelming... When in St. Ives, I found a home, Then looked at seven...
Here's how a few Excel formulas can move data between Excel's most widely used table designs to transfer data from source files into a more usable form.

How to Read and Update Excel Tables Using SUMIFS and INDEX-MATCH

A growing number of Excel users in business are linking their reports and analyses to Excel tables. By doing so, they can update them...
This table summarizes the performance of Excel's worksheet functions primarily designed to work with dates

Overview of Excel’s Date Functions

At last count, Excel 365 offers 25 worksheet functions that return date or time information. The links are to the Microsoft help topics for each...
How to aggregate named groups of GL accounts.

How to Report GL Account Groups in Excel

Believe it or not, this income statement is quite sophisticated. It's not nearly as simple-minded as it looks. In fact, this income statement illustrates a...

Latest Articles

We compare Excel's five columnar lookup functions.

XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

SUMIFS? Really? Why is SUMIFS included among those lookup methods? I'll explain in a few minutes. But first, let's look at the lookup methods shown in...
How to aggregate named groups of GL accounts.

How to Report GL Account Groups in Excel

Believe it or not, this income statement is quite sophisticated. It's not nearly as simple-minded as it looks. In fact, this income statement illustrates a...
If you want all your Excel reports, analyses, forecasts, and other Excel work to be highly productive, this is the only strategy that will work for you.

An Introduction to Excel Data Plumbing

Would you like to: Create your new reports, analyses, forecasts, and other Excel work quickly? Update your Excel work with one command, without using...
Advertisement
Advertisement