General Excel Knowledge
Paste Special
Paste Special in Excel allows users to selectively paste data such as values, formulas, formats, or comments from the clipboard, offering more control than standard pasting. This facilitates data transformation and manipulation without manual changes.
More here.
Data validation
Data Validation in Excel is a feature that allows users to set restrictions on what data can be entered in a cell, preventing incorrect entries. This includes criteria like number ranges, list selection, date ranges, text length, and custom formulas.
More here.
Named ranges
Named Ranges in Excel are a feature that allows users to define a cell or group of cells with a descriptive name, facilitating easier reference in formulas, charts, or functions. This enhances readability and reduces errors in complex spreadsheets.
More here.
Evaluate nested formula
Sometimes, understanding how a nested formula calculates the final result is difficult because there are several intermediate calculations and logical tests. However, by using the Evaluate Formula dialog box, you can see the different parts of a nested formula evaluated in the order the formula is calculated.
More here.
Workbook Statistics
Workbook Statistics in Excel provides an overview of a workbook's content, detailing the number of sheets, cells with data, formulas, tables, charts, etc. It's useful for understanding the complexity and size of a workbook at a glance.
More here.
PivotTables
PivotTables in Excel are a powerful tool used to summarize, analyze, explore, and present large amounts of data in a condensed, interactive format. They allow users to extract significance from data sets by reorganizing selected columns and rows.
More here.
Goal Seek
If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.
More here.
Conjoining Text & Cell Values
You can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function.
More here.
Excel tables
Excel Tables are structured ranges that facilitate data management. They automatically preserve formulas, format data, and add filters, making it easier to sort, filter, and analyze large sets of data. They also adjust dynamically when data is added or removed.
More here.
Data Tables
Data Tables in Excel are part of the "What-If Analysis" tools that allow comparison of multiple scenarios in a structured table format. They help observe how varying input values impact calculated results, useful in sensitivity analysis and modeling.
More here.
Trace dependents / precedents
Tracing dependents and precedents in Excel is a feature that identifies cells that affect the active cell (precedents) or are affected by it (dependents). This helps understand and debug complex formulas, revealing the relationships between cells in a spreadsheet.
More here.
Functions You Should Know
| Date and time | Logical | Information | Lookup and reference | Math and trigonometry | Statistical | Text |
|---|---|---|---|---|---|---|
| DATE | AND | ISBLANK | FILTER | ABS | AVERAGE | CONCAT |
| DAY | IF | ISERR | HLOOKUP | MOD | COUNT | FIND |
| EDATE | IFERROR | ISERROR | INDEX | POWER | COUNTA | LEFT |
| MONTH | IFNA | ISNA | INDIRECT | PRODUCT | COUNTIFS | LEN |
| TODAY | IFS | NA | MATCH | RAND | MAX | MID |
| YEAR | NOT | OFFSET | ROUND | MIN | RIGHT | |
| YEARFRAC | OR | VLOOKUP | ROUNDDOWN | SEARCH | ||
| FALSE | XLOOKUP | SUFIFS | SUBSTITUTE | |||
| TRUE | XMATCH | SUM | TEXT | |||
| SUMIF | TEXT | |||||
| SUMPRODUCT | TEXTSPLIT | |||||
| VALUE |