COUNTIF tips

The COUNTIF function can provide us with very useful information. From a range you can count empty cells, the non-empty cells, or cells with all text. Let us see it with an example.

Tutorial about charting in Excel

Here you will find all the explanations for the charts that I've been putting on the blog:

1. Tornado chart

2. Waterfall chart

3. Band chart

4. Thermometer chart

5. Speedometer chart (coming soon)

6. Growing moustache chart (coming soon)

7. Sparklines (coming soon)

8. In-cell horizontal bar chart using REPT function (coming soon)

9.  In-cell vertical bar chart using REPT and CONCATENATE functions (coming soon)

10. Panel chart (coming soon)

Introduction to band charts in Excel

Today I want to talk about creating band charts in Excel. I use them quite a lot as they help the user to a quick understanding of the visualisation without overloading the chart.

So that we all understand, what I intend to show you is a chart like the following:

The band represents the area between the maximum and the minimum for all products for any one year.

It can also be seen that it is a dynamic chart, each time a product is selected one series in the chart change (could be more if you wanted).


To build a band chart like that one we need to follow a few steps. Let's see how.

Selecting a range from many using the CHOOSE function

A while ago I wrote about the how to convert long NESTED IF formulas to a simple formula with the CHOOSE function. Today we are going to see what else we can achieve by using the CHOOSE function.

We can fetch a range from a selection of ranges

How do we do that?

Let's see by looking at one example:

Say that we have 4 ranges, {A1:A10}, {B1:B10}, {C1:C10} and {D1:D10}, and that depending on a condition or a selection by the user we want to use one of them, for example, we want to sum up the range.

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

5 cool tricks for PivotTables

Today we are going to see 5 super tricks to use when working with PivotTables. With these tricks you will increase the potential of the functionality of our good friends the PivotTables.

1. See which transactions make up a value in the PivotTable

How to count the number of unique values in a range in Excel

Hello, on many occasions we have a list or range with lots of data, and we wondered how many of those values or data are unique, and we have had to develop a complex solution by adding helper columns and doing multiple operations.

That is no longer necessary !!

Because we can calculate the number of unique values in a list with a simple formula.
And what formula is that ?? Let's see ...

Array formulas in Excel

What is an array ??

The term array refers to a collection of data residing in a column or row of Excel or a combination of them. In Excel, these elements may reside in a single row (what is called a one-dimensional horizontal array), a column (one-dimensional vertical array ) or multiple rows and columns (two-dimensional array). In Excel you can not create array formulas or arrays that are three-dimensional.

And an array formula ... ??

"I have enough with the formulas, and now you talk about array formulas.. Help!!"

...

If you feel like this right now wait, they are simpler than the name suggests, and learning them it is for Excel power users.

How to calculate AGE in Excel

Well today I want to look at one of those problems we face from time to time, and that is none other than how to calculate the exact age using Excel, and considering that having to include leap years can present a bigger challenge than we initially anticipated. Well let's see how it's done.

In this tutorial we will look at three methods to calculate age in cell C2, being cell A2 the date of birth and B2 today's date, calculated through the Excel function TODAY.

Conditional Formatting - Formula to search values

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells.

Imagine you have the list of European football teams, located in the range B5:B31.

What we want to achieve is to enter some text in a cell, for example D2, and if that text is in any value from the list then highlight the cell(s) in the way you want.

To do that the formula to be used is:

Conditional Formatting - Formula to highlight duplicates, errors and omissions

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells

1. Highlight duplicates in a list

For this example we will use a standard list of data like this. This list is in the range B7:B23.

So the formula to highlight duplicates is:

=COUNTIF($B$8:$B8,$B8)>1

How to build a Waterfall chart in Excel

A Waterfall chart???? Yes, in this post we are going to see what I mean by that.

The Waterfall chart in Excel is a type of chart that includes what appears to be floating columns, which help to better visualize the contribution of all parties to the total.