Explanation
In this example the goal is to “unwrap” a column of values into separate fields. The values are spaced evenly apart, and the result should be all related values on one row, where each column corresponds to a field of information. The input data appears in column B. Each “record” in the data has three values: Name, Street, and City/State/Zip (combined). New records start every 4th row, and records are separated by an empty cell, which can be thought of as a fourth field that will be ignored. The formula in D5 is:
=TRANSPOSE(OFFSET($B$5,(ROW(D1)-1)*4,0,3))
As this formula is copied down, it extracts the values for each record into cells in a single row.
Note: the formula explained below builds on more basic examples explained here .
Collect fields
Working from the inside out, the core of the solution is the OFFSET function :
OFFSET($B$5,(ROW(D1)-1)*4,0,3)
The OFFSET function is designed to create a reference that is “offset” from a starting point by a given number of rows and columns . In addition, OFFSET has an optional width and height arguments , which specify the size of the reference to be returned.
The starting point inside the OFFSET function is the reference argument , provided as an absolute reference :
=OFFSET($B$5
The reference to B5 is locked so that it won’t change as the formula is copied down. The next argument is rows , which indicates the desired row offset from the starting reference. Rather than a hardcoded number, rows is provided as an expression that calculates the required offset:
(ROW(D1)-1)*4 // calculate rows offset
This is where n is provided as 4, in order to reference every fourth value. The ROW function is used to get the row number for cell D1. We start with D1, because we want to start with the number 1. We subtract 1, because we want the first rows offset to be zero. In other words, we want to zero out the rows argument and start with cell B5. As the formula is copied down the column, the value returned by ROW increments by 1, and creates the logic needed to reference every 4th value. See this formula example for a more detailed explanation .
The last two arguments provided to OFFSET are cols and height . Cols is hardcoded as 0 because we want to stay in column B. The height argument is hardcoded as 3, because each record contains 3 cells of information stacked vertically, and we are intentionally ignoring the empty cell between records. As the formula is copied down, the OFFSET function generates the following references:
OFFSET($B$5,(ROW(D1)-1)*4,0,3) // returns B5:B7
OFFSET($B$5,(ROW(D2)-1)*4,0,3) // returns B9:B11
OFFSET($B$5,(ROW(D3)-1)*4,0,3) // returns B13:B15
More examples of OFFSET here .
Transpose fields
The OFFSET function does almost all of the work in this formula, collecting all three field values for each record. However, the result from OFFSET is a vertical array of values, and we need a horizontal array as a final result. To convert the horizontal array into a vertical array, use the TRANSPOSE function. In the final formula, OFFSET is nested inside TRANSPOSE like this:
=TRANSPOSE(OFFSET($B$5,(ROW(D1)-1)*4,0,3))
OFFSET returns a vertical array like:
{"Micheal Lam";"228 James Street";"Minneapolis, MN 55420"}
And TRANSPOSE catches this array and changes it into a horizontal array like this:
{"Micheal Lam","228 James Street";"Minneapolis, MN 55420"}
Note the commas have replaced the semi-colons. The array is returned to cell D5 and the three values spill into the range D5:F5.
Explanation
This formula is a good example of nesting one function inside another. At the core, this formula uses the IF function to return a check mark (✓) when a logical test returns TRUE:
=IF(logical_test,"✓","")
If the test returns FALSE, the formula returns an empty string (""). For the logical test, we are using the COUNTIF function like this:
=COUNTIF(list,B5)
COUNTIF returns a count of how many times the value in B5 occurs in the named range list (E5:E9). If the value in B5 exists in the range E5:E9, COUNTIF will return 1. If not, COUNTIF will return zero. Excel’s standard behavior is to evaluate any non-zero number as TRUE, and zero as FALSE. So, If the value in B5 exists in E5:E9, COUNTIF returns 1 and IF returns a check mark (✓). If the value in B5 is not found in the allowed list, COUNTIF returns zero and IF returns an empty string (""), which displays nothing.
With hardcoded values
The example above shows allowed values in a range of cells, but allowed values can also be hardcoded into a slightly more complex version of the formula as an array constant like this:
=IF(SUM(COUNTIF(B5,{"red","blue","green"})),"✓","")
Notice that we need to provide the array constant as the criteria , and B5 as the range . This is because COUNTIF will not accept an array constant as the range argument. Because of this change, COUNTIF will return 3 counts (one for each value in the array constant) and we also need to wrap the SUM function around COUNTIF to catch the results from COUNTIF and return a final count.
Check mark character (✓)
Inserting a checkmark character in Excel can be surprisingly challenging and you will find many articles on the internet explaining various approaches. The easiest way to get the check mark character (✓) used in this formula into Excel is simply to copy and paste it. If you are copying from this web page, paste it directly into the formula bar to avoid bringing in unwanted formatting. You can also copy and paste directly from the attached worksheet.
If you have trouble with copy and paste, try using the UNICHAR function to insert a checkmark like this:
=UNICHAR(10003) // returns "✓"
UNICHAR (10003) returns a Unicode version of the checkmark: Unicode 2713 (U+2713) . The original formula can be written like this:
=IF(COUNTIF(list,B5),UNICHAR(10003),"")
Note: the UNICHAR function was introduced in Excel 2013.
Extending the formula
The basic idea in this formula can be extended in many clever ways. For example, the screenshot below shows a formula that returns a check mark only when all test scores are at least 65:
The formula in G5 is:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
The NOT function reverses the result from COUNTIF. If you find this confusing, you can alternately restructure the IF formula like this:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
In the version of the formula, the logic is more similar to the original formula above. However, we have moved the check mark to the value_if_false argument, so the check mark will appear only if the count from COUNTIF is zero. In other words, the check mark will appear only when no values less than 65 are found.
Note: you can also use conditional formatting to highlight valid or invalid input, and data validation to restrict input to allow only valid data.
