All articles with 'Learn Excel' Tag
Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig.
If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.Continue »
Introducing Data from Hell:
Watch out, its data from hell. In this new video series, we are going to examine some nutty, frustrating and fun data reshaping challenges and solve them using Excel. We will use Power Query, Formulas, VBA or other features as needed to free this data from damnation.
For our first installment, let’s reshape unevenly spaced list of values to a table.Continue »
Last week, we talked about how to copy and paste visible cells alone (ie exclude any filtered rows or hidden columns etc.) In the comments section many of you suggested two more ways to deal with this annoying problem. Let’s take a look them.Continue »
Here is something annoying with Excel.
Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.
What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.Continue »
Game for some charting awesomeness?
Off late, I have been doing a lot of data analysis and visualization on performance ratings, salary hike, gender pay equality etc. Today let me share you an awesome way to visualize massive amounts of data.
Scenario: Your organization of 3,686 people recently went thru annual performance ratings & review process. At the end of it, everyone was offered some salary increase (from $0 to $24,000 per year). You have 7 business groups. How do you tell the story of all these salary hikes in one chart?
How about the one above?
Ready to know how to create this in Excel? Read on.Continue »
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.
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.
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.Continue »
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.Continue »
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.Continue »
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?
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.Continue »
Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like above.
How to show selective sub-totals in Pivot TablesContinue »
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.Continue »
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.Continue »
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?Continue »