Dùng Formula Trong Word / Top 14 Xem Nhiều Nhất & Mới Nhất 12/2023 # Top Trend

Insert Table Formulas in Word: Overview

You can insert table formulas in Word tables to perform simple mathematical functions on data. To insert table formulas in Word that add, subtract, multiply, and divide numbers in the table cells, you insert formulas into cells where you want to show the answers to the mathematical operations performed by the formulas.

The Parts of Table Formulas in Word

When you insert table formulas in Word, you insert a field that performs calculations on values in other table cells. Formulas always start with an equal sign (=). They often refer to the cell addresses from which they gather the data for their calculations. These cell addresses can be linked together with standard mathematical operators. These include the plus sign (+), minus sign (-), multiplication sign (*), and division sign (/), among others. You can also perform functions, like SUM, on a cell range in a table. So, a formula might be expressed “=SUM(Above),” which adds the values of the cells above the cell into which you inserted this formula.

A cell address is a way of referring to a cell. A cell address is the relative location of a cell in a table. Imagine there are letters at the top of each column, starting with “A” at the far left and then continuing to increase one letter at a time to the right. In addition, imagine each row has a number assigned to it. The topmost row is row “1.” The row numbering then continues downward, increasing by one for each row. The cell address is the column letter, followed by the row number. For example, the top left cell is always cell A1. B1 is always to the right of A1. Here is a table with the cell addresses entered into the corresponding cells to help you see the cell address naming convention.

Instead of showing the formula itself in the cell, the cell shows the to the formula. Why? Because when you insert table formulas in Word in a cell, Word knows it should show the answer to the formula, not the formula itself. Formulas display their results by default, not their actual contents.

How to Insert Table Formulas in Word

When the “Formula” dialog box first opens, Word tries to guess the formula you want. For example, if you insert table formulas in Word in a cell at the end of a column of continuous numbers, Word assumes you want to add the cell values in the column above the cell. Therefore, Word enters the formula =SUM(Above) as the default formula in the “Formula” dialog box.

After entering the formula into the “Formula:” field, you can then use the “Number format:” drop-down to select a numeric pattern. This helps show the result in a specific numeric format.

In Word, you can use the terms “LEFT,” “RIGHT,” “ABOVE,” and “BELOW” to refer to adjacent cells in the row or column to the left of, to the right of, above, or below the cell within which you insert table formulas in Word. This is a convenient way of selecting the cell range for the function. You can also enter a cell range by typing the cell address of the upper-left cell in the cell range, followed by a colon symbol (:), then followed by the cell address of the lower-right cell in the range. For example, you could also type =SUM(A1:A4) into the “Formula:” field to add the contents of cells A1 through A4.

The word SUM is a formula function. If want to perform one mathematical operation on a range of cells, you can use functions like SUM, AVERAGE, MAX, and MIN when you insert table formulas in Word, instead of individually writing the cell addresses and mathematical operators. Word provides many standard functions in the “Paste function:” drop-down. Selecting any function from the list of functions in the drop-down menu adds it to the formula in the “Formula:” field.

Insert Table Formulas in Word: Instructions Instructions on How to Insert Table Formulas in Word

Optionally, to select a function to add to the formula shown in the “Formula:” field, use the “Paste function:” drop-down.

Optionally, to format the display of the numeric formula’s result, use the “Number format:” drop-down.

Insert Table Formulas in Word: Video Lesson

The following video lesson, titled ” Inserting Table Formulas,” shows how to insert table formulas in Word. It is from our complete Word tutorial, titled ” Mastering Word Made Easy v.2023 and 365.”

You can use simple formulas in Microsoft Word, such as addition (+), subtraction (-), multiplication (*), or division (/). Also, you can calculate a power of (^):

See How to reference a cell of a Word table for more details.

All functions you can see in the Paste function drop-down list of the Formula dialog box:

ABS ()

Calculates the absolute value of the value inside the parentheses.

AVERAGE ()

Calculates the average of the elements identified inside the parentheses.

COUNT ()

Calculates the number of elements identified inside the parentheses.

DEFINED ()

Evaluates whether the argument inside parentheses is defined. Returns 1 if the argument has been defined and evaluates without error, 0 if the argument has not been defined or returns an error.

IF ()

Evaluates the first argument. Returns the second argument if the first argument is true; returns the third argument if the first argument is false.

INT ()

Rounds the value inside the parentheses down to the nearest integer.

MAX ()

Returns the maximum value of the items identified inside the parentheses.

MIN ()

Returns the minimum value of the items identified inside the parentheses.

MOD ()

Takes two arguments (must be numbers or evaluate to numbers). Returns the remainder after the second argument is divided by the first. If the remainder is 0 (zero), returns 0.0.

NOT

Evaluates whether the argument is true. Returns 0 if the argument is true, 1 if the argument is false. Mostly used inside an IF formula.

OR ()

Takes two arguments. If both are false, returns 0, else returns 1. Mostly used inside an IF formula.

PRODUCT ()

Calculates the product of items identified inside the parentheses.

ROUND ()

Rounds the first argument to the number of digits specified by the second argument. If the second argument is greater than zero ( 0), first argument is rounded down to the specified number of digits. If second argument is zero ( 0), first argument is rounded down to the nearest integer. If second argument is negative, first argument is rounded down to the left of the decimal.

SIGN ()

Takes one argument that must either be a number or evaluate to a number. Evaluates whether the item identified inside the parentheses if greater than, equal to, or less than zero ( 0). Returns 1 if greater than zero, 0 if zero, -1 if less than zero.

SUM ()

Calculates the sum of items identified inside the parentheses.

The arguments can be:

See also this tip in French: Fonctions et formules dans Word.

Excel doesn’t have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. At a high level, this formula uses the LEN function to count the number of characters in the cell, with and without spaces, then uses the difference to figure out the word count. This works, because word count is equal to the number of spaces + 1, so long as there is one space between each word.

The first part of the formula counts the characters in cell B5, after removing extra space:

=

LEN

(

TRIM

(

B5

))

// normalize space, count characters

Inside LEN, the TRIM function first removes any extra spaces between words, or at the beginning or end of the text. This is important, since any extra spaces will throw off the word count. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30:

LEN

(

"All Quiet on the Western Front"

)

// returns 30

At this point, we have:

=

30

-

LEN

(

SUBSTITUTE

(

B5

,

" "

,

""

))

+

1

Next, we use the SUBSTITUTE function to remove all space characters from the text:

SUBSTITUTE

(

B5

,

" "

,

""

)

// strip all space

Notice SUBSTITUTE is configured to look for a space character (” “), and replace with an empty string (“”). By default, SUBSTITUTE will replace all spaces. The result is delivered directly to the LEN function, which returns the count:

LEN

(

"AllQuietontheWesternFront"

)

// returns 25

LEN returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to:

=

30

-

25

+

1

// returns 6

which returns 6 as a final result, the number of words in cell B5.

Dealing with blank cells

The formula in the example will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally, after counting space characters between words. To guard against this problem, you can adapt the formula as shown below:

Notice we've replaced 1 with this expression:

This code first trims B5, then checks the length. If B5 contains text, LEN returns a positive number, and the expression returns TRUE. If B5 is empty, or contains only space, TRIM returns an empty string ("") to LEN. In that case, LEN returns zero (0) and the expression returns FALSE. The trick is that TRUE and FALSE evaluate to 1 and zero, respectively, when involved in any math operation. As a result, the expression only adds 1 when there is text in B5. Otherwise, it adds zero (0). This logic could also be written with the IF function statement like this:

and the result would be the same. The expression above is simply more compact.

Want to get the word count in Excel? Believe it or not, Excel does not have an inbuilt word counter.

But don’t worry.

A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you.

In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell.

Formula to Get Word Count in Excel

Before I give you the exact formula, let’s quickly cover the logic to get the word count.

Suppose I have a sentence as shown below for which I want to get the word count.

While Excel cannot count the number of words, it can count the number of spaces in a sentence.

So to get the word count, we can count these spaces instead of words and add 1 to the total (as the number of space would be one less the number of words).

Now there can be two possibilities:

There is a single space between each word

There are multiple spaces between words.

So let’s see how to count the total number of words in each case.

Example 1 – When there is a single space between words

Let’s say I have the following text in cell A1: Let the cat out of the bag

To count the number of words, here is the formula I would use:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

This would return ‘7’ as a result.

Here is how this formula works:

LEN(A1) – This part of the formula returns 26, which is the total number of characters in the text in cell A1. It includes the text characters as well as the space characters.

SUBSTITUTE(A1,” “,””) – This part of the formula removes all the spaces from the text. So the result, in this case, would be Letthecatoutofthebag.

LEN(SUBSTITUTE(A1,” “,“”) – This part of the formula counts the total number of characters in the text that has no spaces. So the result of this would be 20.

LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”)) – This would subtract the text length without spaces from the text length with spaces. In the above example, it would be 26-20 which is 6.

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”))+1 – We add 1 to the overall result as the total number of spaces is one less than the total number of words. For example, there is one space in two words and two spaces in three words.

Now, this works well if you have only one space character between words. But it wouldn’t work if you have more than one space in between words.

In that case, use the formula in the next example.

Example 2: When there are multiple spaces between words

