Microsoft Excel 2007 Fran Kovach [email protected] Reference & Education Librarian Coley Cowan [email protected] Business Manager Department of Information & Communication Sciences

Excel terms • • • • • • • • •

Spreadsheet, sheet Rows are numbered Columns are alphabetical Cells have an address, A1 Ribbon Scrollbars Sheet tabs New sheet insert Formula bar

Office button • Click here to open, save or print. • Click New to start a new sheet • Save as - type the name of the file, the extension for Excel 2007 is .xlsx, can be saved in other formats .xls • Open existing sheets • Right of the office button is the Quick access tool bar • Click on the double arrows to checkmark and customize the Quick access icons.

Prepare the document for distribution • Properties View and edit workbook properties such as Title Author and Keywords • Inspect Document Check the workbook for hidden metadata or personal information • Encrypt Document Increase the security of the workbook by adding encryption • Restrict Permission Grant people access while restricting their ability to edit, copy, and print • Add a Digital Signature Ensure the integrity of the workbook and make it read-only • Mark as Final let readers know the presentation is final and make read only • Run Compatibility Checker check for feature not supported by earlier versions

Excel Options - Popular Edit Custom Lists, Click Add

Excel Options Formulas

Excel options Proofing Custom Dictionaries

Excel Options - Save Format Autorecovery

Excel Options Popular Edit Custom Lists – Click Add

Formulas Proofing – Custom dictionaries Save Format, Autorecovery

1. 2. 3. 4. 5.

Advanced Custom Add-ins Trust Resources

Freeze the Row and column headers Under View ribbon

• Decide what rows to freeze • Freeze the Row • Scroll Down • Unfreeze the row • Freeze Columns

Freeze Panes

Adjust Row Height and Column Width Drag the row up or down

Click to drag column left or right

• If the column is too small ### will show. Increase the size of the column to see the numbers.

Formats Home ribbon Formats

Shortcut menu right mouse click Format Cells

Select columns

Home ribbon Clipboard - cut, copy, paste, paste special Font – styles, sizes, borders Alignment – general, $, % wrap text merge and center

Home ribbon Styles – Conditional Formatting Format as Table Cell styles

Conditional Formatting

Format as Table – Select cells, Choose Format

Insert – select Column or Row

Delete – Select Column or Row

Format - width height row or column, rename sheet

Auto Fill Fill Handle +

• Type January, automatically fills in the months of the year • Type year, automatically fills in the years • Counts type 1, 2 • Sequences 543, 544

+

Home ribbon Editing AutoSum

• • • • • • •

Working formula Sum Average Count Numbers Max Min More Functions

Let’s do the Math Always start the formula with the = equal sign + Addition = A1 + A2 - Subtraction = A1 – A2 * Multiplication = A1 * A2 / Division = A1/A2

Calculation practice • Type in 23, 33, 44, 55 in row 1 • Type in 31, 42, 54, 33 in row 2 1. Add the first row. 2. Subtract 55 – 44 3. Multiply 44 * 55 4. Divide 55/33 Remember two things: Blank cell for the answer and start the formula with an = equal sign

Formula bar and fx Insert Function Insert function symbol AutoSum Formula bar

Practice with the AutoSum January

February Total

45

32

Insert function practice 1. Type 45, 32 2. Go to a blank cell. This is where the answer will appear. 3. Click on fx on the formula bar 4. Choose Sum 5. Click and drag 45, 32 to insert the numbers in the formula. 6. If the answer is 77, press enter.

Click fx on the formula bar

Choose Sum

Click OK

Check the formula Click OK

• Pivot Table Tables Insert ribbon • Table • Clip Art, Picture Illustrations • Shapes Smart art • Column, Other charts Charts • Line, Pie, Area, Scatter

Links Text

• Hyperlink

Header Footer

Header Footer

1. Insert ribbon 2. Click on Header 3. Design view add Header at the top and Footer at the bottom of the page 4. Click View Normal to go back

• Themes Themes • Colors, Fonts, Effects •layout Margins, ribbon Orientation, Size Page Page Setup • Print, Area, Breaks

Scale to Fit

• Width, Height, Scale

Sheet options • Gridlines, Headings Arrange

• Selection, Align

Formulas

• Fx Insert Function

• Autosum, recently used Function library • Financial, Logical, Text • Lookup, Math, More Formulas ribbon Defined names

• Name manager

Formula auditing Calculation

• Calculation options

Get external Data

• From Access, From Web • From Text, From Other

Connections

• Existing, Connections • Refresh All

Data Tools Outline

Data ribbon

• Text to Columns • Remove duplicates • Consolidate, What If

• Group, Ungroup, Subtotal

Proofing

• Spelling, Research • Thesaurus, Translate

• New Comment Comments • Show All Comments • Protect Sheet Review ribbon Changes • Protect Workbook • Share Workbook • Protect and Share • Allow Users to Edit Ranges • Track changes

• Normal, Page Layout Workbook • Page Break, Custom, views • Full Screen View ribbon • Gridlines, Ruler Show/Hide • Formula Bar, Heading • Zoom, 100%, Zoom to Zoom selection

Window Macros

• New Window, Arrange • Freeze Panes, Split, Hide • Save Workspace, Switch workspace

Printing • Page layout, page setup, sheet • Set Print Area, Titles, Order • Request Page Preview

Page Setup

Page selection

Select area to be printed

Print what choose Selection

Print Preview

Office button Save as name file Save as .xlsx or other format