All articles with 'Learn Excel' Tag
Time for some logic check.
Suppose, you have three logic values in A1:C1 (TRUE or FALSE values in each cell)
You need to find out if ONLY two of these values are TRUE.
How would you write the formula?
Got an answer? Awesome. Just post your formula in the comments. Let’s see how much variety we can get from all of our readers.Continue »
Let’s talk about the untrimmable spaces.
We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.
So for example, if A1 has ” something and one more ”
will give “something and one more”
We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31). Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.
The untrimmable spaces…?
The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”
Naturally I tried to TRIM().
But the data won’t budge. See above.
Hmm, let’s investigate why.Continue »
Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.
And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.
Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).
So how would you write the lookup formula?Continue »
Over on Facebook, Kristin asks, Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem.
Let’s simplify Kristin’s problem.
You have some data in the format shown above.
And you want to find out the BP category for each reading, using some rules. Read on to solve the problem.Continue »
Let’s say you work in super hero factory as floor manager. You are looking at the recent time sheet data submitted by your underlings and want to know who works more. So you did what any self respecting floor manager does. You made yourself a large cup of hot chocolate, whipped open Excel and created a column chart.
But now, you want to add a line to it at 6:00 PM (or some other arbitrary point) so you can clearly see which superheros are over working.
So how do you go about it?Continue »
It’s Halloween time. As adults, we can’t go trick or treating. We can of course dress up in costumes and entertain others. But what about the poor spreadsheets. Don’t they deserve some of this fun too?
Hell yeah! So I made a spider web generator in Excel. Just use it to make a spooky cob web pattern and add it to your report / dashboard / time sheet or whatever else. Surprise your colleagues.Continue »
In the 56th episode of Chandoo.org podcast, let me answer the chicken and egg question of Excel users. How many formulas should you care to learn?
What is in this session?
In this podcast,
- Two personal updates
- 3 legs of formula writing
- Function knowledge
- 6 categories of must-know functions
- Basic math
- Date & time
- Work specific
- Closing remarks & resources for you
Here is a quick Pivot table tip. Let’s say your work at ACME inc. requires some fancy pants analysis of product sales. Imagine looking at below data & trying to find out the earliest & latest date for each product sale.
Of course, we can concoct a version of MINIFS & MAXIFS to answer the question. But why bother, when you can answer the question with just a few clicks.Continue »
Over at twitter, @for_the_moves asks,
@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.
— Chandoo.org (@r1c1) October 12, 2016
That got me thinking. How many functions should you care to learn?Continue »
Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like above.
How would you go about it?
If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?
Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.Continue »
Ladies & gentlemen, its time we revived the much loved Chandoo.org podcast. In the 55th episode, I do a lousy imitation of Arnold Schwarzenegger’s famous “I will be back” and tell you why there was such a long gap between episodes, my plans for reviving our podcast and more.
What is in this session?
In this podcast,
- Why there was such a long gap between last and this episode
- What next?
- How to extract every 6th item from a list?
It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.
Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.
My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?
But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.
So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.Continue »
Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.
- Right click on any value field
- Go to Value field settings
- Click on “Number Format” button
- Choose Currency format
- Close the boxes, one after another
Unless you get paid per click, you wont be happy with all those clicks.
Wouldn’t it be cool to just click once and apply most common format to your pivot fields?
Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.Continue »
Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.
Here is a ridiculously simple way to do it.Continue »
Wellington(NZ) sure is beautiful on a clear day. There are so many cool bike tracks, walking trails and beaches to keep you busy. Today, I went cycling on my usual route towards Makara beach. At the turn off point, I thought, “This is selfish. I can’t keep all this beauty to myself. I must share it with you.” So here we go.
A quick video about data analysis while on bike
Technically, I was not on bike when recording this video. Watch it below or on our YouTube Channel.Continue »