Show Months & Years in Charts without Cluttering
We make charts with date axis all the time. So, today we will learn how to set up axis settings in Excel so that Months & Years are shown neatly structured without cluttering your chart. See the example next to understand.
Welcome to Chandoo.org. A short introduction to this website
Over the last few weeks we have seen huge influx of new members to our little blog. It is good time I said hello and introduced my site to you.
PS: If you have been at chandoo.org for a while, you can still find a good link or nugget in this post. So read on.
Formatting Multiple Worksheets? Use Group Sheets option to Speed up [Quick Tip]
Often we come across workbooks that have similar formatting needs for multiple worksheets. For eg. you may have sales records spanning across 12 worksheets, one for each month. Now as a loyal reader of chandoo.org, you want to keep the formatting of all these worksheets consistent. So here is a quick tip to begin your work week.
90% of you can see up to cell M26 & other findings [visualization]
Two weeks back I ran a poll asking you to tell me what is the last visible cell in your excel window. It was a casual poll. So I didn’t expect a lot of responses. But, boy I was wrong! 295 of you have responded at last count. This is so much more than what I expected.
As I said, the purpose of this poll is to make a visualization out of the poll responses. In this post you can see the chart(s) I made from this data & learn more.
Highlighting Data Points in Scatter and Line Charts
Introducing a method of allowing data points to be interactively highlighted in Excel Scatter / X-Y Charts and Line Charts.
You will see a lot of these style charts in various places where you want to highlight various aspects of the chart to your audience. It is a great technique for complex scientific and engineering charts where you may have hundreds or thousands of points.
Getting the 2nd matching value from a list using VLOOKUP formula
Situation
We know that VLOOKUP formula is useful to fetch the first matching item from a list. So what would you do if you need 2nd (or 3rd etc.) matching item from a list?
For eg. If you have below data, and you want to find out how much sales John made 2nd time, then VLOOKUP formula becomes quite useless. Or is it?!?
Read more to find how to solve this.
How to write 2 Way Lookup Formulas in Excel?
Situation
So far we have seen what VLOOKUP formula is and how to put it to some nifty uses. Today, we will go one step further and learn how to do 2 Way Lookups.
What is a 2 Way Lookup?
Lookup is when you find a value in one column and get the corresponding element from other columns. 2 Way Lookup is when you lookup value at the interesection corresponding to a given row & column values.
For example, assuming you have data like below, and you want to findout how much sales Joseph made in month of March, you are essentially doing a 2 way lookup.
Read more to find how to solve this.