Conditional formatting in Excel is one of the things every analyst should know well. When you have to compare loads of data the best way is to plot them, but even better to colorcode the values. That is pretty easy in Excel, as long as you can use conditional formatting (highlight a range, then conditional ...

Yesterday I made a post about concatenating range with strings in Excel. But there is one problem, it does not work with array formulas. So you cannot put condition on one column and based on that concatenate values form another. For this you would need a function like the one below. And a sample use.

The Excel’s concatenate formula, or operator & do not work with ranges. You have to specify each parameter you want to add to the string. Sometimes it may get annoying, especially when the range is dynamic and depends on some parameters. Then it would be useful to have a formula that can work with ranges, ...

Recently I stumbled upon a problem of ranking observations with exclusion, i.e., not including all of them in the rank. In Excel prior to 2010 you would normally use function =RANK(), in all later versions there are also functions =RANK.EQ() and =RANK.AVG(). They differ only in a way of assigning a rank to observations which ...

Knowing Excel shortcuts is one of the features that can save you hours every week, provided you use Excel a lot. Beside very typical and well known like CTRL+C and CTRL+V there are dozens of other. There is no other way to remember all of them than practising. At my previous work I was amazed ...

Excel and Access often do come along. When you have make them work combining one database with dozens of Excel sheets it is necessary to be able to read and write data fast, otherwise it becomes annoying pretty fast. There are several ways to apporach this problem. Reading Excel sheet into Access can be easily ...

Pivot tables are very useful, but later on it might get really boring filling the missing cells in the first columns. For example given a table like the one in the picture below

There are various means of adding some speed to your VBA code. Probably not the most obvious one, but try to keep track of types of variables, define them explicitly. There is an option, which you put at the beginning of the file which will help you with it Option Explicit Moreover, there are different ...

You never know how Microsoft may surprise you. If you think that everything is so obvious try using Round function as an Excel formula and in VBA. Typing in spreadsheet =Round(2.5, 0) will give you as a result 3. While “the same” function in VBA ? Round(2.5, 0) will result in 2. This feature was included ...

If you do not want to restrict yourself to just like or equality in Access WHERE clauses, or you just want to be able to embed typical regular expressions in your Excel formulas, then try this short piece of VBA code. This code was my answer to one of the questions at StackOverflow. Before you ...