MICROSOFT EXCEL

Lecture 1

INTRODUCTION
DATA TYPES , FORMATS, OPERATIONS, AUTOFILL

Assoc Prof. PhD. Vasile Lucian Boiculese

INTRODUCTION
• Microsoft Excel (full name Microsoft Office Excel) is a
spreadsheet application written and distributed by Microsoft
for Windows and Mac OS X. It features calculation, graphing
tools, pivot tables and a macro programming language
called VBA (Visual Basic for Applications). It has been the
most widely used spreadsheet application available for
these platforms since version 5 in 1993. Excel is part of
Microsoft Office.
EXCEL – HISTORY
1982 Microsoft Multiplan on CP/M system but on DOS operating system it was Lotus 1-2-3.
1985 Excel for MAC
1987 Excel 2.0 for Windows – In 1988 MS Excel had started to outsell Lotus 1-2-3 and become the leader on spreadsheet
computation application.
1990 Excel 3.0
1992 Excel 4.0
1993 Excel 5.0 (Office 4.2 & 4.3, also a 32-bit version for Windows NT only on the PowerPC, Alpha, and MIPS architectures)
1995 Excel for Windows 95 (version 7.0) - included in Office 95 (multitasking)
1997 Excel 97 - included in Office 97 (for x86 and Alpha).
1999 Excel 2000 (version 9.0)
2001 Excel 2002 (version 10)
2003 Excel 2003 (version 11)
2007 Excel 2007 (version 12)
2010 Excel 2010 (version 14)
2013 Excel 2013 (version 15)
2016 Excel2016 (version 16)

Note: No MS-DOS version of Excel 1.0 ever existed: the Windows version originated at the time the Mac version was up to 2.0.
There is no Excel 6.0, because the Windows 95 version was launched with Word 7and Excel 7 (Office 7).
Spreadsheet applications use data that are presented as tables. The information is
organized in rows and columns. Table elements are called cells and are uniquely
determined by column and line identifiers (like a chess table).

Excel application use the spreadsheet or sheet concept for table of data. This is a large
table where each cell can contain numerical information, logical data, calendar dates,
string and mathematical formulas that may have data ​as arguments or references to other
cells.

An Excel file may contain several spreadsheets.

A spreadsheet application has a library of mathematical, statistical and economic functions
that minimizes users effort in creating formulas for computation. To complete the list of
items required in the area, this kind of programs has various facilities for charting (creating
graphics) in a concise and user friendly style.
Also in Excel one may create modules with VBA – Visual Basic for Applications.
Spreadsheet features:

• Allows for the creation of computational formula in an easy manner, having regard to the aid libraries of
predefined functions (mathematics, statistical, text, engineering, databases).
• Programing modules with VBA.
• It contains graphic facilities. The charts are automatically updated to the values of the cells that are defined as
arguments.
• Data dedicated techniques : pivot tables, filters, enable aggregates (total or subtotal), building series of data,
automatic generation of data( autofill), sort the data.
• Word processing facilities.

Dimensions:

A spreadsheet can hold up to 256 columns in Excel 2003 respectively 16384 in Excel 2007 and 2010. By default
these are denoted as follows: A, B, C, ..., Z, AA, AB, ..., AZ, BA ... ... ... IA ... .. IV (XFD ). The maximum number
of rows is 65,536 in Excel 2003 (1,048,576 in Excel 2007, 2010). A cell can hold up to 32,000 characters
(alphabetic and/or numeric).
A spreadsheet file contains one or more tables, which are implicitly called Sheet1, Sheet2, .... The user can
rename the spreadsheet by displaying the context menu (right mouse click) and choose rename.
 Name box
EXCEL 2007 / 2010 Formula bar Column heading
Menu bar

Ribbon

Select all
Active cell

Scroll bar
Row
Headings

Worksheet tab Status bar

Microsoft has motivated this change in settlement icons (menu

redefined , layout change) by increasing productivity.
SHAPES (FORMS) OF MOUSE POINTER
– the actions that one can do depends on the shape of mouse pointer.
Pointer Indicator Description
White cross-shaped Occurs when hover over spreadsheet cells. In this case, the
(Thick plus shape) desired cells can be selected.

