Excel 2019 - Intro - 1

Click to access content:
TOPICS :
Flash Fill
Quick Analysis
Create Formula
Insert Function
MICROSOFT EXC
Conditional Tips & Trick
Formatting
Expenses E-Learning Edi
Part 1
INSTRUCTOR :
SOFT EXCEL 2019
MIMIE HARUN
ips & Tricks
-Learning Edition
Part 1
NSTRUCTOR :
MIMIE HARUN
MATHEMATICAL OPERATORS
Operator What it does
Basic arithmetic operators. Perform addition,
subtraction, multiplication & division
+ – * /
Power of operator. Raises something to the

^ power of other value.
To define precedence in calculations. Anything

included in paranthesis is calcuated first.
( )
To combine 2 text values

&
To divide with 100.
%
: Used to specify ranges
To lock a reference column or row or both
Used to structurally refer to columns in table
[ ]
Used to structurally refer to current row values

@ in a table
To specify an inline array of values

{ } ( curley bracket)
Comparison operators. Output will always be

boolean – ie TRUE or FALSE.
< > <= >=
Equality operators. Check whether 2 values are

equal or not equal. Output will TRUE or FALSE
= <>
Used as wild cards in certain formulas like
COUNTIF etc.
* ? (wild card, question marks)
SPACE Intersection operator. Returns the range at

intersection of 2 ranges
CAL OPERATORS
Example
2+3, 7-2, 9*12, 108/3, 2+3*4-2
2^3, 9^0.5, PI()^2, EXP(1)^0.5
(2+3)*(4+5) calcuates 2+3 first, then 4+5 and

multiplies both results.
50
“You are ” & “awesome” returns “You are awesome”
2/4% will give 50 as result. Note: (2/4)% will give

0.5% as result.
A1:B20 refers to the range from cell A1 to B20
$A$1 refers to cell A1 all the time. $A1 refers to column A,

relative row based on where you use it. For more refer to
absolute vs. relative references in Excel.
ourSales[month] refers to the month column in the ourSales

table. Works only in Excel 2007 or above. Know more about
Excel Tables.
ourSales[@month] refers to current row’s month value

in oursales table.
{1,2,3,4,5} – refers to a the list of values 1,2,3,4,5
2>3 will be FALSE. 99<101 will be TRUE.
2=2, “hello”=”hello”, 4<>5 will all return TRUE.

COUNTIF(A1:A10, “a*”) counts the values in range A1:A10
starting with a. For more on this refer to COUNTIF & SUMIF in
Excel
A1:C4 B2:D5 refers to the intersection or range A1:C4

and B2:D5 and returns B2:C4. Caution: The output will
be an array, so you must use it in another formula
which takes arrays, like SUM, COUNT etc.
Name Invoice Date Amount
Mimie 16-Jun-22 5000
Shazlin 16/06/2022 5,000
Ahmed 16th Jun 2022 RM5,000
Danish
Term Due Date data types
30 16-Jul-22 Text Number
30 Date
30 time
30 %
Currency/Acc
Fraction
196946019 Scientific
Currency/Acc
ORDER OF OPERATION
Note : Use parentheses in a formula to override the standard order of operations
Sample 1 Without Parentheses =7+8*3/2-4
15
Extra : Use Evaluate Formula
Sample 2 Include parentheses in the formula = (7+8)*(3/2)-4
18.5
Flash Fill your data
Start typing and let Excel finish your work for you
Email Ic No First Name

