COLUMNS function — how many columns in a range

When you want to count the num­ber of columns in a range, you can use the COLUMNS func­tion and select a prop­er range (Fig. 1)

COLUMNS func­tion — how many columns in a range

=COLUMNS(B13:E17)

Fig. 1 COLUMNS function

The func­tion will return the num­ber of columns in this range (Fig. 2)

Number of columns
Fig. 2 Num­ber of columns

You can also cre­ate a hor­i­zon­tal sequence of num­bers. You just have to write the COLUMNS func­tion and cre­ate a prop­er range, let’s say B13 to B13. In this case, it is impor­tant to lock one of the cells with a $ sign by press­ing the F4 key so that it does­n’t move (Fig. 3)

=COLUMNS($B$13:B13)

COLUMNS function with a range
Fig. 3 COLUMNS func­tion with a range

After enter­ing the for­mu­la and copy­ing to the right, we can see that our range expands, i.e. one cell stays the same, and the oth­er one is mov­ing to the right (Fig. 4).

Formula changes
Fig. 4 For­mu­la changes

https://www.youtube.com/shorts/mQRaB86awNQ

ROWS function, how many rows in a range

When you want to count the num­bers of rows in a range, you can use the ROWS func­tion. This func­tion looks only at rows in a giv­en range, and in the end, it returns the num­bers of rows from the select­ed range. 

ROWS func­tion, how many rows in a range

=ROWS(B11:C16)

ROWS function for a given range
Fig. 1 ROWS func­tion for a giv­en range

The ROWS func­tion allows you also to cre­ate a sequence of num­bers. We just have to select a prop­er range (from cell C11 to C11) (Fig. 2), but we have to lock the first C11 cell with a $ sign.

=ROWS($C$11:C11)

Locking the cell
Fig. 2 Lock­ing the cell

Then, we copy the for­mu­la down. We can see that our range keeps expand­ing because the first cell stayed the same, and the sec­ond cell keeps mov­ing down (Fig. 3)

Expanding range
Fig. 3 Expand­ing range

https://www.youtube.com/shorts/_VNXxiwFqbE

Format Painter — How to copy cell formatting

Today, we are going to talk about copy­ing cell for­mat­ting from one cell to anoth­er. We just have to click a giv­en cell, then go to Home tab, press the For­mat Painter com­mand and click on the cell to which you want to copy the for­mat­ting (Fig. 1).

For­mat Painter — How to copy cell formatting
Copying from one cell to another
Fig. 1 Copy­ing from one cell to another

The For­mat Painter can also copy the for­mat­ting from more than one cell. We just select the cells, then click on the For­mat Painter, then select the range to which you want to copy the for­mat­ting, and enter it (Fig. 2).

Copying to more that one cell.
Fig. 2 Copy­ing to more that one cell. 

When you select a cell range and you dou­ble-click the For­mat Painter com­mand, the copy­ing process is going until you press the Esc key. You can even go to oth­er sheets, and copy the for­mat­ting there. 

https://www.youtube.com/shorts/vhBlkQYJEbk

Paste as values, the Paste Special window

Some­times, when we have for­mu­la results, we want to have only the text, i.e. the answer (Fig. 1). 

Paste as val­ues, the Paste Spe­cial window
TRIM function results
Fig. 1 — TRIM func­tion results

We can copy the results, then right click a tar­get cell and choose the Paste Val­ues option from the pop-up menu (Fig. 2). 

Fig. 2 — Pop-up menu — Paste values

And, we have just text not for­mu­la (Fig. 3).

Pasted values (text)
Fig. 3 — Past­ed val­ues (text)

There is one more solu­tion. When we copy our for­mu­la, we click on a tar­get cell and press Alt + Ctrl + V com­bi­na­tion to show the Paste Spe­cial win­dow (Fig. 4). In the Paste area, we click Val­ues, in the Oper­a­tion area, we choose None, then we press OK. 

Paste Special window
Fig. 4 — Paste Spe­cial window

Now, we have the results only once. We don’t need the extra col­umn, so we must delete it (Fig. 5) 

Deleting column
Fig. 5 — Delet­ing column

https://www.youtube.com/shorts/3UD7n8Jtu3I

Remove unnecessary spaces — TRIM function

Today, we are going to remove unnec­es­sary spaces. Some­times, there’s a space at the begin­ning of text, some­times, there are more than one space between two words, and some­times, there are some spaces at the end. The last ones are the hard­est to notice (Fig. 1)

Remove unnec­es­sary spaces — TRIM function
Unnecessary spaces
Fig. 1 Unnec­es­sary spaces

The name in the first line, Brave Bran­don with a space at the end, is some­thing dif­fer­ent for Excel, than Brave Bran­don with­out a space. In such a case, we want to use the TRIM func­tion to remove the space (Fig. 2)

Trim function
Fig. 2 TRIM function

After copy­ing it down, we have our names with­out unnec­es­sary spaces (Fig. 3)

No unnecessary spaces
Fig. 3 No unnec­es­sary spaces

This sim­ple func­tion removes unnec­es­sary spaces from the begin­ning, the end, and the mid­dle of text beetween words only leaves one space. 

https://www.youtube.com/shorts/Pe8z-PthW6Q