Reverse the two words in MS Excel

Here we want to change Shivani Kumar to Kumar Shivani. It can be very useful when you want to change the data of First Name Last Name to Last Name First or vice versa.

Now lets take look at few basic ideas about develop

1. Right(text, number of characters) function returns number of character of text starting from right. Example: Right(“Shivani Kumar”, 5) will return Kumar as a result
2. Left(Text, number of characters) function returns number of characters of text starting from left. Example: Left(“Shivani Kumar”, 7) will return Shivani as a result
3. Length on any text can be determine using LEN(text) function.Example : Len(“Shivani Kumar”) will return 13 as a result.
4. Now the challenge is to find position of space in the text which can be done using Find(what to find, from where) function. Example : Find(“ “, “Shivani Kumar”) will return 8.

Now, we will combine the entire all the formulas to return reverse of two words.

Formula: =RIGHT(A3,LEN(A3)-FIND(" ",A3))&" "&LEFT(A3,FIND(" ",A3)-1)

Here, we have learned and used three function viz LEFT(), RIGHT(), FIND(), LEN() which are basic. However, combining all together is an advance step.

We assure you knowledge, not SPAM!

Read more on this article...

Shortcuts to select Column(s) or Row(s)

I have impressed many in my team by using shortcuts in MS Excel and trust me this is an excellent trick. Also, you cannot remember all the shortcuts by reading them. The best way to learn shortcuts is learn one at a time and make sure that you bring that in practice.

Press Ctrl + Space key to select the entire column

Press Shift + Space key to select the entire row

Remember you can expand your selection by pressing shift key and arrows keys. A

After consciously trying few times, you will master this shortcut and whenever you need to select a column or row you will press Ctrl+Space and Shift + Space.

Subscribe via email, its free.

We assure you knowledge, not SPAM!

Read more on this article...

Find Day name in Excel

During this post we will try to find out methods to find Enclosed below are Four ways to find day name in Excel. Last one is easiest and very useful. During first way we will also attempt to write a VBA code which is a user define function.

1. Using VBA/User define function.

Public Function DayName(iDate As Date) As String

Day (iDate)

Select Case Weekday(iDate)

Case 1

DayName = "Sunday"

Case 2

DayName = "Monday"

Case 3

DayName = "Tuesday"

Case 4

DayName = "Wednesday"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

Enclosed at the bottom of post is example file with all three methods. To view code of user define function, press Alt + F11 key.

2. Using Lookup function.

Please look at the image below

Formula : = LOOKUP(WEEKDAY(A2), {1,2,3,4,5,6,7},{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"})

2. Using Choose function.

Please look at the image below

 

Formula : =CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

2. Using Text function.

Please look at the image below

Formula: = Text(A2, "DDDD")

Note: this is one method suggested to me via comment. Hence I always appreciate comments. Also, you can subscribe via email.

Click here to download example

We assure you knowledge, not SPAM!

Read more on this article...

Offset Function MS Excel

Offset Function and its uses

In the simple words, OFFSET function returns the value in cell which is certain number of rows and column aways from base(reference cell) cell. Also, it can provide you with range when used with other functions like sum, avg, etc.

Offset require the following parameter

1. Reference:  Reference is place where you with to place your Offset Origin
   
2. Row:  Row from where you start  the Offset
   
3. Col : Column from where you start the Offset
   
4. Height:  Height of the range (which will be an numeric)
   
5. Weight : Weight of the range
   
6. Now lets look at the following example which describe the Offset function.
   
7. In the following example offset is starting at cell C2,
   
8. Referece = C2
   
9. Row= 1  which means next row to C2 ->C3
   
10. Column =-1 which means the one column before C3 which is B3
    
11. Since we are only using Offset  to return value we will provide height as 1.
    

Height parameter is useful when we want range to be returned as a reference.  Like when you want to provide dynamic range to SUM function or Data Validation.

Please download the example to see examples on dynamic range.

Offset Function

We would like to see your comments about this post. Also, you can receive email whenever we update. Just CLICK HERE 

We assure you knowledge, not SPAM!

Read more on this article...

Contact Me

Please feel free to write to us your feedback. Also, write to us about topic in MS Excel that you like us to post on this blog.

Your Name
Your Email Address
Subject
Message	form scripts
Image Verification Please enter the text from the image [ Refresh Image ] [ What's This? ]

Note: We will only reply to those email addresses which are subscribed to this blog. If you have not subscribed than Click here Read more on this article...

Now, Submit your MS Excel tips

Now here is chance to share your knowledge with other users across world. Also, along with your MS Excel tip you can leave link to your web site/blog. We appreciate your help.

Your Name
Your Email Address
Subject
File Attachment
Message	captcha
Image Verification Please enter the text from the image [ Refresh Image ] [ What's This? ]

Note: Please do not use this form to ask queries. Read more on this article...

Uses of Indirect Function in Excel

Here, we are trying to explore the help on INDIRECT function in MS Excel. The MS Excel help example listed below is free to download. Please use that as it will help in clarifying your doubts indirect function

1. Indirect() basically needs the reference like A4
2. Suppose if you try =Indirect(“A4”) in the cell B2 then it will return value present in A4
   Also, I have enclosed the excel with example with application of indirect formula. I am sure this example would help you in using this formula on the job scenario.

Click here to download Example

We assure you knowledge, not SPAM!

Read more on this article...

Shade alternate rows using conditional Formatting

This post is very useful when you want to make your reports look really professional without spending lot of time. There are many templates provided by Microsoft which has alternate shaded rows.

MS Office 2003:

1. Select all the row and columns using CTRL + A keys on keyboard or by clicking on top left hand side corner.
2. Click on Format Menu -> Select Condition Formatting.
3. From the drop down, select the option Formula.
4. Enter the Formula enter =MOD(ROW(),2)
5. Row() function returns the number of row 1, 2, 3 and so on and Mod function basically return you the remainder after the division by 2

• Mod ( 1 ,2 ) will return 1
• Mod(2,2) will return 0
• Mod (3,2) will return 1

6. Click on format button
7. Select the patterns tab and select the suitable color
8. Click on Ok and Ok.

In the above example, we learned two functions ROW() and MOD(). In future, I will post more functions which will use these functions in combination with other functions.

Also, do post your comments about this post and CLICK HERE to see updates on your email.

Read more on this article...

Advertise with us

We got page rank 2 in  Google and we constantly work on content of our blog to provide user friendly help in MS Excel. We have around 4500 visits each month and 5500 page views. If you wish to advertise here, please select the section of page where you want to put your advertisement and proceed with payment. We advertise in 125 X 125 pixel square ads only. Also, we will put on your ads as soon as payment to my account and ad image is received in.  For any other offer, please feel free to write me.

Banner Type

Sidebar $5.00 Middle Square $2.00 Top $10.00

Read more on this article...

Privacy Policy for findsarfaraz.blogspot.com

If you require any more information or have any questions about our privacy policy, please feel free to contact us by email at findsarfaraz@gmail.com.
At findsarfaraz.blogspot.com, the privacy of our visitors is of extreme importance to us. This privacy policy document outlines the types of personal information is received and collected by findsarfaraz.blogspot.com and how it is used.
Log Files
Like many other Web sites, findsarfaraz.blogspot.com makes use of log files. The information inside the log files includes internet protocol ( IP ) addresses, type of browser, Internet Service Provider ( ISP ), date/time stamp, referring/exit pages, and number of clicks to analyze trends, administer the site, track user’s movement around the site, and gather demographic information. IP addresses, and other such information are not linked to any information that is personally identifiable.
Cookies and Web Beacons
findsarfaraz.blogspot.com does use cookies to store information about visitors preferences, record user-specific information on which pages the user access or visit, customize Web page content based on visitors browser type or other information that the visitor sends via their browser.
DoubleClick DART Cookie
.:: Google, as a third party vendor, uses cookies to serve ads on findsarfaraz.blogspot.com.
.:: Google's use of the DART cookie enables it to serve ads to users based on their visit to findsarfaraz.blogspot.com and other sites on the Internet.
.:: Users may opt out of the use of the DART cookie by visiting the Google ad and content network privacy policy at the following URL - http://www.google.com/privacy_ads.html
Some of our advertising partners may use cookies and web beacons on our site. Our advertising partners include ....
Google Adsense
These third-party ad servers or ad networks use technology to the advertisements and links that appear on findsarfaraz.blogspot.com send directly to your browsers. They automatically receive your IP address when this occurs. Other technologies ( such as cookies, JavaScript, or Web Beacons ) may also be used by the third-party ad networks to measure the effectiveness of their advertisements and / or to personalize the advertising content that you see.
findsarfaraz.blogspot.com has no access to or control over these cookies that are used by third-party advertisers.
You should consult the respective privacy policies of these third-party ad servers for more detailed information on their practices as well as for instructions about how to opt-out of certain practices. findsarfaraz.blogspot.com's privacy policy does not apply to, and we cannot control the activities of, such other advertisers or web sites.
If you wish to disable cookies, you may do so through your individual browser options. More detailed information about cookie management with specific web browsers can be found at the browsers' respective websites.

Read more on this article...