Tag Archives: Excel tips and tricks

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 Filter: How to filter number columns efficiently

Following example video demonstrates how to use Excel’s Filter-feature to analyse data of the large tables – in this case population of countries. You can easily filter e.g.

  • Top 10 list
  • Bottom 10 list
  • Above average list
  • Below average list
  • Countries with population between 5 and 10 million people
  • etc…

How to use Excel’s Freeze Panes -feature

It is sometimes annoying when scrolling in big Excel table because you may not know what data the current row or column should contain. E.g. when each row represents one person’s data, it is difficult to analyse data if you have scrolled so that you cannot see the name column and don’t know to who current row belongs and what is the title of the current column.

Excel has (of course :-)) a solution for this. It is a feature called Freeze Panes and with it you can make selected topmost rows and leftmost columns always visible.

Following example video demonstrates the feature:

Excel VBA macro example: How to count concurrent times

Here is an example VBA macro for solving a call log analysis problem:

We have call log

  • Column A: Date of the call
  • Column B: Time of the call
  • Column C: Duration of the call

How many concurrent calls?

Following screen capture demonstrates the situation:

Screen Shot 2013-02-12 at 11.54.03

To solve the problem I implemented Visual Basic macro ConcurrentCalls() and helper functions max(), min() and isConcurrent(). Here is the code:

Function max(a, b)
  If a > b Then
    max = a
  Else
    max = b
  End If
End Function

Function min(a, b)
  If a > b Then
    min = b
  Else
    min = a
  End If
End Function

Function isConcurrent(start1, end1, start2, end2)
  If max(start1, start2) <= min(end1, end2) Then
    isConcurrent = True
  Els
    isConcurrent = False
  End If
End Function

Function ConcurrentCalls()
    maxconcurrent = 1
    r1 = 2
    Do
        concurrent = 1
        r2 = 2
        Do
            start1 = Cells(r1, 1).Value + Cells(r1, 2).Value
            end1 = start1 + Cells(r1, 3).Value
            start2 = Cells(r2, 1).Value + Cells(r2, 2).Value
            end2 = start2 + Cells(r2, 3).Value
            If r1 <> r2 And isConcurrent(start1, end1, start2, end2) Then
                concurrent = concurrent + 1
            End If
            r2 = r2 + 1
        Loop Until Cells(r2, 1) = ""
        maxconcurrent = max(maxconcurrent, concurrent)

    Loop Until Cells(r1, 1) <> ""
    ConcurrentCalls = maxconcurrent
End Function

Feel free to examine and let me know if you see a bug or can imagine a better solution!

Check also 101 Excel Secrets and Everyday Excel video course.

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”.