Excel Basic Formulas
1. SUM
Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)
The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers
together. You can use cell references as well in this formula.
The above shows you different examples. You can have numbers in there separated by commas
and it will add them together for you, you can have cell references and as long as there are
numbers in those cells it will add them together for you, or you can have a range of cells with a
colon in between the 2 cells, and it will add the numbers in all the cells in the range.
2. COUNT
Formula: =COUNT(A1:A10)
The count formula counts the number of cells in a range that have numbers in them.
2
The count formula only works with numbers though:
It only counts the cells where there are numbers
3. COUNTA
Formula: =COUNTA(A1:A10)
Counts the number of non-empty cells in a range. It will count cells that have numbers and/or
any other characters in them.
The COUNTA Formula works with all data types.
It counts the number of non-empty cells no matter the data type.
3
4. AVERAGE
Formula: =AVERAGE(number1, [number2],...)
The average function returns the average (arithmetic mean) of the arguments. For example, if
the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of
those numbers. (Range: Two or more cells on a sheet. The cells in a range can be adjacent or
nonadjacent.)
number1: Required. The first number, cell reference (cell reference: The set of coordinates that
a cell occupies on a worksheet. For example, the reference of the cell that appears at the
intersection of column B and row 3 is B3.), or range for which you want the average.
number2, ...: Optional. Additional numbers, cell references or ranges for which you want the
average, up to a maximum of 255.
5. MAX function
This function Returns the largest value in a set of values.
Formula: MAX(number1, number2,...)
Argument
Description
Remarks
number1,
number2,...
1 to 255 numbers for
which you want to find
the maximum value.
•
You can specify arguments that are numbers, empty
cells, logical values, or text representations of
numbers. The following arguments cause errors:
error values, or text that cannot be translated into
numbers.
• If an argument is an array or reference, only
numbers in that array or reference are used. Empty
cells, logical values, or text in the array or reference
is ignored. If logical values and text must not be
ignored, use MAXA function instead.
• If the arguments contain no numbers, this function
returns 0 (zero).
4
6. MIN Function
This function Returns the smallest number in a set of values.
Formula: MIN(number1, number2,...)
Argument
Description
Remarks
number1,
number2,...
1 to 255 numbers for
which you want to find
the minimum value.
•
You can specify arguments that are numbers, empty
cells, logical values, or text representations of
numbers. The following arguments cause errors:
error values, or text that cannot be translated into
numbers.
•
If an argument is an array or reference, only numbers
in that array or reference are used. Empty cells,
logical values, or text in the array or reference is
ignored. If logical values and text should not be
ignored, use MINA function instead.
• If the arguments contain no numbers, this function
returns 0 (zero).
5
7. Percentage
Displaying values as percentages
To show a number as a percent in Excel, you need to apply the percentage number format. To
apply this format, select the cells that need formatting, and then click the Percent Style button in
the Number group on the ribbon's Home tab.
Of course, it's even faster to use the keyboard shortcut for applying the format, which is Ctrl +
Shift + %.
In Excel, the underlying value is always stored in decimal form. So, even if you've used number
formatting to display something as a percentage (10%), that's just what it is — formatting, or a
symbolic representation of the underlying value. Excel always performs calculations on that
underlying value, which is a decimal (0.1). To double-check the underlying value, select the cell,
press Ctrl + 1, and look in the Sample box on the General category.
Calculating percentages
As with any formula in Excel, you need to start by typing an equal sign (=) in the cell where you
want your result, followed by the rest of the formula. So, I hear your next question in my head —
which formula do I use to get the result I desire? Well, that depends. The basic formula for
calculating a percentage is part/total = percentage, which might look something like this:
6
Now, let's say that you want to reduce a particular amount by 25%, like when you're trying to
apply a discount. Here's a formula that would get you that result. (Think of the 1 in the formula
below as a stand-in for 100%.)
To increase the amount by 25%, replace the minus sign in the formula above with a plus sign.
The next example is slightly more complicated. Say the earnings for your department are $2,342
in November and $2,500 in December. What is the percentage change in earnings between
these two months? To find the answer, divide the difference between December and November
earnings ($158) by the value of the November earning ($2,342). Then, format the difference as a
percentage with two decimal places.
