quick Excel tips

August 2012

How would we manage without Excel? But do we know all the useful tips that are available, to make using it easier?

Here are three more, which we hope you’ll find useful: colour shading alternate rows, adding a drop-down list, and doing maths without using formulas.

All these tips are based on Microsoft Office Excel 2010 (Windows) but other Excel versions offer the same or similar functionality. (Menus and performance may vary.)

Tip 1: Colour-shading alternate rows

Ever wondered how to make green-banded computer paper on screen? It makes lengthy lists much easier to read.

Here's how:

Highlight the range of cells or rows or columns that you want to format.

Click Insert, then Table.

A box will pop up, called Create Table. Click OK.

Then just click the Design button, to choose the colours and pattern you want. Click at the bottom of the dropdown list to see the full range of colours available. If you want to design your own, click New Table Style and do some experimenting!

The alternate row shading will automatically continue, even if you insert or delete rows within the original range.

If you want to delete the shading, click Clear, right at the bottom of the Design dropdown list.

Tip 2: Add a drop-down list

Inserting a drop-down list into a cell makes worksheets more professional and ensures that correct data is entered.

If you want to insert a drop-down list with the months of the year, for example, here's what you do:

Enter the items that you want on your drop-down list into a list on the worksheet, one item per cell. In this example, we will assume that the month names start in cell G1 and extend down to G12 (12 months, one per cell), but they can be anywhere on the worksheet. This group of cells (such as G1 to G12) is called a range.

Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (To select a range, click and drag; to select non-adjacent cells, hold down Ctrl while you click.)

Choose Data, Validation to display the Data Validation dialogue box.

Click the Settings tab.

In the Allow field, select List.

In the Source field, specify the range of cells that contains the list items. In this example, the items are in cells G1 to G12, so type =G1:G12 into the field.

Click OK.

You'll now see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (selected and waiting for input). Click the arrow and choose a month from the list.

Tip 3: Maths without using formulas

If you want to do maths in an Excel sheet, you don't have to be able to write formulas. Instead, you can use the Paste Function dialogue box, which allows you to perform basic maths operations, without using formulas.

Supposing you have figures such as prices in an Excel worksheet, and you want to increase all the prices by 7%.

Here's what you do:

Enter 1.07 into any blank cell – this is the "multiplier" which will increase the price by 7%. If the prices were to increase by 25%, you would type in 1.25

Now select that same cell and choose Edit, Copy or press Ctrl-C.

Select the range of cells which you want to change, and choose Edit, Paste Special to display the Paste Special dialogue box.

Choose the Multiply option and click OK.

Your prices will now be changed. Delete the cell into which you wrote your 1.07 multiplier.



We hope you found these tips useful!