Excel - Add and Remove Line Breaks in a Cell

Many time we struggle for the line break in a multiple excel cells. This article have some useful tricks that will help you to achive this easily.

1) Manully add line break in a cell using "Alt + Enter".
- Edit the cell with F2 or while typing in a cell when you press "Alt + Enter" it will create new line in a cell.
- To remove a line break, edit the cell with F2, go to the start of line break and press "Backspace" and enter.

2) Line break using formula:
- You can use line break character in CHAR(10) function. Here 10 is the line break character.
- example: CONCATENATE("Rakesh" , CHAR(10) , "Patel"), if you can't see the line break when you  press ENTER, then apply WRAP TEXT to the cell.

3) Replace "," or any other character with New Line:
- Press CTRL + H, this will open "Find and Replace" window. Under "Find what" you can put your text that you want to search. and under "Replace with" press CTRL + J (you won't able to see anything) just click on "Replace" or "Replace ALL". This will will add line break wherever it finds your text.
- The same way you can replace Line break with any of your text.

If "CTRL + J" does not work for you then you can also try Alt + type 0010. 

Calculated Birth Date To Age

There are number of way to calculate age in excel, below are the example to do calculation:

	A	B	C
1	8/18/1979
2

example 1:
=INT((today() - A1)/365)

example 2: Use DATEDIF() function
Note: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3, possible in some case this does not work.

Syntex : DATEDIF(start_date,end_date,unit)

unit :
"Y" : The number of complete years in the period.
"M" : The number of complete months in the period.
"D" : The number of days in the period.
"MD": The difference between the days in start_date and end_date. The months and years of the dates are ignored.
Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.
"YM": The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD": The difference between the days of start_date and end_date. The years of the dates are ignored.


Reference Link

=DATEDIF(A1,TODAY(),"Y") & " Years, " & DATEDIF(A1,TODAY(),"YM") & " Months, " & DATEDIF(A1,TODAY(),"MD") & " Days"

example 3: Use YEARFRAC()
SYNTEX : YEARFRAC(start_date, end_date, [basis])

YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.

Basis   Day count basis
0  : US (NASD) 30/360
1 : Actual/actual
2 : Actual/360
3 : Actual/365
4 : European 30/360

=ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)

10 Excel Skills and Top 10 Sites

10 Excel Skills that can help you analyze data like a pro:

1. Sparkline
2. Power Pivots
3. Conditional Formatting
4. Text Formulas
5. Slicers
6. Flash Fill
7. Charts
8. Vlookup
9. IF Formulas
10. Quick Analysis

Top 10 Sites to learn Excel for free:

1. Microsoft Excel Help Center
2. Excel Exposure
3. Chandoo
4. Excel Central
5. Contextures
6. Excel Hero
7. Mr. Excel
8. Improve Your Excel
9. Excel Easy
10. Excel Jet

Get Height and Width of a Cell in Excel

Private Sub Worksheet_Activate()
    Dim getHeight As Single
    Dim getWidth As Single
   
    For i = 1 To 10
        getHeight = Range("A" + Trim(Str(i))).Height
        getWidth = Range(Chr(65 + i) + "1").Width
     
        Me.UsedRange(i, 1) = "Row  cm : " + Str(Round((getHeight / 72) * 2.54, 2))
        Me.UsedRange(1, i + 1) = Chr(65 + i) + "1 :" + Str(Round((getWidth / 72) * 2.54, 2))
     
    Next
End Sub

Best way to get FILE NAMES from any Folder in Excel

This is one of the easy way to get File Names from your Windows Folder via "Name Manager".

• At first, create a new Excel worksheet.
• Then, select Cell A1.
• Next, go to “Formulas” tab and click “Name Manager” button.
• In the popup dialog box, click “New” button.

• Subsequently, in the next dialog box, input “Files” in “Name” field.
• And change “=Sheet1!$A$1” to “=FILES(Sheet1!$A$1)” in “Refers to:” field.

