In 2022 Microsoft added 14 functions to Excel 365 that make text splitting and reshaping ranges a one-formula job. Here’s what each does, with an example, grouped by what they’re for. Works inMicrosoft 365, Excel for the web, Excel 2024 Not inExcel 2021 or earlier GroupsText, stacking, reshaping, resizing Text: TEXTSPLIT, TEXTBEFORE, TEXTAFTER Formula Result … Read more
A hyperlink lets you jump from a summary sheet straight to the detail behind a number. Use the HYPERLINK function, Insert Link (Ctrl + K), or the little-known right-drag trick. ShortcutCtrl + K FunctionHYPERLINK Works inAll Excel versions The example A highway project’s cost summary pulls each total from its own sheet: Roads, Street Lighting, … Read more
To see the formulas instead of their results, press Ctrl + {`} or click Formulas → Show Formulas. To show a single formula next to its result, use FORMULATEXT. ShortcutCtrl + ` (the key left of 1) One cellFORMULATEXT Applies toThe active sheet only The data 1. Show Formulas button Formulas → Formula Auditing → … Read more
Two different jobs share this name: pulling the number cells out of a mixed column, and pulling the digits out of text like INV-2041A. Here are formulas and tools for both. Number cellsFILTER + ISNUMBER, Go To Special, Power Query Digits in textTEXTJOIN + MID, REGEXEXTRACT Works inAll versions (dynamic formulas: 365) The data Part … Read more
In a timesheet or schedule with dates across the top and hourly slots down the side, conditional formatting with NOW() highlights the cell for the current date and hour, so you can see at a glance where you are in the day. FunctionNOW() ToolConditional formatting with a formula UpdatesWhenever the sheet recalculates The schedule Dates … Read more
To see how many times each item appears, use UNIQUE and COUNTIF in Excel 365, GROUPBY for a sorted summary, a PivotTable, or Power Query’s Group By. All four update when the list changes. Excel 365UNIQUE + COUNTIF, or GROUPBY Any versionPivotTable RefreshablePower Query Group By The list The list is an Excel Table named … Read more
To find the names, codes or items that appear in both lists, test each value with COUNTIF, pull out the matches with FILTER, or highlight them with conditional formatting. Power Query’s inner join does the same for large tables. fx=FILTER(B3:B10,COUNTIF(D3:D9,B3:B10)) ProblemList or mark the values that are in both lists Functions usedCOUNTIF, FILTER, MATCH Works … Read more
To put a list or table in random order, for quiz questions, a random draw or a fair rota, use =SORTBY(A2:C20,RANDARRAY(ROWS(A2:C20))) in Excel 365. In any version, a RAND helper column and a sort does the same. Excel 365SORTBY + RANDARRAY Any versionRAND column + Sort New orderPress F9 Method 1: SORTBY and RANDARRAY (Excel … Read more
STOCKHISTORY pulls historical share prices and currency rates straight into Excel: daily, weekly or monthly, for NASDAQ, NSE, BSE and other exchanges. One formula returns the whole table, and it can be refreshed any time. Works inMicrosoft 365 (Windows, Mac, web). Not in Excel 2021 or earlier ReturnsA spilled table of dates and prices NeedsAn … Read more