To remove empty rows between data

There are many ways to remove empty rows from table. One way I prefer is by moving all empty rows to bottom by using sort.

To remove empty rows between data:

1. Select all columns containing data by pressing Ctrl+Shift+End.

2. Click the Sort icon under Data menu(Office 2003 or below) (either Ascending or Descending).

As the purpose of this blog is to train/help all MS Excel users. You will find all the examples from basic to advance. Thanks for spending time in reading this post. Do leave valuable comments

 

Subscription is free here. Click here to subscribe

Read more on this article...

Find how many times a particular character repeats in a cell.

LEN function is used to find the total length of text and Substitute function is used to replace one letter with some other letter on all instance.  Like If the cell A1 contains text 'Safaraz Ahmed' and I want to find out how many times character 'a' is repeat in cell A1.

 

1. Type Sarfaraz Ahmed in cell A1

2. Type a in the cell A2.

Click on image below to view Enlarge

 

 

3. Enter the following formula in A3: =  LEN(A1)-LEN(SUBSTITUTE(A1,A2,""))

SUBSTITUTE(A1, A2,"") will replace 'a' with '' from Sarfaraz ahmed to return Srfrz hmed

which is of length 10 as it also include space.

4. LEN(A1) returns the length text including 'a' which is 14.

5. LEN(A1)-LEN(SUBSTITUTE(A1,A2,""))  returns 4.

Kindly provide your valuable comments.

Read more on this article...

Find and replace ?

Find and replace is very common thing we can expect from any application. However, certain special character can't be replaced like question mark '?'. But this can be very well tricked by using Replace function.

1.  When you want use find & replace to remove ? from the string you will notice that all the next old text will be replaced by new text.

2.  The following formula will replace ?(question mark) from the string.

3.  Remember only one ? mark.

Formula is mentioned below:

=REPLACE(A2,FIND("?",A2,1),1,"")

 

Subscribe via email to receive more trick right in your inbox.

 

We assure you knowledge, not SPAM!

Read more on this article...

Import data from website to MS Excel

Many times we don't want to go to website again and again to check if content is updated. Alternative is to import such data to MS Excel. This post will help you in understanding how to import data to MS Excel. Please carefully read the instruction below.

To import and refresh information from a Web site:

1. Open MS Excel XP/ 2003, and click on the Data menu, click Import External Data, and then New Web Query.
2. In the Address box of the New Web Query dialog box, type or paste the address of the Internet site which you want to see on your excel sheet

For example, open the site www.bloomberg.com, which includes a table of various currency exchange rates. The address of the page containing the table of currency exchange rates is http://www.bloomberg.com/markets/index.html.
3. In the dialog box, notice the Web page. Click the small arrow in the upper left-hand corner of the table to select only the data table.
4. Click Import.
5. To save the query, click Properties in the Import Data dialog box.
6. In the External Data Range Properties dialog box, enter the query name in the Name box, select the Save Query definition checkbox, and then click OK.
7. In the Import Data dialog box, click OK to import.

To refresh the Internet data

1. Select the cell in the sheet containing the data.
2. From the Data menu, select Refresh Data.
OR
Display the External Data toolbar and click the Refresh Data icon. To display the External Data toolbar, select one of the toolbars, right-click and select External Data, and click OK.

To automatically refresh the Internet data:

1. On the External Data toolbar, click the Data Range Properties icon.
2. Select the Refresh every option, and set the number of minutes between each refresh action.
3. Select the Refresh data on file open checkbox to automatically refresh the data when the file is opened.

Do let me know your comments and experience about using information provided in this post via comments. Also, subscribe via email to receive latest updates, add-ins and ebooks on MS Excel.

D

Read more on this article...

Check which CASE is used using Exact function

The formula is not very useful in practical MS Excel application. However, can be use to improve your knowledge about EXACT function.

Exact function basically compares the text from two cells for all aspects like content and case both. For Example, If cell A1 contains S and B1 contains s then the formula Exact (A1, B1) will return false.

Upper functions converts any case into Upper case.

Lower function converts any case into Lower case.

