# 75 Tips and Tricks Excel-Reduced

Pages: 16 (3597 words) Published: May 23, 2011
300 office tips • Special feature

Microsoft Excel
You can convert rows to columns (and columns to rows) by highlighting the cells you want to switch around, clicking on Edit, Copy, selecting a new cell and then going to Edit, Paste Special… Finally, place a tick in the Transpose box on the dialog box and click on OK.

1 Convert rows to columns

the running total of the figures in A1 to A5 in the adjacent column.

Enter in a cell the formula =A2-A1, where A1 is the earlier date, and A2 the later one. Don’t forget to convert the target cell to number format – do this by highlighting the cell, clicking on Format, Cells…, picking on the Number tab and selecting Number from the Category: list.

time between 2 Calculatedates

Pick the function you require from the list box and click on OK. Now highlight the cells on which you want the target to perform the function and click on OK.

To make your tables fit neatly on the page, click on File, Page Setup…, select the Page tab, click on the Fit to: radio button and pick 1 page wide. Click on the tall box and press [Delete], leaving the box empty.

6 Fit wide tables to the page width

3

Enter URLs as text, not hyperlinks

Enter the numbers to be added in column A, say A1 to A5, then enter =SUM(\$A\$1:A1) into column B. Highlight the cells beside the ones with numbers in (in our example, B1 to B5) and go to Edit, Fill, Down. This places

4 Calculatetotals running

If you want to hide from view any sensitive data, highlight the relevant cell and click on Format, Cells… Click on the Numbers tab, select Custom from the Category: list, double-click on the Type: input box and enter ;;;. Undo the operation to make your data visible again.

If you use the same list over and over in different worksheets, you might want to add it to your AutoFill list – this will save you heaps of time in future. Highlight your list, click on Tools, Options… and select the Custom Lists tab. Click on Import, then OK.

Templates can save you considerable time when you’re setting up a new worksheet. Click on File, New…, select the Spreadsheet Solutions tab and choose a template from the list.

8 Use template worksheets

The Paste Function button enables you to browse formulas and is the easiest way to enter Functions.

If you’ve converted from Lotus 1-2-3 and find Excel confusing, you can access help specific to your situation by clicking on Help, Lotus 1-2-3 Help…

9 Access help for Lotus users 10 Use the browser formula

12 Use the AutoCalculator

Filling Down – the easy way to perform dynamic calculations.

Select a cell and click on the Paste Function button on the main toolbar.

If you need to calculate a sum based on a row or a column of figures and you can’t be bothered typing in a function, just select your figures and glance down at the status bar – you’ll find the sum of the selected cells there. April 2001 PCAnswers

05

Special feature • 300 office tips

What’s more, if you right-click on the sum a pop-up menu will appear offering additional quick calculation functions.

Alignment tab, and then drag the Text pointer in the Orientation window.

Excel has two distinct date calculators: one returns the current date, the other returns the creation date.

To enter the...