Blog Posts on MS Excel
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
These are the channels and websites I recommend most often to learners. Every one is run by a Microsoft Excel MVP or a long-standing Excel teacher, and all of them are free. CostFree LevelsBeginner to advanced Updated2026 MrExcel: Bill Jelen Bill Jelen has answered Excel questions at mrexcel.com for decades, and its forum is one … 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
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