intermediate microsoft excel 2010 instructor: steve maier
TRANSCRIPT
• Intermediate Microsoft Excel 2010
• Instructor: Steve Maier
•
Learning Objectives
• Charts & Graphs - Formatting of axis, legends, chart types • Pivot Tables • Formulas and Functions • Grading in Excel-Dropping the lowest scores etc. • VLOOKUP • Calendar Dates & calculations • Concatenation • Top 5 tips and solutions • Password protecting files: working with locked documents
Intermediate Excel 2010
Intermediate Excel 2010
Review
• Reformatting• Reorder• Sort• Find Average• Count• Bar Chart
Creating a new worksheet
Intermediate Excel 2010
Formulas
Intermediate Excel 2010
Operators: + - * /
Cell references Refer to values in
A10 the cell in column A and row 10
A10,A20 cell A10 and cell A20
A10:A20 the range of cells in column A and rows 10 through 20
B15:E15 the range of cells in row 15 and columns B through E
A10:E20 the range of cells in columns A through E and rows 10 through 20
CTRL+`
Functions
Intermediate Excel 2010
Excel Errors
Intermediate Excel 2010
• ##### 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.
Parenthesis in formulas
Intermediate Excel 2010
= (G2*1.15 +10) / 100
Operator precedence:
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
=< ><=>=<>
Comparison
Decimal Numbers
Intermediate Excel 2010
Dates
Intermediate Excel 2010
Excel for Windows – 1900
Excel for Macintosh - 1904
Track student Grades in Excel
Intermediate Excel 2010
• Summing up scores• Calculate % grade• Calculate letter grade• Calculate pass/fail• Drop lowest score• Grade by weighted assignment
Create a custom sort
Intermediate Excel 2010
Pivot Tables
Intermediate Excel 2010
Formatting Charts
Intermediate Excel 2010
Creating a VLookup
Intermediate Excel 2010
Password protect worksheet
Intermediate Excel 2010
Password protect workbook
Intermediate Excel 2010
Importing Data
Intermediate Excel 2010
Tips and Solutions
Intermediate Excel 2010
1. Split Text
2. Merge cells
3. Insert Current Date/Time
4. Quick Math
5. Drop Down List with no Macros
6. Create Sequences