Saturday, June 4, 2011

About Formulas

Many of the Excel tips used in this blog are formulas that calculate or do other functions to data in rows and columns that you specify.  There are many resources on the web where you can learn how formulas work, so this is a very basic primer:

1) Formulas begin with a "+" or "=" followed by some sort of action or calculation that you want to do to data in adjacent column(s) or row(s) that you specify.

Simple calculation formulas use "+", "-", "*", or "/" to add, subtract, multiply, or divide numbers by other numbers.

2) The "range," or cells of data that you want to perform calculations or other activities on are referred to by a letter (column that data is in) and number (row that data is in).

For instance, in the image below of an Excel worksheet, to calculate the sum of "Number" for the first two lines of data, a simple calculation would be "=G2+G3".  This would be entered in any blank cell, for instance in cell H2 or H3.

More complex formulas use functions to do actions on data that you specify in parenthesis.  For example the SUM() function adds all the numbers in the cells that you specify in the parenthesis (i.e., the "range").  For instance "=SUM(G2:G14)" in the example above would return a value of 17.  A logical location to put this formula would be in cell G15 below all the data.  Other simple functions are AVG(), used to average numbers, and COUNT() used to count non-blank cells in a range.  More complex functions use IF statements, VLOOKUP statements, and many, many others.

In this blog, I will often show how a formula is written in the column to the right of (or row below) where a formula exists in a worksheet.  For the example above, Column C is showing the results of a formula which combines text from column A and B.  The text in column D (labeled "Formula") are the exact text that is typed in Column C to produce that result.

More information can be found at these useful sites and others:

Some of my favorite Excel help forums are:


  1. This comment has been removed by the author.

  2. Remembered you mentioned this before, and I finally needed it for a spreadsheet. Thanks.

  3. Glad you remembered it, could find it again, and found it useful!