• Afterwards, click “OK” and close “Name Manager” dialog box.
• Later, copy the Windows folder path in Cell A1 and add “\*” at the end of the path.
• Next, select the Cell A3 and input the function “=INDEX(Files,ROW()-2)”.

At once, a file in this Windows folder will be listed, like the following image.

• Finally, just copy this function down several rows to list the other files until you see the “#REF!” error. The error means that all files have been listed.

Enable the "Insert Rows" with mouse Right Click in Microsoft Excel

To fix this Issue, just follow below steps:

1. Close all you Microsoft Excel files
2. Goto "%appdata%\Microsoft\Excel" in Windows explorer and rename Excel15.xlb to Excel15.old
3. Start Excel and you will see the 'Insert Row' function is now active.

Now you are able to find "Insert Rows" Enable. 

What is .xlb file ?

XLB is a settings file format created by Microsoft Excel. XLB files contain custom settings for the toolbars (or command bars). These settings can be edited or customized by selecting “toolbar” from the view options. XLB files can contain information such as which toolbars are visible, their position, and their function. XLB files allow for the transfer of customization information between computers and users.

Quickly create a dynamic drop down list in Excel

I have a request from friend to create a dynamic drop down list in excel and I would like to share with my blog reader...

Part  1: 
- Open Excel and we start by creating a defined name, 
- Select the Formulas Tab, then Define
- Enter "SomeRangeName" under the Name text box
- Enter =OFFSET(sheet1!$B$2,0,0,COUNTA(sheet1!$B:$B),1) under the Refers to text box

Note: You have to enter drop down items in single column you want here in above formula I have selected Sheet1 and column B, it will start filtering from Row B2.

Part 2:
To create a data validation list:

- Select the cell you want to show dropdown list eg. A1
- Select Data Validation under the Data Tab
- Select List under Allow
- Place the cursor in the Source text box and type formula "=SomeRangeName"

That's it.. now when ever you add items in "Column B" it will automatically populate in drop down list... 

Create User Define Function and Macro as an Add-in

We work lot in excel and manage our work with functions and macros. I am here creating the process that will save your time and effort to re-create again and again the same functions or macros.

Here we are going to create Add-in that will automatically load when you open your excel, and the function will be available all the time. So let's start.

Step 1: Quickly create an empty add-in for excel

1. Open excel and Save a blank workbook as a Excel Add-In (*.xlam) in your Add-In folder (%UserProfile%\AppData\Roaming\Microsoft\AddIns)
2. I named it PersonalAddIn.xlam (Save it with any name you like)
3. Click Office button.
4. Click "Excel options" button.
5. Click "Add-Ins" tab.
6. Select "Excel Add-ins" from manage dropdown list and click GO
7. Click "Browse" button
8. Select the (.xlam) file that you just saved in step 1
9. Click OK (Make sure you enable)

Step 2: Add custom functions and macros to your personal add-in

1. Go to the Developer Tab on the ribbon.
2. Click "View Code"
3. Click on the project panel you see VBAProject (*.xlam)
4. Click Insert Menu --> Module
5. Copy and paste custom functions and macros to code window.

That's it... we are done.

Step 3: Use your user define function

1. Go to the cell
2. Click "Insert Function" in formula bar
3. Select category "User Defined"
4. Select your custom function
5. Click OK

Step 4: Use your user define Macro

1. Go to "Developer" tab
2. Click "Macros" button
3. Select/Type the macro name
4. Click "Run"

Hope this will help you in you daily work, In case of any query you can write to me. Thanks for reading.

Create XML using EXCEL

Many a time I have got question about how to create XML with excel, So I decided to add this into my blog. Hope this will help the readers.

step 1:
Create one schema file (A template that we will use to generate the xml).
Eg. (Save following lines in dataSchema.xml file)

Step 2:
Open your excel file containing the data.

CountryID	CountryName
1	India
2	Pak
3	Srilanka
4	China
5	Koria

Step 3:

Go to the Developer TAB, if it is not on the top then you need to enable it from (File > Option )

