When you want to count the number of columns in a range, you can use the COLUMNS function and select a proper range (Fig. 1)
COLUMNS function — how many columns in a range
=COLUMNS(B13:E17)
Fig. 1 COLUMNS function
The function will return the number of columns in this range (Fig. 2)
Fig. 2 Number of columns
You can also create a horizontal sequence of numbers. You just have to write the COLUMNS function and create a proper range, let’s say B13 to B13. In this case, it is important to lock one of the cells with a $ sign by pressing the F4 key so that it doesn’t move (Fig. 3)
=COLUMNS($B$13:B13)
Fig. 3 COLUMNS function with a range
After entering the formula and copying to the right, we can see that our range expands, i.e. one cell stays the same, and the other one is moving to the right (Fig. 4).
When you want to count the numbers of rows in a range, you can use the ROWS function. This function looks only at rows in a given range, and in the end, it returns the numbers of rows from the selected range.
ROWS function, how many rows in a range
=ROWS(B11:C16)
Fig. 1 ROWS function for a given range
The ROWS function allows you also to create a sequence of numbers. We just have to select a proper range (from cell C11 to C11) (Fig. 2), but we have to lock the first C11 cell with a $ sign.
=ROWS($C$11:C11)
Fig. 2 Locking the cell
Then, we copy the formula down. We can see that our range keeps expanding because the first cell stayed the same, and the second cell keeps moving down (Fig. 3)
Today, we are going to talk about copying cell formatting from one cell to another. We just have to click a given cell, then go to Home tab, press the Format Painter command and click on the cell to which you want to copy the formatting (Fig. 1).
Format Painter — How to copy cell formattingFig. 1 Copying from one cell to another
The Format Painter can also copy the formatting from more than one cell. We just select the cells, then click on the Format Painter, then select the range to which you want to copy the formatting, and enter it (Fig. 2).
Fig. 2 Copying to more that one cell.
When you select a cell range and you double-click the Format Painter command, the copying process is going until you press the Esc key. You can even go to other sheets, and copy the formatting there.
Sometimes, when we have formula results, we want to have only the text, i.e. the answer (Fig. 1).
Paste as values, the Paste Special windowFig. 1 — TRIM function results
We can copy the results, then right click a target cell and choose the Paste Values option from the pop-up menu (Fig. 2).
Fig. 2 — Pop-up menu — Paste values
And, we have just text not formula (Fig. 3).
Fig. 3 — Pasted values (text)
There is one more solution. When we copy our formula, we click on a target cell and press Alt + Ctrl + V combination to show the Paste Special window (Fig. 4). In the Paste area, we click Values, in the Operation area, we choose None, then we press OK.
Fig. 4 — Paste Special window
Now, we have the results only once. We don’t need the extra column, so we must delete it (Fig. 5)
Today, we are going to remove unnecessary spaces. Sometimes, there’s a space at the beginning of text, sometimes, there are more than one space between two words, and sometimes, there are some spaces at the end. The last ones are the hardest to notice (Fig. 1)
Remove unnecessary spaces — TRIM functionFig. 1 Unnecessary spaces
The name in the first line, Brave Brandon with a space at the end, is something different for Excel, than Brave Brandon without a space. In such a case, we want to use the TRIM function to remove the space (Fig. 2)
Fig. 2 TRIM function
After copying it down, we have our names without unnecessary spaces (Fig. 3)
Fig. 3 No unnecessary spaces
This simple function removes unnecessary spaces from the beginning, the end, and the middle of text beetween words only leaves one space.