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
Our task is to repeat each original row three times and then cascade each original number diagonally down to the right. Continue reading
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
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
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
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
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
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.
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
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.