Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence
Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.
Homework: Can you extract dates from text?
So who is up for a challenge? Can you use only formulas and extract dates buried inside text?
- Download this file.
- In column C, write a formula such that you can extract the date in column B
- If you succeed, post your solution here as a comment.
- If you fail, drink some coffee, start afresh.
How to make Box plots in Excel [Dashboard Essentials]
Whenever we deal with large amounts of data, one of the goals for analysis is, How is this data distributed?
This is where a Box plot can help. According to Wikipedia, a box plot is a convenient way of graphically depicting groups of numerical data through their five-number summaries: the smallest observation (sample minimum), lower quartile (Q1), median (Q2), upper quartile (Q3), and largest observation (sample maximum)
Today, let us learn how to create a box plot using MS Excel. You can also download the example workbook to play with static & interactive versions of box plots.
Find the last date of an activity
We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?
Today lets understand how to find the last date of an activity, given data like above.
Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.
Extracting numbers from text in excel [Case study]
Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.
75 Excel Speeding up Tips Shared by YOU! [Speedy Spreadsheet Week]
As part of our Speedy Spreadsheet Week, I have asked you to share your favorite tips & techniques for speeding up Excel. And what-a-mind-blowing response you gave. 75 of you responded with lots of valuable tips & ideas to speed-up Excel formulas, VBA & Everything else.
Thanks to all the contributors
Many thanks to everyone who shared their tips & ideas with us. If you like the tips, please say thanks to the contributor.
Read Excel Speeding-up tips by area
This page is broken in to 3 parts, click on any link to access those tips.
Read everything
Formula Speeding-up Tips
VBA / Macros Optimization Tips
Everything Else
Share your tips
Comparing 2 Lists with a Twist
We love to compare. The instinct to compare leaves no one. Even my two year old twins compare their toys with each other (and fight).
It would make Excel hugely popular if Microsoft builds a handy data comparison tool right in to it. Alas, they have customizable ribbon, 3d effects & equation editor…
Since comparison is one of the main uses of Excel, we have written extensively about it here.
But there is always one more interesting comparison problem. Today, I want to share one such problem, based on a comment left by N-Man.