Functions AND Formulas

---

To build a formula in Excel, start by entering an equal sign (=), followed by the operation or function. For instance, entering =D2*E2 into a cell will multiply the values in D2 and E2. After completing the formula, press Enter to see the result displayed.

While you can type each cell reference manually, it’s more efficient to use arrow keys to select cells as you build the formula. If you want the same formula in multiple cells, enter it in one cell, then copy and paste it into the others using the formula paste option.

The SUM function adds together all the numbers in a chosen group of cells. For example, SUM(F1:F24) totals the values from F1 through F24 — useful for calculating things like total sales.

To count how many numeric entries are in a set of cells, use COUNT. For instance, COUNT(F1:F24) counts how many of those cells contain numbers. If you need to count all non-empty cells (including those with text), use COUNTA.

COUNTIF is useful when you want to count cells that meet a specific condition. For example, it can count how many transactions exceed $30,000.

This function requires two inputs: the range of cells to check, and the condition written in quotation marks, like ">30000".

To total values that satisfy a condition, use SUMIF. It takes three inputs: the range to evaluate, the condition to check, and the range of values to add if the condition is met.

For example, to sum the revenue of all March transactions, use the date range to apply the condition, and the revenue column as the values to be added.

COUNTIFS works like COUNTIF but allows for checking multiple conditions at once. Each condition is paired with its respective range. It counts entries that meet all specified criteria — for example, counting orders in March that involve a specific product.

AVERAGE calculates the mean of a range of values.

AVERAGEIF finds the average of cells that meet a particular condition, requiring the condition range, the condition itself, and the range to average.

For more complex criteria, use AVERAGEIFS, which allows you to apply multiple conditions, each with its own range and criterion.

Use MAX to get the highest number in a range, and MIN to get the lowest.

If you want to apply conditions while doing this, Excel versions 2019 and later offer MAXIFS and MINIFS. In earlier versions, you need to use array formulas to replicate this functionality.

The IF function evaluates a condition and returns one result if true and another if false. For instance, it can be used to flag whether revenue targets have been met.

It takes three parts: the condition, the result if true, and the result if false.

AND checks whether all specified conditions are true. If even one is false, it returns false. This can be used to verify if multiple performance targets are met.

OR, on the other hand, returns true if any of the conditions are met. Both functions can be used within IF for more advanced logic.

Here are common comparison operators:

• = : Equal to
• <> : Not equal to
• > : Greater than
• >= : Greater than or equal to
• < : Less than
• <= : Less than or equal to
   

The SWITCH function evaluates a cell and matches its value against a list of possibilities, returning a result based on the match.

You provide the value to check, a series of value/result pairs, and a default result if there’s no match. It’s useful for categorizing entries based on exact matches — like distinguishing between business and personal clients.

When a formula is copied to another location, Excel adjusts the references automatically. For example, copying =E2*D2 from cell F2 to F3 results in =E3*D3. You can view these formulas using the shortcut Alt → M → H.

If you need to keep a reference fixed when copying a formula, you can “lock” it using the F4 key. This adds dollar signs to the reference:

• $A$1 keeps the row and column fixed
• $A1 locks the column
• A$1 locks the row

This is handy when you’re using a fixed rate, such as a currency exchange, across multiple rows.

To simplify formulas, you can assign a name to a specific cell. Once named, that name can be used in place of the cell reference in formulas.

To do this, select the cell, press Ctrl + F3 to open Name Manager, and assign a meaningful name preferably in all caps. Named cells make formulas easier to read and reduce the need for anchoring.

Array formulas allow for multiple calculations across ranges of data. You use them when a regular function isn’t enough such as finding the maximum value under certain conditions.

To activate an array formula, use Ctrl + Shift + Enter. This wraps your formula in curly braces {}.

The TRANSPOSE function switches data from rows to columns or vice versa. You must select the output range in advance and use Ctrl + Shift + Enter to apply it.

To get the highest value for a specific group (like a product type), nest an IF inside a MAX function. The IF filters based on the condition, and MAX selects the largest value. As with other array formulas, finalize with Ctrl + Shift + Enter.

Array constants are fixed sets of values wrapped in braces. Commas separate values across columns; semicolons create rows.
Examples:

• {1,2,3,4} → one row with four values
• {1;2;3;4} → one column with four values
• {1,2;3,4} → 2x2 grid
   

To get the lowest values from a list, use the SMALL function. It takes two inputs: the range and the position of the smallest value (e.g., 1st, 2nd, etc.).

To find the five smallest entries, use an array like {1;2;3;4;5} inside SMALL

To figure out how many distinct customers are in your data, you can use an array formula that combines SUM and COUNTIF. What happens is COUNTIF builds an array showing how many times each customer appears. So, if someone named Sarah appears five times, the array will have five entries of “5”.

