MS Excel Part II:
Basics Formulas
and Shortcuts
Prepared by:
Miguel Joie S. Polines
Gerami M. Benedicto
Instructor I
College of Engineering and
Information Technology
Spreadsheet Formulas
Formulas are equations that perform
calculations, transformations, or operations on data
within cells. They are written directly into a cell and
typically start with an equals sign (=), signaling
Excel to process the following characters as a
formula rather than plain text.
College of Engineering and
Information Technology
Key Components of Excel Formulas
Operators: Symbols that specify the type of
calculation to perform.
• + is addition
• - is subtraction
• * is multiplication
• / is division
• ^ is exponentiation
College of Engineering and
Information Technology
Call References
These references allow you to use data from
other cells within your formula. There are three
types:
1. Relative – changes based on the cell where the
formula is used (e.g., A1)
College of Engineering and
Information Technology
Call References
These references allow you to use data from
other cells within your formula. There are three
types:
2. Absolute – remains fixed when copied or
moved, marked with a $ (e.g., $A$1)
College of Engineering and
Information Technology
Call References
These references allow you to use data from
other cells within your formula. There are three
types:
3. Mixed – Partially absolute (e.g., $A1 or A$1)
College of Engineering and
Information Technology
Basic Functions
1. SUM: Adds up a range of numbers
Example: =SUM(A1:A5)
What it does: Adds up all values in the range
specified, such as cells A1 through A5.
Usage: Useful for calculating total expenses, total
sales, or any cumulative figures.
College of Engineering and
Information Technology
Basic Functions
2. AVERAGE: Calculates the average of a range
Example: =AVERAGE(A1:A5)
What it does: Calculates the average (mean) of
the values in the specified range.
Usage: Helpful for finding the average revenue
over a period or the average score in a test.
College of Engineering and
Information Technology
Basic Functions
3. MIN/MAX: Finds the smallest or largest value
Example: =MIN(A1:A5)
Example: =MAX(A1:A5)
What it does: MIN finds the smallest value in a
range, while MAX finds the largest.
Usage: Useful for identifying the lowest and highest
amounts
College in sales
of Engineering and or expenses.
Information Technology
Basic Functions
4. IF: Evaluate a condition and returns a value if
true or false
Example: =IF(A1>10, “High”, “Low”)
What it does: Allows you to make logical
comparisons and perform action based on whether
a condition is true or false.
College of Engineering and
Information Technology
Basic Functions
5. COUNT
Example: =COUNT(A1:A10)
What it does: Counts the number of cells
containing numbers within a range.
Usage: Handy for counting the number of
transactions or items in a list that contain numerical
values.
College of Engineering and
Information Technology
Basic Functions
6. COUNTA
Example: =COUNTA(A1:A10)
What it does: Counts the number of cells that are
not empty (useful for both text and numbers).
Usage: Useful for counting filled entries, such as
counting the total clients, transactions or filled cells
in a ofdatabase.
College Engineering and
Information Technology
Basic Functions
7. ROUND
Example: =ROUND(A1, 2)
What it does: Rounds the value in A1 to two
decimal places.
Usage: Essential in financial data for rounding
currency values to two decimal places.
College of Engineering and
Information Technology
Basic Functions
8. NOW and TODAY
Example: =NOW() or =TODAY()
What it does: NOW() returns the current date and
time, while TODAY() returns only the date.
Usage: Great for adding timestamps or tracking
current dates in records, useful for deadlines or
document
College logging.
of Engineering and
Information Technology
Basic Functions
9. CONCATENATE
Example: =CONCATENATE(A1, “ ”, A5)
What it does: Combines values from multiple cells
into one.
Usage: Useful for creating full names from first and
last names or combining data fields.
College of Engineering and
Information Technology
Basic Functions
10. TRIM
Example: =TRIM(A1)
What it does: Removes extra spaces from text in a
cell, which can help in cleaning data for
consistency.
Usage: Important for cleaning up data imported
fromof other
College sources
Engineering and with unnecessary spaces.
Information Technology
Basic Functions
11. IFERROR
Example: =IFERROR(A1/A2, “Error”)
What it does: If an error occurs in the formula (like
dividing by zero), this function returns a specific
value, like “Error”, instead of displaying an error
message.
College of Engineering and
Information Technology
Navigation Shortcuts (Windows)
1. TAB – Move to the next cell
2. SHIFT + TAB – Move to the previous cell
3. CTRL + ARROW KEYS – Move to the edge of
data region
4. HOME (FN + left arrow) – Go to the beginning
of a row
College of Engineering and
Information Technology
Selection and Editing Shortcuts
1. SHIFT + SPACE – Select entire row
2. CTRL + SPACE – Select entire column
3. CTRL + A – Select the entire worksheet
4. SHIFT + ARROW KEYS – Select a range of cells
5. CTRL + D – Fill down
6. CTRL + R – Fill right
College of Engineering and
Information Technology
Formatting Shortcuts
1. CTRL + B – Bold
2. CTRL + I – Italic
3. CTRL + U – Underline
4. CTRL + 1 – Open format cells dialog box
5. ALT + H, O, I – Autofit column width
College of Engineering and
Information Technology
Data Management Shortcuts
1. CTRL + SHIFT + + – Insert a new row
2. CTRL + - – Delete selected row/column
3. ALT + D + S – Open sort dialog box
4. CTRL + SHIFT + L – Filter data
College of Engineering and
Information Technology
Worksheet Shortcuts
1. CTRL + Page Up / Page Down – Switch between
worksheets
2. SHIFT + F11 – Insert a new worksheet
3. ALT + H + O + R – Rename the active worksheet
4. CTRL + Z – Undo
5. CTRL + Y – Redo
6. CTRL + S – Save workbook
College of Engineering and
Information Technology