Tips and Tricks in Excel
This Excel template works out when each policy, subscription or document expires, counts the days left and flags anything Expired or Expiring Soon in colour. Because it uses TODAY, the alerts update every time you open the file. FunctionsEDATE, TODAY and IF AlertsConditional formatting: red for expired, yellow for due within 7 days Works inExcel … Read more
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
To bring a .txt or .csv file into Excel, open it directly, drag it in, use Data → From Text/CSV (best, because it refreshes and keeps leading zeros if you set the column type), or use a short macro. BestData → Get Data → From Text/CSV QuickestDrag the file into Excel Keeps leading zerosSet the … Read more
Pick a name from a drop-down and see its photo. In current Microsoft 365, put pictures in cells and XLOOKUP returns the picture directly. In older versions, use a linked picture driven by a named INDEX/MATCH formula. Microsoft 365Place in Cell + XLOOKUP Any versionLinked picture + named formula ExampleSuperhero drop-down Step 1: The drop-down … Read more
To send a table on WhatsApp or drop it into a report, turn the cells into an image: paste as a picture, use Copy as Picture, save straight to a PNG file, or use a macro. QuickestCopy, then Paste Special → Picture Save as a fileRight-click the picture → Save as Picture Works inAll versions … Read more
A very hidden sheet doesn't appear in the Unhide list. You can only hide or show it from the VBA editor, which makes it useful for lookup tables and calculations you don't want users to change by accident. HiddenRight-click tab → Hide; shows in Unhide Very hiddenVBA editor → Visible = 2; not in Unhide … Read more
To freeze results so they don’t change, or to send a file without your formulas, replace the formulas with their values. The quickest way in Microsoft 365 is Ctrl + C, then Ctrl + Shift + V. Excel 365 shortcutCtrl + Shift + V Any versionAlt, H, V, V UndoCtrl + Z works immediately after … Read more
To see the formulas instead of their results, press Ctrl + {`} or click Formulas → Show Formulas. To show a single formula next to its result, use FORMULATEXT. ShortcutCtrl + ` (the key left of 1) One cellFORMULATEXT Applies toThe active sheet only The data 1. Show Formulas button Formulas → Formula Auditing → … 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
AutoFit resizes columns and rows to fit their contents, so text stops spilling over and #### turns back into numbers. The fastest way: select the sheet with Ctrl + A and double-click any column border. Column shortcutAlt, H, O, I Row shortcutAlt, H, O, A Works inAll Excel versions The data 1. Double-click a border … Read more