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

Linked Picture in Excel

Linked Picture in Excel

A linked picture is a live image of a range. When the cells change, the picture changes too. Use it to show numbers from other sheets on a dashboard, side by side, at any size. FastestPaste Special → Linked Picture Hidden toolCamera (Quick Access Toolbar) UpdatesLive, as the source cells change Why use one Here … Read more

Make a Picture Transparent in Excel

Make a Picture Transparent

Excel can fade a picture so text or data shows through, which is useful for watermarks and dashboard backgrounds. Use the Transparency presets, or set an exact percentage in the Format Picture pane. WherePicture Format → Transparency Exact valueFormat Picture pane Works inMicrosoft 365, Excel 2021 and later Method 1: Transparency presets Select the picture … Read more

Convert Between Hijri and Gregorian Dates in Excel

Hijri and Gregorian Dates

Excel can show any date in the Hijri or Umm al-Qura calendar without changing the date itself. Use a number format to display it, or the TEXT function to write the Hijri date into another cell, next to the Gregorian one. Works inExcel 2010 and later, Windows. Excel for Mac supports fewer calendar options CalendarsHijri … Read more

Limit the Number of Characters in an Excel Cell

Limit Characters in a Cell

Data Validation can stop people typing more than a set number of characters into a cell, or insist on an exact length, such as a 10-digit mobile number or a 6-digit PIN code. WhereData → Data Validation → Text length Exact lengthCustom formula with LEN Works inAll versions Allow up to 5 characters Select the … Read more

Overline Text in Excel (3 Ways)

Overline Text in Excel

Excel has underline but no overline button. You can get the look with a formula that adds a line of underscores above the text, a combining overline character, or the Equation tool. FormulaREPT and CHAR(10) Excel 365UNICHAR(773) Exact lookInsert → Equation Method 1: Underscores above the text =REPT("_",LEN(B3))&CHAR(10)&B3Copy REPT writes one underscore per character, CHAR(10) … Read more

Select Visible Cells

Select Visible Cells Only

When rows are hidden, a normal copy also copies the hidden rows, and pasting can overwrite them. Press Alt + ; to select only the visible cells first, then copy or paste. ShortcutAlt + ; (Windows), Cmd + Shift + Z (Mac) MenuGo To Special → Visible cells only Filtered rowsAlready skipped automatically The problem … Read more