Step 4:

Click "Source" under XML.

It will open XML source panel, having two buttons at the bottom. Click "XML Maps...".

Step 5:

Click "Add" to the XML Maps window.

Step 6:

Select "dataSchema.xml" file that we created in Step 1

Click OK twice

Step 7:

In XML Source you can now see the TREE VIEW of your columns.

Step 8:

Drag and Drop each column to the first row of your data.

Step 9.

Click "Export" button in Developers > XML tab.

Step 10:

You can now save this file as XML and that's it. Done.

The Best Excel Shortcuts You Need to Know

Almost everyone uses Excel in some form or fashion in the modern workforce, but many of us may be wasting a little too much time trying to format those pesky spreadsheets. In order to help you work a little bit faster, or maybe more efficiently. Find below best Excel shortcuts that you need to know!

• F2 : Edit selected cell
• F9 : Calculates all worksheets
• F11 : New chart
• ALT : Access the ribbon for formulas
• ALT + = : Automatically SUM() selected
• ALT + Enter : Start a new line in the same cell
• ALT + o-c-a : Auto size columns
• PG + UP/PG+DOWN : Go to next/previous worksheet
• CTRL + ` : Display Formulas
• CTRL + Backspace : Show active cell
• CTRL + Shift + # : Change Date format with day, month and year.
• CTRL + K : To insert Hyperlink.
• CTRL+ Shift + $ : Applies the currency format to the selected cells.
• CTRL + Shift + & : Applies border to cells
• CTRL + B : Bold
• CTRL + I : Italics
• CTRL + U : Underline
• CTRL + Shift + ~ : General style number
• CTRL + Shift + $ : Currency style number
• CTRL + Shift + % : Percentage style Number
• CTRL + Shift + ^  : Scientific notation style
• CTRL + Shift + # :  Date style
• CTRL + Shift + @ : Time Style
• CTRL + Shift +! : Number Style
• CTRL + F12 : Open
• CTRL + Spacebar : Select the entire column
• CTRL + [ : Select all cells directly referenced by formulas in the selection
• CTRL + A : Select All
• CTRL + Shift + J : List Constants
• CTRL + D : Copy equation down
• CTRL + F : Find
• CTRL + H : Find&Replace
• CTRL + Right arrow : Move right
• CTRL + Left arrow : Move left
• CTRL + Up arrow : Move up
• CTRL + Down arrow : Move down
• CTRL + Enter : Same data in multiple cells
• Shift + Spacebar : Select the entire row
• Shift + F3 : Find Previous

Get alternating row colors, using conditional formatting in Excel

I got a questing about how to set alternative color in excel sheet when not using table.

Steps:
1) Select the rows you want alternate color
2) go to Conditional Formatting and create New Rule
3) Select "Use a Formula to determine which cells to format" from rule type
4) Put "=(MOD(ROW(), 2)=0)" without quotes in the formula values
5) Click Format button, and apply format required and that's all..

Count the number of spaces of text string in Excel

Simple to calculate the number of spaces of text string in EXCEL with following formula:

=LEN(A1) - LEN(Substiture(A1," ",""))

Above formula will count all the spaces in the text.


Count the number of leading spaces of text string with formula

=FIND(LEFT(TRIM(A2),1),A2)-1

Essential keyboard shortcuts for MS-EXCEL

Keyboard shortcuts are the best way to navigate cells or enter formulas more quickly.

Control-Down/Up Arrow = Moves to the top or bottom cell of the current column Control-Left/Right Arrow = Moves to the cell furthest left or right in the current row

Control-Shift-Down/Up Arrow = Selects all the cells above or below the current cell

Shift-F11 = Creates a new blank worksheet within your workbook

F2 = opens the cell for editing in the formula bar

Control-Home = Navigates to cell A1

Control-End = Navigates to the last cell that contains data

Alt-= = Autosums the cells above the current cell

Add a number of months to a date in Excel

Many of my friends have query about how to add / Subtract month in excel, Simple way of doing as below


=Date(Year(Start_Date) + add_year, Month(Start_Date) + add_month, Day(Start_Date) + add_day)

Calculate Time in Excel

The simple way to calculate the time is sum the two time inputs, shown in below example:

Use Excel built-in function SUM to calculate. Use same as you calculate any number:
Below mention three cells A1, A2 and A3 formatted as time (hh:mm)

A1 = 04:15
A2 = 10:15
A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 14:30.

This is only useful until total is below 24 hours, but if your total times are greater than 24 hours then things get different, it will round the calculation and start from beginning and you won't get correct answer.

e.g.: If you have :
A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 (1 day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

Find and Replace a Line Break Character in EXCEL

When you want to create a line break (line feed) in a cell, you press Alt + Enter, to start a new line. You can put one or more line breaks in a cell, to make the contents easier to read.

Find a Line Break
Line breaks are easy to add, but a little trickier to remove.

To find specific text in Excel, you can use Ctrl + F to open the Find and Replace dialog box. However, if you try to type Alt + Enter in the Find What box, you’ll just hear a beep from your computer. Excel won’t let you enter that shortcut.
Instead of using Alt + Enter in the Find What box, you can use a special shortcut -- Ctrl + J -- to enter a line break.
A line break is character 10 in the ASCII character set, and the Ctrl + J shortcut is the ASCII control code for character 10.

Find and Replace a Line Break
To replace a line break with a space character:

• Select the cells that you want to search
• On the keyboard, press Ctrl + H to open the Find and Replace dialog box, with the Replace tab active
• Click in the Find What box
• On the keyboard, press Ctrl + J to enter the line break character -- NOTE: Nothing will appear in the Find What box
• Press the Tab key on the keyboard, to move to the Replace With box
• Type a space character
• Click Find Next or Find All, to find the cells with line breaks.
• Click Replace or Replace All, to replace the line breaks with space characters.

Extracting text from a bunch of =EMBED(“Forms.HTML:Text.1”,“”) in Excel

Sub ExtractData()
Dim obj

    For Each obj In ActiveSheet.OLEObjects
        If obj.progID = "Forms.HTML:TextArea.1" Then
            me.Range("A1").Value = obj.Object.Value 
             ''' change the Range as required
        End If
    Next o
End Sub

Copy/Move/Insert without cut and paste

How to do Copy/Move/Insert without Ctrl+C and Ctrl+V?

• Select the range of cells to be copied, or moved, or inserted. 
• Place the cursor on the heavy outline of this selected range, and the cursor changes into a 4-way arrow. 
• Press and hold the left mouse button as you move the cursor to the location where you want to place the selected range. A gray outline around shows you the area to which the selected cells will be moved. 
• Holding the Shift-key will move (i.e. cut and insert) the selected range without over-writing any cells in the paste location. 
• Holding the Ctrl-key will create a copy of the selected range, leaving the original in place. 
• Holding both Shift-key and Ctrl-key will insert a copy. 

This works on cells, rows, and columns (and sheets?) 
Try it... I think you'll like it. 

Random Function

Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

Syntax

=RAND( )

Remarks

To generate a random real number between x and y, use:
RAND()*(x-y)+x

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number.

Protect Excel Cells in a worksheet

This post is regarding the query, that how can user protect some of the cells in a worksheet for editing.

Steps to do this as follow:


Default when you protect your worksheet it lock all the cells for editing, but in case if you want some of the cells can be edited by user then, for that you have to make that cells unlock. But how can we do that?

Answer is,

• On the Format menu, click Cells, and then click the Protection tab.
• Click to clear the Locked check box and click OK.

                               Or

                        Press Ctrl + 1

• On the Tools menu, point to Protection, and then click Protect Sheet. Type a password if you want one, and then click OK.

Done !!!



NOTE: If you lock a cell and protect the worksheet, then you cannot type data into the cell, modify the data currently in the cell, or change other attributes of the cell (such as cell formatting).