Here’s a boring set of figures that we’re going to liven up:

Set up a table

First of all we need to tell Excel what data to format.  Position your cursor anywhere in the set of figures, select the Insert tab on the ribbon and click Table.

Ideally you should have no blank rows or columns in your data and then Excel will be able to work out what data to include in your table. You can always type in or highlight your data range or adjust the range that Excel has guessed for you by highlighting what you actually want.

I want to include the total row here so I will change the highlighted section to include the total row. Once that’s done, click OK and your table will instantly be colourfully formatted.

Change the design

Excel applies a default format when you first format a table but there are plenty of other formats to choose from if you don’t like the default. Click anywhere in the table and click the Design tab on the ribbon. Click the More button (circled below) to display more formats. You can move your mouse over the formats and get a preview of what your table will look like if you chose that format.

You can also choose to have the last column or total row formatted separately. There are other options you can experiment with under Table Style Options on the Design tab.

Change the colours

The colours used in the table designs are taken from the document theme. You can change the theme from the Page Layout tab of the ribbon.

Click the Themes drop-down button to apply a different theme. Excel provides quite a few themes to choose from and you can also create your own theme. Themes include fonts as well as colours. If you only want to change the colour, you can select a different colour scheme from the Colours drop-down list. Again, you can create your own colour scheme if you don’t fancy any of the Excel built-in colour schemes. As you move your mouse over the colour schemes or themes, you will see how your table will look if you were to apply that scheme or theme.

Remove the filter buttons

If you don’t want the Auto Filter drop-down arrows that that may have been added to the top row, click anywhere in the table, select the Data tab on the ribbon and click the Filter button to switch the filter off.

Outlook’s auto-date feature can work out dates from words and phrases you type in. E.g. create a new appointment and, in a date field, type in “next week” then Enter. The date will automatically move on 7 days. “Next month” and “next year” work in the same way, as do “30 days” and “6 months”.

2. Auto-time

Time fields can also be updated quickly by typing “a” or “p” for am and pm, e.g. “6p” will convert to 18:00.

3. Drag and drop

New records can be created quickly using drag and drop. E.g. to set up a new contact from an email, drag the email to the Contacts folder and a new Contact form is started with the email field already completed. Similarly a new appointment or task can be created by dragging an email to the Calendar or Tasks folders.

4. File As field for company names

The File As field on the Contact form can sometimes be a bit annoying as it defaults to filing a contact as last name, first name which is no good if the contact is a company name. In this case, the name can be added to the Company field and if the Full Name field is left blank, the File As field automatically shows the name in the correct order.

5. Automatic title case

Outlook will automatically apply title case to name details on the Contact form so you can type name, company and job title in lower case and they will automatically be converted to title case. It saves using the Shift key.

6. Views

Data can be viewed in different layouts using the View menu. E.g. Address card view in Contacts can be changed to view a phone list or a Christmas card list. Each view can be customised to hide columns you do not want to see, filter out certain data and change the sort order. To create a new view, click on View, Current view, Define views, New. Once set up, the new view will be available in the list of views so you can just select it again.

7. Categories

Assigning categories to Outlook records enables you to make much more use of the data. You can create your own categories and assign more than one category to a record. Once categories have been assigned you can create custom views and apply filters to view specific records. E.g. you can assign a category of ‘Christmas’ to certain contacts so creating a Christmas card list and you can separate your business and personal contacts and appointments to different lists and calendars. Another useful feature of categories is colour coding (if you use Outlook 2002, 2003 or 2007) so you can show different types of appointments in Calendar view in different colours.

8. Recurring tasks

Recurring tasks can be set for regular tasks that have to be done every week or every day. A task can be ticked off once completed and the task will appear again when it is next due. Click the Recurrence button in the Task form to set it up.

9. Create a personal distribution list

If you regularly send e-mails to a group of people, you can set up a personal distribution list to save you having to type people’s names and e-mail addresses again and again. E.g. if you run a chess club, you can set up a personal distribution list containing the e-mail addresses of all the members in your club. Then when you send an e-mail out to all your members, you don’t need to select each individual member’s e-mail address, you just choose the personal distribution list and let Outlook send the individual e-mails.

To set up a personal distribution list in Outlook 2000, 2002, 2003 and 2007

Go to your Contacts folder, choose File | New | Distribution List.

In the Distribution List input box, type a name for the distribution list and either click Select Members to select e-mail addresses from your contacts list all click Add New to add a new e-mail address. Click Save and Close when you’re finished.

To set up a personal distribution list in Outlook 2010

Go to your Contacts folder, select New Contact Group from the New group on the Home tab of the ribbon.

