iDrive tip: Using Excel

Today’s tip includes some helpful tips for using Excel.

It’s easy to create sums of columns using the AutoSum tool but what about subtotals? Creating these needn’t be hard either. Just use the function =SUBTOTAL (9,B2:B10). The 9 is a function number, representing SUM, and you should replace the cell references with the ones from your own worksheet. You could place it in cell B11 and then repeat it with figures below, say =SUBTOTAL (9,B12:B20), in cell B21. If you then used the AutoSum tool in cell B22 it would just display the sum of the subtotals, from cells B11 and B21. See Excel help for other function numbers to represent Average, Count, Max, Min, and more.  This method is a bit different from using the Data Subtotaling/Totaling features.

Deleting Comments
You can delete all the comments from your worksheet at once, for instance if you have finished the sheet and want to distribute it without annotations. Press Control, Shift and O at the same time, and this will select all the cells in the worksheet that contain comments. Right-click on one of them and select Delete Comment, then click anywhere on the sheet and all the comments will have vanished.

Delete vs. Clear
There two ways to remove information from cells: Delete and Clear. Clicking on a cell and selecting Delete (or pressing Del or Backspace) will remove the cell’s value or formula, but any formatting and comments will remain in place. If you want to return the cell to its original state, with no formatting, choose Clear instead.

Locking Columns and Rows
If you want to freeze a row of titles or categories so that you can keep them in sight as you enter data further down your spreadsheet, Microsoft Excel has a handy Freeze Panes function. “I keep the results budget projections locked in one pane so I can see the bottom-line impact of expense and revenue forecasts I make in another pane,” says Lyn Mason Green, founder of CanadianActor Online, an information Web site for aspiring and veteran actors.

To lock a pane, use your mouse to select the row below or the column to the right of where you want to freeze. On the Window menu, click Freeze Panes.

Forcing Line Breaks
If you enter a lot of data into one cell and then move to a new cell and enter further data, you may notice that the original information gets covered up. The solution is to force line breaks. To do this:

Click on a cell.
Type the first line.
Press Alt + Enter.
Type the second line.
Repeat step 3 to enter additional lines.
Press Enter when finished.

Here are a couple of websites that provide these tips and more for Excel.

Hope these are helpful!