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
Word can save a document as a PDF that asks for a password before it opens, with no extra software. It's a few clicks in the Export screen, using the Encrypt the document with a password option. WhereFile → Export → Create PDF/XPS → Options Works inWord for Windows (Microsoft 365, 2021, 2019, 2016) CostFree, … Read more
Microsoft Word can open a PDF and turn it into an editable document. No converter or upload needed: File → Open, choose the PDF, and click OK. It works in Word 2013 and later. WhereWord → File → Open → the PDF Works inWord 2013 and later, Microsoft 365 Best forText-based PDFs Steps In Word, … Read more