Extract currency amounts from text – Power Query Tutorial
Let’s say you got some text values and want to extract the amounts from them. Something like above.
How to go about it?
We could use a variety of techniques to extract the values.
- Formulas – not easy given the unstructured nature of data. But almost possible. See this for an example.
- VBA – possible, read this forum discussion few ways to do it.
- Power Query – at first glance it might seem tricky, but PQ makes this all too easy. Read on.
Joyplot in Excel
Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.
Take a look at final outcome above. Read on to learn more.
SUMPRODUCT Vs. Power Query on Mt. KauKau
When faced with tough problems I react in one of three ways
- Come up with ingenious solutions
- See if a simpler cheat solution is possible
- Sit back and ignore
For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.
When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.
Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]
Imagine you are head of human resources at Casual Contracting Co. Every month you hire a lot of temporary staff who spend 1-4 months with CCC before leaving. Sometimes you hire the same people again. Of late, you have noticed a strange process gap. You are paying same person two (or more) salaries.
This is because you are hiring a person for new temp role even before their current one ended. See above picture.
So how to avoid making such hiring boo-boos.
Simple, using Excel of course.
Relative References in Excel Tables
Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.
- Introduction to Excel tables
- How to use structured referencing
- Tables and Relationships in Excel
- Using lookups and other formulas with Excel tables
- Simple way to get absolute references in Tables
- Customizing table styles for awesome usability
While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.
Modelling Inventory Run Rate & Cash Flows using Excel
Imagine you run an office furniture company. You want to stop reordering two brands of furniture – Relaxer (a type of chair) and Boca Top (a type of table). You currently have 20,000 Relaxer chairs and 5,000 Boca Tops. These are valued at $200,000 and $100,000 respectively. When sold, they will yield $100,000 and $25,000 gross profit. You are hoping to sell them off in 2 or 3 years. You forecast that we can sell off these as per some yearly schedule.
You need to analyze this and prepare a cash flow model.
Let’s learn how to answer such open ended questions using various analysis techniques in Excel.
There are 5 hidden cells in this workbook – Find them all [Excel Easter Eggs]
It is Easter time, and that means time for another fun Easter Egg hunt in the spreadsheet. For the last 8 years (since 2009), I have been running Easter Egg hunt at Chandoo.org. This year too, I have prepared an exciting egg extraction enigma for you. Check it out.
Can you find all the 5 hidden cells in this workbook?
First step. Download this workbook. There are five worksheets, each containing one egg. You need to find the hidden cell in each worksheet.