welcome. welcome! if you joined the conference via telephone, please enter your audio pin if you...
TRANSCRIPT
Welcome
Welcome!Welcome! If you joined the conference via
telephone, please enter your audio pin if you haven’t already. (The image on the right is an example - your audio pin will be different.)
If you joined with a microphone and headset or speakers (VoIP), please be sure your device volume settings are properly adjusted.
If your mic & headset is not working, please try unplugging and re-plugging in your device.
We will begin promptly at the top of the hour.
OFF120: OFF120: MSExcel for Advocates-MSExcel for Advocates-20032003September 18, 2009
•Rachel R. Medina, LSNTAP
Please, let us know your Please, let us know your questions and comments during questions and comments during the training the training Maximize/minimize your
screen with the chevron symbol
Telephone participants need to enter their audio pin
Ask a question in the questions log
Raise your hand
Ensuring we can hear you Ensuring we can hear you Mic & Speakers users should
make sure the microphone volume is turned up.
You can always switch to telephone at anytime in the training.
Ensure that you have not muted yourself
Un-muted Muted
Questions? Questions? Raise your hand, please!
Today’s training…Today’s training…
…is being recorded…will be archived on our website
Registered attendees will receive an email with a link to this information once it has been posted
OFF120: OFF120: MSExcel for Advocates-MSExcel for Advocates-20032003September 18, 2009
•Rachel R. Medina, LSNTAP
Lists of data◦Contacts, dates, etc.
Financial tracking◦Budgets
Charts and graphsGrant ReportingCustomized calcuation sheets Import and export data
Salient Functions of ExcelSalient Functions of Excel
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Spreadsheet program allowing data entry into rows and columns
Very useful for managing certain types of data
Your funders and partners expect you to be able to use it!
What is Excel and Why Use It?What is Excel and Why Use It?
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Functions Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Name BoxFormula BarCell ReferencesMaking FormulasWorkbook and Worksheets
Excel FundamentalsExcel Fundamentals
Formula Bar and Name Formula Bar and Name Box Box
A colon indicates a range of cells B1:B5 (B1 through B5)
A comma indicates only individually listed cells B1, B2, B3, B5 (does not include B4)
Cell ReferencesCell References
Cell Range Cell Range
Cell Range Cell Range
Formulas contain ◦An = sign◦Cells or cell references◦Calculation symbol
Creating FormulasCreating Formulas
=(B1+B5) Addition=(B1-B5) Subtraction=(B1*B5) Multiplication=(B1/B5) Division=(B1%B5) Percentage=(B1^B6) Exponential
Equations=SUM (B1:B5)=AVERAGE (B1:B5)
Calculation SymbolsCalculation Symbols
Example: =SUM(….)Example: =SUM(….)
Excel documents are called ‘workbooks’ which contain ‘worksheets’
Worksheets are meant to contain sets of data that can be linked to another
Workbook and Workbook and WorksheetsWorksheets
WorksheetsWorksheets
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Managing worksheets◦Renaming ◦Moving◦Adding/Deleting
Linking worksheets together◦Why?◦How?
Using WorksheetsUsing Worksheets
Managing Worksheets Managing Worksheets
Why link worksheets?
How do you link worksheets?◦Move the cursor to the desired cell
on the other sheet, type = and select the cell you want referenced.
Linking Worksheets Linking Worksheets TogetherTogether
◦Manual link with Excel code: =sheetname!cell reference; For example =Budget!B13 will give you
the contents of cell B13 in worksheet named ‘Budget’
Make sure to use single quotes if the title has a space Ex. Worksheet Budget Summary will be linked as =‘Budget Summary’!A1
You can also rename cells in the name box for easier linking codes No numbers or spaces
Manually Linking Manually Linking WorksheetsWorksheets
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill and Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Unformatted WorksheetUnformatted Worksheet
Formatted VersionFormatted Version
Formatting Cells◦AutoFit◦Number◦Merge and Center◦Background Fill◦Font◦Alignment◦Sort◦Border◦Protection
Formatting WorksheetsFormatting Worksheets
Freezing a row and/or column can make spreadsheets much easier to edit, by keeping your primary headers always in view
Freeze PanesFreeze Panes
To Freeze a Row:◦Put cursor in first cell immediately
below the row you want to freeze.◦Go To “Window” on the Menu◦Click ‘Freeze Panes’◦Notice the Line that demarks it as
frozenTo Unfreeze:
◦Go to “Window” on the Menu◦Click ‘Unfreeze’
How to Freeze PanesHow to Freeze Panes
Freeze PanesFreeze Panes
Freeze PanesFreeze Panes
Freeze PanesFreeze Panes
Freeze PanesFreeze Panes
Freeze PanesFreeze Panes
File Menu|Page Setup|Sheet Repeat Rows/Columns
Prints your primary headings on every page
Header/Footer Creates a header/footer for each page
Add/remove gridlines Add or removes cell grid lines
Adjust margins Adjusts page margins
Page Setup for PrintingPage Setup for Printing
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Allows you to conveniently replicate content and formulas across columns or rows
Saving Time: The Fill Saving Time: The Fill FunctionFunction
Fill FunctionFill Function
Fill FunctionFill Function
Click on lower right-hand corner until a + shape appears. Keeping your mouse button depressed, drag below to the cells you want filled.
Fill FunctionFill Function
Fill FunctionFill Function
Fill FunctionFill Function
Fill FunctionFill Function
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Relative is the default.Relative:
◦Copies formulas across a worksheet, but does not copy a fixed value in a static cell.
Absolute:◦Copies a specific cell reference and
its value to other cells.
Relative v. Absolute Relative v. Absolute ReferencesReferences
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Functions Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Click Insert | ChartPick type of Chart or Graph TypeEnter Data RangeAdd Legends and other frills
ChartsCharts
Pie ChartPie Chart
Highlight Data RangeHighlight Data Range
Title, Axis NamesTitle, Axis Names
Data LabelsData Labels
Finished ResultFinished Result
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Survey Tool DataContact Lists from other
DatabasesPIKA ReportsEmail Contact Lists
Importing and Exporting Importing and Exporting DataData
What is Excel and Why Use It? Excel Fundamentals Using Worksheets Formatting Worksheets Fill Function Relative versus Absolute References Charts and Graphs Importing and Exporting Data Excel 2003 vs. Excel 2007
Beginning and Intermediate Beginning and Intermediate Excel TrainingExcel Training
Today’s Agenda
Excel 2003 vs. Excel 2007Excel 2003 vs. Excel 2007
Excel 2003 vs. Excel 2007Excel 2003 vs. Excel 2007
BONUS TIPS!BONUS TIPS!
Excel – Calculate Days Excel – Calculate Days Between Dates Between Dates (NETWORKDAYS)(NETWORKDAYS)
NETWORKDAYS – how it NETWORKDAYS – how it works…works…# of Business Days (or “court”
days) =NETWORKDAYS(B17,B18,A3:A14)
B17 First DayB18 Last Day
A3:A14 refers to the range of dates included in the list of holidays
A3 → A4 → A5 → . . .
A14 →
←B17←B18
Excel – paste Excel – paste special/transposespecial/transposeFlip your data!
Highlight and Copy the spreadsheet
Select Paste Special
Check “Transpose”
Click OK
Excel - ShortcutsExcel - Shortcuts
Excel - ShortcutsExcel - ShortcutsCtrl+A gives you the formula
wizard!
Data/Subtotal Creates Quick Data/Subtotal Creates Quick Subtotals and a Grand Total!Subtotals and a Grand Total!
Data/Subtotal Creates Quick Data/Subtotal Creates Quick Subtotals and a Grand Total!Subtotals and a Grand Total!
Collapse to view just the totals
THANK YOUTHANK YOUDon’t forget to complete our survey
Rachel R. Medina [email protected] Coordinator LSNTAP