S Anand

This is a series on what Google Spreadsheets can do that Excel can’t.

SPLIT(string, delimiter) splits a string using a delimiter. So if you have "one,two,three,four" in cell A1, you could split that into 4 cells using =SPLIT(A1,",")

That’s similar to Data > Text to Columns, except that if the original data changed, Text to Columns does not revise the output. SPLIT can give you dynamic text-to-columns. This is pretty useful when processing text data, in three ways:

1. You retain the original data
2. You don’t need to re-apply Text to Columns. Extending the formula will work (and that’s quicker)
3. It’s dynamic. If the data changes, your split changes

Since SPLIT returns an array, you can do a bunch of useful things with it.

=COUNTA(SPLIT(A1," ")) gives you the number of words in a string

=SUM(SPLIT(A1,",")) sums up a comma separated list. "1,2,3,4" is added up to 10.

=ARRAYFORMULA(SUM(LEN(SPLIT(A1,",")))) sums up the word lengths. So "one,two,three,four" splits into 4 words of length 3,3,5,4 each, which adds up to 15.

The ability to join and split also lets you sort by multiple keys. For example, say you had income by country and product. You want to show it sorted by Country & Product. You also want to show it by Product & Country.

So first take the data sorted by Country and Product.

Now, in column E, create a key that’s sorted by Product and then by Income. Type

=SORT(ARRAYFORMULA(B2:B10&":"&A2:A10&":"&C2:C10))

… in cell E2. That will give you all the data in one cell, sorted by Product and then by Country. Now, just split the data, as shown here.

Note: You could have done the whole thing using one formula:

=ARRAYFORMULA(SPLIT(SORT(ARRAYFORMULA(B2:B10&":"&A2:A10&":"&C2:C10)),":"))

But for some reason, this doesn’t seem to show the first row properly. No idea why.

Can I do that in Excel?

Well, not really. You’re best off creating a user-defined function to duplicate the SPLIT function.

This is a series on what Google Spreadsheets can do that Excel can’t.

To sort data, use the SORT function.

For example, if you have a list of products, their revenues and profits in A2:C9. Type SORT(A2:C9, 2, FALSE) in cell E2 to get the products sorted by the second column, revenues.

This is a dynamic list. If you change the revenues, the products are reordered automatically.

The first parameter to the SORT function is the data range you want to sort. The remaining parameters are optional. The second parameter is the column to sort by. By default, the data is sorted by the first column, in ascending order. In this example, we sorted by the 2nd column. The third parameter is FALSE for descending order, and TRUE for ascending order.

You can specify additional columns to sort by. Just add the second column number and the order, third column number and order, and so on.

For example, this formula sorts by the 2nd column (ascending), 4th column (descending) and 1st column (ascending):

=SORT(A1:D100, 2, TRUE, 4, FALSE, 1, TRUE)

You can create a dashboard with multiple views. Say you wanted to show the above data, and also summarise the top 3 products by revenue and profitability. Go to cell E2, and type

=NOEXPAND(SORT(A1:A9, B2:B9, TRUE))

This sorts the products (A1:A9) using the revenues (B2:B9) in ascending order (TRUE or 1). This would show all 8 products. If you want to keep only the top 3, you need to put the NOEXPAND around the formula. Otherwise, even if you delete the 4th product, Google will put it back.

Now, delete all but the top 3 products. Similarly, in cell E7, type

=NOEXPAND(SORT(A1:A9, C2:C9, TRUE))

This sorts by profitability instead. That’s it! You have a dynamic list of the top 3 products by revenue and profitability.

Can I do that in Excel?

Excel doesn’t have a function to sort. You can sort a list in-place. That changes the order permanently. There’s no way of retaining the original order.

You could make a copy of the list and sort it. But the copy will not change when the original list changes.

If the length of the list is fixed, and the values you want to sort by are unique, you could use the LARGE/SMALL, INDEX and MATCH functions to simulate this effect. First, type the numbers 1-8 in column D. Then type this formula in F2:

=LARGE(B$2:B$9,D2)

This will give you the largest revenue figure. Copy this down the column. This will show the largest revenue figures in descending order. Now, fill cells E2 downwards with the formula:

=INDEX(A$2:A$9,MATCH(F2,B$2:B$9,0))

The MATCH function finds the revenue in the first table, and the INDEX function looks up the corresponding product. You can use the same principle to get the profitability. However, this will not work if two products have the same revenue figure.

This is a series on what Google Spreadsheets can do that Excel can’t

To get a list of unique values from a list, use the UNIQUE function on Google Spreadsheets.

For example, if you have a list of browsers in column A, type =UNIQUE(A1:A17) at cell B1 to get a unique list of browsers. This is a dynamic list. If you change the list of browsers, the unique list gets updated automatically.

You can use UNIQUE to create a dynamic pivot table. Quite often, you end up creating a pivot table simply to summarise by one column. The main purpose the pivot table serves is in getting a list of unique values on that column. Plus, it’s a bit heavy on the UI. And every time the data changes, you need to refresh the pivot. But with the UNIQUE function, you can get a dynamic list of unique values, and you can use the COUNTIF and SUMIF function next to each value. Here is an example showing the frequency table of the browsers shown earlier. Column C does a COUNTIF of the unique values on the original list.

You can also use UNIQUE as the input to another formula:

=COUNT(UNIQUE(LIST)) counts the number of unique values

=COUNT(LIST)-COUNT(UNIQUE(LIST)) gives the number of duplicates

=INDEX(UNIQUE(LIST),3) gives you the third unique value

=LARGE(UNIQUE(LIST),3) gives you the third largest unique value

… and so on.

Can I do that in Excel?

You can, but not easily. There are two approaches, but each has its limitations.

A. Use Advanced Filters: easy but static

1. Create an advanced filter on column A (Alt-D-F-A)
2. Select Copy to another location
3. Click in the Copy to box, and then click the cell B1
4. Select Unique records only
5. Click OK

But the list of unique values that you get here is static. If you changed one of the values, the list of unique values does not change.

B. Use a complex formulae that are dynamic

First, blank out the duplicates by typing this formula:

=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

adjacent to the first cell (into B1), and dragging it all the way down (to B17).

Now, create a named range (Alt-I-N-D) for these cells (B1:B17) called WithBlanks and another named range called NoBlanks for the cells one column to the right (C1:C17).

On the first cell of NoBlanks (C1), type this formula:

=IF(ROW()-ROW(NoBlanks)+1>ROWS(WithBlanks)-COUNTBLANK(WithBlanks),"",
    INDIRECT(ADDRESS(SMALL((IF(WithBlanks<>"",ROW(WithBlanks),ROW()+
    ROWS(WithBlanks))),ROW()-ROW(NoBlanks)+1),COLUMN(WithBlanks),4)))

Press Ctrl-Shift-Enter rather than Enter, because it’s an array formula. Now drag this all the way down (to C17).

The list in column C is dynamic. If you change a cell in column A, column C is updated. But the formula can only handle one column. Google Spreadsheets’ UNIQUE function works with any number of columns. If you had data in the range A1:D100 and wanted the unique rows, UNIQUE(A1:D100) gets that for you.

Note: I’m staying away from user defined functions. You could, of course, create a UNIQUE function in Excel using Visual Basic. In fact, you should!