How to Write Formula in Excel: A Step-by-step Guide
The world of data analysis and business intelligence is still reliant on the good old Excel to crunch numbers and come to conclusions. As such, it is increasingly important for business professionals to understand the basics of Excel and learn how to write formula in Excel.
We have noticed that writing formulas in Excel is a common struggle, whether due to complex syntax or unfamiliar functions. This guide will help you learn some basic Excel formulas and get practical tips to improve your efficiency.
FYI: In the end of this article, you can try our AI Tool that generates Excel formulas in seconds!
Mastering writing Excel formulas takes practice, but with our step-by-step guidelines, you’ll be able to write formulas with confidence and precision.
What is an Excel Formula?
The first step to mastering Excel formula writing is to understand the basics. An Excel formula is a sequence of characters that instructs Excel to perform calculations. Think of it as a mini-programming language containing values, functions, operators, and references that tell Excel what to do.
A formula can be as short as a single character or as long as several lines of code. Formulas can use numerical values, text, dates, times, and other types of data. They can also include functions, operators, and references to other cells and worksheets.
Excel Formula Key Components
- Operators: +, -, *, /, & for text, ^ for exponents
- Cell References:
- A1 – A relative reference that adjusts when copied to another cell.
- $B$2 – An absolute reference that remains fixed, even when copied.
- A1:B10 – A range reference covering multiple cells.
- Functions: SUM, AVERAGE, IF, etc.
- Logical Operators (Used to compare values and return TRUE or FALSE based on conditions): >, <, =, <>, AND, OR
Shortcut: Press Ctrl + ~ to toggle between formula view and result view.
Understanding how all of these components work together is essential in learning how to write an Excel Formula.
Basic Excel Formulas
| Formula | Explanation |
|---|---|
| =A1+B1 | Addition |
| =A1-B1 | Subtraction |
| =A1*B1 | Multiplication |
| =A1/B1 | Division |
| SUM (A1:A10) | Adds all values in a range |
| =AVERAGE(A1:A10) | Calculates the average value of a range |
| =MIN(A1:A10) | Finds the smallest value. |
| =MAX(A1:A10) | Finds the largest value. |
| =COUNT(A1:A10) | Counts numbers in a range. |
| =COUNTA (A1:A10) | Counts the non-empty cells. |
| =NOW() | Returns the current date and time. |
| =TODAY() | Returns the current date. |
Want to learn more Excel formulas? Check our Excel Formula Cheat Sheet.
How To Choose the Right Formula Format?
Once you understand the basics of Excel formula writing, the next step is to choose the right format for your formula. The good news is that there are only two formats.
In Excel, formulas can be written in either a standard or R1C1 format:
- A1 Format (Default) – Uses letters for columns and numbers for rows (e.g., A1 refers to Column A, Row 1)
- R1C1 Format – Uses numbers for both rows and columns (e.g., R1C1 means Row 1, Column 1). Best for complex formula auditing.
The R1C1 format is more complex and requires more knowledge to understand. This format is more suited to advanced users who need to write complex formulas.
For most business professionals, the A1 format is more than enough. It is also a lot easier and more intuitive. Why would you want to complicate your life?
Shortcut? Sure! Toggle between A1 and R1C1 format under File > Options > Formulas > Working with formulas.
How to Write Formula in Excel: Learn the Syntax
The next step is to learn the syntax of an Excel formula. The syntax of an Excel formula is the set of rules and conventions that govern how a formula is written. A formula follows a specific syntax: =FUNCTION(argument1, argument2,…)
For example, the previously mentioned formula =SUM(A1:A10)
Common Syntax Rules:
- Enclose text in double quotes (
""), e.g., =IF(A1>10, “High”, “Low”) - Use parentheses to group operations, e.g., =(B1 + C1)*D1
- Use absolute references ($A$1) to keep a cell fixed when copying formulas.
Learning the syntax of an Excel formula is essential for writing effective formulas. Without understanding the syntax, you might write formulas that produce incorrect results or that are difficult to read and understand.
You might be interested: How to Fix an Excel Formula?
Practice Makes Perfect
The final step to mastering Excel formula writing is to practice. Writing effective formulas takes time and practice. The more you practice, the better you will become at writing formulas. Start by writing simple formulas and then gradually increase the complexity.
In case there is an Excel formula that seems too complex, you can use the Excel Formula Explainer in your learning process.
Here is a short video of this AI tool in action:
As you practice, you will become more familiar with the syntax and the available functions, and you will be able to write more complex formulas more quickly.
Generate your Excel Formula in Seconds
We love saving more time and energy, and this is why we have created more than 18 AI Tools, so you don’t have to.
These AI Tools are specially designed for the efficiency of your spreadsheets. Try them out! It’s the 21st Century, and AI is not the future anymore. It’s the present.
In this article, we’re giving you an incredible opportunity to try one of our tools: Excel Formula Generator.
Incredible, isn’t it?
Conclusion
Mastering how to write formula in Excel is a valuable skill that can significantly improve your productivity. By understanding Excel basic formulas, learning proper syntax, and practicing regularly, you can become a professional in writing formulas in Excel with confidence.
If writing Excel formulas seems more like a headache, we suggest to focus on what matters. Let AI Tools do all the hard work in your place. Excel Formula Generator, Formula Explainer or Ajelix Excel Add-In are only a few of our AI tools you can use to make your life easier.
Want to stay in the tips and tricks loop? Sure, let’s stay connected.
FAQ
An Excel formula is a sequence of characters used to perform calculations or manipulate data in a spreadsheet. It can involve numerical values, text, dates, functions, operators, and cell references. Formulas help automate calculations, saving time and effort in data analysis.
Advanced users who want to do complex calculations should use the R1C1 Format. For most people, it is enough with the default A1 Format where columns are labeled with letters and rows with numbers.
Practice is key! Start with simple formulas and gradually tackle more complex ones. You can use tools like the Excel Formula Explainer to help you learn and understand formulas more effectively.
Some very basic Excel formulas are addition (=A1+B1), subtraction (=A1-B1), multiplication (=A1*B1) and division (=A1/B1).
Yes! Ajelix offers a range of AI Tools. One of them is Excel Formula Generator, which can generate Excel formulas in seconds. It can help you save time and avoid mistakes while writing formulas.
Speed up your spreadsheet tasks with Ajelix AI in Excel
