Remove space from Excel Cell

Hello friends,

                   today I am going to tell a small but useful trick to remove space from Excel

Formula name is TRIM and this is very useful formula because in some cases a little space can make you in a big dilemma, formula will not work if there is any space is in the cell, so that remove the space we use TRIM function, and with the help of this formula it will be easy to get the result by putting the formula using TRIM function with that.

So this is a simple trick to remove space and we will discuss some more function to remove space from excel in the coming tutorial.

Goodbye for now.

Narendra

Format Painter in Excel

Hello,
             Today we will discussed about Formatt Painter in excel. This is the most important topic in excel. Formatting has so many features in excel that it is not easy to discuss in a single topic or single day, it will take much time to be discussed. Some feature like formatting numbers, font, color, table, cell, row, column, border, alignment etc.

We will discuss here one by one and with screen shot, so that it would be easy to understand the exact matter. Suppose that there is a cell in excel  and want to maximise the size of the that cell font so what to do,  simply put the cursor on that cell and go to Home tab and increase the font size by dragging down 12 to 16 and the font size will be increased automatically. Like in the following image

                          Image 1                                                       Image 2

                     

  In the previous image 1 you can see that cell B3 has value 3005 and the font size is 11 but in the next image 2 you can see that the font size is 16 and cell B3 size is bigger than previous one, so this is one part of formatting cell by increasing size of cell and same can be done by the decreasing size of cell. By the help of below mentioned image we can understand the several use of formatting function. Here we can see the buttons and its use in excel.

 

In the below given image we can understand "Format Painting" in a better way, In the below image we can see that in cell "A1" we have a $ sign value but in the another columns "C" and "D" we have the value does not containing any $ sign , so for converting these value also in the $ sign format, simply follow the step,

Click on cell "A1" and go to "Format Painter" button and click on "Format Painter" button red circled in the image and once the cell is copied, take the cursor on cell "C1" and drag it up to "D2" 

And once you done this you will get the following result. As we are seeing that the range "C2:D3" has been changed as the same as cell "A2". This is the one use of format painter button.

Now we will learn another way of using "Format Painter" by below given example :-

Here in this image, we can see that there is only one cell has $ sign format and we want to convert all the range into $ sign format. So we will follow the same procedure but in a different way, 

Simply put the cursor or mouse in cell "A1" and  drag it up to end of the row i.e. cell "A7" and leave the mouse. See the image below :- 

And once you leave the mouse you will get the following result : -

Here we can see that all the cell value have been converted into the same format as cell "A1" but also the change that all the cell has the same value as cell "A1" has ($10.00). As we are seeing that in the above given image there is a small drop down option in the below right side of the series, simply click on this drop down arrow button, you will get the above mention option , choose the third option "Fill Formatting Only" option and click on it you will get the desired result.

See the image below ;-

In the above given image we can see that all the value are come back with the desired format.

Formatting to an Entire Column or Row

Now there is another way of using "Format Painter", situation is, we have a cell and we want to convert entire column in the same format. See the  example :-

In the above image we can see that, we want to convert the entire column "C" as same format as  "A1" has. 

So first copy cell "A1" by press "Ctrl + C" copy command and then select the entire data range or column, where we have to change the format and then : -

 "Right Click"-->"Paste Special" --> "Formats--> "OK"

See the image below ;-

Click on the "Paste Special" option, see the image below :-

Click on the "Formats" button then click "OK". And once press "OK" button you will get the desired result as mentioned in the image below :-

Here in this image you can see that the selected values have been converted in the desired format.

So in this tutorial we learned that with the help of "Format Painter' and another option to change the format of the data and get the desired result.

Best of luck

NarendrasExcelTips.blogspot.com

Knowledge of Excel

Hello friends,
                        I hope that things are going well and you guys are going on a right tract. In my previous posts I was writing about Excel Count function but now I realize that if I will share Excel contents from start to end then it will be very helpful to you.

Like what is a workbook, worksheets, formula bar, cell, row, columns etc.

Workbook :- A Microsoft Office Excel workbook  is basically a file on that we do our work, simply if we are working any job in the excel and  if we save it in the computer with a file name that file name will be called a workbook.

Worksheet:-  A Microsoft Office Excel worksheet is basically a sheet of any workbook, simply if we are working in any workbook (i.e any excel file) and in that file we need to add another sheet, so that sheet will be called a worksheet. For example sheet1, sheet2, sheet3 etc.

Formula Bar:-  A Microsoft Office Excel formula bar  is basically, if we type any formula in any cell the formula will be displayed in the formula bar also, if any cell is not displaying any formula but on the formula bar it will display that formula like in below mentioned example.

Cell              :-  A Microsoft Office Excel  a cell is the intersection of a column and a row.


Row             :-  In Microsoft Office Excel  rows are horizontal row like below mentioned example sl no. 1 2 3 4 5 6 7 8 9 are all  rows

Column       :- In Microsoft Office Excel column are the vertical portion of column and these column's name are written like A B C D E F G H etc.

So friends I hope you understand the small but important things in a better way. This post is not over, it will be continuous in the further future blogs.

Thanks and Goodbye for now.

Narendra

COUNTIFS Function in Excel

Hello friends,

                 Today I am going to post another useful function of count, i.e. Countifs

It will be available in office 2007 and later version.

With the above mentioned example it will be easy to understand that how can we use this function:

In this example we have to search for how many Pen are there in the column A1:A10 and the formula  will be as follows :-

=COUNTIF(A2:A10,"Pen")

But if condition is as follows :-
i.e. Pen having quantity more than 10 nos. then the formula will be as follows

Pen

>10

=COUNTIFS(A2:A10,"Pen",B2:B10,">=10")

I think now it will be easy to understand the use of countifs function.

My next post will be the same on countifs function again with more example.

Thanks and have a nice day!

Narendra

COUNTIF with Wildcard

Hello friends,

                     today I will tell something about Countif with Wildcard, wildcard means result with keeping some character ( example "Pen") in the table or column.  In the below mentioned example we can see that in the cell A 13 the total nos. of pen count is 6, but if we count in the column A then there are only 3 nos. of Pen in the whole column. How this happened, little bit trick is the formula containing, "*", which belong some wild character in the column, i.e. how many items are carrying pen character in the whole column, i.e. simply Pen, Pencil, Gel Pen, all the words having Pen common, so countif with wild character formula count the words having a unique words in the table.

 Simply how many words in the column A3 : A11 having the character in column B13  i.e. Pen so simply 6 nos. of words have Pen character in it.

                                 

1	A	B
2	Item	Qty
3	Pen	5
4	Pencil	9
5	Binder	9
6	Pen	6
7	Gel Pen	12
8	Binder	12
9	Pencil	10
10	Binder	6
11	Pen	11
12
13	6	pen

=COUNTIF(A3:A11,"*"&B13&"*")

This is all for today and next day we will discuss a new topic.

Thanks and goodbye for now

Narendra

Count Blank function

Hello friends,

  

                     today I am going to post a new excel function which is called Countblank function, with below mention example :

This function count only blank cell in the given data in columns or rows as mention in the above example.

Thanking agian

Narendra

Count function no. 3

Hello friends,

                         today we are going to discussed about a new function of Count, i.e CountA.

CountA always count including the text characters also in the given data, just like in the below  mentioned   example :-

Tomorrow we will discuss a new formula.

Take care

Narendra