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
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
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
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
To find the names, codes or items that appear in both lists, test each value with COUNTIF, pull out the matches with FILTER, or highlight them with conditional formatting. Power Query’s inner join does the same for large tables. fx=FILTER(B3:B10,COUNTIF(D3:D9,B3:B10)) ProblemList or mark the values that are in both lists Functions usedCOUNTIF, FILTER, MATCH Works … Read more
To add 10 to hundreds of cells, or raise every price by 5%, use Paste Special → Operation. It changes the values in place, even across separate ranges. A dynamic array formula gives the result in new cells instead. In placePaste Special → Add, Subtract, Multiply, Divide New cells=B2:F6+10 Works inAll versions (formula: Excel 365) … Read more
1 + 2 + 3 + … + 50 = 1,275. It's a classic puzzle, and a neat way to compare Excel tools: a one-line maths formula, SEQUENCE, SUMPRODUCT and ROW, Power Query and VBA all get there. Answer1,275 Shortest=50*51/2 Excel 365=SUM(SEQUENCE(50)) 1. The maths formula =50*(50+1)/2Copy The sum of 1 to n is n(n+1)/2. … Read more
Excel has three random functions: RAND for decimals between 0 and 1, RANDBETWEEN for whole numbers in a range, and RANDARRAY for a whole grid at once. With them you can make random decimals, dates, picks from a list and numbers that never repeat. Decimal 0 to 1=RAND() Whole number=RANDBETWEEN(1,100) A grid (365)=RANDARRAY(5,3) RAND: a … Read more
Excel stores time as a fraction of a day, so 12:00 PM is 0.5. That makes time maths simple once you know the formats: subtract for the difference, use MOD for overnight shifts, multiply by 24 for decimal hours, and MROUND to round. Difference=C5-B5, formatted [h]:mm Overnight=MOD(C5-B5,1) Decimal hours=(C5-B5)*24 How Excel stores time Unit Value … Read more