Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Use File > Info to quickly unprotect multiple worksheets [Quick tips]

Ever had a workbook with multiple protected worksheets? May be you are enterprise architect at Death Star or chief strategist at Mordor and got all the plans in a tidy little but protected workbook. Of course, you hate having to unprotect many of the worksheets every time you have a new evil plan for world domination. Don’t you worry, you can use this handy little trick to unproect en masse.

Just open the workbook
Go to File > Info
Right on the top, you can see all protected worksheets and a link to unprotect them.
Click to unprotect the ones you want to.
Done.

Read This »

August 1, 2017

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.

Read This »

July 27, 2017

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.

Read This »

July 12, 2017

How to add a lot of Goal Seeking to a model

Recently in the Chandoo.org Forums, MR06 asked the question, “I am trying to create a sensitivity table that tells me what amount of equity I

Read This »

July 4, 2017

Rounding time to nearest minute or quarter hour etc. [formulas]

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute. We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

Use below formulas to round time in A1.

Read This »

June 26, 2017

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.

Read This »

June 15, 2017

Jo’s first keyboard shortcut

Jo, my lovely wife quit her job as my partner in crime at Chandoo.org recently and took up a lucrative position at NZ govt. agency. The other day I asked her “how was your day?” when she got home. She smiled and said, “I learned my first Excel shortcut!”.
Guess what it is?

F4.

That is right. The mighty F4 key. You can use it to repeat any action.

Jo was using it to insert rows in her workbook. After inserting first row (using CTRL+ of course), she would press F4 to add more rows as needed.

Read This »

June 12, 2017

Start your bar charts from zero – Excel geeks screaming at you from mountain top

Here is a simple but vital charting rule.

Start your bar (or column) charts from zero.

To illustrate why you should do this, let me share a personal example.

Over the weekend, the Jon Peltier visited Wellington. He is staying with Jeff (who occasionally guest blogs on Chandoo.org). On Sunday, we all decided to hike up a small mountain near my house for a leisurely family picnic.

While on the top of the mountain, Jo (my wife) took a few pics of us three Excel geeks. As we were standing on a sloping mountain face this is how the pictures look.

Looking at the picture on left, you would confidently say that I am way shorter than other two. But picture on right tells a different story.

Read This »

May 15, 2017