excel objects, user interface, and data entry. ◦ application window title bar menu bar toolbars...

Excel Objects, User Interface, and Data Entry

◦ Application Window Title Bar Menu Bar Toolbars Status Bar Worksheet Window

Worksheet Input Area Row and Column

Headings Sheet Tabs Scroll Bars

Common Features

◦ Workbook Stored in an Excel data file Filename has .xlsx extension

◦ Worksheet Workbook contains 3 by default Sheet Tabs

◦ Cell Intersection of a row and a column Cell Address or Cell Reference (A1, BA542, etc.)

◦ Range A range is a grouping of two or more cells Vertical / horizontal grouping of all cells in the dimension Names are Alpha / Numeric

◦ Data are inputs to Cells◦ Excel interprets each cell entry as:

Value – numeric constant Text – any other combination of

numeric/nonnumeric chars. Formula

◦ Each Cell has a Returned Value◦ Value and Text Entry - the returned value is identical

to the entry data

◦ Formula Entry - the returned value is the result of a computation that may include: operators

Some formula (arithmetic) operators: * / + - % ^ literal values or strings

46.9, 2.0, 879, January, Monday cell references

A1, B43, A1:D7 worksheet functions and arguments parentheses

that control the order in which expressions in the formula are evaluated

◦ Starts with = sign◦ Examples (assume value A1 is 4, B2 is 3):

Entry: =7+9 Returned Value:

16 Entry: =A2*5 Returned Value: 20 Entry: =(A1-B2)*6 Returned Value: 6 Entry: =“Sat” Returned Value: Sat

◦ Worksheet Functions a predefined computational task or calculation Every Function Consists of:

Function Name Arguments

literal values, cell references, expressions, functions arguments are separated by commas argument list is enclosed in parentheses

Functions:◦ SUM◦ AVERAGE◦ MAX◦ MIN◦ COUNT◦ ROUND

◦ Value, Text and Formula Entries can be easily duplicated to other cells

◦ When you copy a cell you are copying both entry and formatting

◦ Formatting refers: Numeric Formatting Stylistic Formatting

◦ Ways of Copying Info

◦ Cell and Range References Relative

Cell reference adjusts to new location Absolute

Cell reference does not change Mixed References

Row Absolute Column part adjusts, row part does not change

Column Absolute Row part adjusts, column part does not change

Math operations with multiple spreadsheets

Protection by a password Conditional formatting Functions countif and sumif Function If Function AND Using IF and AND in a formula Data tools

Guidelines for importing foreign data:◦ When possible, save data as tab-delimited

ASCII text in a file with a .txt extension;◦ When data comes from the Web, select a

browser that supports Copy/Paste of tagged tables;

◦ When the foreign data format is messed up, use a text editor with Search/Replace, apply placeholder technique, and write the revised data with .txt extension.