Validate Excel Cell Input

You are doing many critical calculations in a single excel spreadsheet in our daily work, but what if you entered invalid data into the sheet? All the formulas entered will through some error. So can you really validate user inputs in excel?

Yes, you can!! This post demonstrates how to restrict incorrect inputs into a particular column. Here I will demonstrate how to validate a column where you can enter only Numeric values. Just follow these simple steps:

Press ALT + F11 key combination to open Visual Basic Editor >> double click on the Sheet you want to validate from the Project Window. Paste the below VBA code and save the spreadsheet and you are done with the requirement.

Private Sub Worksheet_Change(ByVal Target As Range)

 'do nothing if multiple cells edited

 If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

With Target

If .Row > 1 Then

If IsNumeric(Cells(.Row, 1)) = False Then

MsgBox "Not a valid integer value", vbInformation

End If

End If

End With

End Sub

Isn’t it interesting?

I am not allowed to attach excel files in my posts or else I could have provided you the sample macro for this. Please leave your valuable comment here.

Saving Excel Spreadsheet Data in XML

This post demonstrates how to convert excel spreadsheet data into XML. Why do you want to convert the excel data to XML? Well we have our data in excel spreadsheet and we want to show them into flash or our web.  As we have the data with us it will be unwise to reenter the data into XML manually. This post will help you to do that in excel.

Let’s discuss with the detailed steps. Below is the data in excel spreadsheet as in Figure – 1, which you want to convert into XML.

Figure – 1

 

Step-1: Create a XML schema first as in Figure – 2

 Figure – 2

 

Step-2: Once you created the schema just save it and import it into Excel as bellow:

Open a new excel spreadsheet è then go to Developer Tab è in the XML section click on Source (This will open up a XML Source Window on the right side of Excel application) è click on XML Maps è Add è Choose the XML schema file you created above èOK.

This will look something like Figure – 3 below:

Figure – 3

 

Step-3: Once this is done you just drag and drop the parent node to the spreadsheet. Then copy the data and paste it into the mapped sheet as in Figure – 4

Figure – 4

 

Step-4: Finally save the sheet as a “XML Data” file as in Figure – 5

 

Figure – 5

Now you can use the XML file created with all the data in XML format.

Please send your comments.

Smart Excel Keyboard Shortcuts

Here are some frequently used keyboard shortcuts to speedup the daily work.

Key Combination	Purpose
SHIFT+F11 or ALT+SHIFT+F1	To Insert a new worksheet
CTRL+SHIFT+PLUS SIGN	To Insert blank cells
F11 or ALT+F1	To Create a chart that uses the current range
ALT+DOWN ARROW	To Display the AutoComplete list
CTRL+; (semicolon)	To Enter the current date
CTRL+SHIFT+: (colon)	To Enter the current time
CTRL+ENTER	To Fill the selected cell range with the current entry
SHIFT+F5	To Display the Find dialog box
SHIFT+F4	To Repeat the last Find action
F5	To Display the Go To dialog box
CTRL+1	To Display the Format Cells dialog box

 

Work smart !!! enjoy the day…

Please leave your valuable comments.

My First Post

Just to test things…

Need your valuable comments all the time…

Thank You

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!