Microsoft Excel – freezing headings for easier scrolling

I don’t know about you, but I find it a great nuisance if I scroll down an Excel worksheet and cannot see the column figures as I often can’t remember to what the data refers. You can get Excel to always show the top row headings by freezing panes.

To freeze the top row in Microsoft Excel:

1. Click the View tab and in the Window group, from the Freeze Panes dropdown, click Freeze Top Row.

To freeze both rows and columns in Microsoft Excel

1. Select the cell which is to the right of any columns you want to freeze and under any rows you want to freeze.
2. Click the View tab and in the Window group, click Freeze Panes.

If at some point you want to unfreeze the worksheet, click the View  tab, and in the Window group, from the Freeze Panes dropdown, click the appropriate Unfreeze option.

Find out more about our Microsoft Excel training London UK or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Word – displaying table headings on each page with long tables

If you have a large table in Microsoft Word that is longer than one page, you may want to see the column headings on the top of each page.

To do this:

1. In the table, click the first row containing the column headings you want to view.
2. Click the Table Tools Layout tab and in the Data group, click Repeat Header Rows. The column headings will be repeated at the top of any subsequent pages on which the table appears.

Find out more about our Microsoft Word training in the London area or our Microsoft Word training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Word – creating a table of contents

A table of contents can be used to help you find your way through a long document by creating links to headings throughout your documents. Word can do this most effectively if you use styles Heading1, Heading2 and Heading3 when creating headings and subheadings within your documents; when you activate the Table of Contents feature, Word attempts to build the contents table by searching your document for headings and then sorting the headings based on heading levels.

To create a table of contents:

1. Position your insertion point at the location in your document where you want the table of contents.
2. Click the References tab, and in the Table of Contents group, click the Table of Contents dropdown.
3. Click on one of the in-built styles; alternatively, click Insert Table of Contents, in which case the Table of Contents dialog box will be displayed.
4. Select a format from the Formats drop-down list. Samples of the formats will be displayed in the Print Preview and Web Preview boxes.
5. If you don’t want page numbers showing in your table of contents, clear the Show page numbers check box.
6. If you want the page numbers to be aligned with the right-hand margin, check the Right align page numbers check box.
7. To select leading characters to be shown between the text and the page numbers, choose from the Tab leader drop-down list.
8. If you want the table of contents to be based on less than, or more than, three levels, select the appropriate number in the Show levels box.
9. Click OK. Your table of contents will be created.

• To move to a heading in your document, click on its entry in the table of contents.

Updating and modifying an existing Table of Contents

To update a table of contents, click before one of the headings in the table, then press F9 or click Update Table. Select your required option from Update page numbers only or Update entire table. Click OK.

Find out details of our Microsoft Word training in the London area or our Microsoft Word training in Dorset, Wiltshire, Somerset and Hampshire.

Calculating age from someone’s date of birth

Using Excel and need to find someone’s age from their date of birth? There is a function not readily documented by Microsoft, called DATEDIF, which will do just that.

Say the person’s date of birth is in cell B2,

then the syntax required is

= DATEDIF(B2,TODAY(),”y”)

TODAY() enters the current date, so that means that the age will automatically update as time goes by. Th same calculation could be used, for example, to find someone’s length of service from their Start date.

“y” tells the function that we want the answer in years.

If we want to find how many months since an event, we could use “m” as the final argument.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Dorset, Wiltshire, Somerset and Hampshire.

Pinning files in Microsoft Office

There may well be some Microsoft Office files that you want to open on a regular basis but don’t want to remember where on the system you have stored it. In that case you can pin the file and it will then easily be accessible.

When you have a Microsoft application open, go to the Home tab, and you will see options for Recent, Pinned and Shared with Me. Clicking on Pinned will show what files you have pinned in place.

To pin a file for easy access, click on the pin to the right of the file Name and to the left of the Date modified column.

If you click Open from within a Microsoft app you should see any Pinned files at the top of your file list.

Folders that you use most often, you can pin in a similar way.

Find out more about our Microsoft Office training in the London area or our Microsoft Office training in Wiltshire, Dorset, Somerset and Hampshire.

Left Caps Lock on by mistake in Word, Outlook or PowerPoint?

Have you ever inadvertently left Caps Lock on in either Word, Outlook or PowerPoint, and have a length of text that has upper-case letters instead of lower-case ones and vice-versa?

