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
Highlight duplicates with conditional formatting, label them with COUNTIF, or list them with FILTER in Excel 365. You can also mark only the second and later copies, which is what you need before deleting. QuickestConditional Formatting → Duplicate Values List themFILTER + COUNTIF Remove themData → Remove Duplicates 1. Highlight with conditional formatting Select the … 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
To put a list or table in random order, for quiz questions, a random draw or a fair rota, use =SORTBY(A2:C20,RANDARRAY(ROWS(A2:C20))) in Excel 365. In any version, a RAND helper column and a sort does the same. Excel 365SORTBY + RANDARRAY Any versionRAND column + Sort New orderPress F9 Method 1: SORTBY and RANDARRAY (Excel … Read more
To get the day of the week for a date, such as Tuesday for 28-Dec-1937, use =TEXT(B3,”dddd”). Here are four ways: two formulas, a number format that shows the day without changing the date, and Power Query, plus day names in Malayalam and Hindi. Looking for the DAY formula? =DAY(A2) returns the day of the … Read more
To split a column of amounts into positive and negative columns, such as receipts and payments in a bank statement, use =MAX(B3,0) and =MIN(B3,0), an IF formula that leaves blanks, or FILTER for two separate lists. Power Query does it for large data. fx=MAX(B3,0) ProblemSplit amounts into positive and negative columns Functions usedMAX, MIN, IF, … Read more
Excel has no built-in function to spell out numbers, but one long formula can do it: 456,571 becomes Four Hundred and Fifty Six Thousand Five Hundred and Seventy One. Copy the formula below, or for rupees in lakh and crore, use the RUPEES function instead. fx=TRIM(CHOOSE(LEFT(TEXT(B3,”000000000″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(B3,”000000000″))=0,,IF(AND(–MID(TEXT(B3,”000000000″),2,1)=0,–MID(TEXT(B3,”000000000″),3,1)=0),” Hundred”,” Hundred and “))&CHOOSE(MID(TEXT(B3,”000000000”),2,1)+1,,,”Twenty “,”Thirty “,”Forty “,”Fifty “,”Sixty “,”Seventy … Read more