Excel Tables provide a special kind of formula syntax called a structured reference . In a nutshell, structured references makes it possible to refer to parts of an Excel Table by name, so you can write formulas like this:
=SUM(Table1[Total]) // sum total column
=MAX(Table1[Total]) // max of total column
One of the more complicated examples of this syntax occurs when locking column references. Although it may not look like it, a reference to a table column is relative and will change as a formula is copied. For example this formula entered in G5:
=SUM(Table1[Qty])
will change to:
=SUM(Table1[Price])
when the formula is copied to H5. How can we look a column reference to stop it from changing?
Unlike a standard absolute or mixed reference , you can’t use the $ sign to lock a table column reference. Instead, you must duplicate the column name in another pair of square brackets ([]). For example, in the worksheet shown, the formula in G5 is:
=Table1[[Total]:[Total]]*G4 // locked column reference
This effectively locks the reference to Table1[Total] so that it will not change as the formula is copied. This allows the formula in G5 to be copied to H5 and I5 and keep working correctly.
Note: because this example uses a dynamic array formula , it is not necessary to lock the row 5, which contains the percentages.
- Video: Introduction to structured references
- Video: How to copy and lock structured references
Excel’s logical operators are used in formulas to perform comparisons, and to build formula criteria . Logical operators can be used in formulas on their own, or combined with each other and/or other functions. The table below lists the logical operators available in Excel:
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | =A1=10 |
| <> | Not equal to | =A1<>10 |
| > | Greater than | =A1>100 |
| < | Less than | =A1<100 |
| >= | Greater than or equal to | =A1>=75 |
| <= | Less than or equal to | =A1<0 |
Note: all Excel formulas must begin with an equal sign (=). This is a syntax requirement, not a logical comparison.
Math operators are listed here . See this list of formulas for many examples of logical operators in formulas. Note that a text value is greater than any number in Excel.
