Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Excel Formulas Cheat Sheet, Cheat Sheet of MS Microsoft Excel skills

In this cheat sheet you have all MS Microsoft Excel formulas you need to know: Simple formatting tricks, Conditional statements, Absolute cell references, Dealing with dates

Typology: Cheat Sheet

2019/2020
On special offer
30 Points
Discount

Limited-time offer


Uploaded on 10/09/2020

ryangosling
ryangosling 🇺🇸

4.8

(24)

250 documents

1 / 2

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Excel Formulas
Basic math
Function Formula Example
To add up the total =SUM(cell range) =SUM(B2:B9)
To add individual items =Value1 + Value 2 =B2+C2
Subtract =Value1 - Value 2 =B2-C2
Multiply =Value1 * Value2 =B2*C2
Divide =Value1 / Value2 =B2/C2
Exponents =Value1 ^ Value2 =B2^C2
Average =AVERAGE(cell range) =AVERAGE(B2:B9)
Median =MEDIAN(cell range) =MEDIAN(B2:B9)
Max =MAX(cell range) =MAX(B2:B9)
Min =MIN(cell range) =MIN(B2:B9)
Simple formatting tricks
Function Formula Example
To change a cell to proper
case =PROPER(cell) =PROPER(A2)
To change a cell to upper
case =UPPER(cell) =UPPER(A2)
To change a cell to lower
case =LOWER(cell) =LOWER(A2)
Conditional statements
Function Formula Example
If statement =IF(logical test, “result if
the test answer is true”,
“result if the test answer is
false”)
=IF(B2>69,”Pass”,”Fail”)
Exact =EXACT(Value1, value2) =EXACT(B2, C2)
Absolute cell references
When a formula contains an absolute reference, no matter which cell the formula
occupies the cell reference does not change: if you copy or move the formula, it refers
to the same cell as it did in its original location. In an absolute reference, each part of
the reference (the letter that refers to the row and the number that refers to the column)
is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever
the formula is copied or moved, it always refers to cell A1.
pf2
Discount

On special offer

Partial preview of the text

Download Excel Formulas Cheat Sheet and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!

Excel Formulas

Basic math

Function Formula Example To add up the total =SUM(cell range) =SUM(B2:B9) To add individual items =Value1 + Value 2 =B2+C Subtract =Value1 - Value 2 =B2-C Multiply =Value1 * Value2 =B2*C Divide =Value1 / Value2 =B2/C Exponents =Value1 ^ Value2 =B2^C Average =AVERAGE(cell range) =AVERAGE(B2:B9) Median =MEDIAN(cell range) =MEDIAN(B2:B9) Max =MAX(cell range) =MAX(B2:B9) Min =MIN(cell range) =MIN(B2:B9)

Simple formatting tricks

Function Formula Example To change a cell to proper case

=PROPER(cell) =PROPER(A2)

To change a cell to upper case

=UPPER(cell) =UPPER(A2)

To change a cell to lower case

=LOWER(cell) =LOWER(A2)

Conditional statements

Function Formula Example If statement =IF(logical test, “result if the test answer is true”, “result if the test answer is false”)

=IF(B2>69,”Pass”,”Fail”)

Exact =EXACT(Value1, value2) =EXACT(B2, C2)

Absolute cell references

When a formula contains an absolute reference, no matter which cell the formula occupies the cell reference does not change: if you copy or move the formula, it refers to the same cell as it did in its original location. In an absolute reference, each part of the reference (the letter that refers to the row and the number that refers to the column) is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever the formula is copied or moved, it always refers to cell A1.

Pulling things apart

Function Formula Example To select a certain number of characters from the left

=LEFT(cellwithtext, number of characters to be returned)

=LEFT(A2, 6)

To select a certain number of characters from the right

=RIGHT(cellwithtext, number of characters to be returned)

=RIGHT(A2, 6)

Find text in a field =SEARCH(“text you want to find”, where you want to find it)

=SEARCH(“,”, A2)

Extract information from the middle

=MID(cellwithtext, start position, number of characters you want returned)

=MID(A2, 9, 4)

Separate a last name (Example: Smith, Jane)

LEFT and SEARCH functions

=LEFT(A2, SEARCH(“,”,

A2)‐1)

Separate a first name (Example: Smith, Jane)

MID and SEARCH functions

=MID(A2, SEARCH(“,”,

A2)+2, 20)

Putting things together

Function Formula Example To combine cells with a space in-between

=CONCATENATE(text, “ ”, text)

=CONCATENATE(A2, “ “,

B2)

To combine cells with a space in-between (second option)

=text & “ “ & text =A2 & “ “ & B

Dealing with dates

Function Formula Example Return the year =YEAR(datefield) =YEAR(A2) Return the month =MONTH(datefield) =MONTH(A2) Return the day =DAY(datefield) =DAY(A2) Return the day of the week (1 = Sunday, 2 = Monday, 3 = Tuesday, etc.)

=WEEKDAY(datefield) =WEEKDAY(A2)

To create a date from year, month, and day

=DATE(year, month, day) =DATE(B2, C2, D2)