To create a table, first select a range containing the data that you want to include in the table.

…or press ctrl-T. If you’re a Mac user, this keyboard shortcut won’t work. This key combination is configured to toggle between absolute and relative cell references by default on Office for Mac.

Since Excel was kind enough to bring up the topic of headers (via the checkbox below), I’ll note at this point that tables require you to use unique text column headings, as Excel will be using these to reference ranges of data within the table.

If you select a cell within the table, Excel will allow you to access the Table Design tab in the ribbon. Here you will find a field (on the top left) where you can type a customized table name. Remember that the point of all this is to make data easier to reference in formulas, so try to use the minimum number of characters that are adequately descriptive.

If you need to add a row or a column, Excel will automatically expand the table’s range if you type data into a cell adjacent to the table’s current range. Alternatively, you can click and drag the little blue tab that lives just inside the bottom right corner of the table.

Now, you can easily reference the data in any column (TableName[ColumnName]), just the headers (TableName[#Headers]), all data in the body of the table (TableName), or the entire table including headers (TableName[#All]), and much more, all from the comfort of your friendly neighbourhood formula bar.

And one final, very useful, tip – tables obviate the need to reference the worksheet on which the table is located. This is a great feature to make your formulas cleaner, but it can be jarring the first time you ‘lose’ a table on a distant sheet.

If you press F5, it brings up Excel’s “Go To” dialog box. Here, you will see a list of every table in your workbook. Simply select the name of a table and click OK to navigate directly there.

The formula in column A is obnoxious to type. I have to either navigate to Sheet1 and manually select the range, or conform to Excel’s convoluted reference naming conventions (that is, if I’m lucky enough to remember the column letter where my data is located).

With a table reference, as illustrated in column C, I can get the same result with less typing and I’ll have a better idea of what I’m looking at when I revisit the formula later.

The formulas in columns E and G show what Excel sees when you specify two different ranges. The orange operators show how Excel conducts the operation between two ranges when you specify the formula in column I. As noted in the text preceding this screenshot, remember: one of the most compelling reasons to use tables in conjunction with array formulas is that they will prevent you from accidentally specifying an operation between ranges of different sizes because each of the columns in a table will have identical dimensions.

I highly recommend playing around with operations on ranges of different sizes until you’re comfortable with their behaviour and where errors occur – that (moderately boring) exercise will make you considerably more efficient at troubleshooting errors returned by a single-cell array formulas.

So I was using Excel in French mode today (if you can really call it that…) and it seemed fitting to give Quebec a little love.

In this example, you will see (aside from the use of semi-colons instead of commas) that I’ve set up a multi-cell array formula to test whether each company is situated in Quebec. This shows a useful feature of the IF() function – that is, to conditionally replace data in a range with a desired value.

Here, we see a combination: if a company is in Quebec, the IF() formula returns the corresponding value from the revenue column; if not, it returns a zero.

Importantly, Excel observes order of operations such that formulas are calculated after operations on ranges – in this case, Excel calculates profit margins for each company and then passes the array to the logical_test in IF(). The function then operates on each value and returns the specified text values.

Finally, I’ve set up a slightly more interesting formula to illustrate where we’ll be heading in the next lesson. The ––()*()<>0 structure is explained in a bit; suffice to say that it’s the array formula equivalent of the AND() function. And to make it doubly odd, you can see that the results, when displayed in French, use commas instead of the usual decimal dots.

This formula tests for companies in Quebec that are profitable. Note also that it mixes the types of data that it returns. That is, if the conditions are both true, IF() returns the numerical profit margin of the company; otherwise, it returns a null text value.

The formula in column A is shown for convenience – this column of data is referenced in each of this lesson’s ‘steps’.

Shifting our gaze to column D, the formula tests if each company’s revenues are over $1,000,000. The first thing to notice is that Excel returns values of TRUE and FALSE when performing a logical operation (e.g. =, >, <, >=, <=, <>). There are also a few functions that return this type of value (e.g. ISERROR(), ISNUMBER(), etc.). Someday, I’ll compile a list of them and pontificate at length. In Excel parlance, this type of data is referred to as ‘logical’ values, and it is stored (and operated on) as a distinct type of data (e.g. numbers, dates, text, logical).

Note that logical operators are actually treated very similarly to mathematical operations – prepare to embrace the awkwardness of BEDMASL. As we’re all well aware, Excel respects the order of operations such that operations enclosed in Brackets are performed first, then Exponents, followed by Division, Multiplication, Addition, and Subtraction. Logical operations happen last, and they return logical values. Not numbers.

If we were really sneaky, we might find it useful for a logical operation to return 1 or 0 based on a logical operation. Look no further. If you perform a mathematical operation on a logical value, it behaves as though TRUE=1 and FALSE=0. In this case, I’ve invoked what I call the double negative (it’s got a real name, too: it’s a double unary operator). Mine’s less weird.

So what happens if we want to use an AND() function as part of a logical test? No dice.

It is, of course possible to achieve the intended result, but you have to get your hands a bit dirty to get there. The screenshot below shows how to use arithmetic, with an eye on order of operations, to create the intended result. Although arbitrary, I like to use <>0 to conclude expressions because it can also be used with a similar workaround for OR().

This formula shows that using addition, instead of multiplication, converts the ‘and’ expression into an ‘or’ condition. These can be applied in any combination you see fit to construct.

So… now begins the cautionary part of the tale. This formula asks Excel to evaluate whether the value returned by a logical expression is not equal to zero. Logical operators are different from mathematical operators in that they do not automatically translate logical values into 1 or 0. When Excel tests whether a value of FALSE is not equal to zero, it returns TRUE, because FALSE is, in fact, not equal to zero.

Here, I’ve solved the problem illustrated in the previous step. Using the double negative structure allows you to add and remove logical tests at will, without worrying about the formula breaking if you happen to delete all but one logical expression.

For anyone as paranoid as me about data integrity, I recommend using the full expression whenever implementing arithmetic-based logical operations:

––(logical_test1)*(logical_test2)<>0

While we’re on the topic of Excel behaving weirdly, check this out – for some reason, Excel’s SEARCH() function returns an error if it does not find a match for your search term in the search text.

Not only is this annoying, it can be difficult to figure out where, in a more complex single-cell array formula, an error might originate. Always keep an eye out for functions that have behaviours that can push unwelcome surprises (errors) into the results of array calculations.

However, there is virtually always a way. In combination with an IF() function, this logical expression can be used to catch errors and prevent them from being passed on to another function/operation/formula.

The first example just takes a quick look at revenues for companies that make more than $1 million. It bears repeating (from a passing mention in Array Formulas 101) that statistical functions in Excel ignore any text (or blank) values in the ranges that they operate on. Because IF() returns “” (specified in the value_if_false parameter), this formula performs exactly the same function as a filter in a pivot table.

The next few examples show a couple of ways that you could compute averages in an analysis of profitability.

The formula in row 4 takes the total profit for the entire filtered sample and divides by the total revenue. This gives a profit margin for the sample as a whole, ignoring whether or not revenue tends to be concentrated among a few companies or widely distributed within the sample.

The formula in row 5 calculates average profitability on a company-by-company basis, regardless of the volume of sales at any one company. The difference between this and the previous calculation already tells us that companies with more sales volume are slightly less profitable than their smaller counterparts.

The formula in row 6 calculates the median in this sample – note that this formula is identical to the one that precedes it, replacing only the enclosing AVERAGE() with MEDIAN().

The example in row 7 is similar to the formula immediately above it in column B, except that it multiplies revenue and expenses by the percentage of each that relate to activity A. The result is the overall profitability of activity A within the sample as a whole.

Using a similar calculation to that appearing in row 5, the formula in row 8 finds an n-weighted average on a per company basis.

The formula in row 9 is, like the one above it, identical to the preceding example except for the enclosing MEDIAN().

The final set of examples are a little more interesting. While profitability is naturally weighted by the sales volumes of companies when calculated in aggregate, it requires a bit of manipulation to weight happiness in proportion to revenue. In this formula, happiness is multiplied by the total revenues at each company, and the total is divided by total revenues for the sample (thereby cancelling out the revenue factored into the numerator).

Used in tandem with a revenue-weighted average (as in row 10), this alternative weighting scheme gives insight into the distribution of happiness by profitability. For example, the disparity between this and the n-weighted average suggests that companies with more revenue are slightly less happy than the overall average. A quick comparison such as this may be a good indicator of whether a deeper analysis is a worthwhile pursuit.

Once again, the filter used to calculate the median in row 12 is identical to the formula that precedes it.

Rows 3-7 in the ‘Definitions’ pane (columns A-C), use a few normal formulas to find revenue thresholds that are then used to define company size. I’ve performed this step separately because it leaves the door open to adjust the definitions later. For example, if I’m going to use this analysis to estimate industry size, I might need to incorporate a set of definitions that will exclude outliers and/or match the company sizes to whatever we end up using as a proxy for the distribution of company size in the industry ‘universe’.

Still focusing on the ‘Definitions’ pane (columns A-C), I’ve created a table that links to the revenue thresholds established in the preceding calculation. Note that the first column of this table must be sorted in ascending numerical/alphabetical/chronological order because LOOKUP() is the only lookup function that is compatible with array formulas and it will not work correctly with unsorted data.

The analysis that extends rightward from the locked pane filters data by company size using the LOOKUP() function. If you recall, the punchline in Lesson 3, Step 1 pointed out that the IF() function can be used to conditionally replace data with values as you see fit. LOOKUP() takes this capability one step farther by testing each value in our source column of data against the leftmost column in a lookup table (in this case, the table in the locked pane). The function then returns the corresponding value from the rightmost column.

The screenshot below shows how Excel actually operates on a column of data using LOOKUP() and the company size definition table shown above. In the analysis above, the filter used in every formula to the right of the locked pane tests whether the result of the LOOKUP() function matches the value in the “Label” column in the same row.

This arrangement allows us to use the definition table labels twice – first, as the column from which LOOKUP() returns a value (as in the screenshot below); and second, it provides a value against which LOOKUP()’s result is tested. The benefit of using this approach is that if we ever need to add an extra row to the definitions, it’s just a matter of inserting a row, adding some new values in the definition table, and copying formulas down into the new row from the row above.

Apart from CONCAT(), which appears in column M, all of the functions used in this example were described earlier in this article.

The formula in column M shows how one might extract comma delimited lists from a data set. In this case, I’ve pulled the names of companies that are included in each size category. Returning lists can be extremely useful if your analysis requires that you identify data entries based on the same set of characteristics you’re using in a given row.

As in the previous lesson, this example begins by setting up a definition. This time, I’ve calculated the thresholds that will be used to group companies by profitability. Unlike the previous example, this means that I need to calculate profitability for each company before calculating the percentile thresholds. As such, these formulas will only work if they are entered as array formulas.

In the ‘Definitions’ pane (columns A-C), this table is set up identically to the previous example. In this instance, I’ve added an ‘Overall’ row that can be used to perform sumchecks.

The first crosstab (column E) shows the distribution of companies by profitability and company size. The column headings are provided by a multi-cell array formula drawing values from the “Label” column in the company size definition table. In the crosstab formulas, I’ve added a second condition which tests company size for a match against these column headings. Voila! Crosstabs!

The second crosstab (column J) uses a formula that returns the n-weighted profitability, also filtered by profitability (based on the row headings) and company size (based on the column headings). This formula is essentially identical to that used for the first crosstab, except that the value_if_true parameter in the IF() function is set to return each company’s profitability instead of a 1, the value_if_false parameter is set to “” (to hide these values from AVERAGE()), and the enclosing function is AVERAGE() instead of SUM().

In both crosstabs, I’ve used conditional formatting to display the data as a heatmap for a quick visual inspection. It appears that the profitability of smaller companies varies more widely than that of large companies.

While the first sumcheck is pretty self-evident, the second crosstab in this portion of the analysis presents an interesting problem with regard to sumchecks. Now that I’m working with average profitability by segment, I’d like to make sure that the values I’m looking at can be reconciled with the n-weighted profitability that was calculated previously.

Here, the total row (referring to the formula in cell J28) calculates the n-weighted profitability of each column. The sumcheck in cell N28 uses an array formula to test that these values match the corresponding n-weighted average profit margins calculated in the previous section of the analysis. Likewise, the sumcheck in cell N29 condenses the same operation into one cell (i.e. eliminating the need for a ‘total’ row).

This section uses a slightly different mechanism than anything we’ve looked at thus far – in this case, the data contains the province in which the company is located. While Ontario and Quebec are significant (and distinct) regions, I would like to group the prairie provinces together because they have fewer respondents overall, and it’s silly to generalize about Alberta based on the response of one company. In order to group the provinces together, I set up an (alphabetically sorted) lookup table that returns the name of the region with which I want to associate each province.

The formulas in column C simply allow me to check how many companies reside within each province in the sample, to verify that each region will have a sufficiently large n-value.

Although this lone column of labels is less impressive than the previous lesson, note that this is a point where you will need to be careful. If you edit the definition table above, this list must be updated so that every region is included in the list.

To protect this sheet from accidental breakage, the sumcheck in cell D46 tests whether the total number of companies included for all regions matches the total number of companies calculated from all provinces. If there is a mismatch, the first thing to check would be whether your list of regions contains all of those defined in the table above.

Just like the preceding lesson, the first crosstab (in column E) adds a second filter that looks up the company size in the appropriate definition table and compares it to the column headings.

The final crosstab in this example is interesting. In this case, I have data on the percentage of total revenue derived from, and total expenses directed to, each of three revenue streams. At this point, I’m interested to compare the profitability of each revenue stream in the various regions. This is where things get interesting.

To keep my layout consistent, I’ve decided to use column headings to retrieve data from a relevant column. If you take a closer look at the formula, you’ll see that it filters by region, and then returns profitability computed such that: revenues (expenses) are multiplied by the percentage of revenues derived from (expenses directed to) the revenue stream identified in the column headings. The INDIRECT() function receives a concatenated string that identifies the table column corresponding to the crosstab column heading.