Effective Use of Excel in Your Office

1
Effective Use of Excel in Your Office Margaret
R. (Peg) Stearns, Financial Aid Director Benita
J. Rodriguez, Financial Aid Advisor and Asst.
Dir. University College of Syracuse
University November 6, 2006
2

• Effective Use of Excel in Your Office
• a.k.a. There has to be a way, or an easier way to
  
• We will cover
• Text to Columns
• Pivots
• Naming a cell for use in formulas (cornerstone
  cell)
• VLookup
• Protecting workbooks for distribution

3
Text-to-Columns
4
Text-to-Columns Data Drop Down
5
Text-to-Columns Wizard Defaults
6
Text-to-Columns Wizard Options
7
Text-to-Columns Finished
Voila! Add new column headings, save and you are
ready to use this for your Word Merge for labels
or letters.
8
Pivots
Original format
Data after Pivot
9
Pivot Steps
Step 1
Step 2
10
Pivot Steps Continued
Step 3
11
Pivot Steps Continued
Step 4
12
Different formats of the data using Pivots
Sum of grant amounts awarded by campus
Count of the number of students receiving a
certain grant amount
13
Different formats of the data using Pivots
Continued
Count of students and the sum of grants by campus
Count of students and campuses by grant amount
14
Naming a Cell for formula use
15
Naming a Cell for formula use
Change the name of your anchor or cornerstone
cell from B5 to Rate
16
Naming a Cell for formula use
Enter your formula in your first cell, then copy
it and paste it into the remaining cells.
17
VLookup
Functions menu
18
VLookup
19
VLookup
20
Protecting areas you do not want others
to be able to change

• 4 Steps
• Highlight cells you want others to be able to
  make changes.
• Right click on those cells and go to FORMAT
  CELLS.
• Go to the PROTECTION tab and uncheck the
  LOCKED checkbox.
• Then to TOOLS, PROTECTION, PROTECT SHEET.

Note To highlight more than one column or cell
which are spread out in spreadsheet, hold down
the Ctrl key.
Quoted from Fred Pryor Seminar Excel Workbook
21
Formatting Cells
Step 1
Step 3
Step 2
22
Protecting Sheet
Step 4
23
Protecting Sheet Continued
Step 4 Continued
24
Questions? Comments?
25
Thank You! Peg Stearns mrstearn_at_uc.syr.edu Benita
Rodriguez brodri01_at_uc.syr.edu 315-443-3261 www.Y
eSU.syr.edu/finaid/2006nysfaaasconf