Black cross-shaped Appears by touching the bottom right corner of a selection. It is

(Thin plus shape) used to fill the destination cells.
First select the source cells and then drag and drop to fill the
destination (works for both text and formulas)
 Arrow shaped When pointing on the menu bar, or tools bar. It is used to launch
in execution a command.
I I shaped Occurs when positioning on formula bar or editing a cell (double
click on a cell).
Double arrow shaped Column/Row Resize.
indicator (vertical / Shall be used for resizing (direction of arrow) the column width
or : horizontal) or Black line or row height. Appears when the mouse pointer hover over the line
with arrows pointing left on the column or row headings.
and right…
Diagonal double arrow Indicates the possibility to resize the currently selected object.

↓ or Vertical arrow Select entire column respectively select entire row. Only appears
→ Horizontal arrow when the mouse is over a column or row heading.
Pointer Indicator Description
Mouse arrow with a Occurs when hover over black edges of a selection. It is used
compass shape to move contents of a cell or range of cells.
incorporated.

Normal sheet pointer

Fill pointer

Move pointer

Sizing pointer

Select column/row pointer

Moving within a worksheet

There are situations where the quantity of data exceeds the active window of Excel. To view any subdomain of
the spreadsheet one may use the following methods:
Arrow keys move the selection on the cell in the up / down / right / left direction (the arrow that is used) passing
through each cell.
Another way is to drag on the vertical or horizontal scrollbars.
PgDn / PgUp allow going to the beginning of the document respectively to the end of the document by one page.
Alt + PgDn / PgUp moves to the right or left the displays window by one page.
Ctrl + Home / Ctrl + End positions at the start respectively at the end of the spreadsheet.
Ctrl + arrow means going to the edges of the spreadsheet.
Ctrl + PgUp / Ctrl + PgDn jump to the next worksheet (left / right).

Also for positioning in the spreadsheet can be used the GoTo method which is on the Home+Find&Select Excel
2010 (Edit menu for Excel 2003), or you can type Ctrl + G. On the reference text box (reference required) one
should enter the destination for column and row cell. For example we will place the A200 to go on column A row
200.
DATA TYPES

Definition:
The information we have is actually a data. There are different kinds of information thus data types can be:
numerical, alphanumerical (character), dates/time and logical (true/false). Programming languages accept
different data types that must be defined accordingly.
Each type of date works with specific operators.

Data is actually an abstract form of

working with real information.
The General format is the default number format that Excel applies when you type a number. For the most part,
numbers that are formatted with the General format are displayed just the way that you type them. However, if
the cell is not wide enough to show the entire number, the General format rounds numbers that have decimals.
The General number format also uses scientific (exponential) notation for large numbers (12 or more digits).
Ex: 1234567890123450000 will be written 1.23457E+18 (scientific notation).
E+18 means to multiply by 1018.

Number type is the most used data format.

Microsoft Office Excel provides many built-in number formats,
but if these do not meet your needs, you can customize a built-
in number format to create your own.
On their original condition as default, numbers are right aligned
and text is left aligned. You can change if you need the
alignment type.

Maximum number accepted: 1.79769313486232E+308 (14

decimals precision).
If you want to display numbers as monetary values in Excel 2007/2010, you must format them as currency. To do
this, you apply either the Currency or Accounting number format to the cells that you want to format.
The number formatting options are available on the Home tab, in the Number group.

Obs: The separation between integers and

decimals is default defined by “.” (dot, period).
Also “,” (comma) is utilized, but you must setup
this option.

When you apply the Currency format to a number,

the currency symbol appears right next to the first
digit in the cell. You can specify the number of
decimal places that you want to use, whether you
want to use a thousands separator, and how you
want to display negative numbers.
Accounting
Like the Currency format, the Accounting format is used for monetary values. However, this format aligns the
currency symbols and decimal points of numbers in a column. In addition, the Accounting format displays zeros as
dashes and negative numbers in parentheses. Like the Currency format, you can specify how many decimal places
you want and whether to use a thousands separator. However, you cannot change the default display of negative
numbers unless you create a custom number format.

