A one-page sales dashboard in Excel needs only three things: PivotTables to summarise the data, PivotCharts to show it, and a slicer connected to every chart, so one click on a month updates the whole page. Built withPivotTables, PivotCharts and a slicer ChartsLine, column, bar and pie Works inExcel 2013 and later, Microsoft 365 TimeAbout … Read more
Excel can write a personalised WhatsApp message for everyone on a list and turn it into a link. Click the link and WhatsApp opens with the number and message filled in, ready to send, even if the number isn’t saved in your phone. Functions& (join text), TEXT and HYPERLINK OpensWhatsApp Web in your default browser … Read more
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
VLOOKUP and XLOOKUP stop at the first match. To get every order for a customer, use FILTER in Excel 365 and 2021: one formula returns all the matching rows and spills them into the cells below. Here's how, plus a one-cell list with TEXTJOIN and a formula for older versions. Excel 365 / 2021=FILTER(range,condition) One … Read more
#N/A means not available: the lookup function couldn't find the value. Sometimes the value really is missing. Often it is there, but an extra space, a number stored as text or an unlocked range stops it matching. Here's how to find the cause, fix it, and show a friendly message for values that are genuinely … Read more
An exact match returns a result only when the lookup value is found exactly. An approximate match returns the nearest value below it, which is what you need for grade bands, tax slabs and commission rates. Using the wrong one is the most common reason a lookup returns a wrong answer without showing any error. … Read more
INDEX MATCH is two functions working together. MATCH finds the row number of the value you want, and INDEX returns the value from that row in any column. It looks left as well as right, and it doesn't break when you insert a column. It works in every version of Excel. PatternINDEX(result, MATCH(value, list, 0)) … Read more
When a value could be in either of two tables, nest two VLOOKUPs in IFERROR: if the first table returns #N/A, Excel looks in the second. In Excel 365, XLOOKUP with VSTACK does it in one lookup. Any versionIFERROR + VLOOKUP Excel 365XLOOKUP + VSTACK ExamplePrices from Cadbury and Mars lists The problem A distributor’s … Read more
For numbers, MODE returns the most frequent value. For text, combine INDEX, MODE and MATCH, or in Excel 365, use a short formula that also handles ties. Power Query gives a full frequency list. NumbersMODE TextINDEX + MODE + MATCH Ties and top NExcel 365 formulas or Power Query Most repeated number: MODE =MODE(C3:G10)Copy Dates … Read more