Microsoft excel
Download
1 / 18

Microsoft Excel - PowerPoint PPT Presentation


  • 60 Views
  • Uploaded on

Microsoft Excel. Tips and tricks. 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. Calculate equations. Equations begin with =

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Microsoft Excel' - pearl-wilkinson


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Microsoft excel

Microsoft Excel

Tips and tricks


Microsoft excel1
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


Calculate equations
Calculate equations

  • Equations begin with =

  • =99/11 gives 9

  • =B2/C2 gives 9

  • =B2+C2gives 110

  • =B2-C2gives 88

  • =B2*C2gives 1089


Calculate sums and averages
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


Named ranges
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


Use ranges in calculations
Use ranges in calculations

  • Cells B2 to B4 are named production

  • Cell B5: =sum(production) gives 8099


Relative cell addresses
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


Relative cell addresses1
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


Absolute cell addresses
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


Absolute relative and mixed addresses
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


Reorder data
Reorder data

  • Select the data to sort

  • Data > Sort >

  • Select the field(s) to sort on

  • Click OK


Format cells
Format cells

  • Cells formatted as Date

  • Format > Cells > Number > Date


Format cells1
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


Format cells2
Format cells

  • =WEEKDAY() function, formatted as ddd


Text alignment
Text alignment

  • Text aligned right

  • Aligned to top

  • Wrapped within cell


Repeat headings on printouts
Repeat headings on printouts

  • File > Page Setup > Sheet > Rows to repeat at top

  • Select rows to repeat


Transpose data
Transpose data

To replace columns with rows

  • Select the whole table

  • Edit > Copy

  • Click in an empty cell

  • Edit > Paste Special > Transpose > OK


Charts
Charts

  • Select data

  • Insert > Chart

  • Select type of chart

  • Format chart as needed


ad