Office 2016 Preview 1

Office 2016 Preview
Not the latest news, but I am more than happy to see it. The MS Office 2016 will feature dark themed user interface, which apparently was “the most requested feature for Office 2013”, see theverge.com. If you want you may check it yourself. There is a public preview version available for free from Microsoft’s website. ...

Paste with Formatting

Paste with Formatting
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 ...

Concatenate Array

Concatenate Array
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.

Concatenate Range 1

Concatenate Range
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, ...

Rank with Exclusion

Rank with Exclusion
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 ...

Shortcuts in Excel

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

Paste Faster into Excel

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

Fill To The Next

Fill To The Next
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

Speed-up VBA in Excel

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

Rounding in VBA and Excel

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

Regular Expressions in Excel or Access

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