Power Query, scripts and Excel 365’s newer formulas, in a free spreadsheet

Much of the work people do in Excel is getting data into shape, a CSV from the bank or an export from the till, and then doing the same thing again next month. Between 6 and 8 October, Worksheets in Rutba Office gained tools for both halves of that job.

Get & Transform, as Power Query does it

Data → From Table/Range opens the Power Query Editor on the table round the active cell. From Text/CSV opens it on a CSV, TSV or text file, with the first row as the headings. The editor shows the first hundred rows the query would load, and builds its steps from the column you pick. You can:

• remove the column, or every other column, rename it or give it a type
• filter, sort, replace values, and keep the top rows
• split a column at a comma, a tab, a space or any character you choose
• trim it or change its case
• group by it with a count, a sum, an average, the least or the most
• remove duplicates or blank rows, use the first row as headings, or add an index

The applied steps are listed beside the rows. Click one and the table shows how it stood after that step. A new step goes in after the one you picked, and × takes a step away. Close & Load writes the result as a table on a sheet named after the query.

1.29.10 added the two steps that join data. Merge Queries joins each row to the rows of another table or query whose key matches, as a Left Outer, Inner, Left Anti or Full Outer join, and brings in the other side’s columns. Append Queries puts another table’s rows after the query’s own. A query can read another query’s result, so a cleaned list of customers can feed an orders query.

Queries & Connections lists the workbook’s queries with the number of rows each one loaded. Refresh runs a query again on its source as the source is now. Refresh All runs every query before it refreshes the pivot tables, so the pivot reads this month’s data rather than last month’s. Each refresh is one step of undo.

Scripts that run on your computer, and nowhere else

Automate → New Script opens a Code Editor. A script has the shape of Excel’s Office Scripts, a function main(workbook), and reads and writes ranges by address: values, formulas and number formats, bold, italic, colours, fills, sizes and alignment. It can add and rename sheets, and anything it logs appears in the editor.

function main(workbook) {
  const sheet = workbook.getActiveWorksheet();
  sheet.getRange('A1:D1').getFormat().getFont().setBold(true);
  sheet.getRange('E2').setFormula('=SUM(B2:D2)');
  console.log(sheet.getUsedRange().getAddress());
}

The interesting part is where it runs. A script runs in a worker of its own that can reach neither the network nor a file, on a snapshot of the workbook. What it did comes back and is applied as one step of undo. A script that fails changes nothing and says why, and one still running after fifteen seconds is stopped. Scripts are kept on your computer, listed under All Scripts, and are not stored in the workbook you run them on.

Record Actions writes the script for you: switch it on, type and format as you normally would, and what you did comes back as a script you can run again or edit.

The formulas Excel 365 added

1.29.4 added the functions that make a modern Excel workbook hard to open anywhere else:

• LAMBDA, named or called where it is written, with MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY and ISOMITTED
• XMATCH and SORTBY
• TEXTSPLIT, TEXTBEFORE and TEXTAFTER
• VSTACK, HSTACK, TOROW, TOCOL, WRAPROWS, WRAPCOLS, TAKE, DROP, CHOOSEROWS, CHOOSECOLS and EXPAND
• RANDARRAY, VALUETOTEXT and ARRAYTOTEXT

The same release fixed how they are saved. A function newer than Excel 2007 has to carry a prefix in the file, and without it Excel showed #NAME? in the cell. They are written with it now.

Copy, paste, fill and point, as in Excel

Some of the largest changes in these releases are to copying and pasting. Before 1.29.7, Ctrl+C put nothing on the system clipboard and Ctrl+V did nothing; only the ribbon’s Paste worked, and only as plain words. Now:

• A range is chosen with the mouse, and a drag held past the edge of the grid scrolls on and keeps selecting. Ctrl adds another range beside it.
• Copy and paste go through the system clipboard, as words for any application and as a table another spreadsheet reads cell by cell. Formulas move with the cells.
• A cut pasted takes every reference with it (1.29.8). Any formula in the workbook that names only the moved cells now names where they went, and so does a defined name. One undo puts every sheet back.
• Paste Special pastes formulas, values or formatting alone, adds, subtracts, multiplies or divides the copy into the cells already there, skips blanks, and transposes rows into columns.
• The fill handle continues a series from the whole selection, with Auto Fill Options at the corner and a Series dialog for growth and date steps.
• Pointing at cells while typing a formula (1.29.5). Each reference is coloured in the cell, in the formula bar and round the cells it names. A click puts a cell’s reference in, a drag makes a range, and F4 turns the dollar signs.

And the rest

• PivotTable Fields stands beside the sheet when the cell is in a pivot: tick fields into Rows or Values, move them between areas, and pick a sum, count, average, largest or smallest.
• Data bars and icon sets are drawn in their cells, each bar as long as its number’s share of the range.
• SUBTOTAL leaves out hidden rows as Excel’s does, and Auto Outline groups rows and columns by the totals that add them up.
• Advanced Filter can copy a list from another sheet to the one it is started on.
• Sheet Right-to-Left puts column A on the right, for a workbook in Arabic or Hebrew.
• Insert → Equation, WordArt, SmartArt, Icons and the Draw tab, and Review → Thesaurus on the active cell’s word.
• The formula bar amends what it shows (1.28.1). A letter typed there used to replace the whole cell.
• Deleting a sheet is one step of undo, and the sheet comes back with everything on it.