Tip To quickly apply the Accounting format, select the cell or range of cells you want to format. On the Home
tab, in the Number group, click Accounting Number Format . If you want to show a currency symbol other than
the default, click the arrow next to the Accounting Number Format button and then select another currency
symbol.
When a date or time is typed in a cell, it appears in a default
date and time format. The default date and time format is
based on the regional date and time settings that are
specified in Control Panel, and changes when changes are
made to those settings. You can display numbers in several
other date and time formats, most of which are not affected
by Control Panel settings.
Obs.
If you entered a date or time then that cell will accept only
this type unless you define otherwise.

Note Date and time formats that begin with an asterisk (*)
respond to changes in regional date and time settings that are
specified in Control Panel. Formats without an asterisk are not
affected by Control Panel settings.
This will take a negative effect on some old Excel versions in
exporting/importing the data.
If you apply the Percentage format to existing numbers in a
workbook, Excel multiplies those numbers by 100 to convert
them to percentages. For example, if a cell contains the
number 10, Excel multiplies that number by 100, which means
that you will see 1000.00% after you apply the Percentage
format. This may not be what you expected.
To accurately display percentages, before you format the
numbers as a percentage, make sure that they have been
calculated as percentages, and that they are displayed in
decimal format. Percentages are calculated by using the
equation amount / total = percentage.
For example, if a cell contains the formula =10/100, the
result of that calculation is 0.1. If you then format 0.1 as a
percentage, the number will be correctly displayed as 10%.
Use the Fraction format to display or type numbers
as actual fractions, rather than decimals.

After you apply a fraction format to a cell, decimal

numbers as well as actual fractions that you type in
that cell will be displayed as a fraction. For
example, typing .5 or 1/2 results in 1/2 when the
cell has been formatted with a fraction type of Up
to one digit.

If no fraction format is applied to a cell, and you

type a fraction such as 1/2, it will be formatted as
a date. To display it as a fraction, apply a Fraction
format, and then retype the fraction.
E+2 means to multiply the number by 10^2.

1000 1.00E+03

120000000 1.20E+08

12000.67 1.20E+04

text: text cell 123

number: 123
date: 3/25/2010
Text cell are treated as text even if a number is in
that cell. The text cell alignment differ from
number format.
Guidelines for using decimal places

This digit placeholder displays insignificant zeros if a number has

0
fewer digits than there are zeros in the format. For example, if you
(zero)
type 8.9, and you want it to be displayed as 8.90, use the format #.00.

This digit placeholder follows the same rules as the 0 (zero).

#
However, Excel does not display extra zeros when the number that you
(hash or
type has fewer digits on either side of the decimal than there are #
number
symbols in the format. For example, if the custom format is #.##, and
sign)
you type 8.9 in the cell, the number 8.9 is displayed.

This digit placeholder follows the same rules as the 0 (zero).

? However, Excel adds a space for insignificant zeros on either side of the
(question decimal point so that decimal points are aligned in the column. For
mark) example, the custom format 0.0? aligns the decimal points for the
numbers 8.9 and 88.99 in a column.
.
This digit placeholder displays the decimal point in a number.
(period)
 Result Format
12300.00123 general
12300 custom: 0
12300.00 custom: 0.00
12300. custom: #.##
12300.001 custom: #.###
12,300.00 custom: #,##0.00
12.00 custom: #,##0.00

-12300.00123 general
(12,300) custom: #,##0;[Red](#,##0)
12300.00123 general
12,300 custom: #,##0;[Red](#,##0)

A number format can have up to four sections of code, separated by semicolons. These code sections define the
format for positive numbers, negative numbers, zero values, and text, in that order:
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
For example, you can use these code sections to create the following custom format:
[Blue]#,##0.00;[Red](#,##0.00);0.00;"sales "@; @ means to concatenate with the input text.
Result: 0.00 12.34 (12.34) sales Hello !
Example of custom format
Calculation operators
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft
Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Types of operators
Arithmetic operators To perform basic mathematical operations such as addition, subtraction, or
multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator Meaning (Example)

