75 Tips and Tricks Excel-Reduced
300 office tips • Special feature
Microsoft Excel
75 tips and tricks for your favourite spreadsheet.
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
If Excel has already converted your written URL into a hyperlink, you can cancel it by right-clicking on the offending address and selecting Hyperlink, Remove Hyperlink from the menu that pops up.
5 Remove hyperlinks from your work
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 prying eyes 7 Hide your data from 11 Customise your AutoFills
3
Enter URLs as text, not hyperlinks
To prevent Excel from converting written Internet addresses into hyperlinks, add an apostrophe to the beginning of the address, for example ‘www.futurenet.com.
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
Microsoft Excel
75 tips and tricks for your favourite spreadsheet.
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
If Excel has already converted your written URL into a hyperlink, you can cancel it by right-clicking on the offending address and selecting Hyperlink, Remove Hyperlink from the menu that pops up.
5 Remove hyperlinks from your work
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 prying eyes 7 Hide your data from 11 Customise your AutoFills
3
Enter URLs as text, not hyperlinks
To prevent Excel from converting written Internet addresses into hyperlinks, add an apostrophe to the beginning of the address, for example ‘www.futurenet.com.
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