How to use AGGREGATE function in Excel 2013

AGGREGATE function is very useful when we need to do a series of summary statistics calculations on subsets of data in Excel 2013

AGGREGATE function combines 19 commonly used excel stand-alone functions like SUM, AVERAGE, MEDIAN, COUNT etc.

AGGREGATE function is introduced to improve on the old SUBTOTAL function adding eight important functions like MEDIAN, MODE, PERCENTILE and QUARTILE

The key advantage of AGGREGATE function is that it includes options to ignore hidden rows, error values or both. Thus, it is very useful for performing operations only on filtered data.

Syntax:
=AGGREGATE(function_num, options, array, [k])
=AGGREGATE(function_num, options, ref1, [ref2], …)

Arguments:
array: range of cells
k:        required argument for percentile and quartile values
ref1:   first number in a string of cell references

How to count cells that contain numbers in Excel 2013 - COUNT

The COUNT function is used to count cells that contain numbers.

Syntax:
=COUNT(value1, [value2],..)

Arguments:
value1: These can be cell references or can be directly entered. A continuous range of cells can be given as single argument.

Follow the below steps to count cells with numbers:

1. Enter the data in the worksheet

2. Select any cell where you want to get the count of cells

3. Enter an COUNT formula and select the required cell range as argument

4. Press the Enter key.

This will give the count of the cells that contain numbers

Note: Since excel stores dates in number format, the same will also be counted using COUNT function

How to count non-empty cells in Excel 2013 - COUNTA

The COUNTA function is used to count cells that are not empty.

Syntax:
=COUNTA(value1, value2, …value30)

Arguments:
value1: These can be cell references or can be directly entered. A continuous range of cells can be given as single argument.

Follow the below steps to count cells that are not empty:

1. Enter the data in the worksheet

2. Select any cell where you want to get the count of cells

3. Enter a COUNTA formula and select the required cell range as argument

4. Press the Enter key.

This will give the count of the cells that contain non-empty cells

Note: The COUNTA function also count cells with formulas (even if they were value pasted later) and spaces

For example, formula like =IF(A1="","", "na") may have inherent "" value to signify empty cell and it may appear to be blank, but COUNTA fuction will include this in its count.

Add-on:
To check if the cell contains any character (which looks empty), you can use =LEN(cell) function. This should ideally give 0 if the cell is empty.

However, the "" (often after value pasting the formula) does not get detected, to see this we need to check mark "Transition navigation keys"

File >>> Options >>> Advanced >>> Lotus Compatibility

How to count blank or empty cells in Excel 2013 using COUNTBLANK

The COUNTBLANK function is used to count empty cells in Excel 2013

Syntax:
=COUNTBLANK(range)

Arguments:
The range can be cell references or can be directly entered.

Follow the below steps to count cells that are not empty:

1. Enter the data in the worksheet

2. Select any cell where you want to get the count of blank or empty cells

3. Enter a COUNTBLANK formula and select the required cell range as argument

4. Press the Enter key.

This will give the count of the empty cells in the selected range

Note: The COUNTBLANK function will NOT count cells with formulas (even if they were value pasted later) and spaces

For example, formula like =IF(A1="","", "na") may have inherent "" value to signify empty cell and it may appear to be blank, but COUNTBLANK function will exclude this in its count.

Add-on:
To check if the cell contains any character (which looks empty), you can use =LEN(cell) function. This should ideally give 0 if the cell is empty.

However, the "" (often after value pasting the formula) does not get detected, to see this we need to check mark "Transition navigation keys"

File >>> Options >>> Advanced >>> Lotus Compatibility

Create hyperlink in a cell to another worksheet in Excel 2013

Creating a hyperlink in a cell is an efficient way to quickly navigate to specific cell of another worksheet in Excel 2013. The hyperlinks are also helpful to switch to the other worksheet and locate specific cell.

The best example of hyperlinks could be creating index pages in the first excel worksheet which is linked with all other worksheets.

Select a cell from where you want to create a hyperlink to another sheet

1. Open the Hyperlink dialog-box from "Insert" tab

Alternatively, we can use right click button or keyboard shortcuts (Alt+NI or Ctrl+K)

2. Click on "Place in This Document" button on the left, since we want to link the cell with another cell in the worksheet.

3. The options given on the right side needs to be used:

Text to display: Enter the text that needs to be displayed in the cell

Type the cell reference: Enter the cell address where you need to reach in another worksheet (default A1)

Or select a place in this document: Click on the worksheet where you want to hyperlink the cell

4. Click OK

The hyperlink will be created with specified text appearing in blue colour with an underline.

Remember: After you follow a hyperlink text to its destination, the color of its text changes from the traditional blue to a dark shade of purple.