Tag Archives: Office

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

 

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:

Excel VBA macros: How to record a VBA macro

It is a good idea to automatise time consuming routine tasks in Excel. You don’t need to know how to implement VBA macros, because you can just record your tasks. In following example I record a macro that assumes that I have people’s whole names listed in column A and want to split names (first name and last name) into columns A anB. This is good example when simple recorded macro can save you from doing this routine task all over again.

Here is a video that demonstrates the VBA macro recording and shows how to run the macro:

How to import text and use Excel’s Text to Columns -feature

Normally, when you paste text to Excel, the whole text will appear in one column. Luckily Excel has nice feature called Text to Columns. With that feature you can spread your text data to columns nice and easy. In my example, I have text where items are delimited with commas and colons.

Following video demonstrates how to import that text to Excel’s columns easily: