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…)
Something weird is going on here…
You’re trying to compare two lists of data to see what items from ‘List B’ are in ‘List A’. Sounds simple enough, right?
But some of the lookup values are not found even though you can clearly see the value in ‘List B’.
Sometimes our data isn’t perfect and we just have to deal with it. In this post you’ll see an awkward data-set from Mr Excel with a VBA solution from Bob Umlas and a formula solution from me. (more…)
Hide and Seek
Imagine a data validation drop down list with many names that are not sorted. Trying to find a particular name in that list would get frustrating as the list grows.
Did you know that there are ways to sort text in Excel? (more…)
Create a dynamic hyperlink using an array formula!
This means that you can jump to a location in your data depending on conditions in the values.
This is one of my favorite ways to use an array formula! (more…)