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