Proper function converts any case into Proper case (First letter of each word capital).

Formula:
=IF(EXACT(C9,UPPER(C9)),"UPPER CASE",IF(EXACT(C9,LOWER(C9)),"LOWER",(IF(EXACT(C9,PROPER(C9)),"PROPER","INCORRECT CASE"))))

In this trick,  IF Exact(C9, Upper(Exact(C9))  returns true only if C9 is in upper case and same goes other cases.

This formula may not be very useful in practical application. However, very useful in understanding Exact function.

Thank you for spending your valuable time in reading above post and if you like to read more such tricks subscribe to blog via email.

We assure you knowledge, not SPAM!

Read more on this article...

How to Remove Apostrophe

Suppose if column A contains an imported list of numbers. All these number will have an invisible apostrophe due to which MS Excel interprets such numbers as text. These numbers can’t be formatted as currency or they cannot be using in calculation as SUM and AVERAGE. From office XP onward you can recognize them by a triangular green color mark at the left side top of the cell.

We will have to convert them into values/numbers, so we can use them in formulas or format them as currency.

Please have a look at the steps below
Office XP or Above:
1. Select the enter column from which you wish to remove apostrophe. On the highlighted cell, you will see an exclamation mark .
2. Click on the exclamation mark and select Convert to Number from menu.

Office 2000:
You will have to use the VALUE function as shown in the following

Formula:

=VALUE(A1)

Subscribe here to receive more such tips. Your comments motivate us to post more.

We assure you knowledge, not SPAM!

Read more on this article...

Calculate Six Sigma in Excel

Sigma is calculated using error and number of opportunities. At times, people need different tool for calculating Sigma. At times, people even search in Google. I wish if you reach here and understand Sigma calculation once an and all in MS Excel.

1. The Six Sigma mainly consist defects/error and Total number of opportunities.
2. Please check the formula in the image below. For reference use, it's mentioned below.

Formula:

=IF(A2=0,6,IF(A2=B2,0,IF(B2=0, "DENOMINATOR CANNOT BE ZERO",ROUND((NORMSINV(1-A2/B2)+1.5),2))))

If you like this post, spend two minutes in leaving comments. To receive update over email do subscribe to us.

We assure you knowledge, not SPAM!

Read more on this article...

Separate Text based on SPACE

This post describe a scenario where you have list of first name and last name separated by space and you want to take only first name. In other words you have two word separated by text and you want result as only first part as result.

 

The following formula will separate the text part based on the position of space in the text.

You can see the formula in formula bar of image. Also the same is mentioned below

Formula

=LEFT(A2,FIND(" ",A2,1))

 

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

User Define function to Convert Number to Text

This function is something I wrote myself on request of one of my community member from my MS Excel community on Orkut.  Through this post  I am also attempting to teach blog readers how to move code to Visual Basic Editor for use.

The Function will convert 122 into One Hundred and Twenty Two
1. Press ALT + F11 to start Vb Editor
2. Insert Module
3. Copy paste the entire code into module
4. Check the function in the list of User Define Function.
e.g = N2T(a2)
Where a2 cell contain numeric value

'The below program is written to convert the number into text
'The logic in solely developed by Sarfaraz Ahmed and is not referred from any internet on internet or any book
'Hence, user in this forum can freely use the function.
'User are requested not to remove the comments

Dim fplace(0 To 20) As String
Dim splace(0 To 10) As String
Dim PlaceName(1 To 5) As String
Private Sub LoadValue()

fplace(0) = ""
fplace(1) = "One"
fplace(2) = "Two"
fplace(3) = "Three"
fplace(4) = "Four"
fplace(5) = "Five"
fplace(6) = "Six"
fplace(7) = "Seven"
fplace(8) = "Eight"
fplace(9) = "Nine"
fplace(10) = "Ten"
fplace(11) = "Eleven"
fplace(12) = "Twelve"
fplace(13) = "Thirteen"
fplace(14) = "Fourteen"
fplace(15) = "Fifteen"
fplace(16) = "Sixteen"
fplace(17) = "Seventeen"
fplace(18) = "Eighteen"
fplace(19) = "Nineteen"
fplace(20) = "Twenty"

splace(0) = ""
splace(1) = "Ten"
splace(2) = "Twenty"
splace(3) = "Thirty"
splace(4) = "Forty"
splace(5) = "Fifty"
splace(6) = "Sixty"
splace(7) = "Seventy"
splace(8) = "Eight"
splace(9) = "Ninety"
splace(10) = "Hundredth"

PlaceName(1) = "Hundred"
PlaceName(2) = "Thousand"
PlaceName(3) = "Lakh"
PlaceName(4) = "Crore"
End Sub

Function N2T(DbVal As Double) As String
LoadValue
Dim LFTPART As String
Dim RGTPART As String
Dim RGTLEN As Integer
Dim LFTLEN As Integer

If Len(Trim(DbVal)) < 3 Then
N2T = D2T(DbVal)
ElseIf Len(Trim(DbVal)) = 3 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 4 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 2, 1)) & " " & CheckZero(Mid(DbVal, 2, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 4 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 2, 1)) & " " & CheckZero(Mid(DbVal, 2, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 5 Then
N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 3, 1)) & " " & CheckZero(Mid(DbVal, 3, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 6 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Lakh") & " " & D2T(Mid(DbVal, 2, 2)) & " " & CheckZero(Mid(DbVal, 2, 2), "Thousand") & " " & D2T(Mid(DbVal, 4, 1)) & " " & CheckZero(Mid(DbVal, 4, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 7 Then
N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 2), "Lakh") & " " & D2T(Mid(DbVal, 3, 2)) & " " & CheckZero(Mid(DbVal, 3, 2), "Thousand") & " " & D2T(Mid(DbVal, 5, 1)) & " " & CheckZero(Mid(DbVal, 5, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 8 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Crore") & " " & D2T(Mid(DbVal, 2, 2)) & " " & CheckZero(Mid(DbVal, 2, 2), "Lakh") & " " & D2T(Mid(DbVal, 4, 2)) & " " & CheckZero(Mid(DbVal, 4, 2), "Thousand") & " " & D2T(Mid(DbVal, 6, 1)) & " " & CheckZero(Mid(DbVal, 6, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 9 Then
N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 2), "Crore") & " " & D2T(Mid(DbVal, 3, 2)) & " " & CheckZero(Mid(DbVal, 3, 2), "Lakh") & " " & D2T(Mid(DbVal, 5, 2)) & " " & CheckZero(Mid(DbVal, 5, 2), "Thousand") & " " & D2T(Mid(DbVal, 7, 1)) & " " & CheckZero(Mid(DbVal, 7, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
Else
N2T = "Value is too big, function support upto 9 digits only"
End If

End Function
Private Function D2T(intVal As Double) As String
If intVal > 20 Then
D2T = splace(Left(intVal, 1)) & " " & fplace(Right(intVal, 1))
Else
D2T = fplace(intVal)
End If
End Function

Private Function CheckZero(intVal As Double, PlaceName As String) As String
If intVal = 0 Then
CheckZero = ""
Else
CheckZero = PlaceName
End If

End Function

Read more on this article...

Classification Using Lookup function

Many times I came across MS Excel sheet where people use IF Then to classify grades. IF then is really big and confusing in this kind of scenarios. I would suggest you to use LOOKUP function to

1 The scenario is to classify values into Fail, Second Class, First Class, Distinction and Merit.

2. Refer to formula in the image.

3. This is an easy alternative to IF formula.

Formula : =LOOKUP(A2,{0,35,45,60,75,90,100},{"Fail","Pass Class","Second Class","First Class","Distinction","Merit"})

 

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

Filter by Color

MS Excel has come with great features like Filter by color is just one them.

1. Applicable in Office 2007

2. After you apply conditional formatting from the home Icon.

3. Apply filter by clicking on Filter under the Data icon.

4. Click Filter Cell (which is A1 in this case)

5. Go to filter by color option from drop down.

6. Select the color using which you want to filter.

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

Using IF and AND to Classify

This is a very common scenario that I have came across. This is first thing I did when I opened MS Excel in 2000. In fact this query is raised by one girl in my MS Excel community on Orkut.

The IF formula below classify based on the value A1.
Above 70 - ,"CONGRATS KEEP IT UP",
70 to 60 - "Good Work"
and so on

Formula:
=IF(A1>70,"CONGRATS KEEP IT UP",IF(AND(A1>60,A1<=70),"GOOD WORK",IF(AND(A1>45, A1<=60),"MUST WORK HARD","BAD")))

Read more on this article...

Check if Value is Present in the give RANGE

Creating a formula that will return TRUE if each number in column E appears at least once in the range A2:C6.

If any of the specified numbers are missing, the formula will return FALSE.

Solution: Use AND and COUNTIF functions in the following Array formula:{=AND(COUNTIF(A2:C6,E2:E10))}

Read more on this article...

Calculate difference in Time in MS Excel

To calculate an employee's working hours. Especially when shift start one days and ends on the other day. The calculation becomes tricky as time end time becomes less than start time and time can't be negative.

Formula: =D1-C1+IF(C1>D1,1)

Take a look at image below to understand the formula.

Click on image to view Enlarge

To view formula on MS Excel sheet download the Timesheet example through link below.

Timesheet download

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

VBA, Simple Macro Example

The purpose of creating this page is to help you understand how to declare a integer variable in VBA, assign value to it and compare it using IF THEN and show the result using using message box. However, in near future we will move step by step to more complex conditions.

1) Dim xValue As Integer -> Creates the integer variable which can hold numeric values and in case if you are not sure about the user input, please use variant type.
2) xValue = Sheet1.Cells(3, 4) -> This part of code is written under 'Private Sub CommandButton1_Click()' hence will only execute when someone clicks on the command button 1 which is placed on sheet1.
3) If xValue >= 18 Then MsgBox "You are major", vbInformation + vbOKOnly, "IF Then Macros"Else MsgBox "You are Minor", vbInformation + vbOKOnly, "IF Then Macros"End If
The above code check if the xValue is greater or equal to 18 and display the necessary output using the msgbox.
The link below has MS Excel workbook with above macro for download.

