Tag Archives: Office

How to use Excel’s DATE, DATEVALUE, MONTH, YEAR and DAY functions

Following video demonstrates the usage of Excel’s Date functions:

  • DATE() : Takes year, month and day as parameters and returns Excel date value.
  • DATEVALUE(): Takes date string as parameter and returns Excel date value.
  • YEAR(): Takes Excel date value as parameter and returns corresponding year number (1900..9999).
  • MONTH(): Takes Excel date value as parameter and returns corresponding month number (1..12).
  • DAY(): Takes Excel date value as parameter and returns corresponding day number (1..31).
If you are unfamiliar with Excel’s way to handle dates, please check
Excel date system basics.

How to use Excel COUNT functions

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

  • COUNT(value1value2,…): Counts number of cells (or arguments) containing numbers
  • COUNTA(value1value2,…): Counts number of non empty cells (or arguments)
  • COUNTIF(range, criteria): Counts number of cells meeting the criteria
  • COUNTIFS(range1, criteria1range2,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:

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 🙂

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.

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.

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:

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.

Excel Filter: How to filter data according to dates

Filter Options

Filter Options

Today I will demonstrate Excel’s powerful capabilities to filter date columns. You have many options for selecting which time period to show or not to show.

In following video I will show how to select time period

  • by selecting start date and end date
  • by selecting “Last Year” from the menu
  • by selecting any year

Excel Filter: How to filter data according to text content

Following demonstration video shows how to filter data according to text content. In this example case we have list of countries and their populations. The list contains independent countries and dependent territories with country it belongs to in parenthesis. We can show only independent countries by filtering “country name”-column with criteria “show only cells not containing parenthesis” and with the opposite logic show only dependent territories. The video will clarify this: