Category Archives: Excel

Excel Formula / Vlookup / Index Match To Get A Hyperlink

So just combining a few tutorials and code from the net here. Basically, this allows you to do a vlookup, or index match, to grab a cell’s value WITH the hyperlink. You may have found this because you realised after … Continue reading

Posted in Excel, VBA | Tagged , , , , | Leave a comment

Excel: How To Select A Certain Number Of Cells Within A Range

Ok, I tried searching for this and eventually found some results, but thought I’d put up a piece to make it a bit easier for people to find. Basically we want to select a certain number of cells within a … Continue reading

Posted in Excel | Tagged , , | Leave a comment

Excel: How To Select A Cell X Cells Away From Another

To select a cell that is x cells away from another we can use the =offset() formula. Example of how this is very useful: I often have daily or monthly return series, and need to calculate the annual return. So … Continue reading

Posted in Excel | Tagged , , | Leave a comment

How To Insert A User Form With Checkboxes In Excel

Looking for a way to add checkboxes that allow users to tick what they want in Excel?   Check out these excellent guides:   http://www.fontstuff.com/ebooks/free/fsUserForms.pdf http://www.exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html

Posted in Excel, VBA | Tagged , , , | Leave a comment

Excel Paste Values Shortcut: CTRL+ALT+V, Or ALT, E, S

2 options here. CTRL + ALT + V This will bring up the dialog box, which will allow you to choose the type of paste you want to do ALT, E, S This will navigate through the ribbon. Pretty sure … Continue reading

Posted in Excel | Tagged , | Leave a comment

Excel Connect Data Points On A Chart When There’s A Gap

Quick post on connecting data points on a chart in Excel when you have a gap in the time series. Basically I export a lot of time series that when paired together has gaps for certain periods. Basically looks like … Continue reading

Posted in Excel | Tagged , | Leave a comment

Excel Shortcut To Edit A Cell Formula: F2

Another short… well shortcut post. Just wanted a helpful piece that has the shortcut actually in the title. So shortcut for editing a cell formula in Excel is F2 Apparently on a Mac it is CTRL + U. But that … Continue reading

Posted in Excel | Tagged , | Leave a comment

Word VBA To Resize All Images In A Doc By Percent

Quick post on how to resize all images in a word doc using VBA. After finding a few examples around the net, the best I found is below.

Also this one resizes just the selected image.

 

Posted in VBA, Word | Tagged , , | Leave a comment

Excel Shortcut To Fill Down: CTRL+D, Right: CTRL+R

Very shortcut and time-saving shortcut I never looked into until recently seeing an excel whizz do his thing. To fill down: select the cell and hit CTRL + D.   Result:   To fill right: CTRL + R  

Posted in Excel | Tagged , | Leave a comment

Excel VBA To Change A Chart Point Color Based On Its Label

Yes…. I spelt color the American way… So I have a bunch of column charts with the same category list. I want to highlight the data point that corresponds to one particular category. Like below. The simple VBA is this: … Continue reading

Posted in Excel, VBA | Tagged , , , , | Leave a comment