By flipping that to 1 divided by the count, you get five entries of 1/5 for Sarah. Adding these together gives one representing one unique customer. Doing the same for all customers and summing it up provides the total number of unique individuals in the dataset.

SUMPRODUCT is a powerful function that multiplies items in two or more arrays and adds up the total.

For example, to calculate total income, you can multiply the unit prices with their quantities and sum it all in one go using SUMPRODUCT.

You can also use SUMPRODUCT for conditional logic, much like COUNTIF. Say you want to count how many times a customer (Sarah) bought a specific product (a laptop). You compare each entry to see if the name is Sarah and the product is a laptop. These checks return TRUE or FALSE, which Excel sees as 1 and 0. Multiplying them gives 1 when both conditions are met.

Need a filtered sum instead of just a count? Extend the earlier approach: once you’ve got an array of 1s and 0s for matches, multiply it by the sales amount to get actual values where the conditions are met. Then just use SUMPRODUCT to total those.

Let’s say you want to count orders from either Sarah or Jimmy. Instead of multiplying the condition checks (which would require both to be true), you simply add them. This way, if either is true, it’ll show up as 1 in the array, and you can tally the count with SUMPRODUCT.

Once you’ve created an array identifying orders from either Sarah or Jimmy, multiply it by the order amounts to find total sales from those two customers.

To combine info from different worksheets say, monthly sales data Excel’s Consolidate feature is handy. You can access it with Alt > A > N. After selecting and adding the relevant ranges, choose a function like “sum” to pull everything together into a new summary table.

All selected ranges must have the same layout. If the structure doesn’t match—like mismatched row or column labels it won’t work correctly. Sometimes, writing formulas manually can be faster and more flexible than using Consolidate.

RAND() outputs random values between 0 and 1. For instance, if you want to randomly sample one-third of orders, you could use RAND() with an IF statement to flag rows where the value is under 0.33. Remember: RAND() recalculates every time the sheet updates, so once you're happy with the result, use Paste Values to freeze them.

If you need a whole number between two limits, use RANDBETWEEN. For example, RANDBETWEEN(1,3) will randomly return 1, 2, or 3. You can then tie these numbers to categories like “Full Review,” “Survey,” or “No Action” using nested IF formulas.

Excel’s formula auditing tools help you catch and fix errors. Access them through the Formulas tab or use Alt + M. These tools can show what cells feed into a formula and which ones are affected by it.

Use Alt + M + H to switch the display from results to actual formulas. It highlights dependent cells, which helps you trace any issues. For large models, this might not be as helpful since seeing the actual numbers is often more intuitive.

To see what feeds into a formula cell, use Trace Precedents (Alt + M + P). To find out what depends on a selected cell, use Trace Dependents (Alt + M + D). Clear the arrows with Alt + M + A + A. This visual guide makes it easy to follow the flow of calculations.

Tracing doesn’t show arrows across worksheets. You’ll need to use the Go To box to manually check linked cells. Keeping formulas on one sheet as much as possible helps make tracing and debugging easier.

Fill commands help replicate formulas and formats efficiently. One quick way is double-clicking the bottom right of a cell to autofill it down a column. This is great for larger datasets.

Ctrl + R fills right, and Ctrl + D fills down. Select both the cell with the formula and your target range before applying the shortcut.

Be careful: using Fill might remove cell borders. If maintaining formatting is important, use Paste Formulas instead.

Flash Fill detects patterns in data. If you manually enter the first few values, like postcodes extracted from addresses, Excel can auto-complete the rest. Activate with Alt + A + FF if Excel doesn’t auto-suggest it.

Info functions tell you what’s in a cell. For example, ISBLANK() checks for emptiness, and returns TRUE if a cell has no content. These are often used inside IF formulas to control output more clearly.

Once you’ve used info functions to flag problem rows, apply filters (Ctrl + Shift + L) and select the relevant category to narrow down and review.

To change how numbers are stored not just displayed use Excel’s rounding functions. ROUND adjusts a number to a set number of decimals. ROUNDUP and ROUNDDOWN work the same way but always round in one direction.

Sometimes Excel totals don’t match what you visually see due to hidden decimals. Rounding fixes that so your totals make sense both behind the scenes and on screen.

LAMBDA() allows you to create your own named formulas. You build a formula as normal, then turn it into a custom function using LAMBDA. First test it directly, then save it via Name Manager (Ctrl + F3). After that, you can use it anywhere like a built-in function.

It’s best for formulas you use repeatedly or for simplifying complex calculations. Once defined, a custom LAMBDA function makes your work neater, faster, and less error-prone.