All articles in 'Excel Howtos' Category

Lets say you are looking at some data as shown above and wondering what is the sum of budgets for top 3 projects in East region with Low priority. How would you do that with formulas?

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.

Tony sends this chart and asks if it can be done in Excel.

It sounded like a good challenge for a lazy Sunday morning. So here we go. (Posting it on Monday).

Now I could not get an oil rig photo or that data. So I made up few numbers and used a photo of Flinders street station I took when I was in Melbourne last year.

Imagine you have a worksheet with lots of charts. And you want to make it look awesome & clean.

Solution?

Simple, create an interactive chart so that your users can pick one of many charts and see them.

Today let us understand how to create an interactive chart using Excel.

Last week, we had our very first quiz – “How well do you know your LOOKUPs?”. I hope you have enjoyed it.

Today lets understand the answers & explanations for this quiz.

Recently, I had a peculiar problem. I have a list of zip codes and I wanted to find out nearest zip codes for each of them.

Now, If I wanted to find out near by zip codes for one area, I could go and search in Google. But, how to do it for dozens of them?

Today, lets understand how you can use Excel (that’s right) to do this automatically. We will be using Excel 2013 for this.

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.

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.

Today lets tackle a familiar data clean-up problem using Excel – Transposing data.

That is, we want to take all rows in our data & make them columns. Something like this:

Learn these 4 techniques to transpose data:
1. Using Paste Special > Transpose
2. Using INDEX formula & Helper cells
3. Using INDEX, ROWS & COLUMNS formulas
4. Using TRANSPOSE Formula

Hello folks,

If this article was a person, they would be schizophrenic. You see, it has 2 purposes:

• Give you all the details about my upcoming Power Pivot course
• Give you a solution to last week’s vacation days problem

Details about Power Pivot Course
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from familiar interface of Excel. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

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.

A while ago, we published a new year resolution template. This was a hit with our reader with thousands of you downloading it. During last week, Peppe, one of our readers from Italy, took this template and made it even more awesome.

The original template had tasks and completion check mark. As you finish each task, you can see the progress bar moving.

Peppe added priorities to this. With his new version, progress is measured based on how much priority we assigned that particular task. Pretty neat eh?!?

In this post we will learn how to use the Advanced Filter option using VBA to allow us to filter our data on a separate sheet. This has been requested by a lot of our readers and here is how we will use them.

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.

Pivot tables are great help when analyzing lots of data. One of the common questions managers & analysts ask (when looking at monthly sales data for example) is,

How is the monthly performance of our teams (or regions, products etc.)?

A pivot report can answer this question in a snap. Today lets learn how to do that.