# Category Archives: Excel Formula examples and reference

## Get the current cells column letter

A formula to return the column letter(s) when placed in any cell. For instance if the formula was put in cell G56 the result in cell G56 would be G. If it was put in IMB213 then the result would be IMB. =LEFT(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),”\$”,””),LEN(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),”\$”,””))-LEN(ROW()))

## Random number generator in Excel

To generate a ‘Random’ set of numbers in Excel you just need to use the Randbetween formula as follows: =Randbetween(1,5000) This will put a random number between 1 and 5000 in the cell where the formula is. Drag this down to as many cells as you like.

## Add every other cell on a row

Occasionally when you have a large number of cells in a row that you want to sum, ideally it would just be in a range. ie A1:A7500 for instance. This would sum up everything from Cell A1 to Cell A7500. Now what if the data contained daily takings where 1 column was Gross and the […]

|

## VBA border around a cell

Border all around a cell Range(“K5”).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 […]

|

## Force #N/A error in cell

This may seem illogical, and we even asked ourselves why anybody would want to do this, when the whole point of a clean and fully functional excel project is to avoid errors and ensure nothing can get in the way of an accurate result or outcome. However, we were asked to create a formula that […]

|

## Excel Formulas – Using IF/AND together

Datanology usually consider all the formula based options for your project before looking at introducing VBA or userform based projects. Formulas in Excel can be very efficient, and when you use nested (several formulas mixed into 1) formulas and especially combining the IF & AND formulas a very quick, accurate and useful outcome for your […]

## Excel Match Formula

The ‘Match’ formula could be described as a versatile relative of the Vlookup and Hlookup functions. Whilst vlookup can only find corresponding data to the right of the lookup data, match can help you to return information from any direction using it’s friendly assistant, ‘Offset’. Match also has it’s own unique abilities and there are […]

## Excel task management Date highlight

Sometimes, it is necessary for Excel projects to be handled entirely by formulas as opposed to using VBA and creating a fully bespoke VBA system. This project was requested with the specifications that it must be formula based which meant it could be used, updated and amended on any mobile device such as an iPhone […]

## UK Postcode formula for Excel

When dealing with UK addresses in Excel, having a clean database of clients, potential clients addresses is important to ensure that when you make contact with them you make sure that contact happens and shows your business in a professional way. Incorrect addresses, spelling errors etc can look unprofessional to a prospective client. Using formulas […]