Blog Posts on MS 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
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
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 Method 1: INDEX and RANDBETWEEN =INDEX(B3:B8,RANDBETWEEN(1,ROWS(B3:B8)))Copy RANDBETWEEN picks a position from … 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
To flip a list so the last item comes first, sort by a helper column, use a formula, or in Excel 365 use one SORTBY formula that updates automatically. Power Query’s Reverse Rows does it for whole tables. Excel 365SORTBY + SEQUENCE Any versionINDEX + ROWS No formulaHelper column and sort The list 1. SORTBY … Read more
Everyone knows Ctrl + C. These are the shortcuts even regular Excel users often miss, from opening a function's help dialog to jumping back to the active cell after a long selection. Shortcuts12 Works inExcel for Windows VideoIncluded Video Quick list Shortcut What it does Ctrl + L Create a table (same as Ctrl + … Read more
In Excel you divide and then apply the Percent Style (Ctrl + Shift + 5), with no need to multiply by 100. Here are the formulas for percentage of a total, percentage change, adding or removing a percentage, and working backwards. Percentage=Part/Total, then % Change=(New−Old)/Old Add 6%=A2*(1+6%) Quick reference To find Formula Example Percentage of … Read more