Blog Posts on MS Excel
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
Paste an Excel table into Word as an editable Word table, as a linked table that updates when the Excel data changes, as a picture, or as an embedded object. Editable tablePaste → Keep Source Formatting Updates from ExcelPaste → Link & Keep Source Formatting Fixed imagePaste → Picture 1. Paste as a Word table … Read more
A slicer is a panel of buttons that filters an Excel Table or PivotTable with one click, and always shows what's filtered. One slicer can also control several PivotTables at once, which makes simple dashboards easy. Excel TableTable Design → Insert Slicer PivotTablePivotTable Analyze → Insert Slicer Multi-selectCtrl + click Add a slicer to an … Read more
In this blog post, I will show you how to hide the formulas used in an Excel Worksheet and display only the results of those formulas. Following is an Excel Worksheet used by a H.R Manager, for generating the Payslips of his colleagues. Before sharing this workbook with other executives, the H.R manager wants to … Read more
Excel has three kinds of protection, and they do very different things. A file password encrypts the workbook so it can't be opened. Sheet protection and workbook structure protection only stop accidental changes; they aren't security. Can't open without passwordFile → Info → Encrypt with Password Lock cellsReview → Protect Sheet Lock sheet tabsReview → … Read more
Find and Replace can swap formatting, not just text. Use it to change every yellow cell to green across a sheet, or a whole workbook, in one click. WhereCtrl + H → Options → Format ScopeSheet or whole workbook Works onFill, font colour, borders, number formats Steps Press Ctrl + H and click Options. Click … Read more
Excel adjusts references when you copy a formula: =C2*C3 copied three columns right becomes =F2*F3. Here are five ways to keep the formula exactly as it is, from a quick cut-and-paste to moving a whole block of formulas. One cellCopy from the formula bar Cell belowCtrl + ' A blockFind and Replace = with a … Read more
Data → Forecast Sheet turns a column of dates and values into a forecast table and chart, with upper and lower confidence bounds, in one click. Behind it is the FORECAST.ETS function, which you can also use directly. WhereData → Forecast → Forecast Sheet MethodExponential smoothing (ETS AAA) Works inExcel 2016 and later for Windows … Read more
To join first name, last name and other columns into one cell, use &, CONCAT, or TEXTJOIN, which adds a separator and skips blanks. Flash Fill and Power Query do it without formulas. Best formulaTEXTJOIN No formulaFlash Fill (Ctrl + E) RefreshablePower Query 1. The & operator =B3&" "&C3&" "&D3Copy ” ” adds a space … Read more