Most people start learning Excel in the same way: open a blank sheet, then get confused about what to type. In fact, daily office work revolves around very few functions. Just six or seven, the rest are variations and combinations of those.
What is rarely mentioned in tutorials: the two most commonly taught functions have been replaced by Microsoft with newer ones, and a single formatting error can cause a correct formula to give a wrong answer without any error message. This last part is usually only discovered after the report has already been sent.
Six functions that cover most daily tasks
SUM adds up a range, written as =SUM(B2:B50). AVERAGE calculates the average in the same pattern. These two rarely cause problems.
COUNT and COUNTA are often confused, which can be troublesome. COUNT only counts cells containing numbers. Used on a column of employee names, it returns zero, not an error, making it seem like the data is indeed empty. What you need there is COUNTA, which counts all non-empty cells.
MAX and MIN find the largest and smallest values. IF compares something with a condition, for example =IF(C2>=75;"Pass";"Fail"). Note the semicolon there. If your computer's regional settings are set to Indonesia, the argument separator is a semicolon, whereas almost all English-language tutorials write it as a comma. A formula copied raw from the internet fails because of this trivial issue, and the error message does not explain anything.
Once IF feels comfortable, SUMIF and COUNTIF are the next steps that quickly prove useful: summing or counting only rows that meet certain criteria.
VLOOKUP or XLOOKUP, depending on your Office version
XLOOKUP is simpler than VLOOKUP. It does not require a column number, can search to the left, and does not break when a column is inserted in the middle of the table.
But don’t rush to discard VLOOKUP. Microsoft documentation states that XLOOKUP is not available in Excel 2016 and Excel 2019, and those two versions are still widely used in offices that have not updated their licenses. If the file will be opened by someone else, VLOOKUP is actually the safer choice. Check your Office version first through the File menu and then Account before making a selection.
Combining text: CONCATENATE is no longer the first choice
Many tutorials still teach CONCATENATE. Microsoft itself has replaced it with CONCAT since Excel 2016, stating in its documentation that CONCATENATE is only retained for compatibility and may not be available in future versions of Excel.
To combine names or addresses, TEXTJOIN is usually more practical because it can insert a separator while skipping empty cells, so there are no dangling commas at the end. And for simply joining two cells, the operator & is sufficient: =A2&" "&B2.
The formula is correct, the answer is wrong
This is the most costly and least taught part. Excel stores numbers with a precision of 15 digits, while NIK has 16. Pasting NIK as a number silently changes the last digit to zero, and once the file is saved, Undo cannot revert it. The same issue affects NPWP, NITKU, account numbers, and tracking numbers. A complete discussion is available at Excel's 15-digit limit and why NIK changes to zero.
The second common cause that often makes VLOOKUP return #N/A even though the data is clearly present: non-breaking spaces that are carried over when the table is copied from a web page. At a glance, they look identical to regular spaces, but TRIM does not clean them, as TRIM only recognizes regular spaces. If two texts appear exactly the same but never match, suspect that first.
A sensible learning order is roughly as follows: master the six functions above until you don’t have to think, then one lookup function, followed by one way to combine text. After that, take time to understand how Excel treats long numbers and copied text. This last part is what distinguishes a neat report from one that needs to be redone from scratch.
References
- CONCATENATE function, Microsoft Support – https://support.microsoft.com/en-us/office/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d
- XLOOKUP function, Microsoft Support – https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
- TEXTJOIN function, Microsoft Support – https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c