Microsoft Excel is one of the most widely used software packages in business today. It is incredibly powerful and is packed full of features for handling and analysing large amounts of data. In this article we look at some simple but brilliant Excel tips.

Freeze Panes

Freeze panes in Excel to keep your headers visible as you scroll through a spreadsheet. You can freeze rows and/or columns on your sheet.

When freezing panes, Excel will freeze everything above and to the left of the selected cell. So for example, if you select cell B2 and freeze panes, Excel will freeze row 1 and column A.

  1. Select the required cell
  2. Click the Window menu and then Freeze Panes in Excel 2003

Or click the View tab, Freeze Panes and then Freeze Panes again in Excel 2007 and 2010.

Use the Ctrl and Shift Keys with Large Spreadsheets

When working with large spreadsheets it can be difficult to navigate and select large amounts of data. The Ctrl and Shift keys on your keyboard are extremely useful in this respect.

Use the Ctrl key with the keyboards cursor arrows to navigate a large spreadsheet. For example Ctrl and the right arrow will take you to the end of the row, and Ctrl and the down arrow will take you to the bottom of the column.

The Shift key can be added to select the range of cells. So Ctrl + Shift and the down arrow would select all the cells from the current cell to the bottom of the column.

Technically what actually happens is Excel moves to, or selects all the cells to the next blank cell, and assumes that this must be the end of the row or column. This obviously may not be the case, but where possible try to avoid empty cells in Excel.

Use Lists and Tables

Lists in Excel 2003, and Tables in Excel 2007 and 2010 are used to create a dynamic data range. When you create a chart, PivotTable or write a formula and base it on a range of cells, they do not automatically update and include any extra rows or columns that you may add at a later date.

It is fair to say that at some point you are likely to need to add more rows to the bottom of the range of cells. If you want your formulas and charts etc to automatically pick these extra rows up, you should create a list or table from the cell range.

To create a list in Excel 2003;

  1. Select the range of cells you want to use
  2. Click the Data menu, select List and then Create List

Or press Ctrl + L

  1. The range of cells you want to use is shown, Click Ok

To format data as a table in Excel 2007 and 2010;

  1. Select the range of cells you want to use
  2. Click the Home tab on the Ribbon
  3. Click the Format as a Table button and select a table style
  4. The cell range that you selected is shown. Click Ok

Select Visible Cells Only

A very useful tool in Excel 2003 is the Select Visible Cells tool. This tool can be used for copying filter results and Subtotal results in Excel 2003.

When copy and pasting the results of a filter or subtotals in Excel 2003, Excel pastes the hidden rows as well as the visible ones by default. You will need to specify if you wish to use the visible cells only.

To do this:

  1. Select the cells you want to copy
  2. Click Edit > Go To or press Ctrl + G
  3. Click the Special button
  4. Check the Visible cells only box and click Ok
  5. Copy the cells as usual

The Select Visible Cells button can also be added to a toolbar for ease of use in the future. As you can see from the steps above, it is not the most accessible tool around.

From Excel 2007 this feature is no longer required when copying and pasting results from filters or subtotals. The default is set as visible cells only.

Clear Cells

The clear cells feature of Excel is used to completely clear a cell. When using the keyboards Delete key it will only remove the cell contents. Other aspects of a cell such as its formatting, data validation and comments remain. To remove all the aspects of a cell, the Clear feature should be used.

In Excel 2003;

  1. Click the Edit menu
  2. Select Clear and then choose either All, Formats, Contents or Comments from the submenu

In Excel 2007;

  1. Click the Home tab

Click the Clear button and then choose either All, Formats, Contents or Comments from the menu