MS Excel Simple macro download

Note: To view the code, press ALF +F11.

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

Formula to find duplicate records

This is my first post of blog where I have just posted one image. Later while moving images from one place to another I update this with more details. The post is useful if you are using MS Excel 2003 or below as MS Excel 2007 has option to remove duplicates.

Here we will use countif to determine all unique as 1 and duplicate as 2 or more. Mentioned below is formula to be placed in first row. For details take a look at screenshot/image below.

Formula: =COUNTIF($A$2:A2,A2)

Click on image to view enlarge

Also, you can download file with find duplicate example.

Find duplicate Example

Please do let me know your views and comments about this post and subscribe to us to receive latest updates via email

We assure you knowledge, not SPAM!

Read more on this article...

Convert HH:MM:SS to Seconds without using Formula

Convert HH:MM:SS to Seconds without using FormulaConvert HH:MM:SS to Seconds without using FormulaThe idea of converting HH:MM:SS to seconds without using formula is useful if you have more than few columns/rows in HH:MM:SS form. Otherwise, formula is ideal way.I am Enclosing both method below.

Step 1: If A1 cell in sheet1 is containing value 00:12:04.Select A1 -> Press Ctrl + 1 -> Select Number tab-> Select Number from Category list-> Click on OK.


Step 2: Now type 86400 in any cell in Excel sheet. Copy that 86400 (86400 is number of seconds in a day) and use paste special (click value and multiply) over A1. It will return 724.Note: The above paste special can be done for multiple cell at one time. Hence,its an ideal way to save time.

USING formulaType: HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)in A2 cell. It will return 724 in A2. Please try the first method with any AVAYA report in HH:MM:SS form. I am sure it will definitely save your lot of time. Read more on this article...

Link Partner

Free web directory

Best Photoblog
Read more on this article...