1 / 27

NTAP’s Intermediate Excel Training

NTAP’s Intermediate Excel Training. Topics Covered:. Multiple Worksheets in one File Using the Fill Function Relative versus Absolute References Charts and Graphs. Each Workbook Contains “Sheets”. To Rename or Move, Right-Click. . Shortcuts.

hera
Download Presentation

NTAP’s Intermediate Excel Training

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. NTAP’s Intermediate Excel Training Topics Covered: • Multiple Worksheets in one File • Using the Fill Function • Relative versus Absolute References • Charts and Graphs

  2. Each Workbook Contains “Sheets”.

  3. To Rename or Move, Right-Click.

  4. Shortcuts • You can also move a worksheet by dragging and dropping the tabs • You can rename by double-clicking a tab and then when the name is highlighted you type the new name

  5. Multiple Worksheets? Why Useful? • Group Important Data in one File • Worksheets can contains references to each other • Example: The State of Colorado Child Support Spreadsheet creates a pleading from data entered by the plaintiff

  6. Create Cross Reference btw Worksheets • Move the cursor to the desired cell on the other sheet, type “=“ and select the cell you want referenced. • OR: Type the reference as !sheetname followed by the cell reference; for example =Gabrielle!B13

  7. The Fill Function • Allows you to conveniently replicate a formula across columns or rows • Can be absolute or relative • A Time Saver.

  8. Before

  9. After

  10. Fill to the Right – Relative Reference • OPTION 1…. • 1. Enter the formula that will be filled • 2. Highlight row to be filled and click <ctrl>R • OPTION 2… • 1. Menu Bar | Edit | Fill to Right. • 2. Menu Bar | Edit | Fill… Series • OPTION 3… • Enter formula in one cell. • Copy it. • Drag cursor across fields you want it copied to… Paste. • Relative Reference moves the referenced field in relation to the fill

  11. Fill Down – Relative Reference • Enter the formula that will be filled • Highlight column to be filled and click <ctrl>D • Other 2 Options Work too…

  12. Relative References • Think of compound interest…. • Every cell will be changed relative to the prior cell.

  13. Fixed or Absolute Values • Every cell is changed according to the original formula / value. • Think of simple interest…

  14. Absolute Reference: Fill to the Right • OPTION 1: • Enter the formula to be filled • Use format $COLUMN$ROW for fixed reference • OPTION 2: • Shortcut to get fixed reference is F4

  15. Fill to the right – Fixed Reference From another worksheet • Enter the formula to be filled • Use format Sheetname!$x$z for fixed reference where x and z are the cell references on the other sheet

  16. Advanced Topic Preview: Food for Thought • Partially Fixed references like $COLUMNROW or COLUMN$ROW • F4 toggles through full and partial fixed references

  17. Charts • Click Insert | Chart • Pick type of Chart or Graph Type • Enter Data Range • Add Legends and other frills

  18. Pie Chart

  19. Select Pie from Wizard, then Next

  20. Highlight Data Range

  21. Click Series Tab and Select Category Labels and Name chart then Next

  22. Click on Data Labels, Select Desired Items

  23. Click on Legend Tab, Select Location then Next

  24. Select where to put Chart and name new Sheet if applicable

  25. Finished Result

  26. Final Words on Charts and Graphs • There are endless varieties of charts • Play around with the chart wizard and see what happens • If you change the data in chart or graph by clicking on the data points and moving them around, the spreadsheet values change • The size of the chart may affect how much data shows click and drag corner to resize and see what happens • You can format many of the elements of a finished chart click, i.e. click on legend elements to reformat them

  27. Questions? www.lsntap.org/techlibrary Gabrielle Hammond gabrielle@lsntap.org

More Related