Microsoft Excel Tips

Do you work with spreadsheets that are many columns wide or many rows deep? Grouping columns or rows can help to reduce the amount of scrolling you do, because it allows you to close up areas of the spreadsheet temporarily, and is more user-friendly than hiding columns.

To close up a group of columns, select the columns you want to get out of the way (by selecting the column headers), then click on Data, Group. A black outline will be drawn over the columns you selected, and clicking on the minus sign at the end of it will close up the columns, leaving just a + sign visible. This allows you to expand the columns again easily.

In this example the sheet has a repetitive horizontal layout which means navigation is made easier by grouping the detail columns for each delegate. The delegate names are not included in the group, allowing the sheet to be reduced to a list of delegate names. Clicking the + sign beside a name displays all the detail.

If you want to remove or adjust your grouping, select the columns again and click the Ungroup tool.

Autofill Cells

If you’re familiar with using the fill handle to continue a sequence in Excel or to populate all cells in a column with the same formula, you might be interested in this fill-handle shortcut.

If you have a column of data, and in the next column you create a formula, which you want to copy to every populated row of the sheet, instead of dragging the fill-handle all the way down the column, you can double-click it. Your formula will be automatically copied all the way to the bottom of the data.

Dates

Do you find typing dates a pain? If you want to quickly enter today’s date into an Excel worksheet or an Access database, just type Ctrl and Semi-colon together (CTRL+;).

Freezing the header row in Excel

If the column headers in your worksheet scroll out of sight after you have entered a number of rows, select the row below your header (click on the number at the start of the row), then

2003 - Select Window, then Freeze Panes. The header row(s) will now stay visible, while the lower part of the screen scrolls.

2007 - use the view ribbon, select freeze panes, then click freeze top row.

Show Full Menu

As with all the Office 2003 applications, when you first use Excel the menus will display only the most recently used commands, until you expand them by clicking on the double arrow at the bottom, or you pause your mouse over the menu.

If you wish to see all of the menu options all the time (which is useful when you are learning to use a new application) you can go to the Tools menu, then Customize, and click on the Options tab. Check the box which says Always show full menus.