Excel supports both full column , and full row references. A full row reference is a reference that refers to an entire row . For example, to sum all of the values in row 1, you can use a formula like this:
=SUM(1:1)
Notice a full row reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no columns in a full row reference, the literal translation of the range 1:1 is “every cell in row 1”. Like other ranges, a full row reference can include multiple rows. For example, to reference rows 1 through 5:
=SUM(1:5)
In the example shown above, the formula in cell C8 sums all quantities in row 5:
=SUM(5:5)
Here, 5:5 is a range that includes all 16,384 columns in a worksheet, so it includes 16,384 cells.
Pros and cons
Full row are easy to enter, and one big advantage of full row references is that they always include every cell in a row, so there is no need to update the reference if data is added later to a worksheet. Also, full row references are a very compact and easy-to-read way to represent a generic range, so they can be useful when explaining certain formulas.
However, when using full row references, you need to take care to ensure there aren’t stray values in other columns to the right (or left) of the target values. Any value, no matter how far down the worksheet, will be included. In addition, full row references can cause performance problems in some worksheets, since Excel can bog down calculating many thousands of unnecessary cells.
An Excel function is a pre-built formula with a specific purpose. Excel provides hundreds of functions in various categories like dates and times, text, statistics, lookup, financial, engineering, etc.
Most Excel functions require specific inputs, called function arguments . For example, the DATE function creates a date value based on three arguments: year, month, and day:
=DATE(year,month,day)
Some functions don’t require any arguments. For example, the TODAY function returns the current date without any input:
=TODAY()
Formulas with multiple functions
=A1+A2
Formulas can include multiple functions and/or constants. For example, the following formula returns a date 30 days from today:
=TODAY()+30
The formula below uses three different functions to calculate current age based on a birthdate in A1:
=INT(YEARFRAC(A1,TODAY()))
See this page for a full explanation of this formula.
Formulas vs Functions
When you use a function in Excel, you are using a formula. So, by definition, all functions are formulas. However, you can create formulas in Excel without any functions at all. For example, this formula returns the sum of A1 and A2 without any functions.
