If you've ever gone into a grocery store without your reusable bag because you're "just getting one thing," you've probably learned the hard way that consolidation solves many of life's problems. Entering a formula in Excel without a function is kind of like carrying your groceries without a bag: you can do it, but it's much easier when you package all those tiny little elements into one convenient vessel.
Let's say you want to add cells A1 through A3 together—instead of the manual formula entry of =(A1+A2+A3), you can use the =SUM function, so the formula instead looks like: =SUM(A1:A3). The whole point of Excel functions is to streamline the computation process and cut down on human error. And since there aren't many types of calculations you can make in Excel without them, entering formulas without functions isn't very common.
Granted, there are a few niches in which it can be useful to know how to enter a formula without a function in Excel, which I'll go into below. If you're in one of those niches, buckle up, because things are about to get Excel-lent.
Table of contents:
What's the difference between a formula and a function in Excel?
Excel formulas are mathematical sequences a user enters to calculate something within the spreadsheet. It's like using a calculator but with the capabilities of your full keyboard. Meanwhile, Excel functions are preset terms you can use within a formula that consolidate one or more actions into one tidy command, like SUM, AVERAGE, or VLOOKUP. Basically, they're math shortcuts.
Before I elaborate further, here are the elements that go into Excel calculations:
Operators: Symbols used to solve equations like addition (+), subtraction (-), multiplication (*), division (/), and colons (:)
Example: In the formula =(5 * A1) + SUM(A6:A10), (*) multiplies 5 by A1, and (+) adds that amount to the value of SUM(A6:A10).
Constants: Numbers or text values that don't change in a formula
Example: In the formula =(5 * A1) + SUM(A6:A10), 5 doesn't change as a result of the calculation.
Cell references: The field where a column letter and row number intersect in a spreadsheet
Example: In the formula =(5 * A1) + SUM(A6:A10), A1 refers to the cell contents that get multiplied by 5.
Cell ranges: A group of selected cells in a consecutive sequence
Example: In the formula =(5 * A1) + SUM(A6:A10), A6:A10 selects cells A6 through A10 only.
Functions: Preset shortcuts for applying a calculation
Example: In the formula =(5 * A1) + SUM(A6:A10), SUM adds the values in cells A6 through A10 together.
Example of entering a formula without a function
Think of this concept like baking a cake. You can bake that cake one of two ways: from scratch or with a pre-made cake mix. In this analogy, a function would be the pre-made cake mix you use to speed up the baking process. Manually creating a formula without including functions would be like baking that cake from scratch.
The only difference: in Excel, you get the same results.
To illustrate what I mean in Excel terms, let's consider three approaches to doing the same calculation. Below, I'll find the average of values in cells A1 through A6 of a spreadsheet without a function, with only a function, and with both a function and manual calculation.
Formula without a function: =(A1+A2+A3+A4+A5+A6) / 6
Formula with a function and manual calculation: =SUM(A1:A6) / 6
Formula with only a function: =AVERAGE(A1:A6)
As you can see, all three mixes lead to the same cake. In the first example, the user types out all the individual cell references and operators to combine cells A1 through A6, then divides the sum by 6. In the second example, they use the SUM function to automatically combine the values of those cells before dividing by 6. In the third, they use the =AVERAGE function to cut out the extra division step.
Typing up a formula without a function is usually more tedious and gives you more opportunities to mess up your math with a typo. Using functions like =SUM or =AVERAGE solves those problems. But it's not a zero=SUM game. If you embed functions into formulas, you combine the flexibility of a formula with the efficiency and accuracy of a function.
When to enter formulas without functions
While functions are generally much more efficient than manually typed formulas, there are some situations in which building out those formulas can be useful. Namely, when the calculations you need to make are very complex or don't fit neatly into one function box.
To illustrate, imagine you're a financial analyst, and you want to calculate a customized risk metric that doesn't have a built-in Excel function. You need to consider factors like market volatility, correlation, coefficients, asset allocation weights, and asset standard deviations. In this scenario, it may be easiest to manually create formulas, so you can have more control over the calculations. Here's what that might look like:
Entering a formula without a function (including Excel tricks)
To enter a formula without a function in Excel, follow these steps:
Click on the cell where you want to enter your formula.
Input an equal sign (=) to begin your Excel formula.
Input your formula. For example, if you wanted to multiply the value in cell B1 by 5, your formula would look like this: =B1*5.
Hit Enter to calculate your formula and display the results.
Need to type up a formula that's too complicated for a function? Want to replicate the concept of a function to help you stay organized without actually using one? Here are two Excel tricks for function-free formula entry.
Enter formulas using cell references
Here's how to calculate formulas using cell references.
Suppose you're a sales manager analyzing your team's performance. You can create a custom sales performance metric based on salesperson target achievement and experience level, each with different weightings. Follow the steps below to create a customized formula using cell references and operators.
Step 1: First, you want to calculate the target achievement. Label cell A1 Target Achievement and cells A2 through A4 with your salespeople. Input the target performance percentages in the cell of your choice. In this example, I've added the percentages in cells B2 through B4.
Step 2: Input each employee's experience level in cells D1 through D4, and years of experience in E2 through E4.
Step 3: Set weightings for target and experience in F1 through F3. Input target values in G2 through G3. This formula calculates sales performance by multiplying each salesperson's target achievement by the weighting for target achievement (G2) and then adding it to the product of their experience level and the weighting for experience (G3).
Step 4: Select an empty cell and input an equal sign (=) to start the operation. Select your cell references and input multiplication and addition operators to get the total performance value.
Step 5: Hit Enter for your total sales performance value.
In this particular instance, there's no real shortcut a function could offer you to streamline this formula. Since there are so many cells with different operators involved, the only real option is doing your spreadsheet calculations the old-fashioned way: with manual formula entry.
Write formulas in Excel using predefined names
If you want to input formulas without functions but still crave the packaged flavor of a function, there's an easy cheat code you can use: Define Name.
Using the Define Name feature is like giving nicknames to math expressions. You can assign a name to a single cell or series of cells to represent a formula you can reference later by searching the term. This can be useful for labeling and organizing data in a spreadsheet, so you can easily track what the value of a cell corresponds to or represents.
Step 1: Click on your cell of choice. To name it, either click on Define Name in the formulas tab box or type your term in the Name box.
For this example, I named three cells Profits, Costs, and Earnings to help organize a basic net revenue calculation. Using the Define Name feature, I named A2 Profits and B2 Costs. Then, I input values for profits and earnings.
Step 2: Use your newly defined cell names in a formula.
For my example, I input an equal sign into cell C2 and then entered Profits-Costs. If I were to select A2 and B2 or type A2-B2 in the formula bar, it would automatically display as Profits-Costs.
Step 3: Execute the formula.
In my example, when I hit Enter, I get the outcome of costs subtracted from profits, giving me an earnings figure of $4,100.
Another way you could do this would be to enter Profit-Costs or =A2-B2 into its own cell, and then use Define Name to name that calculation Earnings. Then, any time you enter =Earnings into a cell, you automatically populate it with the result of whatever the value is of B2 subtracted from A2.
Could you do all this without Define Name? Absolutely. This feature doesn't do anything a function can't do, but it does replicate the concept of a function, which can help you stay organized when entering complex formulas.
Excel formula entry FAQ
How do you enter a formula without entering a function?
To enter a formula without using a function:
Type = into a cell.
Enter the first cell (or number) you want to include in the calculation.
Enter the operator of your choice (without a space in between), and then press Enter.
If you want to include other cells and operators, include those without adding spaces in between. To separate cells and operators, use parentheses to isolate them. For example, (A1-A2)+(B1-B2) would add the difference between A1 and A2 to the difference between B1 and B2.
How do you use the formula builder in Excel?
The formula builder, also known as the function arguments box, is a tool for building complex formulas. Begin by clicking the toolbox icon in the upper-right corner and clicking on the formula builder. Scroll through the list of predefined function names, and select one based on your calculation needs. Highlight or transfer the cell values into the formula builder, and press Enter to calculate your results.
How do you turn off functions in Excel?
To turn off functions in Excel, click the Microsoft home button, click Excel options, and select Formulas. Click the Formula checkbox to turn off functions. While most users won't need (or want) to do this, it could be useful if you're trying to type custom elements into cells that commonly trigger formulas.
Automation and Excel: A winning formula
You can spend less time in your spreadsheets by automating Excel with Zapier. Automatically log form and survey submissions, send notifications for important updates, and update data across multiple spreadsheets. Learn more about how to automate Excel, or get started with one of these pre-made workflows.
Add new Typeform entries as rows on an Excel spreadsheet
Add or update Mailchimp subscribers from new rows on Excel
Related reading:
