MS Excel: Underused formulas you should know

Steven HarrisCST Manager
Published:
Updated:
With the growth of technology and the relative ease of use of programs such as Microsoft Excel, users are finding it more efficient to document and perform calculations.  If you were to open an Excel file, the odds are that you will see a variety of formulas ranging from the basic addition (+) and subtraction (-)  mathematical calculations to the more complex vlookup and hlookup logical formulas. With its growing popularity, Excel is constantly raising the bar and setting the standards for included functions and formulas with each new release.

Despite the popularity of Excel and the Formula Bar which guides you to creating those formulas, there are a few uncommon formulas that could ease your spreadsheet and calculation tasks.  Feel free to grab the attached workbook and follow along!

DATEDIF
The DATEDIF formula is used to calculate the difference between two dates, using a set interval.

Use:
=DATEDIF(date1,date2,interval)

date1 is the starting date
date2 is the ending date

Intervals can be any value from the following:
d – Days
m – Months
y – Years
yd – Days, excluding Years
ym – Months, excluding Years
md – Days, excluding Months and Years

Examples:
Start Date (cell E7) is 1/15/2011
End Date (cell E7) is 6/26/2013

=DATEDIF(E7, E8,”d”) would return a value of 893, representing 893 Days
=DATEDIF(E7, E8,”m”) would return a value of 29, representing 29 Months
=DATEDIF(E7, E8,”y”) would return a value of 2, representing 2 Years
=DATEDIF(E7, E8,”yd”) would return a value of 162, representing 162 Days, excluding Years (1/15 to 6/26)
=DATEDIF(E7, E8,”ym”) would return a value of 5, representing 5 Months, excluding Days and Years (Jan to Jun)
=DATEDIF(E7, E8,”md”) would return a value of 11, representing 11 Days, excluding Months and Years (15th to the 26th)


CONVERT
The CONVERT formula is used to translate a measurement system into another measurement.

Use:
=CONVERT(number,from_unit,to_unit)

Number is any numerical value.

The from_unit and to_units can be any form of measurement from within a category (Liquid Measurement, Force, Weight, etc.) to another unit in the same category.

For a complete listing of units, please visit the Microsoft Office Support pages.

Examples:
In cell E23 is the value 30.

To convert Inches to Feet, we would use =CONVERT(A1,”in”,”ft”) which returns a value of 2.5, representing 2.5 Feet in 30 inches.
To convert Hours to Seconds, we would use =CONVERT(A1,"hr","sec") which returns a value of 108000, representing 108,000 seconds in 30 hours.
To convert Inches to Meters, we would use =CONVERT(A1,"in","m") which returns a value of 0.762, representing 0.762 meters in 30 inches.

If you are following along in the provided workbook, you should note that the formula used in E24 contains cell references in place of the measurement unit values as described herein.


REPT
The REPT formula is used to repeat a value a given number of times.

Use:
=REPT(value,number_of_times)

Value is any text or numerical string value or cell reference to a any text or numerical string.

Number_of_times is any numerical value.

Examples:
In cell E36 is the value abc
In cell E37 is the value 4

=REPT(E36,E37) would return the string:  abcabcabcabc


SIGN
The SIGN formula is used to determine the (sign) of a number, either positive (1), negative (-1) or zero (0).

Use:
=SIGN(number)

Number is any numerical value.

Examples:
In cell E45 is the value: -351

=SIGN(E45) would return the value: -1 (meaning the number is negative)

If in cell E45, we use the formula: =(-4*19)/(2*-6)*4

=SIGN(E45) would return the value: 1 (meaning the resulting value is positive)


DOLLAR & USDOLLAR
The DOLLAR and USDOLLAR formulas are used to convert a number to currency format, with decimal rounding.

While the formulas work the same in functionality, they DOLLAR formula will use the local PC currency setting while the USDOLLAR formula will always be formatted to US $.

Use:
=DOLLAR(number,number_of_decimals)
=USDOLLAR(number,number_of_decimals)

Number is any numerical value.

Number_of_Decimals is any numerical value.

Examples:
In cell E53 is the value: 8596.1652
In cell E54 is the value: 2

=USDOLLAR(E53,E54) would return the value: $8,596.17

In cell E63 is the value: 456.256548
In cell E64 is the value: 4

=USDOLLAR(E63,E64) would return the value: $456.2565


While sticking to your normal formulas is always a safe fallback, don’t be afraid to get out there and experiment!  Your Excel Spreadsheet is only as advanced as the formulas and functions used to make it; so feel free to dive deeper into those Excel formulas and if needed, post a Question here on Experts-Exchange for any clarification or help that may be needed.
Underused-Formulas.xlsx
16
4,799 Views

Comments (9)

chrisyu Dstudent

Commented:
Great information !! I am a beginner in programming , this article helps me better understand a project - create excel formulas in C# on MSDN..

Commented:
Thanks Steve.

I never knew of the datedif function. I've added it to my cheat sheet

Commented:
I think you've got a typo:
Start Date (cell E7) is 1/15/2011
End Date (cell E7) is 6/26/2013

I'd say that you're End Date cell is cell E8

Commented:
Also, in my Dutch version of Excel (where DATEDIF is called DATUMVERSCHIL), the syntax has ; instead of ,
So in my case it's like this:  =DATUMVERSCHIL(e7;e8;"d")

Commented:
Datedif works on earlier versions of Excel back to the early 1990s, but was not documented.  It was introduced to mimic the functionality of the equivalent Lotus 1-2-3 @datedif to assist in users transferring to Excel.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community