Excel MVP Oz Du Soleil contacted me a few days ago with an interesting Excel challenge: “how could Excel alphabetize titles, but ignore A, An or The if a title begins with one of those?” Continue reading
Video 00149 Alphabetize Challenge (Oz001)
Post Category → Solutions
Video 00148 Insert Rows & Cascade Numbers
Our task is to repeat each original row three times and then cascade each original number diagonally down to the right. Continue reading
Video 00142 Horizontal list to Vertical list (layered transpose)
We don’t always find out how data got twisted into awkward forms. Our job is to fix it by normalizing, unwinding, transposing, etc. Once the data is in the correct format we can then get to the real job of extracting meaning from it. Continue reading
Video 00139 Beyond 3D Formulas
Excel’s 3D formulas allow you to work with values from the same range in different sheets. However, not all Excel functions work with 3D formulas. Read my post to see how they work, how to go beyond 3D formulas and how to often avoid complex formulas with proper spreadsheet design! Continue reading
Video 00122 Normalizing Exported Data
Many of us enjoy puzzles. Sudoku, Crosswords, Chess, etc. I solve a lot of data puzzles using Microsoft Excel. A few months ago, a YouTube viewer had this question for me. Continue reading
Video 00135 Cali Data Cleanup
Have you ever received an awkward data set? In my life as a Data Analyst this has happened to me many times in different shapes and forms. Continue reading
Video 00137 Vlookup_Returns Multiple Values
Vlookup returns only 1 value, right? Well…normally yes but there are some tricks to return multiple values (numbers) and add them up. Continue reading
Video 00121_Crazy String Manipulation!
Look carefully at the picture below for a few seconds…
If you extract all numbers, from left to right, you can create a Date/Time value of Nov 28, 2014 5:08pm (and 51 seconds) in Microsoft Excel.
Continue reading
Video 00120 Is the solution benefit worth the cost
Is the solution benefit worth the cost?
With any product there is a balance between the number of features versus the cost/performance. Race cars and stretch limos have different features and different purposes. Continue reading
Video 00114_Cleanup Data From Internet
Have you copied data from the internet and pasted it in Excel?
It can be very frustrating when the data is messy!
Sometimes there are data quality issues and you should examine the data before adding formulas and formatting.