Do you prepare reports, keep a sales list or work with accounting spreadsheets? This article is for people who know Excel at a basic level but want to work faster and more neatly. The explanations of the 6 tools below are based on Microsoft's official support pages: for each tool, we describe what it does, give Microsoft's example and offer a practical tip.
Lookup functions: XLOOKUP and VLOOKUP
1. How does XLOOKUP work?
According to Microsoft, the XLOOKUP function is used to find data in a table or range by row. For example, you want to look up a product code in a long list and find the product name in the same row.
An important feature of this function: if no additional parameter is specified, XLOOKUP looks for an exact match, meaning it finds exactly the value you are looking for.
2. VLOOKUP: don't forget the key rule
For the VLOOKUP function to work correctly, the value you are looking up must be in the first column of the range. On the same page, Microsoft recommends trying the XLOOKUP function as an improved version that works in any direction. If your version of Excel has XLOOKUP, it is worth trying.
Conditional calculations: SUMIF and IF
3. SUMIF: add up only what you need
The SUMIF function sums only the values in a range that meet the condition you specify. Microsoft's example:
=SUMIF(B2:B25,">5")
This formula sums only the numbers greater than 5 in the range B2:B25. Note that in the example the condition (">5") is enclosed in quotation marks. Using the same logic, you can, for example, add up only payments above a certain amount.
4. IF: one result if the condition is true, another if it is false
The IF function makes a logical comparison between a value and the result you expect. So it can have two results: one if the condition is true, the other if it is false. Microsoft's example:
=IF(C2="Yes",1,2)
This formula returns 1 if the value in cell C2 is "Yes", and 2 otherwise. In the same way, you can, for example, show one of two different labels in a report based on the entry in a payment column.
Analyzing and organizing data: PivotTable and Flash Fill
5. PivotTable: summarizing a large table
A PivotTable is a powerful tool for calculating, summarizing and analyzing data. For example, you can use it to summarize a long sales list by product.
To get started:
- Check that the data is arranged in columns with a single header row.
- In the English interface, select Insert > PivotTable.
Arranging the data into neat columns in advance also makes the table easier for your colleagues to read.
6. Flash Fill: instant filling with Ctrl+E
Flash Fill automatically fills in data when Excel senses a pattern, i.e. a regularity. Microsoft's example: separating first and last names written together in one column. If it doesn't work automatically, you can start Flash Fill from the Data menu or with the Ctrl+E keys. It's a good habit to review the automatically filled result in a few rows.
Which tool is for what?
| Tool | What it's for |
|---|---|
| XLOOKUP | Finding data in a table by row (exact match by default) |
| VLOOKUP | Finding data when the lookup value is in the first column of the range |
| SUMIF | Adding up only the values that meet a condition |
| IF | Returning one of two results depending on a condition |
| PivotTable | Calculating, summarizing and analyzing data |
| Flash Fill | Automatically filling in data when a pattern is detected |
If you are just starting to learn Excel, begin with IF and SUMIF, then move on to the lookup functions, and after that to PivotTable. Trying each one out on your own real spreadsheet helps you remember it.
Summary
- XLOOKUP searches a table by row and finds an exact match if no additional parameter is specified; this function is not available in Excel 2016 and Excel 2019.
- For VLOOKUP, the lookup value must be in the first column of the range.
- SUMIF adds up only the values that meet a condition, while IF returns one of two results depending on a condition.
- For a PivotTable, the data must be arranged in columns with a single header row.
- If Flash Fill doesn't work automatically, start it with Ctrl+E.
At Estavor Academy, office software courses are held online only — in individual or group format. If you have a question about Excel, message us on WhatsApp: +994 77 754 10 12.
📚 Sources
🌐 The original article is in Azerbaijani; the official sources are available in Azerbaijani.
More articles
Language levels A1 to C2: what do they mean? (English and Russian)
What do A1, B2 and C1 mean? CEFR language levels and how the Russian TORFL (TRKI) levels correspond to them, for learners of English and Russian.
Accounting basics: assets, liabilities, equity, income and expenses
What are assets, liabilities, equity, income and expenses? Simple definitions based on the IFRS Conceptual Framework, examples and a short note on 1C.