3. ALT-ernative Existence
In Excel, the problem with keyboard shortcuts is that, over time, I forget what sequence summons up a spreadsheet command such as, say, Filter. Zelman showed that Excel makes it easy to refresh my memory on the desired sequence. It all boils down to judicious use of the Alt key.
With your Excel file open, click the Alt button. (The sample file is called “chem_midterm”–but don’t let that turn you off!) The letters displayed show how to summon the desired tab or ribbon, as can be seen here:
To access the Data functions, click Alt + A to get to the Data ribbon. Click Alt again, and you will see keyboard shortcuts to quickly summon Filter and Advanced Filter, as shown here. (The buttons for Filter and Advanced Filter can be seen in the middle of the Data ribbon here.)
Zelman, in his “war on the mouse,” emphasized that, with constant use, the sequence Alt + A + T to get to Filter will be much faster than using the mouse to point and click. By the end of the morning (with numerous exercises involving said Advanced Filter) I agreed unreservedly.
4. If you filter, use SUBTOTAL, not SUM
At the bottom of a column of numbers, I’m used to typing a formula that is SUM. However, that function will add up the values of all rows in the range, even the hidden rows.
If you want to sum up only the visible rows, use SUBTOTAL instead. Note, however, that you have to specify an extra argument. The argument number depends on the specific operation (count, average, sum, etc.) as shown here.
To get the subtotal sum you must use “9” as the first argument. ª