Blog Posts on MS Excel
INDEX MATCH is two functions working together. MATCH finds the row number of the value you want, and INDEX returns the value from that row in any column. It looks left as well as right, and it doesn't break when you insert a column. It works in every version of Excel. PatternINDEX(result, MATCH(value, list, 0)) … Read more
This short Python program opens every Excel file in a folder, replaces text, adds a formula and fixes formatting, then saves. Changes that take half an hour by hand across 52 workbooks finish in seconds. NeedsPython 3 and the openpyxl library EditsValues, formulas, number formats Tested withPython 3.12, openpyxl 3.1 Setup Install Python from python.org, … Read more
This Python program opens every workbook in a folder, reads the same cells from each, and writes them into one summary sheet. Collecting data from 139 scorecards takes seconds instead of an hour of copy-paste. NeedsPython 3 and openpyxl ReadsValues, including formula results Tested withPython 3.12, openpyxl 3.1 Video Setup Download Python from python.org and … Read more
When a value could be in either of two tables, nest two VLOOKUPs in IFERROR: if the first table returns #N/A, Excel looks in the second. In Excel 365, XLOOKUP with VSTACK does it in one lookup. Any versionIFERROR + VLOOKUP Excel 365XLOOKUP + VSTACK ExamplePrices from Cadbury and Mars lists The problem A distributor’s … Read more
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
For numbers, MODE returns the most frequent value. For text, combine INDEX, MODE and MATCH, or in Excel 365, use a short formula that also handles ties. Power Query gives a full frequency list. NumbersMODE TextINDEX + MODE + MATCH Ties and top NExcel 365 formulas or Power Query Most repeated number: MODE =MODE(C3:G10)Copy Dates … Read more
A Gantt chart shows each task of a project as a bar along a calendar. Excel doesn’t have a Gantt chart type, but a stacked bar chart with the first series hidden gives you one in about ten minutes. Chart typeStacked Bar DataTask, Start date, End date, Duration Works inAll versions Video The finished chart … Read more
To bring a .txt or .csv file into Excel, open it directly, drag it in, use Data → From Text/CSV (best, because it refreshes and keeps leading zeros if you set the column type), or use a short macro. BestData → Get Data → From Text/CSV QuickestDrag the file into Excel Keeps leading zerosSet the … Read more
The Developer tab holds macros, the VBA editor, form controls and add-ins, but it’s hidden when Excel is installed. Turn it on once and it stays. WindowsFile → Options → Customize Ribbon MacExcel → Preferences → Ribbon & Toolbar Time30 seconds Windows Right-click any ribbon tab and choose Customize the Ribbon (or File → Options … Read more
Pick a name from a drop-down and see its photo. In current Microsoft 365, put pictures in cells and XLOOKUP returns the picture directly. In older versions, use a linked picture driven by a named INDEX/MATCH formula. Microsoft 365Place in Cell + XLOOKUP Any versionLinked picture + named formula ExampleSuperhero drop-down Step 1: The drop-down … Read more