Blog Posts on MS Excel
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
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
The easiest way to count Sundays (or any day) between two dates is NETWORKDAYS.INTL with a weekend code: =NETWORKDAYS.INTL(C2,C3,"1111110") counts Sundays. A SUMPRODUCT and WEEKDAY formula does the same and is easy to adapt. Any versionNETWORKDAYS.INTL Excel 365SUMPRODUCT + WEEKDAY + SEQUENCE 202252 Sundays Method 1: NETWORKDAYS.INTL NETWORKDAYS.INTL counts working days. Its third argument is … Read more
Excel’s From PDF connector pulls tables out of a PDF into Power Query, where you can clean them and load them to sheets. Replace the PDF with a newer one and click Refresh to update. WhereData → Get Data → From File → From PDF Works inMicrosoft 365 and Excel 2021 for Windows Not forScanned … Read more
To get 0.159 from 4.159, subtract the whole-number part: =B3-TRUNC(B3). MOD and Power Query work too, but they handle negative numbers differently, so pick the method that gives the answer you need. Formula=B3-TRUNC(B3) Always positive=MOD(ABS(B3),1) Whole part=TRUNC(B3) Formulas compared Formula 4.159 −4.159 =B3-TRUNC(B3) 0.159 −0.159 =ABS(B3-TRUNC(B3)) 0.159 0.159 =MOD(B3,1) 0.159 0.841 =MOD(ABS(B3),1) 0.159 0.159 MOD … Read more
To send a table on WhatsApp or drop it into a report, turn the cells into an image: paste as a picture, use Copy as Picture, save straight to a PNG file, or use a macro. QuickestCopy, then Paste Special → Picture Save as a fileRight-click the picture → Save as Picture Works inAll versions … Read more
Excel's Remove Duplicates button deletes repeated rows in one click. When you need to keep the original, or the data keeps growing, use UNIQUE, Advanced Filter, Power Query, a PivotTable or a macro instead. One clickData → Remove Duplicates Keeps the originalUNIQUE, Advanced Filter, Power Query RefreshablePower Query Which method? Method Changes the original? Updates … Read more
A very hidden sheet doesn't appear in the Unhide list. You can only hide or show it from the VBA editor, which makes it useful for lookup tables and calculations you don't want users to change by accident. HiddenRight-click tab → Hide; shows in Unhide Very hiddenVBA editor → Visible = 2; not in Unhide … Read more