+ (plus sign) Addition (3+3)
Subtraction (3–1)
– (minus sign)
Negation (–1)
* (asterisk) Multiplication (3*3)
/ (forward slash) Division (3/3)
% (percent sign) Percent (20%)
^ (caret) Exponentiation (3^2)
Comparison operators You can compare two values with the following operators. When two values
are compared by using these operators, the result is a logical value either TRUE or FALSE.
In a formula if there is the option TRUE/FALSE one can use 1 as an alternative for TRUE respectively 0
for FALSE.

Comparison operator Meaning (Example)

= (equal sign) Equal to (A1=B1)
> (greater than sign) Greater than (A1>B1)
< (less than sign) Less than (A1<B1)
>= (greater than or equal to
Greater than or equal to (A1>=B1)
sign)
<= (less than or equal to sign) Less than or equal to (A1<=B1)
<> (not equal to sign) Not equal to (A1<>B1)

Formula

Result
 Text concatenation operator Use the ampersand (&) to join, or concatenate,
one or more text strings to produce a single piece of text

Text operator Meaning (Example)

Connects, or concatenates, two values to produce one
& (ampersand)
continuous text value ("North“ & "wind")

Reference operators Combine ranges of cells for calculations with

the following symbols or signs.
Reference
Meaning (Example)
operator
Range operator, which produces one reference to all the cells
:
between two references, including the two references e.g.:
(colon)
(B5:B15)
, Union operator, which combines multiple references into one
(comma) reference e.g.: SUM(B5:B15,D5:D15)
Intersection operator, which produces on reference to cells
(space)
common to the two references e.g.: (B7:D7 C6:C8)
 Formula
Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells
Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the
operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a
specific order for each operator in the formula.
The precedence of operators
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following
table. If a formula contains operators with the same precedence— for example, if a formula contains both a multiplication
and division operator— Excel evaluates the operators from left to right.
Operator Description
: (colon) maximum priority
(single space) Reference operators
, (comma)
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison minimum priority
Use of parentheses

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example,
the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2
by 3 and then adds 5 to the result.
=5+2*3

In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by
3 to produce 21.
=(5+2)*3

In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and
then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)
LOGICAL OPERATORS - AND
Returns TRUE if all its arguments are TRUE and FALSE if one or more argument is FALSE.
Syntax
AND(logical1,logical2, ...)
Logical1, logical2, ... are 1 to 255 conditions you want to test that can be either TRUE or FALSE.
Remarks
• The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays or references
that contain logical values.
• If an array or reference argument contains text or empty cells, those values are ignored.
• If the specified range contains no logical values, AND returns the #VALUE! error value.

A B
Formula Description (Result)
=AND(TRUE, TRUE) All arguments are TRUE (TRUE)
=AND(TRUE, FALSE) One argument is FALSE (FALSE)
=AND(2+2=4, 2+3=5) All arguments evaluate to TRUE (TRUE)
 A B
1 Data
2 50
3 104
4 Formula Description (Result)

5 =AND(1<A2, A2<100) Because 50 is between 1 and 100 (TRUE)

6 A B

