How to use Excel COUNT functions

Excel has several functions for counting the number of cells that meet specific criteria:

• COUNT(value1, value2,...): Counts number of cells (or arguments) containing numbers
• COUNTA(value1, value2,...): Counts number of non empty cells (or arguments)
• COUNTIF(range, criteria): Counts number of cells meeting the criteria
• COUNTIFS(range1, criteria1, range2,criteria2,...): Counts number of cells meeting all given criteria
• COUNTBLANK(range): Counts number of empty cells
• There are also functions DCOUNT and DCOUNTA, but I will handle those later

Following video demonstrates the usage of COUNTX-functions:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Remove Duplicates -feature

Very typical task with Excel is to clean tables by removing duplicates. Fortunately Excel has handy tool to do it automatically. There is the feature Remove Duplicates in Data tab of Ribbon bar.

Check the video below.


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Skip Blanks in Paste Special and what does the feature do

There is an option Skip blanks in Excel's Paste Special -dialog. You may have wondered what is the purpose of that feature.

After watching this video, you will know :-)


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

The big secret has now been revealed: Skip blanks pastes only non blank cells and leaves other cells untouched.

Excel VBA macro: How to loop through selection

In following video I will show an Excel VBA macro example about how to loop through each selected cells and write location information to cells.

We can use Excel's Selection object (Range type containing selected cells of the active worksheet) and cycle through selected cells by using For-Each-Next loop:

For Each c in Selection
    ...
    c.Value = ...
    ...
Next

c stands for single cell (Range type).

Following video clarifies this.
Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel's Switch Reference -tool

Following video demonstrates nice and easy way to quickly change cell reference type between absolute and relative reference:

1. Select Formulas tab in Ribbon bar
2. Select reference in formula
3. Click Switch Reference to change reference type A1 --> $A$1 --> A$1 --> $A1

See my earlier posting about Absolute and Relative References.

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel's Formula Audit -tools

Sometimes it is hard to understand the logic of complex Excel formulas - Especially when there are lots of references to different cells or ranges. Excel has (of course :-) ) great helper tools for this. There is set of Audit Formulas -tools in Ribbon bar's Formulas tab.

• Trace Precedents: Shows with arrows from where the selected formula takes it's parameters
• Trace Dependents: Shows with arrows the formulas that contains reference to the selected cell
• Check for Errors: Tool for analysing possible errors in formula
• Remove Arrows: Clears arrows from the screen

Following video demonstrates this:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel Filter: How to analyse data by filtering multiple columns

Following example video demonstrates how to analyse big data tables by setting filtering criteria to multiple columns simultaneously. In the example below I show only countries with population less than one million and population updated recently.


Tip: If you have difficulties to see the video, try to maximise it from lower right corner: