Excel Formulas Cheat Sheet: Syntax, Examples and Practice Exercises
The formulas that Excel tests and everyday work actually use, in six groups: basics, logic, lookups, text, dates and dynamic arrays. Each row gives the syntax, the case it solves and a small example; where a graded exercise trains the formula, the row links to it. Examples use the same column letters throughout: A is a label, B a number or date, C a second criterion.
Fragen zu Excel-Formeln
What are the most used Excel formulas?
SUM, AVERAGE, COUNT and COUNTA; IF; COUNTIF and SUMIF with their plural forms COUNTIFS and SUMIFS; VLOOKUP or XLOOKUP; INDEX with MATCH; CONCAT or &; TRIM; TODAY; and, in current Excel, FILTER and UNIQUE. These are also the ones hiring tests name most often.
What is the difference between a formula and a function?
A formula is anything that starts with = and calculates something, such as =B2*1.2. A function is a named operation inside a formula, such as SUM or IF. Every function call is a formula; not every formula uses a function.
What does $ mean in an Excel formula?
It locks part of a reference so it does not move when the formula is copied. $B$1 always points at B1; B$1 lets the column change but not the row; $B1 the opposite. Press F4 while editing a reference to cycle through the four forms.
Should I learn VLOOKUP or XLOOKUP?
Both. XLOOKUP is simpler and looks in any direction, but it needs Excel 2021 or 365, and many tests and shared files still use VLOOKUP. INDEX with MATCH is the version-proof alternative that interviewers also recognise.
Where can I practise these formulas?
Most rows link to a graded exercise or its topic page. The exercises open a real spreadsheet with a realistic dataset, check your result and your formula, and explain the fix. Free, no account.
Probieren Sie es in einer echten Tabelle aus
Kostenlos, ohne Konto und ohne Installation.
Excel ist eine Marke der Microsoft-Unternehmensgruppe. Indeed, TestGorilla, SHL, iMocha, Adaface, Criteria und andere Marken sind Eigentum ihrer jeweiligen Inhaber. GridBelt ist unabhängig, steht mit keinem dieser Unternehmen in Verbindung und wird von keinem von ihnen unterstützt oder gesponsert.