Apply a Formula to an Entire Column in Excel (5 Ways)

Formula to an Entire Column

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+) Watch … Read more

Reverse Text in Excel (4 Ways)

Reverse Text in Excel

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 Watch the short video: Excel formula to Reverse a Text or Number 1. Formula (Excel 365) =CONCAT(MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1))Copy Step … Read more

Highlight cells according to current Time in Excel

Highlight by Time of Day

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

Count Each Item in a List in Excel (4 Ways)

Count Each Item in a List

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

Add, Subtract or Multiply a Number Across Many Cells in Excel

Add a Number to Many Cells

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

Find Duplicates in Excel (5 Ways)

Find Duplicates in Excel

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

Sum of the First 50 Natural Numbers in Excel (5 Ways)

Sum of 1 to 50 in Excel

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

Generate Random Numbers, Dates and Values in Excel

Random Numbers, Dates, Values

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) Watch the … Read more

Shuffle Rows Randomly in Excel

Shuffle Rows Randomly

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 Watch the short video: Randomly sort … Read more

Find Day Name from Date in Excel

Day Name From a Date

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. Watch the short video: Excel formula to get the Day name … Read more