Excel is defined as a spreadsheet application to manage data.
Excel organizes information itno rows and columns.
The intersection of a row and a column forms a cell.
A cell in excel holds text, numbers and formulas.
In excel basic formatting is essential as it makes data clear and professional.
Excel Formulas and Basic Calculations.
They are used for basic math and cell referencing.
A formula is an expression that performs calculations.
Arithmetic Operators
- Add (+)
- Substract (-)
- Multiply (*)
- Divide (/)
- Exponent (^)
Excel Functions
They are crucial and are used as they are fast as well as perform accurate calculations.
BASIC FUNCTIONS
| FUNCTION | SYNTAX |
|---|---|
| SUM | Adds a range of numbers. SUM(range) |
| SUMIF | Adds values if a condition is met. SUMIF(Criteria range, criteria, sum range) |
| AVERAGE | Returns mean of a range. AVERAGE(range) |
| AVERAGEIF | Gets the average values that meet a condition. AVERAGEIF(criteria range, criteria, Average range) |
| MIN | Returns the smallest value. MIN(range) |
| MAX | Returns the largest value. MAX(range) |
| COUNT | how many numbers. COUNT(range) |
| COUNTIF | how many cells meet a specific criteria. COUNTIF(range, criteria) |
LOGICAL FUNCTIONS
The logical functions allow excel to check if certain conditions are TRUE or FALSE and return customized results based on thes outcomes.
They are IF, AND, OR.
IF FUNCTION
It returns one value if a condition is TRUE and another if FALSE.
Syntax
=IF(K2>=20, "Elligible", "Not Elligible")
In the above scenario k2 houses the performance score and we are checking whether the employee is elligible for a bonus
AND FUNCTION
It returns true if only all conditions are met.
Syntax
=IF(AND(condition1,condition2),"Yes","No")
Tests multiple True conditions.
OR FUNCTION
Returns TRUE if any condition is met.
Syntax
=IF(OR(condition1,condition2),"Yes","NO")
TEXT FUNCTIONS
They are used to clean, format and combine related text data.
They help generate structured outputs and standardize reporting for dashboards
| FUNCTION | Description | SYNTAX |
|---|---|---|
| CONCAT | Combines multiple text pieces into one string | =CONCAT(A2," ",D2) |
| LEFT AND RIGHT | Left returns the first n charachters. Right returns the last n characters | LEFT(B2,n) Right(B2,n) |
| LEN FUNCTION | Returns total number in a cell inclusive of spaces | LEN(B2) |
| UPPER, LOWER, PROPER | UPPER: Returns all caps. LOWER: Returns all lower case. PROPER: The firts letter is capitalized | UPPER(B2) LOWER(B2) =PROPER(CONCAT(A2," ",B2))
|
| TRIM | Removes the extra spaces from text except single spaces between words | TRIM(B2) =PROPER(TRIM(CONCAT(A2," ",B2)))
|
DATE AND TIME FUNCTIONS
They handle dates intelligently
TODAY Function: Returns current date based on your system clock
=TODAY()
Can be used in formulas to compare dates
=Year(TODAY()) - Year(G2)
In the above scenario we are calculating the years of service for an employee assumning that G2 contains the hire date.
Flag employees with more than 10 years in a company.
=IF((Year(TODAY()) - Year(G2)) >9, "Vetearan"", "Rookie")
YEAR,MONTH,DAY FUNCTIONS
- YEAR(G2) : Extracts the year
- MONTH(G2) : Extracts the Month
- DAY(G2) : Extracts the day
G2 in this case houses the hire date.
Flag employees hired before 2010
*=IF(YEAR(G2)<2010,"Long-Term","Recent">)*
DATEDIF FUNCTION
Gets the difference betweeb two dates in years, months, days
= DATEDIF(startdate, enddate,unit)
UNITS
- Y : Full Years
- M : Full Months
- D : Full Days
- YM : Months Ignoring Years
- YD : Days Ignoring Years
Get the full years since an employee was hired.
= DATEDIF(G2,TODAY(),"Y")
If we want to get the months or days we replace the unit section with the required unit.
NETWORK DAYS FUNCTION
Calculates the working days between two dates excluding weekends and optionally holidays.
Get The Days Between Hire and Today
=NETWORKDAYS(G2, TODAY())
Get the working days between two fixed dates
=NETWORKDAYS(DATE(2022,1,1), TODAY())
Top comments (0)