Tip o’ the Week 323 – Some lesser-known Excel spreadsheetery

Spreadsheets did – or do, still – make the modern IT world go round. clip_image001Until Dan Bricklin and VisiCalc invented the familiar grid-based software environment, business school boffins had to manually write up large grids of numbers on blackboards, and routinely calculate the impact of changes in any one “cell” based on a book of formulae. An error-prone process that could, as you’d imagine, take a long time.

As it happens, VisiCalc powered the Jobs’n’Woz enterprise to mass success, as Apple IIs were selling (even fully kitted out at $5k+ a time, in the 1980s) to middle managers who were sick of the Data Processing department taking ages to turn around financial reports, so they took to sticking an Apple on their desk and doing the sums themselves. If you’re interested in all of this, see here.

This threat from Valley spooked Big Blue enough to worry about the mainframe franchise being under threat, and after a couple of false starts and a skunkworks project called Chess, the PC was born.

Lotus software quickly became the de facto spreadsheet provider running on PC-DOS (as it was so fast, meaning the spreadsheet jockeys ditched their Apple IIs and flocked to PCs), but Lotus got distracted with OS/2 while Microsoft’s Windows 3.0 started to gain traction. In place of WordPerfect, Lotus 1-2-3 and dBase, the 1st party Microsoft Office suite took early and full advantage of Windows, ultimately powering Office to the front. So, Excel trumped 1-2-3, just as Lotus eclipsed VisiCalc.

That means Microsoft Excel for Windows has been around for a very long time, and there are many functions you’ve probably never used – but there are loads of useful tips that could make your life easier. Here are a few…

  • Text editing – yes, yes. Spreadsheets are for putting in numbers, making calculations and drawing up charts… but some management types do like to go on about other stuff in text fields. Did you know if you press ALT+ENTER whilst entering text, you’ll add a new line to the text box and, most-likely, resize it in the sheet?
  • clip_image003Change the Enter key – when Power Users press ENTER, they mean, er… moving to the cell below? What if they’d prefer to move to the cell to the right? Well, you can change it … just go to File | Options | Advanced
  • The power of F4 – one of the handiest shortcut keys in any Office app, pressing F4 simply re-does whatever the last action was. Say you’ve just changed a cell’s format; well, instead of using Format Painter to select the formatting and paste it into another one, you could just press F4 to apply the same changes to another selected cell. And keep on pressing F4 to re-apply the same settings to other cells too. CTRL+Y has the same effect.

clip_image004

  • Stripey rows are a nice way of drawing differentiation within tables – if you select cells and use the Format as Table command on the Home tab, one of the side effects of tablifying your cells is to give you the option of making the rows and/or columns stand out from each other. If you want to apply the same sort of formatting to a block of cells without making clip_image006them act like a table (maybe you’ve copied and pasted cells from a proper table and then removed some of the rows, thereby breaking the colour sequence), then a simple trick is to apply formatting:
  • Select your block of cells then go to Conditional Formatting on the home tab
  • Choose a new rule, then use a formula…
  • Enter =MOD(ROW(),2)=0 as the formula itself and then click the Format… button to select the formatting you’d like to apply to every other row (fill a colour, for example). You could try the same trick with =MOD(COLUMN(),2)=0 if you’d prefer… or change the number to highlight only every 10th row, etc.
  • Add a calculator to the Quick Access Toolbar – the QAT was featured the other week, and here’s another clip_image008handy use for it. Although spreadsheets are great for calculation, sometime you don’t want to add a formula to process numbers, but would rather tot them up yourself and add the result to your sheet. If you’ve done this before, there’s no need to feel inadequate – assuage your tech guilt and put a shortcut to the Windows Calculator by clicking the down-arrow to the right of the Quick Access Toolbar, selecting All Commands from the drop-down, and then Adding the Calculator so you can launch it easily in future. (Or just press Windowskey+R then enter calc).