Everything You Need to Know about Microsoft Excel Formulas and Functions

In this article, I will explain everything you need to know about Microsoft Excel Formulas and Functions. After reading this tutorial, you will be well on your way to becoming a Excel Ninja.

Microsoft Excel Spreadsheet Formulas

In this tutorial, I will tell you everything you need to know about Microsoft Excel spreadsheet formulas.

Microsoft Excel is a wonderful tool used by professionals worldwide. It is the most popular spreadsheet application and is available on Windows and Mac OS X. The latest version of Microsoft Excel is Excel 2010.

Microsoft Excel also includes a programming component based on Visual Basic for Applications.

Excel also includes a wide range of spreadsheet formulas which you can use to automate your workflow and make it easier along with macros. After completing this tutorial, you will know everything there is to know about Excel formulas and functions.

You can use either direct values in Excel: values like numbers (1,2,3,4.5,12.10 etc) or strings ("BrightHub", "Pathik", "Awesome").

You can also use cell values as variables. For example, to use the value in the first cell as a variable, use A1. For the cell in the first row and 5th column, use E1.

All Excel formulas and functions start with a "=" sign.

For example, =SUM(A1,B1) will give you the sum of the values in the cells A1 and B1. You can enter the formula syntax in the function bar on the top.

I will list all the basic in Microsoft Excel Spreadsheet Formulas and Functions.

Mathematical Formulas in Excel

SUM(value1, value2)

This returns the sum of the 2 values.

For example,

=SUM(1,2) returns 3

=SUM(A1, B1) returns the sum of the values stored in A1 and B1

PRODUCT(value1, value2)

This returns the product of the 2 values.

For example,

=PRODUCT(2,3) returns 6

=PRODUCT(A1, B1) returns the product of the values stored in A1 and B1

POWER(value1, value2)

This returns the value of value1 raised to the power of value 2.

For example,

=POWER(2,3) returns 8

=POWER(A1, B1) returns the value of value1 raised to value2.

RAND

This returns a random number between 0 and 1

=RAND() could return 0.53545 or 0.98273

ABS(value)

This returns the absolute positive value of the value or number entered.

For example,

=ABS(5) returns 5

=ABS(-10) returns 10

CEILING(value, significance)

This returns the value of the number entered rounded up to the nearest multiple of significance.

The significance is the unit you want to round it up to. CEILING always increases the absolute value of the number

For example,

=CEILING(5.4,1) returns 6

=CEILING(-10.2,-1) returns -11

FLOOR(value,significance)

This returns the value of the number entered rounded down to the nearest multiple of significance.

The significance is the unit you want to round it down to. FLOOR always decreases the absolute value of the number

=FLOOR(5.4,1) returns 5

=FLOOR(-10.2,-1) returns -10

GCD(value1, value2)

This returns the greatest common divisor (GCD) of the two numbers.

For example,

=GCD(20,15) returns 5

=GCD(A1,B1) returns the GCD of the values stored in A1 and B1

LCM(value1, value2)

This returns the least common multiple (LCM) of the two numbers.

For example,

=LCM(20,15) returns 60

=LCM(A1,B1) returns the LCM of the values stored in A1 and B1

LOG(value1, base)

This returns the log of the value to the base. If no base is specified, the default base is 10.

For example,

=LOG(10) returns 1

=LOG(10,2) returns 3.321928

Besides these, there are also the trigonometric formulas.

The most basic ones are

PI

This returns the value of the trigonometric constant Pi.

=PI() returns 3.141593

SIN(value1)

This returns the sine value of the variable specified in radians

=SIN(10) returns -0.54402

COS(value1)

This returns the cosine value of the variable specified in radians

=COS(10) returns -0.83907

TAN(value1)

This returns the tangent value of the variable specified in radians

=TAN(10) returns 0.648361