Examples of SPL XLL

  1. When Excel lacks certain necessary functionalities
  2. Enable old versions of Excel to have features of the latest ones
  3. Power up common Excel functions
  4. Simplify Excel data handling
  5. Approach computing difficulties after grouping conveniently

01When Excel lacks certain necessary functionalities

Excel does not offer functionalities or functions for certain computing tasks.

Split A1 according to separator “->“. The expected result is C1:F1:
Usually need VBA as a formula alone cannot manage
High technical threshold & low efficiency
Use multi-character splitting function split(). Enter formula in C1:
=spl("=?.split(""->"")",A1)
Directly use a splitting function without turning to VBA, and has high efficiency
Find data existing in column A but not in column B, that is, the difference. The expected result is shown in column C:
Do not support set operations such as difference, and can only piece together a complex and hard to understand formula using existing functions (such as if, iserror, vlookup and filter)
Use the difference operator \
=spl("=?1\?2",A1:A6,B1:B5)
Support various operators and functions for set operations; formulas are simple and easy to understand and allow automatic filling

More SPL functionalities

  • Calculate median of values in unspecified cells (like those where max and min values will be removed)
  • Get TopN from an unspecified number of cells (N is a cell value, for instance)
  • Return the substring before the specified marker string
  • Split a string into an array of numbers and an array of characters
  • Split away all dates from a string
  • Insert an empty row every N rows
  • Insert an empty row wherever the specified data changes
  • Split key value pairs in a certain range into two columns – the key and the value
  • Get all Sundays within a specified time period

02Enable old versions of Excel to have features of the latest ones

You cannot access the more powerful functions the latest version offers through an old version of Excel.

The old version does not support the unique function (for getting unique values) the 2019?&365 offers.
Get upgraded or write a complex formula
Use the SPL counterpart id@u
=spl("=?.id@u()",A2:A17)

More about SPL id function:

  • Sort result by default, and is equivalent to Excel sort(unique())
  • id@u does not sort the result and is equivalent to Excel unique
Achieve a lot of new functionalities in an old version without upgrading, and support automatic filling

New functionalities achievable in an old version of Excel:

  • filter
  • sort\sortBy
  • xlookup
  • maxifs\minifs
  • xmatch
  • sequence
  • randArray
  • switch
  • concat\textjoin

03Power up common Excel functions

Certain commonly used Excel functions are not powerful enough.

Filter function does not return column names for the filtering result, cannot reference column names in the condition, does not support fuzzy query.
Copy column names manually and formulas are hard to write (accompanied by functions such as search\iferror)
Use more powerful SPL select function
=spl("=E(?).select(Salary>5000 && Salary< 10000 && like(Name,""*Jo*""))",A1:G499)

More about SPL select function：

  • Return the first matched record only
  • Return all eligible records after the first matched one
  • Support reverse filtering
  • Return non-matched records
  • Support binary search
Return column names for the filtering result, allow using column names in the condition and support fuzzy query

More powered-up Excel functions

  • Vlookup: return multiple matched records
  • Xlookup: Perform cross-match and two-way match
  • Index: Get ordinal numbers backwards
  • avg: Skip non-number members
  • max: Return ordinal number(s) of the max value
  • count: Calculate formula on each member and return number of the non-null results
  • datedif: Calculate the number of quarters/weeks/Mondays/Sundays between two dates
  • concat: Use a user-defined separator and enclose members with quotation marks
  • find: Case-insensitive and find eligible members backwards

04Simplify Excel data handling

In same scenarios, it’s harder for Excel to process table data (multiple records with column names)

Find products whose sales quantities rank in top10 in Jan. but do not rank in top10 in Feb., that is, records existing in table on the left but not in table on the right (their difference).
Do no support getting difference of simple data sets as well as of table data; need cooperation of multiple functions to achieve, which is hard
Use the specialized function merge@od to get difference on records
=spl("=[E(?1),E(?2)].merge@od(ProductName)",Jan!A2:C12,Feb!A2:C12)

More about SPL merge：

  • By default return duplicate records after combination (which is concatenation) and data remains ordered
  • @o means data is still unordered
  • @i gets common records of two tables – the intersection
  • @u gets all unique records of two tables – the union
  • Can perform a set operation between single columns/multiple columns/whole rows
Support various set operations on table data with simple and easy to understand formulas
Table Orders’ SellerID column is associated with table Seller’s ID column (in a many-to-one relationship), but both have missing data (highlighted in blue and yellow). Our goal is to perform a two-way search (full join) on the two tables and generate a new table using certain columns while displaying missing data in the other table.
xlookup has incomplete functionalities, and can perform a one-way search only (inner join and left join) but cannot perform a two-way search (full join)
Use the two-way search (full join) function join@f on records. Enter the following formula:
=spl("=join@f(E(?1),SellerID;E(?2),ID).new(_1.OrderNo,_1.Amount,_2.ID,_2.Name)",A2:D8,F2:H7)

More about SPL join：

  • By default perform a one-way search without displaying missing values (which is an inner join)
  • @1 enables a one-way search and displaying missing data (left join)
  • @p enables a search by row numbers in a table
  • Support join queries between single columns/multiple columns/whole rows
Support all query types on tables with simple and easy to understand formulas

More simplifiable computations on table data

  • Perform a cross-join (calculating Cartesian product) on two tables
  • Create a new group whenever data changes
  • Create a new group whenever an empty row appears
  • Create a new group whenever a non-empty row appears
  • Expand one row into N rows according to value (Like N) of a specified field
  • Get records where values of a specified field increase for at least continuous three days
  • Transform one column with multiple rows into a table having the specified column count (row count can be specified, too)
  • Transform one row with multiple columns into a table having the specified row count (column count can be specified, too)

05Approach computing difficulties after grouping conveniently

It is harder for Excel to compute grouped table data.

Find employees whose salaries rank in bottom three in each department.
Do not support computations after grouping; need large amounts of manual operations
Use group function to group table data and then ~.topt to get TopN in each group
=spl("=E(?1).group(Dept).conj(~.top(3;Salary))",A1:G499)

More about SPL top function:

  • Return a single row/column
  • -3 represents top3 employees getting the highest salaries
Support various computations after table data is grouped

SPL can handle more computations on grouped table data

  • Get ranking of values of a specified field in a group
  • Get positions of odd numbers, even numbers or steps in a group
  • Get a relative position in a group, such as calculating YOY, link relative ratio or moving average
  • Perform filtering on detailed data in a group according to a condition like greater than average and less than a certain percentage of the aggregate value
  • Perform aggregate-value-related computations on detailed data in a group, such as calculating percentage of a certain field’s value in the aggregate value
  • Use a separator to combine multiple rows in a group into a single row
  • Split each row into multiple rows according to a specified separator