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.
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
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:
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
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:
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:
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!
Following Excel Conditional Formatting example demonstrates how to highlight cells with dates older than 1 year, 4 years, 8 years and 12 years with different colours:
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”.
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…
- Windows: ctrl + 1
- Mac: cmd + 1
Following demonstration video shows some of these techniques to format a table:
Excel provides many ways to analyse data. One powerful tool is Goal Seek -tool. Following demo presents a simple example to find out body weight when target Body Mass Index (BMI) is known by using Goal Seek analysis. All we need to do is to know the formula of BMI and then define target BMI. We try to solve out the body weight that is needed to achieve our target BMI.
The formula for BMI is:
BMI = Weight[kg] / (Height[m] x Height[m])
The following video demonstrates this: