SQL Insert / Update statements from CSV files

Often when you are working on 2 incompatible systems and try to import data from one to another, excel can be a handy tool. I have used excel plenty of times to generate SQL insert / update statements which I could later execute on the database console. Here is a tutorial if you ever have [...]

Calculating Paydays in a calendar year using Excel

In India salary is usually paid on the last working day of a month - the payday. It is slightly different in countries where payrolls are processed every 2 weeks. For eg. in US most companies pay salary on every 2nd Friday / Thursday.
We can calculate the paydays / payroll periods in excel with [...]

Power Outage & 99 Excel Tips to keep you Busy

We have a major power outage in Ohio due to a wind storm on Sunday. AEP, Our electric provider says that power would be restored to my area by this Friday midnight, that is almost 6 days without power. I will not be able to blog regularly during this period.
So here is a list [...]

Splitting text in excel using formulas

Often when you are processing text using excel it is important to split the text in to multiple parts based on a delimiter. For eg. you may want to divide this|needs|to|be|split in to five parts this needs to be split. There is a simple way to do this in excel, using “import text” option. But [...]

Initials from Names using Excel Formulas

Often when you are processing customer records or doing mail merge, it might be useful to get initials from a given name, like JFK for John F Kennedy.
You can do this using simple text formulas (left(), mid(), find()) combined with if(). Here is how:
Assuming cell B3 has the full name, then this is the [...]

Quickly process text using split() spreadsheet function in google docs

Google docs spreadsheet app is going mainstream with each passing day. Recently they have introduced split() formula to divide text in to multiple cells using a user specified delimiter.

The syntax for this function is =split(text value, delimeter), for eg. =split(”pointy haired dilbert”, ” “) would result in 3 individual cells with “pointy”, “haired” and [...]

Simulating Dice throws - the correct way to do it in excel

If you ever had to simulate random outcomes in excel sheet, you might have already heard of about the spreadsheet function rand(), this little function generates a random fraction between 0 and 1 whenever you use it. So I usually write =round(rand()*12,0)int(rand()*12)+1 if I need a random number between 0 to 12. Of course, if [...]