=IF(AND(1<A2,
Displays the first number above, if it is
A2<100), A2, "The
7 between 1 and 100, otherwise displays a
value is out of
message (50)
range.")
OR
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax
OR(logical1,logical2,...)
Logical1,logical2,... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
Remarks
•The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical
values.
•If an array or reference argument contains text or empty cells, those values are ignored.
•If the specified range contains no logical values, OR returns the #VALUE! error value.
•You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press
CTRL+SHIFT+ENTER.

A B
1 Formula Description (Result)
2 =OR(TRUE) One argument is TRUE (TRUE)
All arguments evaluate to FALSE
3 =OR(1+1=1,2+2=5)
(FALSE)
4 =OR(TRUE,FALSE,TRUE) At least one argument is TRUE (TRUE)
NOT

Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one
particular value.
Syntax
NOT(logical)
Logical is a value or expression that can be evaluated to TRUE or FALSE.
Remark
If logical is FALSE, NOT returns TRUE; if logical is TRUE, NOT returns FALSE.

A B
Formula Description (Result)
=NOT(FALSE) Reverses FALSE (TRUE)
=NOT(1+1=2) Reverses an equation that evaluates to TRUE (FALSE)
AutoFill
It is used to fill cells with data derived from information contained in the source cells. Suppose we have to
fulfill the column labeled, “reference number” of a table of 100 patients. It is a simple but very tedious
operation by doing manually step by step.

Method of operation:
Enter in the first two cells the starting values ​of the series. In our case type the values ​1 and 2 in cells A1 or
A2.
Select the two cells.
Position the mouse in the lower right corner of the selection (the mouse pointer turns in thin black plus
shape).
Click and drag thru the destination. Destination cells can be only along the row otherwise along the column
(as the first two cells define this). In our example one will drag from cell A2 to cell A100. Thus, the
computer will automatically fill by counting down with numbers 3, 4 … and reaching 100.

If you select a single cell then the command becomes a simple copy on line or column.

Next a table with the data entered using the AutoFill method is presented.
Examples using the AutoFill
Source data AutoFill

1,2 3, 4, 5, 6, 7, …

5, 15 25, 35, 45, …

a1, a2 a3, a4, a5, a6, …

q1, q2 q3, q4, q5, …

21:00, 22:00 (time data) 23:00, 0:00, 1:00, …

Monday, Tuesday Wednesday, Thursday,Friday,…

10/10/2000, 10/11/2000 10/12/2000, 10/13/2000,… (calendar data

in format: mm/dd/yy)

5, 4.5 4, 3.5, 3, 2.5, 2, 1.5, 1, 0.5, 0, -0.5, …..

(decrease with 0.5)
test test, test, test, … (copy)
(o cell)
 Nonconsecutive cells

The Ctrl key will be used for non-contiguous cell selection. First the source cells will be selected then with CTRL
key hold down select the nonconsecutive cells destination.
Next from the Edit menu choose the option: Fill then Serie. Linear type should be selected and the step value
should be inserted. Next click on the OK button.
The following example was made in the manner presented.

1 2 3 4 5 6 7

We started with the values ​1 and 2 then from 3 to 7 the values were automatically filled by Fill and Series.

Excel 2007/ 2010 :

Home + Editing + Fill

33
 Power type series

AutoFill function works only with linear series. If we want to fill some cells with values ​of power
series we have to proceed as follows:

 Type in the first two cells the desired values for the series you want.
 Select the cells and holding down the Ctrl key define the destination. Destination cells may be
non-consecutive but must be selected on the same line otherwise in the same column defined by
the first two cells.
 From the Edit group of commends choose the option Fill then Series.
 Here we have to activate the Growth radio button then insert in the Step text box the desired
value (to define the series growth). As a facility one can enter a maximum numerical value to stop
uploading the series.
 The trend option helps to determine the series step automatically.
Below is a power series with ratio 2 completed by this method.

3 6 12 24 48 96 192 384
Examples
 Autofill – does not work for alphabet characters…..
A
B
A
B
A

There are two functions:

CHAR(number) => CHAR(65) will display A

Reverse function for CHAR is CODE(char)

CODE(“A”) will display 65 which is the ASCII

code for A
Custom lists

If working often with a certain type of data that must be repeated, it may be possible to make custom lists -
defined by the user.

One should follow the next steps to create a custom list (Excel 2003):

 From the Tools menu choose Options.

 Click on Custom Lists.
 Choose next New Lists then List Entries.
 Insert the new list by pushing enter after each element.

Another way is to create the new list in a spreadsheet (on a line or column) and then import into logical page of
Custom Lists.
Excel 2007:
Click on Microsoft Office Button , then Excel Options.
Click on Popular, apoi Top options for working with Excel, click Edit Custom Lists.

Excel 2010: File + Options + Advanced + General + Edit Custom Lists…

 Hands-on Excel

Practical examples:

- Body Mass Index

- Weighted average

Weight ( Kg )
BMI 
Height (m) 2
1.3  Weight ( Kg )
Adjusted formula : BMI 
Height (m) 2.5