All articles with 'quick tip' Tag
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
Excel contains an often overlooked function
The Excel = Function
Today we quickly review what it can do for us.
Zebra lines, the dull highlighting applied to alternative rows is a very good way to make your tables readable & pretty.
We can use either conditional formatting or table formats to quickly add zebra lines to our data.
But what if you want a little more?
What if you want to highlight, lets say 3 rows in one color and 3 in another and repeat this …Continue »
Sometimes you want to turnoff decimal points if the value after point is 0. Mireya, Chandoo.org member had one such situation. She writes:
I am a complete beginner in excel, how can I keep the zeros when I am working with decimals and remove them when are not required.
This is where we can use General formatting. Read on to learn how General formatting works and what to do in extreme cases.Continue »
It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,
A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.
So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).Continue »
Each new sheet in MS Excel comes up with a 1,048,576 rows and 16,384 columns. While it has a certain binary romantic ring to it (2^20 rows & 2^14 columns), I am yet to meet anyone using even half the number of rows & columns Excel has to offer.
So why leave all those empty rows & columns hanging in your reports?
Would it not look cool if your reports showed only few rows & columns as needed, like this:Continue »
A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.
Today, lets learn a simpler formula to check if a list has duplicate numbers.
Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.Continue »
Here is a quick tip that I learned while conducting training classes in Australia. If you have several dates in a range and you want to find out what the latest date is, just use MAX, like: =MAX(A1:A10) would give you the latest date. A Question…, Assuming you have some dates (not necessarily sorted) in [...]Continue »
Ever looked at a Pivot table & wondered how you can sort it differently?
“If only I could show this report of monthly sales such that our best months are on top!”
Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.Continue »