In the Contact Group input box, type a name for the distribution list and click the Add Members button on the Contact group input box then select either a contact from your contacts list or add a new e-mail address.

10. Actions

The Actions functions include some useful features. If you have a contact open, you can create a new task or appointment assigned to that person, start an email and even start a letter in Word to that person. You can also create a new contact from the same company to save typing in the company details again.

Outlook 2000, 2002, 2003

The Actions functions are available from the Actions menu.

Outlook 2007 and 2010

The Actions functions are available on the Contact tab of the ribbon.

You can use the Go To Special dialog box to select only the constants in a range of cells or select just the formulae or select all blank cells. Press F5 for the Go To box then press the Special button and select an option.

2. Paste Special

As well as copying a value to a cell you can also add a copied value to the value already in a cell or multiply, subtract and divide a copied value to a cell.

Type a value in a cell and press CTRL + C to copy it. This is the value that you will add to, subtract from, multiply by or divide into other values in the worksheet.

Excel 2000, 2002 and 2003

Select the destination cell or cells (i.e. the values you want to include in the calculation) and select EDIT | PASTE SPECIAL. Select an operation to use, e.g. multiply to multiply all your selected values by the copied value.

Excel 2007

Select the destination cell or cells (i.e. the values you want to include in the calculation) and click the arrow beneath the PASTE button on the HOME tab of the ribbon. Relect an operation to use, e.g. multiply to multiply all your selected values by the copied value.

3. Zoom Selection

If you want the used range of your spreadsheet to stretch across the full width of the window or if your used range stretches outside the window and you want to be able to see all of it in the window, you can set the Zoom value to a selection. Select the columns you want to view in the window then:-

Excel 2000, 2002, 2003

Select either the ZOOM drop down button on the standard toolbar or the VIEW | ZOOM command from the menu and choose Selection.

Excel 2007

Click the ZOOM TO SELECTION button on the VIEW tab of the ribbon.

4. Entering fractions

You can enter fractions in Excel by typing 0 followed by a space and then the fraction (e.g. 0 3/8). The cell will show 3/8 but the value of the cell will be the decimal (0.375) thereby allowing you to use the cell in formulas. This is very useful for entering stock market prices such as 700 3/16.

5. Custom number formats

If you often have to type in numbers that have to be in a specific format, such as stock numbers, set up a custom number format. Ahen you enter the numbers, it is much quicker and easier to type them without any formatting. You can then apply the custom number format to the cells afterwards, or have the cell formatting already set to the custom number format.

To call the Format Cells dialog box:-

Excel 2000, 2002, 2003

Select FORMAT | CELLS from the menu. In the Format Cells dialog box, select the Number tab then the Custom option under Category. Type in your format in the Type box, e.g. ### ### ### and press OK.

Excel 2007

Click the dialog launcher in the NUMBER group on the HOME tab of the ribbon. In the Format Cells dialog box, select the Number tab then the Custom option under Category. Type in your format in the Type box, e.g. ### ### ### and press OK.

6. Include leading zeros

Another use for custom number formats is to include leading zeros. If certain figures are in the format 000467, Excel will normally omit the 0s in the cell. If you apply the custom number format 000### Excel will show them.

7. Add a text label to a cell

You can use custom number formats to add text to a cell and the text will not be included in calculations, e.g. if the result of a calculation is 6000, the cell can display:

Total Sales: 6000

Add your formula to the cell (e.g.”=sum(A1:A10)”) then apply a custom number format of “Total Sales: ” #”. The text will be displayed in the cell but it will not affect the calculation.

8. Quick calculations

You do not need to enter a formula to see a quick calculation of a range of cells. Select the cells to be calculated then look at the status bar in the bottom right-hand corner of the screen. It will show you the sum of the selected cells. You can change the calculation method to Count, Average, Min, Max and Count Nums by hovering the cursor over the result in the status bar and use the right mouse button to display the different calculation options.

9. Quick charts

To create a chart quickly, select the data range and press F11. This will create a chart of your data using Excel’s default chart options.

10. Custom lists

If you regularly enter lists of data such as sales areas, products or departments, custom Lists can be very useful for quick data entry. Days of the week and months of the year are pre-set custom lists but you can also enter your own. When you type in one of the list members, you can drag the Fill Handle to copy out the rest of the list.

Excel 2000, 2002, 2003

Select TOOLS | OPTIONS | CUSTOM LISTS to add a new custom list

Excel 2007

Click the OFFICE button, click EXCEL OPTIONS, select POPULAR, click the EDIT CUSTOM LISTS button.

