Let's discuss the classic spreadsheet program Excel.
Magic Bidirectional Formulas
I've often thought it would be cool to have bidirectional formulas in Excel. For example, you should be able to set up a relationship between two cells such that one was three times the other value. You could edit *either* cell, and the other cell would update itself. There may be a built-in way to do this, but I'm not aware of it. I'll have to roll up my sleeves and write a macro.
The goal: Write an Excel macro to convert between minutes / seconds and seconds and vice versa...Converting both directions in the same cells...
I edited the Worksheet's code by right-clicking on Sheet1.
Add macro code to "Sheet1." I found a way to run code after the worksheet is changed, and to set the value of a certain cell. I'm using Office 2003, but the approach should be compatible with many versions.
The result:
Here's an animated gif of it in action:
Running macros is a security risk, and so many people rightfully turn off macros in Office. But if you are writing your own macros, it's a good way to add productivity.
Another creative way I've used Excel:
Renaming Files
One of the best features of Excel is "pattern extension"; clicking in the lower-right corner of a cell and dragging downwards to fill the cells below it based on a pattern or sequence.
Say I have many files in a directory with different names. I want to assign each file a number. (I want to have 01.txt, 02.txt, 03.txt, and so on.) Here's my unorthodox approach:
In a command-line window, use dir /b to get a list of the files.
Copy this list into Excel. (By default, the way to copy from a command window is a little strange. Right-click, select all, drag the selection rect, and press Enter to copy). Create a new column for the resulting filenames by using the "pattern extension" feature mentioned earlier. For example, to have leading 0s, type the string '01 into the first cell, and '02 into the second cell. Select both cells, click in the lower-right corner of the selection, and drag downwards to fill the cells below.
Next, see the example and add more columns. I've included " because DOS doesn't otherwise take files with spaces in the filename. Again, the fill downwards is very useful.
Now, copy this and paste it into, well for this example, notepad.exe. You'll see many tab characters in the file. Copy one of them, go to Search/replace, and paste the tab character into the search: field. Replace all to removing all tabs. Look, we just created a batch file!
Save the batch file (with the .bat extension) and run it. Now your files are renamed.
The same principle can be used in other applications. For example, let's say you have a list of urls to download: http://www.example.com/abc.tar.gz
http://www.example.com/def.tar.gz
http://www.example.com/ghi.tar.gz
http://www.example.com/jkl.tar.gz
Assuming you didn't have a download manager, you could copy this list into a text editor and delimit by newlines. Then copy this data into an Excel sheet. Put wget before each of these, eliminate the tabs, and make a batch file. (There is a Windows version of wget).
This method is also good for general file renames, if you need to append onto the name or apply some time of transformation.
Random Information
To enter in formula-like symbols in a cell (like the string "=equals") when you're not trying to write a formula, type a single ' character to "quote" the contents of the cell as literal. Then you can have cells that display as "-> " and so on.
If you don't have your favorite programming language handy, you can use Excel's Data-> Text to Columns as a poor man's split().
In Excel 97 there is the classic flight simulator. That's right, it's probably the most ridiculous easter egg ever made. I've played it. Read more here: http://eeggs.com/items/718.html .
Favorite Keyboard Shortcuts
| Alt+Shift+F1 | Create new worksheet |
| Ctrl+Pg Up/Pg Down | Switch between worksheets |
| Alt+O,H,R | Rename current sheet |
| Alt+E,L | Delete current sheet |
| Ctrl+Space | Select Column |
| Shift+Space | Select Row |
| F2 | Edit cell contents |
| Ctrl+D | Fill down (a favorite) |
| Ctrl+' | Fill down and edit |
| Alt+= | Formula summing above numbers |
| Ctrl+Shift+Plus | Insert row/col |
| (If you have many cells selected when inserting a row, you create many rows at once). | |
| Ctrl+Minus | Delete row/col |
| Ctrl+& | Add cell border |
| Ctrl+_ | Remove border |
| Cell Properties | Ctrl+1 |
| General number format. | Ctrl+Shift+` |
| Currency format | Ctrl+Shift+$ |
| Time format | Ctrl+Shift+@ |
| Number format, two places | Ctrl+Shift+! |