Blog Posts on MS Excel
STOCKHISTORY pulls historical share prices and currency rates straight into Excel: daily, weekly or monthly, for NASDAQ, NSE, BSE and other exchanges. One formula returns the whole table, and it can be refreshed any time. Works inMicrosoft 365 (Windows, Mac, web). Not in Excel 2021 or earlier ReturnsA spilled table of dates and prices NeedsAn … Read more
New to Excel, or teaching someone who is? These are the terms you'll meet in every tutorial, from workbook and cell reference to spill range and Power Query, each in one or two plain sentences. Terms40 LevelBeginner to intermediate Applies toExcel for Windows, Mac and the web The basics Term Meaning Workbook An Excel file … Read more
Every Excel error code tells you what went wrong: #DIV/0! is a division by zero, #N/A is a value that wasn’t found, #REF! is a reference that no longer exists. Here is what each error means, a typical cause, and how to fix it. Most common#N/A, #DIV/0!, #VALUE!, #REF! Newer errors#SPILL!, #CALC!, #FIELD!, #BUSY! Find … Read more
How to import Facebook Friends List to Excel is one of my most watched YouTube videos. In this blog post I will explain this technique step by step, for those whose prefer reading over video lectures. Facebook has an information section, where we can View or Download all those data related to our Facebook Account. … Read more
Power Query normally hard-codes values such as a year or a row count into its steps. Point those steps at a cell instead, and anyone can change the cell and click Refresh All to get new results, without opening the editor. TechniqueTable → Drill Down → use in M code ExampleTop N films of any … Read more
Power Query converts text to UPPERCASE, lowercase or Capitalize Each Word in two clicks, and the result refreshes when the source changes. Use Transform to change a column in place, or Add Column to keep the original. WhereTransform → Format, or Add Column → Format M functionsText.Upper, Text.Lower, Text.Proper Works inExcel 2016 and later, Power … Read more
Reports often show a group name once and leave the rows below it blank. Power Query’s Fill Down copies each value into the empty cells beneath it, and Fill Up copies upwards, so the data is ready for a PivotTable. WhereRight-click a column → Fill → Down or Up Fillsnull cells only Works inExcel 2016 … Read more
Power Query’s Text.Select keeps only the characters you list, and Text.Remove throws them away. With one custom column you can strip !@#$%&* from messy text, or pull out just the numbers, capitals or letters. FunctionsText.Select, Text.Remove Works inExcel 2016 and later, Microsoft 365 RefreshableYes The data Step 1: Load the data into Power Query Click … Read more
To find which words appear most often in a list of sentences, such as survey comments or product reviews, Power Query can split the text into words, count each one and sort the list, all refreshable with one click. StepsSplit → Unpivot → Group By → Sort Works inExcel 2016 and later Example100 English proverbs … Read more
Power Query’s Merge compares two tables and returns matching or non-matching rows, depending on the join kind. Here are all six, explained with two cast lists, and the steps to use any of them. WhereData → Get Data → Combine Queries → Merge Join kinds6 Works inExcel 2016 and later, Power BI The six joins … Read more