Recent Posts


Excel Tips


Frustrated by Magento? Then you’ll love Commerce Bug, the must have debugging extension for anyone using Magento. Whether you’re just starting out or you’re a seasoned pro, Commerce Bug will save you and your team hours everyday. Grab a copy and start working with Magento instead of against it.

Updated for Magento 2! No Frills Magento Layout is the only Magento front end book you'll ever need. Get your copy today!

If there’s a bash Microsoft party I’ll be there with bells on, but that won’t stop me from loving Excel. This, of course, is less to do with Excel and more to do with the concept of computer spread sheets, but it’s hard to argue with Excel’s dominance of the field.

Of course, like any program that’s been around for 20 years there’s a lot of cruft. This entry/page is a collection or useful links and tips, kept primarily so I don’t have to go hunting around the web whenever I forget the keyboard shortcut to edit a cell, (the F2 function key).

There’s a long list of keyboard shortcuts over here.

If you’ve ever used Excel’s auto-fill feature with formulas, you’ve probably run into a situation where you wanted to keep one cell reference constant, but allow the others to progress. (B1 + C1 + F1) to (B2 + C2 + F1). You can achieve this by defining a “Name” which works like a constant variable in your formulas. Insert -> Name -> Define.

David Vega (email withheld to stop the spam bots) wrote in to tell me the proper way to do the above. If you precede a character with a $, Excell will know to keep the cell reference fixed. (B1 + C1 + $F$1), (B2 + C2 + $F$1), etc. Even better, you can tell Excel to increment the column but fix the row or vice versa with a F$1 or $F1. Which proves I ought to buy an Excel book one of these days.

Copyright © Alan Storm 1975 – 2021 All Rights Reserved

Originally Posted: 23rd May 2005