Tag Archives: Excel

How to use Excel’s cell comments

You can add descriptive comments to Excel cells. Comment doesn’t have any influence to content of the cell. Each comment can be always visible or visible only when mouse pointer is on cell. You can drag always visible comments to any location on the worksheet. Comment boxes are also resizable.

Following video demonstrates this:

Excel VBA macro: How to implement running clock

Following video demonstrates how to implement simple VBA macro showing running clock in Excel worksheet. My solution has three macros:

  • RunClock: Sets cell (A1) value to current time by using Now()-function. Then it recursively calls itself in every one second until global variable clockOn = FALSE.
  • StartClock: Sets global variable clockOn = TRUE and calls RunClock
  • StopClock: Sets global variable clockOn = FALSE
Recursive call is made using Excel’s function
 
Application.Ontime(EarliestTime, Procedure, LatestTime, Schedule)
In this case only first two parameters are needed:
  • EarliestTime: Time when Procedure is been run. In this case Now + TimeValue(“00:00:01”)
  • Procedure: The name of the procedure. In this case RunClock itself
If you want to create buttons for starting and stopping the clock, please check how to add button

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:

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: