All articles with 'Microsoft Excel Formulas' Tag

Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

How to combine the power of pivot tables with elegance of your dashboards?

The answer is: GETPIVOTDATA()

We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention […]

Here is a simple & effective tip on charting.

Give your charts descriptive & bold titles.

How to set up title that are smart & descriptive?

Simple, follow below steps.

1. Create the title you want in a cell
2. Select the chart title
3. Go to formula bar, press = and point to the cell with title
4. Press enter.

Whenever we talk about product ratings & customer satisfaction, 5 star ratings come to our mind. Today, let’s learn how to create a simple & elegant 5 star in-cell chart in Excel. Something like above.

Read on to learn how to create the above chart.

VLOOKUP is one of the most useful Excel functions. So much so that I even wrote a book about it. But it has one serious limitation.

It looks up the first occurrence and returns corresponding data.

What if you want to find the last value?

Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?

We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

Slicers – what are they?

Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.

1. Add region as report filter and filter for the region you want.
2. Add a slicer on region and click on the region you want.

With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.

Read more to learn all about slicers

Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.

So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.

Calculating Bill’s total working hours (video)

Here is a familiar problem: You create a workbook to track some data. You ask your staff to fill up the data. Almost all the input data is fine, except the date column. Every one types dates in their own format. Here is a fun, simple & powerful way to warn your users when they […]

True story:

On Friday (17th April – 2015), I flew from Vizag (my town) to Hyderabad so that I can catch a flight to San Francisco to attend a conference. As I had 10 hours of overlay between the flights in Hyderabad, I checked in to a lounge area so that I can watch some sports, eat food while pretending to do work on my laptop. There was a gentleman sitting in adjacent space doing some work in Excel. As I began to compose few emails, the gentleman in next sitting space asked me what I do for living. Our conversation went like this.

Me: I run a software company
He: Oh, so you must be good with computers
Me: smiles and cringes at the stereotyping
He: What is the formula to select all the blank cells in my Excel data and highlight them in Yellow color

Mind you, he had no idea that I work in Excel. We were 2 random guys in airport lounge watching sports and eating miserable food.

Me: Well, what are you trying to do?
He: You see, I am auditing this data. I need to locate all the blank rows and set them in different color so that my staff can fill up missing information. Right now, I am selecting one row at a time and filling the colors. Is there a one step solution to this problem?

Needless to say, I showed him how to do it faster, which led to an interesting 3 hours at the lounge.

End of true story.

So today, let’s understand how to find & highlight all the blank cells in the data.

Hello folks,

Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

1. Who is the best seller?
2. Who is the most active seller?
3. Who is the most consistent seller?
4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Here is a fun problem to think about.

Let’s say you are looking at some data like above.

And you want to find out what is the longest streak of wins in the list.

How do you calculate it?

bonus question: What formula calculates when the longest streak began?

Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”

So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.

Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:

• Regular incandescent bulbs (the kind Hogwarts currently uses)
• Compact Fluorescent Light bulbs (CFL)
• Light Emitting Diode bulbs (LED)

Now your job is to do a cost benefit analysis of these options and pick one.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 28th session of Chandoo.org podcast, let’s figure out how to express business rules & logic to Excel.

What is in this session?

What good are spreadsheets if they can’t solve business problems?

But we all struggle when it comes to modeling real world business conditions in Excel. For example, if you have below business rule to decide how much discount to offer a customer,

• If the customer bought 3 or more times previously and offer 15% discount
• If the customer bought 1 or 2 times previously AND customer’s age is >40, offer 10% discount
• If the customer visited our New York store between 6PM-9PM offer 5% discount
• Else no discount

How would you go about modeling these in Excel?

That is our topic for this podcast session.

In this podcast, you will learn

• The challenge of modeling business logic & rules in Excel
• My struggles with such formulas in early days
• 4 features of Excel that can help you with this.
• Example business rules & how to write formulas

Want to write formulas faster? Here is a quick tip.

Use Auto-correct!

That is right. Excel’s auto-correct feature can be setup to help you write formulas faster. See above demo. Read on for details.