You can also use the custom list as a sort key when sorting data.

Excel 2000, 2002, 2003

Select DATA | SORT and then click the Options button. Select a list from the First key sort order drop-down.

Excel 2007

Click the SORT button on the DATA tab of the ribbon. In the Order column, select Custom List from the drop-down list.

Tables are much easier to use than tabs for positioning text and images on a document. You can remove the borders so it does not look like a table when the document is printed. You may see grid lines on the screen but they will not print out. Resize the columns and rows to fit your layout by dragging the column or row borders.

Word 2000, 2002, 2003

To insert a table: Select TABLE | INSERT | TABLE and type in the number of columns and rows you require.

To remove borders: Click anywhere in the table then select TABLE | TABLE PROPERTIES then on the TABLE tab, click BORDERS AND SHADING, select the BORDERS tab and click NONE under Setting.

Word 2007

To insert a table: Click TABLE on the INSERT tab of the ribbon and highlight the number of columns and rows you require.

To remove borders: click anywhere in the table then select PROPERTIES from the LAYOUT tab on the ribbon. On the TABLE tab, click BORDERS AND SHADING, select the BORDERS tab and click NONE under Setting.

2. Quickly insert a new row in a table

Click in the last cell of the table and press the TAB key.

3. Go Back shortcut

You can return to the place where you last were in your document by clicking SHIFT + F5. Word remembers your last 3 locations so you can keep pressing it to go back to previous editing points. This can be useful if you have just copied a large amount of text; you can go back to the place where you first copied the text.

4. Document Browser

The Document Browser makes it easy to move around a long document. It can be found at the bottom of the vertical scroll bar – the button with a ball between a double up arrow button and a double down arrow button (see adjacent screen shot). The default setting is browse by page so when you press the double down arrows you are taken to the next page in your document. Bhe double up arrows take you to the previous page. Press the Document Browser button to change the object you want to browse by. You can browse the document by tables, fields, headings, footnotes, endnotes, graphics, sections and edits.

5. Draft Font

If you are editing a very long document and you are finding it a bit slow, you can change the font temporarily to Draft Font to speed it up.

Word 2000, 2002, 2003

Select TOOLS | OPTIONS | VIEW then click Draft Font in the last section on the dialog. You must be in Normal View for the draft font to take effect.

Word 2007

Click the OFFICE button, click WORD OPTIONS, select ADVANCED, scroll down to Show Document Content and select Use Draft Font in Draft and Outline Views. You must be in either Draft view or Outline view for the draft font to take effect.

6. Find and Replace

Word’s Find and Replace feature is so useful. As well as being able to find and replace general text, you can also find and replace formatting and styles and even graphics.

e.g. To replace a specific string of text with a graphic. Select the graphic and click CTRL + C; this will copy the graphic to the clipboard.

To paste into Word 2000, 2002, 2003

Select EDIT | REPLACE, type the text you want to replace in the Find box and type ^c in the Replace box (^c is the shortcut to use for pasting the clipboard in the Find and Replace box. Click Replace All and the text will be replaced with your graphic.

To paste into Word 2007

Select REPLACE on the HOME tab of the ribbon, type the text you want to replace in the Find box and either type ^c in the Replace box or you can select Clipboard Contents from the Special drop-down list. Click Replace All and the text will be replaced with your graphic.

7. Go the the halfway point

To go immediately to the halfway point in your document, click F5, select Page in the Go To What box then type 50% in the Enter Page Number box. You can use other percentages to go to other points in the document too.

8. Section breaks

If you frequently have trouble with section breaks, especially when deleting sections, add a dummy section break at the end of the document when you first create a document. The settings for a section (e.g. headers, footers, page orientation) are stored in the section break therefore your original document settings will be stored in the final dummy section break. When you delete a section break, the new merged section takes on the settings of the section break at the end.

The quickest way to copy settings between sections is to copy the section break. So if the settings of your sections have got confused, you can either delete the section breaks so you are left with your original settings in the dummy section break at the end of the document or you can copy the dummy section break to the end of a section to reset the settings to the original.

When working with section breaks, it is easier to work in Normal View in Word 2003 (VIEW | NORMAL) and Draft View in Word 2007 (VIEW tab | DRAFT). This will enable you to see clearly where the section breaks are.

9. Move text up and down a page

To quickly move a section of text up or down a page, select the text and click SHIFT + ALT + either the UP arrow key or the DOWN arrow key.

10. Remove manual formatting

To remove manually applied formatting from a section of text, select the text and press CTRL + SPACEBAR. The formatting will revert to the formatting set in the paragraph’s style.