Blog Posts on MS Excel
Excel stores time as a fraction of a day, so 12:00 PM is 0.5. That makes time maths simple once you know the formats: subtract for the difference, use MOD for overnight shifts, multiply by 24 for decimal hours, and MROUND to round. Difference=C5-B5, formatted [h]:mm Overnight=MOD(C5-B5,1) Decimal hours=(C5-B5)*24 How Excel stores time Unit Value … Read more
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 Method 1: SORTBY and RANDARRAY (Excel … Read more
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. fx=TEXT(B3,”dddd”) ProblemShow the weekday name for a date Functions usedTEXT, or … Read more
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
Excel drops leading zeros from numbers, so 00125 becomes 125. To keep or add them for IDs, PIN codes or invoice numbers, use a number format, the TEXT function, REPT for text, or Power Query. Display onlyCustom format 00000 As textTEXT or REPT TypingStart with an apostrophe: ‘00125 1. Custom number format (keeps the number) … Read more
Following are 4 different methods that can be used to Jump to the First or Last sheet of an Excel Workbook. Method 1 Holding the Ctrl key, left-click on the small right arrow present in the bottom left corner of the workbook. You will land on the last worksheet of the active workbook. Similarly, to select the … Read more
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
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