1 / 57

Microsoft Excel

Microsoft Excel. Spreadsheet Software . What is a Database and what is it used for ?. A database is an organized collection of data related to a particular topic or purpose.

magnar
Download Presentation

Microsoft Excel

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Excel Spreadsheet Software

  2. What is a Database and what is it used for? • A database is an organized collection of data related to a particular topic or purpose. • The primary function of a database is to enable the user to organize and retrieve information in a manner defined by the user. Microsoft Excel 2000

  3. Flat-File vs. Relational • A Flat-file database consists of a single database file or table which contains all the information about a topic. It does not physically link or point to other files. • A Relational database consists of multiple tables linked together by at least one common field. Microsoft Excel 2000

  4. STUDENTINFORMATION TABLE

  5. Products Table Supplier Table

  6. Basic Concepts Microsoft Excel 2000

  7. Basic terms • Columns have letter headings • Rows have number headings • Intersection of a row and a column is called a “cell” • Cells are basic building blocks of Excel column row cell Microsoft Excel 2000

  8. Customizing the toolbar • If menus show recent commands only… • Tools/Customize/Options to turn it off, delay, or reset • If you can’t see all of the standard & format toolbars… • Tools/Customize/Options to break it into 2 separate toolbars Microsoft Excel 2000

  9. Text and Numbers • Type in cell, appears in formula bar • Edit 3 ways • Backspace (if you’re still in cell) • D-click inside cell • Formula bar • Text has no value • Numbers (0-9) and symbols (+, /, *, -) have values, can use formulas • Negative numbers use - or ( ) Microsoft Excel 2000

  10. Inserting, deleting, shifting cells • Insert/cells > to insert • R-click will do it too (context-sensitive) • Always above and to the left • Always reletters, renumbers • Edit/Delete > to delete Microsoft Excel 2000

  11. Cutting, copying, pasting cells • Select first cell, then cut or copy • Select destination cell, then paste • For multiple pastes,Toolbars/Clipboard > Clipboard • 12 items stored on clipboard • Screentips shows you which one Microsoft Excel 2000

  12. Text formats • Adjust text formats with: • Font and size pulldowns • B, I, U buttons • Font color pulldown • Fill color pulldown • Font style • Left, center, right buttons Microsoft Excel 2000

  13. Numeric formats • Don’t type in commas or dollar signs – use Format/Cells/Number for numeric format options • Buttons for $ and , formats • Decimal increase, decrease buttons will round off, but don’t change value Microsoft Excel 2000

  14. Alignment, size, rotation • Defaults: text left justify, numbers right justify • Change with alignment buttons • Format/Cells/Alignment for more alignment options • Wrap, shrink to fit, merge checkboxes for sizing problems • Rotate text with orientation box • Merge and center button useful for headings Microsoft Excel 2000

  15. Sizing cells • Drag rows and columns to proper size by pulling borders • Choose all rows or cells and drag as one – it will evenly widen size • D-click border will auto-fit box to longest record Microsoft Excel 2000

  16. Sheet formats • Format/Autoformat/Choose a style to automatically format a sheet • Options button allows only certain aspects to be chosen • Borders buttons to manipulate borders • Style painter button will copy styles – (1-click for 1 time use, 2-click to leave it on) • Paste special will allow you to copy certain aspects Microsoft Excel 2000

  17. Formulas and logical functions Microsoft Excel 2000

  18. Formulas • Always pick destination cell first • Always click = to begin (except autosum) • Autosum (∑ button) will automatically add a column of figures • If wrong, You can adjust by typing in formula bar Microsoft Excel 2000

  19. Copying formulas • Drag by lower right-hand corner(cursor will be small black + sign, not big white + sign) • Relative referencing (i.e. - D9) – will change as cells are added or deleted • Absolute referencing ($D$9) – specified cell only – will not refigure Microsoft Excel 2000

  20. Math functions • 4 main functions: + (add), - (subtract), / (divide), * (multiply) • Type it just like an algebraic formula (e.g. – e3*f3 means to multiply the value in cell e3 by the value in cell f3) Microsoft Excel 2000

  21. Math functions (con’t) • For more complex formulas, click the = button, then pull down a function in upper right • Sum • Average • Min • Max • Count Microsoft Excel 2000

  22. Ordering • 10 * 2 – 1 = 19 or 10? • Order of preference – which order excel will calculate expressions • PEDMAS • Parentheses • Exponents • Division • Multiplication • Addition • Subtraction Microsoft Excel 2000

  23. Date and time functions • “Now” function, “today” function to capture time or day, choose format after the fact in Format/Cells/Number • Dates/times represent mathematical values • Date due minus date delivered to track lag time Microsoft Excel 2000

  24. Logical functions • If statements – checks values and returns text on false and true statments • Or statements - returns true if any cell fits a condition • And statements – returns true if all cells fit a condition Microsoft Excel 2000

  25. More functions • Financial functions • FV > Future value • PMT > Payment functions • Randomizing • RAND • Remember to set value, then cut and paste • SUMIF to count only certain values • COUNTBLANK to count blanks Microsoft Excel 2000

  26. Previewing and printing Microsoft Excel 2000

  27. Page setup • File/Page setup • Place to set up printing format – Use “Print preview” to check • Page setup – 4 tabs • Page (portrait vs. landscape, scaling, scale to fit) • Margins – set margins, center horz. And vert.) • Headers/footers – set standard or customized footers and headers here) • Sheet (print all or part of book/sheet?, repeat rows?, gridlines?) Microsoft Excel 2000

  28. Setting and clearing print areas • To print part of worksheet • L-click and drag area you want • File/Print area/Set print area • Dashes show print area • File/Print/Chose “Selection” • Be sure and clear it when done Microsoft Excel 2000

  29. Web page preview • File/Web page preview • Opens in new window • Tabs included • HTML code (View/Page source) Microsoft Excel 2000

  30. Charts Microsoft Excel 2000

  31. Chart Wizard • Use chart wizard icon • Wizard leads you through decision process • Choose chart type (press and hold for preview) • Define data range • Set titles, axes, gridlines, legends, labels • New sheet vs. embedded Microsoft Excel 2000

  32. Modifying charts • Click and drag to include more cells, chart will change • L-Click on specific elements to choose them – R-click to get format options – change fill colors, fonts, gridlines, etc. • Change cell numbers, chart will change Microsoft Excel 2000

  33. Inserting objects • Chart is an object • You can add others (pictures, clip art, logos, text boxes) • Size (use sizing boxes) and move (click and drag) them afterward This is a textbox, and an arrow pointing to clipart. Microsoft Excel 2000

  34. Drawing toolbar • Icon next to Chart Wizard, or R-click in toolbars • Text box • Size it, start typing • Size and move when finished • Line and arrow buttons • Click and drag, use sizing boxes to manipulate • Multiple objects – group them before printing (hold down shift key, select all objects) Microsoft Excel 2000

  35. Printing charts • If you only want to print chart, click chart, then File/Print Preview • To print whole page, click away from the chart Microsoft Excel 2000

  36. Sorting and filtering Microsoft Excel 2000

  37. Sorting caveat • Filtering masks, doesn’t change numbering • Sorting rearranges, changes numbering • BE CAREFUL – when sorting, click inside column, NOT in column letter • it will strip column from data when sorting • When filtering, you can use column Microsoft Excel 2000

  38. Sorting • Click anywhere within column • Click ascending, descending order buttons • Data/Sort (for multiple sorts) Microsoft Excel 2000

  39. Auto-filter • Select column letter • Data/Filter/Auto-filter • Click arrow, choose value, all records with that value will be shown • Notice the row numbering changes – and is in blue • If you don’t select column, Data/Filter/Auto-filter, and you can filter by multiple columns Microsoft Excel 2000

  40. Restoring the List • Restore all or some • All > Data/Filter/Autofilter again • Some > choose column, arrow, then all Microsoft Excel 2000

  41. Custom filter • Two criteria filter • Data/Filter/Autofilter/(Custom) • 1st agument, 1st value, 2nd argument, 2nd value • Wild cards (* and ?) can be used Microsoft Excel 2000

  42. Subtotals • Choose cell within sheet • Total, Subtotal • Choose Column to separate by • Choose math function • Choose column with values • OK • Use levels on left to manipulate level of detail Microsoft Excel 2000

  43. Managing workbooks Microsoft Excel 2000

  44. Changing zoom setting • Zoom window allows you to choose percentage from 10-200% • Ctrl + mouse wheel does it too • Doesn’t change document, or print size, just your view • Zoom to any part of sheet by selecting it, choosing selection in zoom window Microsoft Excel 2000

  45. Freezing and unfreezing columns and rows • Easy way to freeze certain areas of long spreadsheets • Click cell where you want the page to “break”, then Window/Freeze pane • Window will freeze columns and rows above and to the left of the cell you chose • Doesn’t change document, just your view • Window/Unfreeze pane to unfreeze Microsoft Excel 2000

  46. Hiding and unhiding • For confidential data (salaries), or data you don’t want to see • Hidden data doesn’t print – good way to shrink size of large spreadsheets • Select column or row > R-click/Hide • To unhide, select columns or rows before and after hidden one, R-click, unhide Microsoft Excel 2000

  47. Inserting and deleting sheets • Insert/Worksheet • Will insert to left • R-click on tab to delete Microsoft Excel 2000

  48. Copying, renaming, moving sheets • D-click tab to rename, or Format/Sheet/Rename • Click and drag tab to move • To copy: • R-click on tab, choose “move or copy” • Choose sheet, check “make a copy” • Filename (2) will appear • Click and drag + Ctrl will copy as well Microsoft Excel 2000

  49. Linking workbooks Microsoft Excel 2000

  50. Linking formulas • Used to create hierarchies of data • 3-D formulas will reference cells from other sheets • Good for summarizing large amounts of data spread over several sheets Microsoft Excel 2000

More Related