3 Useful Excel Tips For Dummies – How-To

All of us who use computer at a point of time come across a situation to use Excel due to its efficient way of handling data. Listed are few excel tips for beginners who starts working with excel.

For Newbies :

What is Excel?

Excel is an electronic spreadsheet program used for storing, organizing and manipulating data.

Tip 1 : Wrap the Text

Wrapping a text feature helps to make the content visible in a cell without increasing the width of the cell.

Step 1: Select the text to be wrapped.

excel word wrap

Step 2:  Click Wrap text button (as shown in the image)

Continue reading

How to make all content visible in a cell – Wrap Text Microsoft Excel

When we type some long text in a excel column it would overlap the other columns.To avoid this and make the full content visible there is a  Microsoft office excel tip.

UnWarpped Text
Select the cell(s) to wrap.
ToWarp
click on Home Tab Wrap Text.
We can also see the Microsoft help describing the Wrap Text feature in excel.
WarppedText
That’s it.Now the cell is fully visible in the Excel 2007 file.

How to Convert a Rows to Columns – Columns to Rows in Excel

There are times we need to convert excel rows to columns.Excel inbuilt has a option to do this in easy steps.

Rows to Columns

Step 1 : Select the all rows to be converted as columns and copy them.

Copy rows in excel

Step 2 : Click in any empty cell and right click and select  Paste Special option from it.

Paste-Special

Step 3 : A popup could come, from it check Transpose.

Transpose

That’s it.See the result.

Rows to Column

The same steps applies for converting columns to rows.

Creating Excel as Datasource for Mail Merge using C#

We can use excel as datasource for mail merge.We have to write the header and records in excel file which will act as the datasource for word mail merge.

The first row of excel will be treated as the header in the excel.

while using word as datasource and create a the word datasource we get an error “String longer than 255 characters ” when the concatenated fields string length increases 255. To overcome we can use excel as datasource.

Download Zip

Coding :

//Creating Excel file
private void CreateMailMergeExcelDataFile()
{
try
{
string[] fileds,record1;
 
Object oName = "C:\\TempDoc.xls";
string strHeader = "FirstName, LastName, Address, CityStateZip";
string strRecord1 = "John,Roy,31 New street,320009";
 
MSExcel.Application excelapp;
MSExcel.Workbook excelwrbook;
 
excelapp = new Microsoft.Office.Interop.Excel.Application();
excelapp.Visible = true;
MSExcel.Worksheet ws = new MSExcel.WorksheetClass();
 
excelwrbook = excelapp.Workbooks.Add(objMissing);
 
ws = (MSExcel.Worksheet)excelapp.ActiveWorkbook.ActiveSheet;
 
fileds = strHeader.Split(',');
record1 = strRecord1.Split(',');
//writing in excel you Can use datatable and Get the records and loop.here for
sample i have writing keeping two strings 
 
for (int i = 0; i < j =" 0;" style="color: rgb(0, 102, 0);"  
 //saving the excel workbook
excelwrbook.SaveAs(oName, 
MSExcel.XlFileFormat.xlTemplate, objMissing, 
objMissing, objMissing,objMissing, 
MSExcel.XlSaveAsAccessMode.xlExclusive,
objMissing, objMissing, objMissing, 
objMissing, objMissing);
excelapp.Quit();
 
//opening the excel to act as a datasource for word mail merge
wrdDoc.MailMerge.OpenDataSource("C:\\TempDoc.xls", 
ref objMissing, ref objMissing, ref objMissing, ref objMissing, 
ref objMissing,ref objMissing, ref objMissing, 
ref objMissing,ref objMissing, ref objMissing,
ref objMissing, ref oQuery,ref objMissing, ref objMissing,
ref objMissing);
 
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
}
 
private void button1_Click(object sender, System.EventArgs e)
{
try
{
Word.Selection wrdSelection;
Word.MailMerge wrdMailMerge;
Word.MailMergeFields wrdMergeFields;
Word.Table wrdTable;
string StrToAdd;
 
wrdApp = new Word.Application();
wrdApp.Visible = false;
 
// Add a new document.
wrdDoc = wrdApp.Documents.Add(ref objMissing, ref objMissing,
ref objMissing, ref objMissing);
wrdDoc.Select();
 
wrdSelection = wrdApp.Selection;
wrdMailMerge = wrdDoc.MailMerge;
 
// Create a MailMerge Data file using excel 
CreateMailMergeExcelDataFile();
// Create a string and insert it into the document.
StrToAdd = "Mail Merge";
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphCenter;
wrdSelection.TypeText(StrToAdd);
 
InsertLines(2);
 
// Insert merge data.
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphLeft;
wrdMergeFields = wrdMailMerge.Fields;
wrdMergeFields.Add(wrdSelection.Range, "FirstName");
wrdSelection.TypeText(" ");
wrdMergeFields.Add(wrdSelection.Range, "LastName");
wrdSelection.TypeParagraph();
 
wrdMergeFields.Add(wrdSelection.Range, "Address");
wrdSelection.TypeParagraph();
wrdMergeFields.Add(wrdSelection.Range, "CityStateZip");
 
InsertLines(2);
 
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphRight;
 
Object objDate = "dddd, MMMM dd, yyyy";
wrdSelection.InsertDateTime(ref objDate, ref oFalse, ref objMissing,
ref objMissing, ref objMissing);
 
InsertLines(2);
 
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphJustify;
 
wrdSelection.TypeText("Dear ");
wrdMergeFields.Add(wrdSelection.Range, "FirstName");
wrdSelection.TypeText(",");
InsertLines(1);
 
StrToAdd = "Thank you for using Mail Merge.";
wrdSelection.TypeText(StrToAdd);
// Perform mail merge.
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref oFalse);
 
// Close the original form document.
wrdDoc.Saved = true;
wrdDoc.Close(ref oFalse, ref objMissing, ref objMissing);
 
 // Makes the merged doc visible
wrdApp.Visible = true;
 
// Release References.
wrdSelection = null;
wrdMailMerge = null;
wrdMergeFields = null;
wrdDoc = null;
wrdApp = null;
 
// Clean up temp file.
System.IO.File.Delete("C:\\TempDoc.xls");
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
}