Riverbank It management

Don’t get stuck in the 10% trap, where you use just 10% of the power of your software. Work better, faster and more effectively by continuing to learn more about the software you use. Use that extra time you liberate to drive growth in your business.

Here are ten tips to help you accelerate your work with Excel.

1.    Entering text
If you want to enter multiple lines of text, or create paragraphs within a cell, use Alt Enter to move to a new line.

Pasting text from another application? If you paste it directly into the cell it will retain its current formatting (font, bold, italic etc). If you just want the text and not its current format, paste it into the formula bar (see below) and press the Enter key to send it into the cell.  

If you want your text to span multiple cells use Merge and Center. Remember you can go vertically as well as horizontally.

2.    Formatting cells
Once you have got a cell formatted as you want, copy the formatting to other cells using the Format Painter. Click it once then paint the format of the current cell to a single cell or a range of cells (notice the mouse pointer becomes a paint brush). Click it twice to turn it on so you can paint a series of different cells or ranges. Click it again to turn it off.

 

3.    Paste special
Paste special is a great tool and worth a few minutes experimenting with it. The screen shot below shows where to find it.

 

 Here are a few of the things you can do with Paste Special:

• Have you ever done a copy and paste and then had to change lots of column widths? Just go to Paste Special and paste the column widths.
• Ever got your table the wrong way round and want rows to be columns and columns to be rows? Copy your table then go to Paste and select Transpose.
• Have you got a calculated value that you want to fix forever? Copy it then paste back just the value. Go to Paste then Paste values.

4.    Page break preview
One of the painful aspects of Excel is printing a spreadsheet. The page divisions can be arbitrary and unpleasant. Manage the way your spreadsheet will print by going to View | Page break preview. Drag the page boundaries (shown as dotted lines) or change your column widths to get the pages the way you want them. Then go to Print | Print Preview to see what it will really look like when you print.

Use View | Normal to return to the normal spreadsheet view.

5.    Freeze panes
Have you got a very wide or very long spreadsheet where the row or column headings disappear as you scroll through the table? If so, use Freeze Panes to keep the headings in view. Put your cursor on the first cell in your table that is NOT a heading then click View | Freeze Panes.

6.    Get up to speed with formulas
Formulas are what Excel is all about. Invest time in learning how to create formulas and get Excel to do the hard work for you. The Excel help facility (press F1) has lots of information about formulas.

7.    Working with dates
Excel does some great stuff with dates. A date in Excel is handled in two parts; first there is the date value that Excel holds as a number, and completely separate is the way that date will be displayed – the date format. When typing a date into Excel just enter it in the format dd/mm/yyyy or omit the year if it is the current year. For example, entering ‘1/2’ will create ‘1 February 2010’. Use the cell formatting to get the date displayed the way you want, whether it is ‘25/12/09’ or ‘25 December 2009’.

Because dates are held internally as numbers you can do lots with them – you can add days, subtract days, find the number of days between two dates and so on. There are also lots of date functions to incorporate into formulas to enable you to do more advanced tasks.

8.    Keyboard shortcuts
I am a great believer in keyboard shortcuts. You can work faster and with less effort. Remember to use Page Up and Page Down to move up and down quickly. Use Alt IR to insert a row and Alt IC to insert a column – it’s quicker than getting the mouse and clicking Home | Insert | Insert sheet columns.

Select a range of cells by holding down the Shift key and using the arrow keys to highlight the cells. Select the entire spreadsheet by using Ctrl A.

Have you got a number of different sheets in one Excel workbook? If so, move quickly between the tabs with Ctrl Page Up and Ctrl Page Down.

9.    Sort
Sort your lists using the sort facility (Home | Sort & Filter). To maintain the integrity of a table of data remember to select the entire table and sort on one of the columns. If you include the column headers in your selection you can then sort using the column names.

10.    Filter
Filtering is very useful to sift out small amounts of information from a long list. For example, in a list of names and addresses use the filter to find just the people who live in Zimbabwe. Or in a staff list with details of their skills the filter facility enables you to select just those with Excel skills.

To get started with filtering select a table of data including the column headings. Click Home | Sort & Filter. You will see little drop-down buttons on each column heading. Use these to filter your table.