Tag Archives: Tutorial

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.

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:

Excel VBA Macro: How to use InputBox

Following example video demonstrates how to use Input Box dialog from the Excel VBA Macro code.  In this example input type is Range-object. Example code asks user to select cells and then fills those cells with text “Foo”.

 

How to customise Excel table’s look and feel

There are numerous ways you can customise how your Excel table looks. Yo can e.g.

  • Change the font color or cell’s fill color
  • Make texts bold, italic, etc.
  • Draw different kinds of borders around your cells
  • Align cell’s texts different ways
  • Merge cells to make it easier to align texts the way you like
  • Adjust column widths and cell heights
  • Hide Excel’s gridlines
  • …and much more…
A shortcut tip: You can open Format Cells -dialog by pressing
  • Windows: ctrl + 1
  • Mac: cmd + 1

Following demonstration video shows some of these techniques to format a table:

How to use Excel’s VLOOKUP-function

VLOOKUP-function is a great help when you need to find data from the large tables. With VLOOKUP you can search for a value in the first column of the table and return a value in the same row from the column you specify. VLOOKUP has three mandatory parameters and one optional parameter:

  1. parameter: Value to search in the first column
  2. parameter: Table to search from
  3. parameter: Index of the column from which the matching value must be returned. The index of the first column is 1
  4. optional parameter: If omitted or TRUE, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will only find an exact match.
The following video demonstrates a simple example how to use VLOOKUP: