Connect Google Sheets to Excel

Connect Google Sheets to Excel

You can pull a Google Sheet into Excel with Power Query and refresh it whenever the sheet changes: no download, no copy-paste. The trick is one small change to the sheet’s share link. Works inExcel 2016 and later, Microsoft 365 (Windows); Excel for Mac with Power Query NeedsA Google Sheet shared as “Anyone with the … Read more

Excel formula to Split data into Rows or Columns

Split Data Into Rows or Columns

A User Defined Function (UDF) which can split data into multiple Rows or Columns on the basis of the specified delimiter in Excel is explained in this blog post. Watch the video on Excel Formula to split data into Columns or Rows Text to Columns feature is the most commonly used Tool to split data … Read more

Change Text Case Using Power Query

Change Text Case

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

Remove or Extract Special Characters Using Power Query

Remove Special Characters

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

Group and Summarise Data With Power Query (Group By)

Group By in Power Query

Power Query's Group By turns a long list of transactions into a summary: total sales per product, count of orders per region, or several totals at once. Unlike a formula summary, it refreshes with one click when new rows arrive. WhereHome → Group By OperationsSum, Average, Min, Max, Count Rows, Count Distinct, All Rows Excel … Read more

Transpose Data using Power Query in Excel

Transpose Data

To turn rows into columns, use Paste Special → Transpose for a one-off, the TRANSPOSE function for a live copy, or Power Query for a version that refreshes and can be cleaned on the way. One-offPaste Special → Transpose Live formula=TRANSPOSE(B2:G4) RefreshablePower Query → Transpose The data Power Query Click in the data → Data … Read more

Automatically Number Rows With Power Query

Number Rows Automatically

Power Query’s Index Column numbers every row and renumbers when you refresh, so new or removed rows never break the sequence. You can start at any number and add prefixes like INV-0150. WhereAdd Column → Index Column RefreshRenumbers automatically Works inExcel 2016 and later Step 1: Load the data Click in the data → Data … Read more