Entries by Oz du Soleil

Creating a Variable Hyperlink in Excel

This is cool! The problem: 11 worksheets and hundreds of codes Objective: the ability to type a code and be taken directly to that code wherever it is in the workbook. It’s like the Find/Select feature in Excel, the user wanted to stay in the worksheet and minimize use of the ribbon. One thing to […]

Power Query: Summing Data from Specific Ranges

This is a CRAZY one! Kevin Lehrbass presented this problem: A tall stack of mini data ranges that need to be totaled. BUT! Only total the ranges that say “TOTAL.” Ignore the ranges that are labeled anything else. (Kevin’s solution: https://www.youtube.com/watch?v=oomf8bfcFEw) Kevin’s video shows various formula solutions. My video shows a Power Query (Get and […]

Excel Power Query: Import Data from Web Page and Clear Line-Breaks

Power Query has Clean and Trim features, but sometimes those don’t work with line-breaks. 1:49 Importing the data from the Wikipedia page 2:29 Unpivot 3:13 Split columns 3:35 Explanation of the problem of the data being on multiple lines 4:30 Clean & Trim (they don’t work) 4:48 Setting up the data to 5:36 #(lf) to […]

Power Query Merge Columns

More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns and the “gotchas” that you need to know about. 0:49 Description of the 3 warnings 1:09 Showing the problem 2:00 Problem 1: The source columns are gone 2:17 Problem 2: The columns are out of order 2:50 Merge […]