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
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
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
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
To put the same formula down a whole column, double-click the fill handle, use Ctrl + Enter, let a dynamic array spill, or turn the data into an Excel Table so new rows get the formula automatically. FastestDouble-click the fill handle Automatic for new rowsExcel Table Works inAll versions (dynamic arrays: 365 and 2021+) 1. … Read more
To turn Hana into anaH, Excel 365 can do it with one formula. In older versions, use a one-line VBA function, or Power Query’s Text.Reverse. Excel 365CONCAT + MID + SEQUENCE Any versionStrReverse in VBA Power QueryText.Reverse 1. Formula (Excel 365) =CONCAT(MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1))Copy Step For “Hana” LEN(B3) 4 SEQUENCE(4,,4,-1) {4;3;2;1}, counting down MID(B3,{4;3;2;1},1) {“a”;”n”;”a”;”H”} CONCAT(…) anaH … 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