Excel-2 is a guide to the most popular Excel formulas and functions. Here you'll find practical tips, instructions, and illustrations about working with basic functions, Named Ranges, and Paste Special.
📚 Goal: Help you understand and use the most common Excel features efficiently—perfect for beginners and intermediate users!
- COUNT
- SUM
- IF
- AVERAGE
- COUNTIF
- SUMIF
- VLOOKUP
- MIN and MAX
- SUMPRODUCT
- MOD, ABS, PRODUCT, SQRT
- Named Ranges
- Paste Special
- Screenshots
- Requirements
- Author
COUNT function is used to count the number of cells containing numbers.
Note: To count all non-blank cells use COUNTA.
SUM function adds up a range of cells.
In this example, the sum of the top 3 numbers in the range is calculated.
Note: The LARGE function returns an array constant {92, 88, 54}. This result is then summed.
IF function checks if a condition is met, returning one value if true and another if false.
Example: Numbers in range C2:C6 are checked. If they are ≥50, return "Pass", otherwise "Fail".
AVERAGE calculates the average of a group of numbers.
Here, the average of the 3 smallest numbers in the range is calculated.
Note: The SMALL function returns an array constant {1, 7, 14}.
COUNTIF counts the number of cells that meet a condition (here: TRUE).
SUMIF sums values in range L1:L6 if the corresponding cells in K1:K6 contain exactly one character plus a star.
Note: The ? symbol matches exactly 1 character.
VLOOKUP searches for a value in the leftmost column of a table and returns a value in the same row from another column.
Example: Lookup value is 58 (cell T1).
Note: Last argument—FALSE means exact match, TRUE means approximate match.
Use MIN to find the minimum value and MAX to find the maximum value.
SUMPRODUCT calculates the sum of the products of corresponding numbers in one or more ranges.
Here: (10 × 1.50) + (7 × 3.00) + (23 × 2.00) + (11 * 1,70) = 100,70.
Use / for division. To find the remainder, use the MOD function.
Use * or the PRODUCT function to multiply numbers. You can also use Paste Special.
To find the square root, use SQRT or raise to the power ^0.5.
ABS removes the minus sign from negative numbers.
To create a named range:
- Select the range.
- Type the name in the Name Box and press Enter.
Use the Name Manager (Formulas tab > Defined Names group > Name Manager) to view, edit, or delete named ranges or constants.
Using a named range in a formula.
If your data has labels:
- Select the range (e.g., D1:G13), then in the Formulas tab, Defined Names group, click Create from Selection.
- Choose "Top row" and "Left column" and press OK—Excel will create named ranges automatically.
Use the intersect operator (space) to return the intersection of two named ranges.
You can perform subtraction and other operations using Paste Special.
- Select cell D9 and press CTRL + C to copy.
- Select range D1:D7, right-click and choose Paste Special. In the Operation section, check Subtract.
- See the result:
All screenshots can be found in the /Screenshots folder.
- Microsoft Excel (recommended: 2021/365 for modern formulas)
- Windows OS
Project and documentation by Kuba27x
Repository: Kuba27x/Excel-2