Change slash separator in date with period in Microsoft Excel

If you are one of those people who love to use period instead of / and would like to change the default setting for the date format, perform the following steps:
From Windows, choose Start, Settings, Control Panel, Regional Options.
Select the Date tab. In the Date separator box, change the slash (/) to a period (.).Click Apply and OK.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Displaying the serial number behind Date format in Microsoft Excel

Select a cell and enter today date into the cell by pressing Ctrl+; than press Ctrl+~.

Press Ctrl+~ again to restore the sheet to its normal appearance.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Use Smart Tag to fill series of dates in Microsoft Excel

The Smart Tag lets you enter a series of dates automatically. You can enter an ascending series according to days, months and years.

1. Select Cell A1, and press Ctrl+;.
2. Click the Fill handle at the bottom right edge of Cell A1, and drag it to several cells in the column.
Do not cancel the selection of the range of dates. Excel creates a series according to days.
3. Click the arrow in the Smart Tag. Select Fill Months

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Calculating Differences Between Dates

Use the DATEDIF function. The results of the calculation are displayed as days, full months, and full years.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

To calculate the date at the end of a month:


Use the EOMONTH function.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Adding a Custom Function That Returns the Quarter Number

1. Press Alt+F11 to open the VBE.
2. Double-click a Module name in the Personal Macro Workbook, or insert a new Module into the Personal Macro Workbook by selecting Module from the Insert menu.
3. Enter the following code lines into the Module:

Function QuarterNum(Enter_Date)
QuarterNum = DatePart ("q", Enter_Date)
End Function


To test the Custom Function:

1. Select any empty cell and press Shift+F3 to open the Paste Function dialog box.
2. In the User Defined category, select QuarterNum, and then click OK.
3. In the Function Arguments dialog box, enter the address of a cell containing a date, and click OK.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Calculate number of minutes between dates - time in Microsoft Excel

Data in cell A1:B2

A B
1 End Date/Time 05/11/2003 15:33 <-- =NOW()
2 Start Date/Time 10/01/2002 9:33 <-- =NOW()-222.25

Formula in cell E2: =(B1-B2)*1440 ( 24 hours*60 minutes=1440 )

Format in cell E2: regular number format

Result: 320,040 minutes

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Cell Function Returns Sheet Name, Workbook Name and Path

To get the sheet name:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

To get the workbook name:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,(FIND("]",CELL("filename"))+1)-FIND("[",CELL("filename"))-2)

To get the path address & workbook name:
=CELL("filename")

To get the path address:
=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Problem: Unnecessary Empty Characters in Cells

o remove empty characters, use the Trim function.

The Trim function is in the Text category in the Insert Function dialog box.

To insert the Trim function:

1. Select a cell and type:
=Trim
2. Press Ctrl+A to open the Function Arguments dialog box

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Add Combo Box to a Sheet in Microsoft Excel

What are the Advantages of working with Combo Box?

Choosing an item from a list in the Combo Box returns a number. The number then replaces the use of the Match formula to calculate the column number in the Vlookup formula (read tip) and replaces the use of the Match formula (read tip) to calculate the column number in the Index formula.

Working with the Combo Box

A list of the cell ranges in the sheet is attached to the Combo Box. After an item is selected from the list, the Combo Box enters into the linked cell the ordinal number of the item that is selected on the list.



Example:

The screen shot displays a profit and loss statement as compared to the previous year. Changing the month in the Combo Box will automatically change the display of data according to the selected month.


Step 1: Define names

1.Enter the months list - January-December into the cells A1 to A12 in the new worksheet.

2.Select cells A!:A12, press Ctrl+F3, enter the text MonthsList into the Names In workbook box, and click OK.

3.Select Cell B1, press Ctrl+F3, enter the text MonthNumber into the Names in Workbook box, and click OK.


Step 2: Add a Combo Box to a worksheet

1. Select one of the toolbars, right-click, and select the Forms toolbar.

2. Copy the Combo Box by clicking the Combo Box icon, and then release the mouse. Create a rectangle with the mouse in the worksheet, and then release the mouse.

Step 3: Format the Combo Box

1.Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab

2.In the Input range box, type the name MonthsList (You cannot press F3 to paste a name with an object).

3.In the Cell link box, type the name MonthNumber.

4.Click to select the 3-D shading box (more aesthetic).

5.Click OK.

Exit the formatting mode of Combo Box, and select a cell in the sheet. Open the list of items in the Combo Box, and select a month. Note that the new month number is shown in cell B1.


Advantages of working with names in a Combo Box

Attaching a list with Define Names causes your list references to be updated automatically in the Combo Box. Sorting the list in the sheet will automatically sort the list in Combo Box as well.

Deleting a Combo Box

Select the object; right-click; and from the shortcut menu that appears, select Cut.

Adjusting the size of the Combo Box to the cells

Select the Combo Box and right-click; the Combo Box is now in editing mode. Adjust the width or height of the Combo Box by dragging one of the corner boxes; adjust the placement of the Combo Box on the worksheet by clicking anywhere inside the box and dragging it.


Automatic adjustment of the Combo Box

Hold down the Alt key while you click the mouse to adjust the size of the Combo Box.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Coloring Sheet Tabs


Select a sheet tab, right-click, and select Tab Color from the shortcut menu.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Increasing/Decreasing the Screen Magnification Percentage

To quickly increase or decrease the screen magnification percentage:

1. Select a cell.
2. Press Ctrl, and roll the mouse wheel forward (to increase the magnification) or backward (to decrease it).

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Selecting a Sheet in the Workbook

Using keyboard shortcuts

To select to the next sheet in the workbook, press Ctrl+Page Down.
To select to the previous sheet in the workbook, press Ctrl+Page Up.

Using the mouse

To the left of the sheet tabs in the horizontal Scroll Bar are four small arrows.
1. Place the mouse pointer over one of the arrows and right-click.
2. From the shortcut menu, select a sheet from the list of sheet names.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Selecting a sheet from shortcut menu in Microsoft Excel

To the left of the sheet tabs in the horizontal scroll bar row are several small arrow buttons. Place the mouse pointer over one of the arrows and right-click. From the shortcut menu, select a sheet from the list of sheet names.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Quickly Selecting a Range in the Workbook

To quickly Select a range in the Workbook:

The best way to select a cell or range of cells in either the current or any other sheet in the workbook is by selecting the Name defined for that range from the Name Box dropdown list to the left of the Formula Bar.
The Name Box contains a list of the Names assigned to the cell addresses. Selecting a Name is the same as selecting the address of a cell or range of cells in the active workbook.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Coloring Gridlines

To color the gridlines:

1. From the Tools menu, select Options, and then select the View tab.
2. In the Window options section, choose a color from Gridlines color dropdown list, and click OK.

Note:
In Excel 97 and Excel 2000, select the color from the Color dropdown list.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Reducing the Workbook Size for Quick Sending via E Mail

To reduce the workbook size:

1. Press Ctrl+End to find the last cell in the used area within the sheet. In the screenshot, the last cell is E17.
2. Find the last cell containing data in the sheet. In the screenshot, the last cell containing data is cell C11.
3. Delete all the rows between the cells containing data to the row of the last cell in the used area. In the screenshot, the rows to delete are 12:17.
4. Delete all columns to the right of the column of the last cell containing data, up to the column of the last cell in the area used. In the screenshot, the columns to delete are D:E.
5. To quickly delete the rows, select the first row to delete (row 12 in the screenshot), press Ctrl+Shift+Down Arrow. To quickly delete the columns, select the first column to delete (column D in the screenshot), and press Ctrl+Shift+Right Arrow, press Shift+F10 and from the shortcut menu, press Delete.
6. Repeat the steps above for each sheet in the entire workbook.
7. Press Ctrl+S to save the file.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Displaying Formulas and Values of the Same Cells

To display formulas and values of the same cells:

1. From the Window menu, select New Window.
2. From the Window menu, select Arrange.
3. Select the Horizontal option button and click OK.
4. Select one of the two windows and press Ctrl+` (the key to the left of the number 1).

To move between windows, press Ctrl+Tab or Ctrl+F6.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Auto-Recovering/ Auto-Saving a Workbook

In Excel 97 and Excel 2000, the Auto Save option operates from the Autosave Add-In. In Excel 2002 and Excel 2003, this option is automatic and the add-in no longer exists.

To enable the Autosave Add-In in Excel 97 and Excel 2000:

1. Install the Autosave Add-In by selecting Add-Ins from the Tools menu, and then selecting the Autosave Add-in checkbox.
2. Click OK.
3. In the Auto Save dialog box, set the Desired Every ... minutes.

To change the default AutoRecovery option (if required):

1. From the Tools menu, select Options, and then select the Save tab (this is a new tab in Excel 2002 and Excel 2003).
2. Change the Save AutoRecover info every: ... minutes, set the Auto Recover save location, and then click OK.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

To open a copy of an existing workbook:

1. Press Ctrl+F1 (new in Excel 2003), or select Task Pane from the View menu.
2. In the New Workbook Task Pane, click From existing workbook.
3. In the New from Existing Workbook dialog box, find and select the workbook, and then click Create new.

__________________
V.Vadivelan

There never a wrong time to do the right thing.