Let’s say you have the following text: Let the cat   out of    the bag

In this case, there are multiple space characters between words.

To get the word count, we first need to remove all the extra spaces (such that there is only one space character between two words) and then count the total number of spaces.

Here is the formula that will give us the right number of words:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

This is a similar formula used in the above example, with a slight change – we have also used the TRIM function here.

Excel TRIM function removes any leading, trailing, and extra spaces (except single spaces between words).

The rest of the formula works the same (as explained in Example 1).

Note: If there are no spaces between words, it is considered as one word.

Using VBA Custom Function to Count Words in Excel

While the above formulas work great, if you have a need to calculate the word count often, you can use VBA to create a custom function (also called a User Defined Function).

The benefit of using a custom function is that you can create it once and then use it like any other regular Excel function. So instead of creating a long complex formula as we did in the two examples above, you have a simple formula that takes the cell reference and instantly gives you the word count.

Here is the code that will create this custom function to get the word count in Excel.

Function WordCount(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1

How to Count Total Number of Words in a Cell of Excel?

The steps to count the total number of words in a cell of Excel are listed as follows:

Step 1: Select the cell in the Excel sheet where you want the result to appear.

Step 2: For counting the number of words in cell A1, enter the formula shown in the following image.

To count the number of words in a range of cells, apply the equation that counts the words in a cell and implant it either inside the SUM or the SUMPRODUCT function.

The formula to count words of a particular range is “=LEN(TRIM(cell))- LEN(SUBSTITUTE(cell,” “,””))+1.”

Step 1: Select the range of data whose words you wish to count.

Step 2: Enter the formula in the cell where you want the result to display as shown in the succeeding image.

Step 4: Drag the fill handle to all cells to get the word count of each cell.

To count the number of times a specific word appears in a range of cells, we utilize a comparative methodology. We count the explicit words in a cell and consolidate it with the SUM or SUMPRODUCT function.

Step 1: Select the cell and enter the formula “=(LEN(cell)-LEN(SUBSTITUTE(cell,word,””)))/LEN(word)” as shown in the following image.

The result in cell A14 is 4.

The formula for counting the number of words in Excel is:

LEN(SUBSTITUTE(A2,” “,””))

Let us understand the working of this formula.

To begin with, we utilize the SUBSTITUTE function to evacuate and displace all spaces in the cell with a vacant content string (“). The LEN function restores the length of the string without spaces.

Popular Course in this category

Further, we utilize the TRIM function to remove extra spaces in a cell. A worksheet may contain a lot of imperceptible spaces. Such coincidental occurrence might be towards the start or end of the text (leading and trailing spaces). Since extra spaces return an incorrect word count, the TRIM function is used before computing the length of the string.

Characteristics of the Word Count Formula

The features of the word count formula are listed as follows:

It is not an in-built formula of Excel and needs to be entered manually.

It is not case sensitive which implies that any type of alphabetical letters can be used.

It is essential to place the $ sign to fix the cell reference while copying the formula.

It works well if the cell, for which the formula is being used, is checked beforehand.

It requires the correct range to be specified at the time of usage.

Frequently Asked Questions #1 – How to count the number of times a single character appears in a cell?

The formula to count the occurrence of a single character in a cell is stated as follows:

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,”a”,””))

The “cell_ref” stands for cell reference. The letter “a” stands for the character that the user wants to count.

#2 – How to count the number of times a single character appears in a range of cells?

The formula to count the occurrence of a single character in a range of cells is stated as follows:

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”a”,””)))

The “range” stands for the range of cells to which the formula is applied. The letter “a” stands for the character that the user wants to count.

#3 – How to count the number of times a specific word appears in a row or a column?

The steps to count the number of times a particular word appears in a row or a column are listed as follows:

If the column is named “NamesColumn,” the cells in this column will use “NamesColumn” for reference.

Apply the formula “=COUNTIF(NamesColumn,”Jack”)” to count the number of times “Jack” appears in the “NamesColumn.”

Note: Every time a new name is added to a cell of “NamesColumn,” the result of the formula will automatically update.

Key Takeaways

The formula to count words of a particular range is “=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1.”

The word count formula is combined with the SUM or SUMPRODUCT function to handle arrays.

The SUBSTITUTE function replaces all the spaces of the cell with a vacant content string (“).

The LEN function restores the length of the string without spaces.

The TRIM function removes the leading and trailing spaces found at the beginning or at the end of the text.

The number of words in a cell is equivalent to the number of spaces plus 1.

Recommended Articles

This has been a guide to Word Count in Excel. Here we discuss how to count the total number of words in a cell and a range of cells using Excel formulas (LEN, SUBSTITUTE, TRIM) along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles –