Microsoft Excel Tips

Hiding and unhiding columns

You can close columns up so that they don't show in your spreadsheet, and only the gap in the column letters indicates that there are more columns available. You might use this to hide some calculation detail and focus on the results, or just do it temporarily to bring particular columns into view. Select the columns you want to hide by clicking on the column headers. Now right-click and select Hide.

When you want to make these columns visible again, you must first select the columns either side of the hidden area, then right-click and select Unhide.

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.

Quick way to enter 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, 2010 - use the View tab, select Freeze Panes, then click Freeze Top Row.

You can also freeze columns at the left hand side, by selecting the column after the area that should stay fixed, then selecting Freeze Panes.

To freeze both a row and a column, select the cell that intersects the freeze row and the freeze column, then select Freeze Panes.

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.

Working with Split Screen

Split View allows you to move areas of one spreadsheet around in different panes on your Excel window. For example by splitting the screen you could bring a totals line from the bottom of your spreadsheet into view at the same time as the header rows.

The top pane can be vertically scrolled independently of the bottom pane

The right pane can be horizontally scrolled independently of the left pane.

To create the split, click on a cell in the middle of your worksheet then click Split on the View tab, your Excel window will be divided into 4 panes. If you click anywhere in the first column then click Split, the screen will be divided into 2 panes horizontally. If you click anywhere in the top row then click Split, the screen will be divided into 2 panes vertically. In any of these cases you can click and drag the grey dividing bars to reposition them.

To remove a split, click the Split tool again, or double-click the dividing bar.