TECH TIPS :

Excel tips to make your spreadsheets more efficient

Last month we discussed tips for making Microsoft Word documents look great, quickly and efficiently. This month we'll discuss some tips for fixing up your spreadsheets or getting your work completed a little faster using some Excel tips.

Copying Formats Across a Spreadsheet

Once you get a particular cell to look the way you want it to look-font style; font size; formatted perhaps as currency or a percentage or date; the correct number of decimal places; bold or italic; and perhaps with a fill color or border, you often want to use that same formatting on other cells throughout your spreadsheet-and usually these cells are not adjacent. No problem. Simply click on the cell that's formatted as you like it to select that cell, and then click the Format Painter icon.

This icon is on the Standard Toolbar and it looks like a small paintbrush. Once you've clicked this icon, your cursor will turn into a paintbrush. While the paintbrush is visible, all you have to do is click (or click and drag) to apply all of the formatting attributes from your selected cell to any other cells in your spreadsheet.

Once you apply format painter to a cell or group of cells, it will turn off. If you want to apply the same formatting to a number of cells in different locations in your spreadsheet, just double-click the Format Painter icon, instead of clicking it once. It will remain on until you either click the icon again when you are finished copying formats or press the Escape (Esc) key on your keyboard.

Transposing Columns and Rows

Have you ever started a worksheet and after using it for a while realize it would look or work a lot better if you had used the row descriptions as column headings. But you decide to just live with it as is, because who wants to rekey all that data you've entered across the rows instead of down columns? Well, Excel can transpose your data for you.

One of the options in Excel's Paste Special menu is Transpose. First, select (highlight) the entire range of data you want transposed, and select Edit, then Copy. Click on a new location somewhere in your worksheet, then click Edit, and then Paste Special. A dialogue box will open with a variety of Paste Special options. Select Transpose (make sure there is a check in that box) and Excel will transpose the column and row labels and data for you. After you have everything in your preferred order, you can copy and paste the whole thing back where it originally was if you would like.

Selecting Cells

In working with our clients we often see people having difficulty getting their mouse to select just the cells they want-and with Excel it's very easy to trigger its rolling feature while selecting cells. All of a sudden, you find you've selected hundreds of rows and columns. And sometimes you want to select several cells that are not contiguous (for example you want to graph information from cells in columns C and E and use the descriptions in column A) , so your routine click and drag just won't work. Here are some shortcuts to selecting cells in Excel:

o To select an entire worksheet, click in the empty space at the top of the worksheet where the column letters and row numbers meet. It is a little gray box to the left of Column A and above Row 1.

o To select non-contiguous cells (for example, perhaps cells A10 and C15 and F4 through F8). Press and hold the control key (Ctrl) on your keyboard, and with your other hand and mouse, click on each of the cells, or cell ranges you want to select (in our example, first click on cell A10, then C15 and then drag your mouse over cells F4 through F8). When you finish, release the Ctrl key and your selections will all be highlighted.

o To select a range of contiguous cells (this is especially helpful when you are trying to select a large group of cells that spreads down below your window, when you most often run the risk of run-away highlighting), press and hold the Shift key while clicking the first cell you want to select, and keep holding down the Shift key while you maneuver the mouse to the last cell you want to select, click it and release the Shift key. The entire range will then be selected (highlighted).

o To select only the blank cells in a range within a worksheet, first select the entire range, then click Edit, then Go To, then click Special, and then click Blanks.

What Day is it?

To keep track of when you printed an Excel spreadsheet, it's often helpful to have the current date printed right on your spreadsheet. To quickly insert the date, select a cell and press Ctrl +. Excel will insert the current date.

Hope these tips help you save some time working with spreadsheets!

Cindy Shimko is the owner of CM IT Solutions of Lake Norman and CM IT Solutions of University/Cabarrus County, companies that help small to mid-sized businesses use technology effectively, providing managed IT services as well as on-site technology training and computer trouble-shooting, repairs and support. If you have questions on this column, or other technology issues, she can be reached at 704-895-0156 or cshimko@cmitsolutions.com