Functions in Excel
A simple Excel sheet can track your income, expenses and savings month by month. You type the figures; SUM totals each section and one subtraction gives your savings, all updating as you go. FunctionsSUM and a subtraction LayoutMonths across, income and expense items down Works inAny version of Excel, and Google Sheets Video Step 1: … Read more
With Python in Excel you can write a number out in words with a simple cell formula: 1,234.56 becomes One Thousand Two Hundred Thirty-Four Point Five Six, or One Thousand Two Hundred Thirty-Four Rupees and Fifty-Six Paise Only for invoices and cheques. There are two codes: one for words in thousands and millions, and one … Read more
In 2022 Microsoft added 14 functions to Excel 365 that make text splitting and reshaping ranges a one-formula job. Here’s what each does, with an example, grouped by what they’re for. Works inMicrosoft 365, Excel for the web, Excel 2024 Not inExcel 2021 or earlier GroupsText, stacking, reshaping, resizing Text: TEXTSPLIT, TEXTBEFORE, TEXTAFTER Formula Result … Read more
Need 1, 2, 3… down a column? The quickest way is to type 1, then drag the fill handle with Ctrl held down. For numbers that update themselves, use a formula such as =SEQUENCE(10) or =ROW()-2. Here are ten methods, from drag-and-fill to formulas that skip blank rows. fx=SEQUENCE(10) ProblemNumber rows 1, 2, 3… automatically … Read more
Two different jobs share this name: pulling the number cells out of a mixed column, and pulling the digits out of text like INV-2041A. Here are formulas and tools for both. Number cellsFILTER + ISNUMBER, Go To Special, Power Query Digits in textTEXTJOIN + MID, REGEXEXTRACT Works inAll versions (dynamic formulas: 365) The data Part … Read more
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
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
To find the names, codes or items that appear in both lists, test each value with COUNTIF, pull out the matches with FILTER, or highlight them with conditional formatting. Power Query’s inner join does the same for large tables. fx=FILTER(B3:B10,COUNTIF(D3:D9,B3:B10)) ProblemList or mark the values that are in both lists Functions usedCOUNTIF, FILTER, MATCH Works … Read more
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
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