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
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.
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 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”.
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:
parameter: Value to search in the first column
parameter: Table to search from
parameter: Index of the column from which the matching value must be returned. The index of the first column is 1
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:
In my previous blog post I launched Excel VBA macro from Ribbon bar’s Developer tab (Macros). More user friendly way is to add push button for macro directly to worksheet. Following video demonstrates how to do it: