Excel(lent) Hacking (5 things - Week 86)

I think I've mentioned this before. After years of hearing that a good academic record is the foundation on which a career is built, someone who graduates out of a professional college expects to use all those things they have learned over two decades to show their class in the office. Alas, for most of us, it's a different, and most probably, a sad little ending.

We are not allowed to use only the desired high end software and technology. At the end of a day's or project's work, we always end up using the same software that your local DTP shop uses - MS Office. Why?? One word - DOCUMENTATION.

No matter whether you are designing toilets to go on a spacecraft heading for Mars, or just a plain old toilet; at the end of the project, before it's release, you've to sit down and write prose about whatever you had just done. I've worked in two very different fields, and this is the common factor at both places.

I guess it's because documentation is part of the ISO certification processes that most institutions undergo these days. To get certified with the appropriate certification, you are required to maintain the proper documents regarding whatever is the process or transaction that you are performing at your organization.

I once had a junior ask me, what is the difference between us and DTP people?? My reply was that the DTP people just made what was kept in front of them. We are supposed to understand, modify, verify and then create these critical documents. And the other difference is that we have knowledge of all these hacks - these shortcuts. We know how to finish the job by 5pm instead of 9pm.

And it is some hacks of this nature that we are going to discuss for the "5 things" of this week. Shall we begin then??

***

1. Formulas and Macros

Now, I can't teach you each and every function that is provided to us - because there's too many. Nor can I teach you about the macros - because I don't know what the heck that is myself. But if you can master them both, it's said that you can master the tool completely. Let me just guide you onto this site, which provides detailed explanation of all formulas in Excel. The only issue is that you have to figure what to use for your application.

http://www.techonthenet.com/excel/formulas/

2. Ctrl + PgUp/PgDown

Use these to move back and forth among the various sheets in the excel file you have kept open.

3.  Ctrl/Shift + Space

This allows you to select an entire row or column. Use Shift button to select the entire current row. Use Ctrl button to select the entire current column

4. Ctrl + <arrow keys>/<backspace>

Using the arrow keys, will move the cursor to the next word in the document, or next populated cell in the spreadsheet. You could use backspace to delete the previous word too.

5. F4

This is the mother of all hacks. One press of that button lying inconspicuously at the top left corner of your keyboard and Excel is forced to do the last action you did on the current selection. If you had just done some cell formating, Excel does the same on the currently selected cells. If you had inserted a new row, Excel inserts a new row at your current selection.

***

There's one more thing though. If you just search in the net for "Excel hacks", you'll get a lot of sites which shall give you a lot more hacks than the ones I have mentioned over here. But just knowing the hacks doesn't make you an "expert" in Excel. You've to use them properly. And to use them properly, you must be as lazy as only a lazy genius could be.

To borrow an idea from some wise guy, most of the items of daily use intended to reduce human effort are the work of geniuses who were so lazy that they went out and made something to do it for them. Likewise, just knowing these hacks ain't enough. You gotta be truly lazy to use them at the apt places. Hope you can find that inner laziness that is required of you.

***

So, that's all for this week then.

Have a great weekend!!

'til next week.