If you are using Microsoft Word or Microsoft PowerPoint, then on the Home tab, in the Font group, you will see a button that has Aa on it; select the text which is causing problems, then click on the drop-down next to the Aa button and select the bottom option, which says tOGGLE cASE. Your text will then be transformed so that it is looking how you wanted it to.

If you are composing an email in Microsoft Outlook, select the text which is causing problems, click on the Format Text tab, then in the Font group, you will see a button that has Aa on it. Click on the drop-down next to the Aa button and select the bottom option, which says tOGGLE cASE. Your text will then be transformed so that it is looking how you wanted it to.

Find details of our Microsoft Office training in the London area or our Microsoft Office training in Wiltshire, Dorset, Somerset and Hampshire.

Sorting data in Excel is messing up rows!

I had a call from a client to say that several people were using the same worksheet, and somehow when the records were sorted, the records were no longer altogether with rows going astray. If you just click in one cell in a data area to be sorted, and then sort, the whole data area will be sorted. If you select a whole column and then try and sort you will get a message coming up asking whether you want to expand the selection or just sort on that column. Similarly, if you select a few cells in one column, the same message will appear – usually you do want to expand the selection and sort the whole data list.

The problem that was occurring was that people were selecting a subset of the data and then asking it to sort. The sort was then being performed just on the cells selected, with no warning message at all!

How do you get round this? Well you could send your staff on Excel training – no doubt they would pick up a lot of other hints and tips along the way. But in the short term, change the data set into a table – select the data area, then from the Home tab, then in the Styles group, click Format as Table or press Ctrl + T. Choose a table design of your choice. The data will be formatted as a table. Now if anyone tries to sort, it will sort the whole data table, using the leftmost column of the selected cells as the sort column.

Find out more about our Excel training London, UK or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Word – formatting pasted information

A client was copying information from one document into another Word document. The paragraph in the document to be added had shading round it, which was causing problems when copied over as shading was not wanted in the new document.

When pasting into a new document, you should see a Paste Options symbol at the bottom right of the pasted paragraph. Click on this and a dropdown appears asking whether you want to merge the formatting or keep the source formatting or keep text only.  Select Merge formatting. The shading will disappear as will any other odd formatting that may have been in the original document.

Buttons from left to right:

• Keep Source Formatting
• Merge Formatting
• Picture
• Keep Text Only

Find out more about our Microsoft Word training London, UK or our Microsoft Word training in Wiltshire, Somerset, Hampshire and Dorset.

Customising the Microsoft Office Quick Access Toolbar

The Quick Access Toolbar is located at the top left of the window and by default contains buttons Autosave On or Off, Save, Undo and Redo.

You can add (or remove) whatever buttons you like to this toolbar. If you click on the dropdown at the right of the toolbar, you will see various other commands.

Clicking on them will add the relevant button to the Quick Access Toolbar. If there are buttons not on that list you would like to add, click where it says More Commands. The Options window opens with Popular Commands on the left hand side and those currently on the Quick Access Toolbar at the right. To add a button, select it on the left hand side and click Add to add them to the toolbar.

If there are other buttons you would like to add that you can’t see click the dropdown where it says Popular Commands and select All Commands. Select the required buttons, then click Add. When you have finished, click OK. The buttons are now on the Quick Access Toolbar. You will need to do this separately for each package within the Microsoft suite.

Another way to add buttons to the Quick Access Toolbar is to right-click on any button on any of the ribbons, then select Add to Quick Access Toolbar. To remove a button from the toolbar, right-click on it on the toolbar, then click Remove from Quick Access Toolbar.

You can also move your Quick Access Toolbar below the ribbon, by clicking on the dropdown at the right of the toolbar and selecting Show Below The Ribbon.

Find out more about our Microsoft Office training in the London area or our Microsoft Office training in Wiltshire, Dorset, Somerset and Hampshire.

Displaying all the formulas in an Excel worksheet

To quickly display all formulas in a worksheet, press Ctrl + ` (the button to the left of the number 1 on the keyboard). This enables you to check for consistency. If you have inherited the workbook, it quickly allows you to see how it was set up.
To see the data again, press Ctrl + `  again.

Alternatively, on the Formulas tab, click Show Formulas in the Formula Auditing group.

Visit Microsoft Excel training  for training in London UK, or Microsoft Excel training for training in Wiltshire, Dorset, Somerset and Hampshire.