All articles with 'COUNTA()' Tag

Create Dynamic Hyperlinks in Excel [Video]

Published on Jul 14, 2011 in Excel Howtos, Learn Excel
Create Dynamic Hyperlinks in Excel [Video]

During one my recent training programs, a participant asked an interesting question.

I have a list of values. I would like to place a hyperlink in my workbook that would always take me to the last value in the list.

Something like shown aside.

Click on the image to learn how this is done.

Continue »

Remove duplicates & sort a list using Pivot Tables

Published on Sep 27, 2010 in Analytics, Learn Excel, Pivot Tables & Charts
Remove duplicates & sort a list using Pivot Tables

Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it. It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8). But today I want to show you a technique I have been using to dynamically extract and sort […]

Continue »

How to Get “Either-Or” Condition in Data Validation?

Published on May 4, 2010 in Learn Excel
How to Get “Either-Or” Condition in Data Validation?

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 […]

Continue »

How to Check whether a Table is Filtered or not using Formulas

Published on Mar 29, 2010 in Excel Howtos, Learn Excel
How to Check whether a Table is Filtered or not using Formulas

Let us start the week with a simple formula (well, to be fair, let us start the week with a strong cup of coffee, then this formula).

Often when we have large data sets, we apply data filters to select and display only information we want to see.

Some of you know that whenever we apply filters on a dataset, we can look at status bar area to find out if any filter is applied on the current worksheet.

But, what if you need a way to show “filtering” status thru formulas? Like this…,

Continue »