All articles with 'Microsoft Excel Formulas' Tag

Checking if a value falls between 2 other values is fairly common when you are working with data. In today’s quick tip, we are going to learn how to check for such between conditions in excel. You will be learning how to check if a value, date or text falls between 2 other values, dates or texts (when arranged in dictionary order) as shown in the picture aside.

Comparison of lists of data is something that we do all the time. Today, lets learn a few tricks that you can apply immediately to compare 2 lists using Excel. This post discusses how to compare two lists with formula based rules. If you just want to quickly highlight common values, click here. If you […]

It is almost 3:30 am now, I stayed awake for last 23 hours so that Excel School 2 can be ready for rolling. But that is no excuse for not having a post here. So here it goes. Excel has formulas for converting a bunch of text to UPPER, lower and Proper Cases. But not […]

The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard. Now, if she wants average of all values, we can use AVERAGE() formula if she wants top 5 values […]

Dashboards can be overwhelming with lots of details and context. A simple way to drag user’s attention to important stuff in the dashboard is to use alerts. See this example to understand what alerts mean. How to display alerts in Excel Dashboards? The easiest way to display alerts is to use Excel 2007’s Conditional Formatting […]

Denice, an Excel School student emailed me an interesting problem. I have a bunch of data from which I want to find the sum of values that meet a criteria. But I also want to exclude any rows that are hidden. Well, we know how to find sum of values that meet a criteria – […]

If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape!

How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost”

This post is going to detail the use of the Data Table function within Excel, which can help you answer that question and then so so much more.

We all know that Chart Data Labels help us highlight important data points. When you “add data labels” to a chart series, excel can show either “category” , “series” or “data point values” as data labels.

But what if you want to have a data label that is altogether different, like this:

Of course you can point data labels to any cell. In this quick tip, we will learn how to do this, read on…

In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this: My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation […]

In yesterdays post – Find Quarterly Totals from Monthly Data, we have learned how to use SUMPRODUCT formula to find totals by Quarter from a set of monthly values. The approach is fine, but has one glaring draw back. It only works when you have data for one year. In that post, Martin said, … […]

Here is a problem we face very frequently. You have a list of values by months. And you want to find out the totals by Quarter. How do you go about it? There are 2 options: You can make a pivot report from the data and then group dates in that to find totals by […]

We all know that SUMIF formula can be used to find the sum of values meeting a criteria. Like this, But I was pleasantly surprised to realize that SUMIF works equally well for 2D ranges too, like this: During a recent consulting work with a client I had a requirement to sum up values that […]

In a follow up to last week’s how to visualize survey data in excel, we will explore how you can create an incell dot plot panel chart. Please follow the links in the article to get more commentary and insights from established persona in the visualization world (including Stephen Few, Naomi, Jon etc.)

We all know that legend can be added to a chart to provide useful information, color codes etc.

Today we will learn how to make the chart legends smarter so that they provide more meaning and context to the chart, like this:

This type of legend can be more useful than a plain legend as this provides more useful information without taking up too much space.

Sometimes we think there is very little we can improve in something and then we come across an idea that would change our perceptions. I have been writing excel formulas for such a long time that it was easier to remember when I first shaved than when I first wrote a formula. (may be, because […]