Intermediate Microsoft Excel 2010 Date August 7 th

  • Slides: 19
Download presentation
 • Intermediate Microsoft Excel 2010 • Date: August 7 th, 2012 Time: 9:

• Intermediate Microsoft Excel 2010 • Date: August 7 th, 2012 Time: 9: 00 AM to 11: 00 AM • Location: Maher 114 • Instructor: Steve Maier •

Learning Objectives • • • Charts & Graphs - Formatting of axis, legends, chart

Learning Objectives • • • Charts & Graphs - Formatting of axis, legends, chart types Pivot Tables Formulas and Functions Grading in Excel-Dropping the lowest scores etc. Calendar Dates & calculations Concatenation Password protecting files: working with locked documents VLOOKUP Top 5 tips and solutions University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Review • • • Reformatting Reorder Sort Find Average Count Bar Chart University of

Review • • • Reformatting Reorder Sort Find Average Count Bar Chart University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Creating a new worksheet University of San Diego, Academic Tech Services i. Team -

Creating a new worksheet University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Formulas CTRL+` Operators: + - * / Cell references Refer to values in A

Formulas CTRL+` Operators: + - * / Cell references Refer to values in A 10 the cell in column A and row 10 A 10, A 20 cell A 10 and cell A 20 A 10: A 20 the range of cells in column A and rows 10 through 20 B 15: E 15 the range of cells in row 15 and columns B through E A 10: E 20 the range of cells in columns A through E and rows 10 through 20 University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Functions University of San Diego, Academic Tech Services i. Team - iteam@sandiego. edu, ext.

Functions University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Excel Errors • ##### The column is not wide enough to display the content.

Excel Errors • ##### The column is not wide enough to display the content. Increase column width, shrink contents to fit the column, or apply a different number format. • #REF! A cell reference is not valid. Cells may have been deleted or pasted over. • #NAME? You may have misspelled a function name. • Cells with errors such as #NAME? may display a color triangle. If you click the cell, an error button appears to give you some error correction options. How to use the button is not covered in this course. University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Parenthesis in formulas Operator precedence: – Negation (as in – 1) % Percent ^

Parenthesis in formulas Operator precedence: – Negation (as in – 1) % Percent ^ Exponentiation * and / Multiplication and division + and – Addition and subtraction & Connects two strings of text (concatenation) = <> <= >= <> Comparison = (G 2*1. 15 +10) / 100 University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Decimal Numbers University of San Diego, Academic Tech Services i. Team - iteam@sandiego. edu,

Decimal Numbers University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Dates Excel for Windows – 1900 Excel for Macintosh - 1904 University of San

Dates Excel for Windows – 1900 Excel for Macintosh - 1904 University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Track student Grades in Excel • • • Summing up scores Calculate % grade

Track student Grades in Excel • • • Summing up scores Calculate % grade Calculate letter grade Calculate pass/fail Drop lowest score Grade by weighted assignment University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Create a custom sort University of San Diego, Academic Tech Services i. Team -

Create a custom sort University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Pivot Tables University of San Diego, Academic Tech Services i. Team - iteam@sandiego. edu,

Pivot Tables University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Formatting Charts University of San Diego, Academic Tech Services i. Team - iteam@sandiego. edu,

Formatting Charts University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Creating a VLookup University of San Diego, Academic Tech Services i. Team - iteam@sandiego.

Creating a VLookup University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Password protect worksheet University of San Diego, Academic Tech Services i. Team - iteam@sandiego.

Password protect worksheet University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Password protect workbook University of San Diego, Academic Tech Services i. Team - iteam@sandiego.

Password protect workbook University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Importing Data University of San Diego, Academic Tech Services i. Team - iteam@sandiego. edu,

Importing Data University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400

Tips and Solutions 1. Split Text 2. Merge cells 3. Insert Current Date/Time 4.

Tips and Solutions 1. Split Text 2. Merge cells 3. Insert Current Date/Time 4. Quick Math 5. Drop Down List with no Macros 6. Create Sequences University of San Diego, Academic Tech Services i. Team - [email protected] edu, ext. 7400