Nancy.Freehafer@fourthcoffee.com 780211-02-5556 Nancy
Andrew.Cencini@northwindtraders.com 800311-02-5557 Andrew
Jan.Kotas@litwareinc.com 680913-02-5558 Jan
Mariya.Sergienko@graphicdesigninstitute.com 700929-02-5559 Mariya
Steven.Thorpe@northwindtraders.com 900913-02-5560 Steven
Michael.Neipper@northwindtraders.com 650913-02-5561 Michael
Robert.Zare@northwindtraders.com 870913-11-5562 Robert
Laura.Giussani@adventure-works.com 950413-02-5563 Laura
Anne.HL@northwindtraders.com 990413-02-5564 Anne
Alexander.David@contoso.com 820417-02-5565 Alexander
Kim.Shane@northwindtraders.com 790413-02-5566 Kim
Manish.Chopra@northwindtraders.com 870413-02-5567 Manish
Gerwald.Oberleitner@northwindtraders.com 950413-02-5568 Gerwald
Amr.Zaki@northwindtraders.com 910413-02-5569 Amr
Yvnonne.McKay@northwindtraders.com 880419-02-5570 Yvnonne
Amanda.Pinto@northwindtraders.com 871104-02-5571 Amanda
Last Name FullName IC No.
Freehafer NANCYFREEHAFER 780211025556
Cencini ANDREWCENCINI 800311025557
Kotas JANKOTAS 680913025558
Sergienko MARIYASERGIENKO 700929025559
Thorpe STEVENTHORPE 900913025560
Neipper MICHAELNEIPPER 650913025561
Zare ROBERTZARE 870913115562
Giussani LAURAGIUSSANI 950413025563
HL ANNEHL 990413025564
David ALEXANDERDAVID 820417025565
Shane KIMSHANE 790413025566
Chopra MANISHCHOPRA 870413025567
Oberleitner GERWALDOBERLEITNER 950413025568
Zaki AMRZAKI 910413025569
McKay YVNONNEMCKAY 880419025570
Pinto AMANDAPINTO 871104025571
Analyze data with Quick Analysis
Select and play with your data using live previews
Company Industry Q1 Sales

A. Datum Corporation Tech $ 195,449
Adventure Works Travel $ 123,721
Blue Yonder Airlines Travel $ 934,763
City Power & Light Utilities $ 299,293
Coho Vineyard Beverage $ 228,783
Contoso, Ltd Misc $ 239,219
Contoso Pharmaceuticals Medical $ 371,570
Consolidated Messenger Tech $ 579,825
Fabrikam, Inc. Utilities $ 639,630
Fourth Coffee Beverage $ 876,740
Graphic Design Institute Education $ 788,390
Humongous Insurance Financial $ 682,545
Litware, Inc. Tech $ 810,776
Lucerne Publishing Misc $ 859,102
Margie's Travel Travel $ 907,427
alysis
Q2 Sales
$ 746,907
$ 733,396
$ 246,554
$ 674,295
$ 659,385
$ 287,989
$ 644,368
$ 448,399
$ 635,474
$ 567,216
$ 540,282
$ 577,599
$ 521,978
$ 515,592
$ 509,206
Next >
Next >

Excel 2019 - Intro - 1

Uploaded by

Copyright:

Available Formats

Excel 2019 - Intro - 1

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Excel 2019 - Intro - 1

Uploaded by

Copyright:

Available Formats

Click to access content:

Expenses E-Learning Edi

Power of operator. Raises something to the

To define precedence in calculations. Anything

To combine 2 text values

Used to structurally refer to columns in table

Used to structurally refer to current row values

To specify an inline array of values

Comparison operators. Output will always be

Equality operators. Check whether 2 values are

SPACE Intersection operator. Returns the range at

2^3, 9^0.5, PI()^2, EXP(1)^0.5

(2+3)*(4+5) calcuates 2+3 first, then 4+5 and

2/4% will give 50 as result. Note: (2/4)% will give

A1:B20 refers to the range from cell A1 to B20

$A$1 refers to cell A1 all the time. $A1 refers to column A,

ourSales[month] refers to the month column in the ourSales

ourSales[@month] refers to current row’s month value

{1,2,3,4,5} – refers to a the list of values 1,2,3,4,5

2>3 will be FALSE. 99<101 will be TRUE.

2=2, “hello”=”hello”, 4<>5 will all return TRUE.

A1:C4 B2:D5 refers to the intersection or range A1:C4

30 16-Jul-22 Text Number

Note : Use parentheses in a formula to override the standard order of operations

Sample 1 Without Parentheses =7+8*3/2-4

Sample 2 Include parentheses in the formula = (7+8)*(3/2)-4

Email Ic No First Name

Company Industry Q1 Sales

You might also like