How to make a formula for a column in Excel

Excel is a powerful tool for managing and analyzing data. In addition to performing calculations, formulas can be created that manipulate data within spreadsheets. You can automate repetitive tasks in Excel by creating formulas for columns, which will save you time and ensure accuracy.

It may seem difficult at first to create a formula in Excel for a column or row. However, anyone can create a powerful formula in a few simple steps. In this article, we will discuss how to create formulas for columns in Excel. We will also learn how to make Excel spreadsheets more efficient and productive, whether you are new to Excel or an experienced user.

We will calculate the total sales amount by creating a Non-Array & Array Formula with the help of the following dataset.

Before moving on, let’s understand the difference between array & non array formulas.

Array Formula: An array formula is a special type of formula that can perform calculations on a group of cells, known as an array. An array formula is entered into a single cell but can affect a range of cells or an entire column or row. It is enclosed in curly braces { }, and pressing Ctrl + Shift + Enter is necessary to complete the formula entry.

Non-Array Formula: A non-array formula is a standard formula that calculates a result based on one or more cell references, but does not operate on an array of cells. Non-array formulas are the most common type of formula in Excel and are entered by pressing Enter after typing the formula in a cell.

Method 1 – Total Sales By Non-Array Formula

Step 1 – Select Cell

• Select the cell where you want to enter the formula & place equals (=) to sign.

Step 2 – Type Formula

• Type formula =B2*C2 & press Enter.

Step 3 – Implement the formula for the whole data column

• With the help of selection handle, drag the formula downwards to find out the total sales of each product by non-array formula.

Method 2 – Total Sales By Array Formula

Step 1 – Select Range

• Select the range of cells where you want to apply the formula starting with equals (=) to sign.

Step 2 – Type Formula

• Start typing the formula =B2:B6*C2:C6.
• Instead of pressing enter, we will press CTRL + SHIFT + ENTER keys. By doing this excel will automatically place curly braces & create an array formula & calculate the total sales in the selected range.

Step 3 – Array Formula Created

• This is one of the simplest methods by which you can create a formula for a column. In this way the formula is written in the first cell only but implemented to the whole column at once.