25 RUMUS EXCEL PALING PENTING - LANGSUNG JAGO EXCEL!

Summary

The video teaches commonly used Excel formulas through practical examples involving employee sales, text cleanup, string manipulation, rounding, and data lookups. The presenter emphasizes understanding what each formula does and when to use it, rather than memorizing formulas alone. Viewers are encouraged to practice with the accompanying exercise file.

1. Basic calculations, conditions, and counting

• SUM — Adds values in a range, such as an employee’s sales across several months.
• AVERAGE — Calculates the mean of a range. The presenter also demonstrates using Excel’s decimal-place controls to display a rounded result.
• IF — Tests a condition and returns one value if it is true and another if it is false. For example, it can mark an employee as passing if their total sales exceed a threshold.
• RANK — Ranks a value against a range, such as ranking employees by total sales. Lock the comparison range with an absolute reference using F4 before filling the formula down.
• COUNT — Counts cells containing numbers.
• COUNTA — Counts non-empty cells, including cells containing text. It can be used to count names.
• MAX — Returns the largest value in a range.
• MIN — Returns the smallest value in a range.
• SUMIF — Adds values that meet a criterion, such as summing sales for employees of a particular gender. It uses a criteria range, a criterion, and a sum range.
• COUNTIF — Counts cells that meet a criterion, such as the number of employees marked “passed.”

2. Cleaning and changing text

The presenter uses intentionally messy names to demonstrate how to tidy data:

• TRIM — Removes extra spaces from text, including unnecessary spaces at the beginning or between words.
• LOWER — Converts text to lowercase.
• UPPER — Converts text to uppercase.
• PROPER — Capitalizes the first letter of each word.

3. Combining, measuring, and extracting text

• CONCATENATE — Joins text or values from multiple cells. Add quoted text between cell references to insert separators such as spaces or hyphens.
• LEN — Counts the characters in a cell. Spaces and punctuation count as characters too.
• LEFT — Extracts a specified number of characters from the start of text.
• MID — Extracts a specified number of characters beginning at a chosen position within text.
• RIGHT — Extracts a specified number of characters from the end of text.

4. Rounding numbers

The video distinguishes between rounding decimal values and rounding to a multiple:

• ROUNDDOWN — Rounds down to a specified number of decimal places, regardless of ordinary rounding rules.
• ROUNDUP — Rounds up to a specified number of decimal places.
• ROUND — Uses standard mathematical rounding: values at or above the halfway point round up, while those below it round down.
• FLOOR — Rounds down to a specified multiple, such as the previous multiple of 100.
• CEILING — Rounds up to a specified multiple.
• MROUND — Rounds to the nearest specified multiple.

5. Looking up information and handling errors

• VLOOKUP — Looks up a value in a vertically arranged reference table and returns information from a specified column. The lookup value must be in the table’s first column. The presenter uses an exact-match setting (FALSE or 0) and locks the reference range before copying the formula.
• HLOOKUP — Performs a similar lookup in a horizontally arranged table, returning information from a specified row. Use it when the reference data is arranged across columns rather than down rows.
• IFERROR — Returns a chosen alternative when a formula produces an error. For example, it can replace a lookup error caused by a missing order number with a readable message.

Main lesson

Choose a formula based on the task and the structure of the data. Use COUNT or COUNTA depending on whether you’re counting numbers or filled cells; use VLOOKUP or HLOOKUP depending on the orientation of the reference table; and select the appropriate rounding formula based on whether you need to round decimals or round to a multiple.

Speakers or sources featured

• Ignasius Ryan — The video’s presenter and Excel instructor. No other speakers or sources are featured in the subtitles.