Microsoft Excel

1. Microsoft Excel Tips and tricks

2. Microsoft Excel • Equations and formulas • Ranges • Absolute and relative addresses • Reorder information • Format cells • Repeat headings on printouts • Transpose data • Charts Paul Mundy, www.mamud.com

3. Calculate equations • Equations begin with = • =99/11 gives 9 • =B2/C2 gives 9 • =B2+C2gives 110 • =B2-C2gives 88 • =B2*C2gives 1089

4. Calculate sums and averages • Enter equation here • Cell B5: =sum(b2:b4) gives 8099 • Useful functions: • =sum() • =average() • =count() Range of cells: B2 to B4 = B2:B4

5. Named ranges • To name a range, select the cell(s), then Insert > Name > Define • Use range names in equations • Example • C2 (=15) is named price • C3 (=200) is named quantity • Cell C4: =price*quantity gives 3000

6. Use ranges in calculations • Cells B2 to B4 are named production • Cell B5: =sum(production) gives 8099

7. Relative cell addresses • Create a formula in cell B5: =sum(B2:B4) • Copy the formula to cell C5. It changes automatically to =sum(C2:C4) • The addresses C2 and C4 are relative cell addresses

8. Relative cell addresses • Cell C2: =B2/C7 gives $3.33 • Copying the formula to cell C3 gives =B3/C8 • But C8 = 0, so C3 gives an error • This is because the formula uses relative addresses

9. Absolute cell addresses • Use an absolute address instead, like this: $C$7 • Absolute addresses are fixed. They can be moved or copied, and will not change

10. Absolute, relative and mixed addresses • Relative address: C7 • Absolute address: $C$7 • Mixed addresses: • $C7 (column absolute, row relative) • C$7 (column relative, row absolute) • To toggle through relative, absolute and mixed addresses, select the address and press F4

11. Reorder data • Select the data to sort • Data > Sort > • Select the field(s) to sort on • Click OK

12. Format cells • Cells formatted as Date • Format > Cells > Number > Date

13. Format cells • Cells formatted as Date • Format > Cells > Number > Date Custom formatting • Format > Cells > Number > Custom: • d mmm yyyy = 1 Jan 2004 • dd mmmm = 01 January

14. Format cells • =WEEKDAY() function, formatted as ddd

15. Text alignment • Text aligned right • Aligned to top • Wrapped within cell

16. Repeat headings on printouts • File > Page Setup > Sheet > Rows to repeat at top • Select rows to repeat

17. Transpose data To replace columns with rows • Select the whole table • Edit > Copy • Click in an empty cell • Edit > Paste Special > Transpose > OK

18. Charts • Select data • Insert > Chart • Select type of chart • Format chart as needed