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
Need 1, 2, 3… down a column? The quickest way is to type 1, then drag the fill handle with Ctrl held down. For numbers that update themselves, use a formula such as =SEQUENCE(10) or =ROW()-2. Here are ten methods, from drag-and-fill to formulas that skip blank rows. fx=SEQUENCE(10) ProblemNumber rows 1, 2, 3… automatically … Read more
To freeze results so they don’t change, or to send a file without your formulas, replace the formulas with their values. The quickest way in Microsoft 365 is Ctrl + C, then Ctrl + Shift + V. Excel 365 shortcutCtrl + Shift + V Any versionAlt, H, V, V UndoCtrl + Z works immediately after … Read more