Posts Tagged ‘excel’

how irritating

Wednesday, December 19th, 2007

OpenOffice doesn’t import the EOMONTH() function from Excel spreadsheets, but knows what it is when you type it manually. C’mon, people, get hep!

Display or hide zero values - Excel - Microsoft Office Online

Wednesday, August 15th, 2007

Display or hide zero values - Excel - Microsoft Office Online
You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set of format standards that requires you to hide zero values. There are several ways to display or hide zero values.

excel: alpha code to numbers

Thursday, August 2nd, 2007

I use an annoying program that labels its output A..Z, AA..AZ, BA … rather than numerically from 1. This is annoying, as a spreadsheet won’t sort it correctly (it does A, AA, AB …). The following code will convert this code to the right numbers, assuming your alpha code is in cell B3:

=IF(LEN(B3)=1,CODE(B3)-64,26*(CODE(B3)-64)+CODE(RIGHT(B3))-64)

This will only work for codes of two characters or less, and is case sensitive.

excel: pasting only visible cells

Saturday, January 27th, 2007

Ever tried to paste a range of cells in Excel that included hidden cells, but you didn’t want the hidden stuff? This works: Now Who Told Excel To Paste That Data?

“paste special: values” is special

Wednesday, January 10th, 2007

This saved me a bunch of time yesterday when I was pasting many sets of values into a spreadsheet: Daily Dose of Excel » Blog Archive » Mouse shortcuts.