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
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
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 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
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. Watch the short video: Convert Numbers in English Words 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),” … Read more
Interpolation estimates a value between two known points: the level at chainage 115.7 when you know the levels at 99.5 and 201.1. Excel can do it with the straight-line formula, with FORECAST.LINEAR, or straight from a whole table with a lookup. Two pointsFORECAST.LINEAR From a tableMATCH + FORECAST, or LET Example answer40.49 The formula For … Read more
To pick a random winner, question or name, use =INDEX(B3:B8,RANDBETWEEN(1,ROWS(B3:B8))). It works for a list of any length and gives a new pick each time you press F9. Any versionINDEX + RANDBETWEEN Several, no repeatsExcel 365: TAKE + SORTBY + RANDARRAY New pickPress F9 Watch the short video: Pick a random name from a list … Read more
The quickest way to strip every macro from a workbook is to save it as .xlsx, a format that can't hold macros. To remove just one macro or module, delete it from the Macros dialog or the VBA editor. All macrosSave As → Excel Workbook (*.xlsx) One macroAlt + F8 → Delete One moduleAlt + … Read more
A drop-down list lets people pick a value instead of typing it, so entries are consistent and typing mistakes disappear. You make one with Data Validation, from a range of cells, a typed list, or a Table that grows by itself. WhereData → Data Validation → List Open a listAlt + ↓ Grows automaticallySource in … Read more
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