This afternoon, I came across an interesting link (warning 1 hour long) to a talk discussing some really handy tips for making the most of Excel - you know, that thing you may have used to make silly gridded layouts with ions ago...
For me, these are particularly timely/relevant tips, as next week I'll be teaching a lab of undergraduates (actually, two streams in one day) on how to make use of performance modelling techniques to compare different UI designs.
In particular, here are some of my favourite takeaways from this talk:
* The "R1C1" style of referring to cells relative to the current one - As a programmer, this just makes so much more sense!
* You can assign names to individual cells, and then make use of those names in formulas! - Edit the "current cell name" dropdown beside the cross and tick to do this. (Now you can see why I said this video is particularly insightful ;) Now that you know, it's criminal to continue just referring to "F24" or "E3" in your formulas. Label your variables instead of making people hunt down what exactly they were!
* Heck, you can even assign a name to a column (by selecting all cells in that column) in the same way, and then simply use that column name as a variable in a formula in an adjacent cell. - Now we're talking!
* Tables - If you're ever dealing with a dataset (e.g. one imported from a .csv, or from a database dump), this apparently Excel-only feature makes things a hell of a lot easier for filtering, sorting, and manipulating the data (e.g. you get the named columns, auto filling of formulas down columns, and data insertion magic that you've been wanting for ages). Why did I not know about this before embarking on mucking around with data processing scripts to try and find a library that could cope with those pesky datasets with dud values in them
* Pivot Tables - Well actually, I accidentally stumbled into figuring out how to use these last week when dealing with the previous lab. Basically, if you've ever needed to find a quick way to aggregate your data in terms of one or more factors (e.g. if you want to find the average time for each unique combination of frequency and amplitude parameters), Pivot Tables are what you want. In the rows/columns, you specify the factors of categories to combine by, and in the "values" section, you specify how to combine the values (e.g. sum, average, min, max, count, etc.) in each category extracted from the data using the chosen factors. Combined with the Tables feature above, this pair is very handy if you want to go digging around in your data
* Ctrl-Accent = Toggle Formulas - Ever wondered what cells had formulas and what formulas were being used? Well this is the solution. Really nice when combined with named cells!
* Goal Seek - It iteratively tries different values in one cell to obtain a desired value in another. 'Nuff said.
* Ctrl-A on a spreadsheet where you've got whitespace around the table of data will select just that island of data in one go (and none of the other junk/whitespace). This is handy for anyone who's ever waited for the autoscroll to kick in.
No comments:
Post a Comment