NTAPs Intermediate Excel Training

1
NTAPs 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 ltctrlgtR
• 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 ltctrlgtD
• 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 COLUMNROW 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!xz 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
  COLUMNROW
• 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_at_lsntap.org