Find last day of any month with this simple trick [formulas]
Here is a handy trick to calculate last day of any month.
Assuming y and m contain the year & month for which you want to find the last day’s date, write
=DATE(y, m+1,0)
That is right, you can use ZERO (0) as the day.
When you do this, Excel tells us the last day of previous month.
Replace formulas with values using this shortcut [quick tip]
Often in my work, I need to replace a bunch of formulas with values. Blame it on old habits, but this is what I used to do:
- Copy the cells with formulas (CTRL+C)
- Press ALT+ESV and then enter.
While this is ok, it does take quite a bit of time and key strokes.
Here is a shorter way I recently learned…
Sort by Birthday [Quick tip]
Lets say you have a list of employees and their birthdays. Now you want to sort this list, based on their birthday, not age. How would you do it?
How to find the lowest value? [Quick tip]
Lets say you are the head of purchasing department at Big Corp Co.
You are obviously very busy. Every day starting with a large cup of coffee and ends with a big smile, as you save your company thousands of $s by negotiating best deals, finding best providers and being awesome.
Today, let me share a small Excel tip with you that will make you even more awesome.
How to transpose a values in a row to column using formulas… [Quick tip]
This is interesting, I am in Columbus to meet one of my college friends. I remember him as a very meticulous person from college days. So it is no surprise when he showed me his massively impressive finance tracker last night. He has been tracking expenses, income, credit card payments and gas (petrol) consumption since 2008. Very impressive indeed.
Then out of blue he said, he has a problem with his spreadsheet. In this own words,
When entering data for credit cards, I use one column per card. But in my report view, I want to show credit card details in rows. How do I do this?
Something like above…. Today, lets learn how to do this using Excel formulas.
How to remove all cells containing John (or anything else) [Quick tip]
Here is an interesting question someone asked me recently,
If I have to delete all rows with “John” in it. Do you know how to do it?
Well, it looks like they really hate John. But it is none of my business.
So lets go ahead and understand a dead-simple way to get rid of all cells with John or whoever else you fancy.
Work with several Excel files everyday? – Save them as a workspace [Quick tip]
If you work with multiple Excel workbooks everyday, then here is a handy tip.
Use Save workspace feature to save your workbook collection & layout.