All articles with 'quick tip' Tag

How to transpose a values in a row to column using formulas… [Quick tip]

Published on May 14, 2013 in Excel Howtos

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 »

How to remove all cells containing John (or anything else) [Quick tip]

Published on Mar 15, 2013 in Excel Howtos

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 »

Work with several Excel files everyday? – Save them as a workspace [Quick tip]

Published on Mar 4, 2013 in Learn Excel

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 »

Transpose a table quickly using Copy – Paste [Quick tips]

Published on Feb 4, 2013 in Excel Howtos

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 »

Insert Multiple Columns at once [Quick tip]

Published on Jan 15, 2013 in Excel Howtos

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 »

Highlight Quarters, Weekends in pivot reports using styles [quick tip]

Published on Nov 8, 2012 in Excel Howtos, Pivot Tables & Charts

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 »

Even faster ways to Extract file name from path [quick tip]

Published on Oct 24, 2012 in Excel Howtos, VBA Macros

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
Continue »

Check Cells for Equality – Follow Up [Quick Tip]

Published on Oct 16, 2012 in Huis, Posts by Hui, Quick Tip

Excel contains an often overlooked function
The Excel = Function
Today we quickly review what it can do for us.
Continue »

Customize Zebra lines Quickly using Table Styles [tip]

Published on Sep 19, 2012 in Excel Howtos

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 »

Show Decimal Points if needed [Quick Tip]

Published on Sep 5, 2012 in Excel Howtos

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 »

Sorting values in Olympic Medal Table style [Quick Tip]

Published on Aug 7, 2012 in Excel Howtos

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 »

Show only few rows & columns in Excel [Quick tip]

Published on Jul 24, 2012 in Excel Howtos

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 »

Check if a list has duplicate numbers [Quick tip]

Published on Jun 28, 2012 in Excel Howtos, Learn Excel

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 »

Use MAX to find latest date in a list [Quick tip]

Published on Jun 14, 2012 in Excel Howtos

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 »

Sort Pivot Tables the way you want [Quick tip]

Published on May 31, 2012 in Excel Howtos, Pivot Tables & Charts

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