All articles with 'quick tip' Tag
The other day, I got a text message (SMS) from one of our readers. It read,
So today, let us learn a very easy trick to select random sample from your data.Continue »
Ever wondered how to go from a bunch of cells with text to one big combined text? Like shown above.
Well, there is a simple trick. One of our readers, Grant shared this with us. Read on to learn this.Continue »
Learn how to Count Text, Blanks and Non-Blank cells with this Countif() Quick TipContinue »
You have been there. You are looking at a complex workbook with some hidden rows /columns. You want to know what is in that hidden cells. Alas, the worksheet is protected.
It is like the special coffee machine on top floor reserved for senior executives. You know it is there, but you cannot get to it. Now what?!?
Simple. Follow this process.
(I am talking about hidden cells, not the coffee machine.)Continue »
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
That is right, you can use ZERO (0) as the day.
When you do this, Excel tells us the last day of previous month.Continue »
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…Continue »
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?Continue »
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.Continue »
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.Continue »
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.Continue »
If you work with multiple Excel workbooks everyday, then here is a handy tip.
Use Save workspace feature to save your workbook collection & layout.Continue »
On Friday, we learned how to transpose a table of data using Excel formulas. Today lets learn a quicker & easier way to do this by just using copy, paste, find & replace.Continue »
Here is a quick tip to start your week. Ever wanted to insert a few columns in between like this? Here is how to do it. Hold down control key. Select one column at a time Right click and choose Insert Done! Bonus tip: You can use this to insert rows too! More Quick tips.Continue »
Here is a quick pivot table tip.
When reporting summaries by month, it would be better to highlight 3 months at a time (Jan, Feb, Mar in one color, Apr, May, Jun in another color) than showing all in one color. Today, lets learn how to do this in easiest possible way.Continue »
The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.
1. Using find replace
2. Using text to columns
3. Using UDFs