Our task is to repeat each original row three times and then cascade each original number diagonally down to the right. (more…)
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. (more…)
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! (more…)
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. (more…)
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. (more…)
Vlookup returns only 1 value, right? Well…normally yes but there are some tricks to return multiple values (numbers) and add them up. (more…)
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. (more…)
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.
A Classic Excel Problem
In large spreadsheets if you drag formulas down too far then you are increasing the calculation time and also the chances that Excel will freeze and/or crash. (more…)