Ctrl+Z Undo
Ctrl+C Copy,

Enter, Paste,
Ctrl+V Multiple Paste,
Ctrl+X Cut,

Ctrl+F, Find,
Ctrl+H Find&Replace

Ctrl+P, Print,
Ctrl+S, Ctrl+F4, Alt+F4 Save, Close,
Close
Excel
Ctrl+Arrow Move to edge of region
Ctrl+* Select current region
Ctrl+A Select all cells
Ctrl+Home Select
Ctrl+End A1,
Select last cell in used range

Ctrl+Shift+End Select from active cell to last cell in used range.
Ctrl+Shift+Home Select from active cell to A1
Ctrl+Tab Move to next open workbook


- Vignesh

Using keyboard shortcuts :

To move to the last (or first) cell in a range:

• Vertically from top to bottom, press Ctrl+Down Arrow.
• Vertically from bottom to top, press Ctrl+Up Arrow.
• Horizontally from left to right, press Ctrl+Right Arrow.
• Horizontally from right to left, press Ctrl+Left Arrow.

Using the mouse :

Double-click one edge of the selected cell when the mouse image changes to four directional arrows.


- Vignesh

To select a range of non-adjacent cells:

1. Select cell A1, and press Ctrl+Shift+Down Arrow.
2. Continue holding down Ctrl, and select another range.
3. Select additional ranges while continuing to press Ctrl.


To select the Current Region/List:

• Select a cell inside the Current Region/List range and press Ctrl+Shift+* (when using the * on the numeric keypad, press Ctrl+*).
• New keyboard shortcut in Excel 2003: Select a cell inside the Current Region/List and press Ctrl+A (you can still use Ctrl+Shift+*).


- Vignesh

Automatically Loading Add-ins :


Follow these general steps:

1) Load the worksheet for which you want a specific add-in loaded.
2) Press Alt+F11 to display the VBA Editor.
3) Double-click on the "This Workbook" object in the Project Explorer. Excel opens a code window for This Workbook.
4) Place the following macros in the code window:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Add-In Name").Installed = False
End Sub

Private Sub Workbook_Open()
AddIns("Add-In Name").Installed = True
End Sub

- In the code, change the name of the add-ins ("Add-In Name") to the real name of the add-in you want to use with the worksheet.
- Close the VBA Editor.
- Save your worksheet.


- Vignesh

After you have created your own add-in, you can use it in your system. Once the add-in has been loaded, the functions or features in the add-in become available to any other workbook you may have open, or any time you are using Excel. All you need to do to use your add-in is follow these steps:

1) Choose Add-Ins from the Tools menu. This displays the Add-Ins dialog box. (Click here to see a related figure.)

2) If your custom add-in is visible in the dialog box, click the check box beside it and skip to step 6.

3) Click on the Browse button. Excel displays a standard file dialog box.

4) Use the controls in the dialog box to locate and select your custom add-in.

5) Click on OK. The add-in is loaded and made a part of Excel. (You can tell that the add-in is available because it is now listed in the Add-Ins dialog box.)

6) Click on OK to close the Add-Ins dialog box.

- Vignesh

Excel allows you to translate your macro programs into add-ins, which can become part of Excel--the same as the Analysis ToolPak and others. Eventually you might want to take advantage of this capability. The files you convert to add-ins do not need to be elaborate, nor do they have to be fancy. Converting them to add-ins does have several advantages, however:

> The program code cannot be altered by others.
> The program code runs a bit quicker.
> The add-in is available without needing to open any particular workbook.
> The functions provided by the add-in appear to be a part of Excel.
> In essence, add-ins are nothing but a special type of workbook which you have converted to an add-in format that is understood by Excel.

You may want to make sure your macro code which is destined to be an add-in performs some initializing routine that modifies, in some way, the Excel user interface. For instance, most add-ins modify the menu structure in some way so that the functions in the add-in can be accessed. Your macros should take care of the menu modification so that people can access your add-ins. If you don't modify the interface in some way, then users can only get to the macro code in your add-in by directly referencing in a worksheet formula the names of any functions in your add-in.


- Vignesh

The EDATE function can be quick and easy. For instance, if you are working with expiration dates for six-month contracts, you can use the following formula:

=EDATE(NOW(),6)

The function takes the first date provide (in this case, using the NOW() function) and uses the second parameter to determine the number of months future or past that should be calculated. The date parameter you use should resolve to a date serial number, and not be a textual date.

If you use a negative value for the second parameter, then EDATE calculates a date in the past. For instance, if you wanted a date that was three months in the past, then you could use the following:

=EDATE(NOW(),-3)

EDATE returns a date serial number; you may need to format the cell so it uses a date format that formats the returned value as you want it to appear.

You should note that if the EDATE function does not work on your system, it means you have not installed or enabled the Analysis ToolPak. Install the ToolPak and then you should be able to use the function.


- Vignesh

How Excel Stores Dates and Times :

" Internally, Excel stores a date or time as a number. The whole part of the number (the part to the left of the decimal point) represents the number of days since an arbitrary starting point (typically January 1, 1900). The decimal portion (the part to the right of the decimal point) represents the time for that date. These internal representations of dates and times are often referred to as serial numbers.

To see how this works, enter the number 23 in a cell. If you have not previously formatted the cell, Excel uses the General format, displaying the number simply as 23. If you later format this cell using a date format--m/d/yy, for instance--Excel changes the display to 1/23/00, or January 23, 1900.

The portion to the right of the decimal point represents a fractional portion of a day. Thus, a single second would be equal to approximately 0.00001157407, since that is equal to 1 (a day) divided by 86,400 (the number of seconds in a day).

Since Excel stores dates and times as serial numbers, you can do math on them. For instance, if you wanted to determine the number of days between two dates, or the amount of time between two times, simply subtract them from each other. The result is the number of days and fractions of days between the two. "

- Vignesh

Using Excel for Timing :

" You may want to use Excel to record the elapsed time for different events. There are two ways that this can be approached: either native, within Excel, or with a macro.

If you don't want to use a macro, you can easily set up three columns for your timing. The first column can be used to record the start time, the second column the end time, and then the third column the elapsed time (calculated by using a formula that subtracts the start time from the end time). In order to record times, you select a cell in either the start time or end time columns and press Ctrl+: (the colon). Excel enters the current time in that cell.

If you want to use a macro that simply returns the elapsed time, then you can use the following:

Public Sub TimeIt()
Dim vStartTime As Date

vStartTime = Time
MsgBox Prompt:="Press the button to end the timing" & vbCrLf _
& "Timing started at " & Format(vStartTime, "hh:mm:ss"), _
Buttons:=vbOKOnly, _
Title:="Time Recording Macro"
ActiveCell.Value = Time - vStartTime
End Sub
This macro records a start time (in vStartTime), and then displays a message box. When you click on the message box button, the difference between the current time and the start time is stored in the current cell. (You need to make sure the current cell is formatted with one of the time formats.)

The above macro works very well for recording short events during which you don't need to use Excel for other tasks. If you need to record longer events, then a different approach is in order. The following macros work in tandem. The first one records a start time; that is all it does. The second one uses that recorded time to calculate an elapsed time which is placed in the currently selected cell.

Global vStTime

Sub StartTiming()
vStTime = Time
End Sub

Sub EndTiming()
ActiveCell.Value = Time - vStTime
End Sub
You could easily assign these two macros to different toolbar buttons that would, respectively, start and stop the timing process. "




- Vignesh

Using the EOMONTH Function :

" The EOMONTH function is used to return the serial number value for the last day of any particular month, past, present, or future. The syntax for the function is as follows:

=EOMONTH(base, offset)
The base value is a date from which the function should do its calculations, and the offset is a number that indicates how many months from the base date should be used. For instance, an offset of 0 would indicate that EOMONTH should return the last day of the month represented in base, whereas an offset of 4 would be four months after base, and -2 would be two months before.

As an example, the following are typical uses of EOMONTH. The first line can be used to return the last day of the current month, and the second line returns the last day of the month six months later than the date in A1:

=EMONTH(NOW(),0)
=EMONTH(A1,6)
Remember that EOMONTH returns a serial number. Excel does not automatically format the serial number as a date. In other words, you will need to explicitly format the cell as a date. (Just select Cells from the Format menu and you can do all the formatting you want.)

The EOMONTH function is a part of the Analysis Toolpak. If you get an error when you try to use the function, you can make sure the toolpak is loaded in this manner:

1) Choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box. (Click here to see a related figure.)

2) Make sure the Analysis Toolpak check box is selected.

3) Click on OK.

- Vignesh

Automatically Advancing by a Month :

" If the date in the cell is today's date, and you simply want to have the cell display the current month up through the 14th, and then next month after that, then you can use a formula such as the following:

=CHOOSE(IF(DAY(NOW())>14,MONTH(NOW())+1,MONTH(NOW( ))),"January","February","March","April","May","Ju ne","July","August","September","October","Novembe r","December","January")
This formula returns the name of a month, not a date. If you prefer to have a date returned, you can use this formula:

=IF(DAY(NOW())>14,DATEVALUE(IF(MONTH(NOW())=12,1,M ONTH(NOW())+1) & "/" & DAY(NOW()) & "/" & IF(MONTH(NOW())=12,YEAR(NOW())+1,YEAR(NOW()))),NOW ())
Both of these formulas account for the "end of year wrap-around" when you advance from December to January. A shorter version of this last formula can be created if you use the DATE function instead of the DATEVALUE function:

=DATE(YEAR(NOW()),MONTH(NOW())+((DAY(NOW())>14)*1) ,1)
This formula, unlike the DATEVALUE example, always returns a date that is the first day of any given month.

If you really want to advance the value of a particular date in a cell, then you must use a macro to do the task. Further, you must make sure that the macro only runs once a month, at a particular time on a particular day. For instance, if you wanted the macro to run at 00:00:00 on the 15th of each month, you would need to set up the macro so that it checked the date and time, and then ran at that particular date and time. You would also need to make sure that the workbook containing the macro was open over that date and time.

The following macro will fetch the date from a cell and increase it by a month. The macro assumes that you have a named range, DateCell, which refers to the cell to be updated.

Sub IncreaseMonth()
Dim dDate As Date
dDate = Range("DateCell").Value
Range("DateCell").Value = _
DateSerial(Year(dDate), _
Month(dDate) + 1, Day(dDate))
End Sub
To make sure that the macro runs at the appropriate time, you would need another macro. The following macro is designed to be run whenever the workbook is opened:

Private Sub Workbook_Open()
If Day(Now) = 14 Then
Application.OnTime ("23:59:59"), "IncreaseMonth"
End If
End Sub
Notice that this particular macro sets the OnTime method so that it runs the IncreaseMonth macro at 23:59:59 on the 14th. This date and time was chosen because it is easier to catch than is 00:00:00 on the 15th.

Remember that the IncreaseMonth macro will only run if you open the workbook on the 14th, and then leave the workbook open until the 15th. "


- Vignesh

Calculating Business Days :

" Excel makes this easy--you just subtract the earlier date from the latter.

In a business environment, however, you may not want to know just the number of days--you probably want to know the number of business days between two dates. In other words, how many workdays are there between two dates?

Believe it or not, Excel makes it almost as easy to calculate business days as it is to calculate regular days. All you need to do is use the NETWORKDAYS worksheet function. This function is not intrinsic to Excel; it is part of the Analysis ToolPak. (You make sure this add-in is loaded by choosing Add-Ins from the Tools menu.)

Let's suppose for a moment that you had two dates: one in A3 and the other in A4. The date in A3 is your starting date and the date in A4 is the ending date. To calculate the work days between the two dates, you could use the following formula:

=NETWORKDAYS(A3,A4)

This returns a count of all the days between the two dates, not counting weekends. You should note that the function returns the number of full days. Thus, if your starting date was Sept. 4 and your ending date was Sept. 5, the function would return a value of 2. (Provided neither day was a weekend day.)

If you want to account for holidays, the easiest way is to enter your standard holidays in a range of cells, and then define a name for that range. (I always like the terribly obvious name of "Holidays.") You can then alter the NETWORKDAYS formula in this manner:

=NETWORKDAYS(A3,A4,Holidays)


- Vignesh

Automatically Converting to GMT :

" GMT is an acronym for Greenwich Meridian Time, which is a reference time for the world; it is the time in Greenwich, England, and is sometimes referred to as "Zulu time." (Zulu is the phonetic name for zero, and the zero refers to the longitude of Greenwhich, England.)

You may have a need to convert a local time to GMT in your worksheet. If you always know that the time will be entered in local time, this can be done quite easily with a formula. For instance, assume that you are entering the local time in cell B7, and that you are in the Pacific time zone. In this time zone, you are either seven or eight hours behind GMT, depending on if daylight savings time is in effect. The following formula will adjust the time entered in B7 by either seven or eight hours, depending on whether the date associated with the time is within the period of daylight savings time.

=IF(AND(B7>=DATEVALUE("4/6/2003 02:00"),B19<=DATEVALUE("10/26/2003 02:00")),B7+7/24,B7+8/24)
Remember that whenever you enter a time into a cell, Excel automatically attaches a date to it. Thus, if you enter a time of 10:15 into a cell, and the day you make the entry is January 17, then Excel automatically converts the entry in the cell to 01/17/2003 10:15:00. This is done even though you may only be displaying the time in the cell--in Excel, every date has a time associated with it, and every time has a date associated with it.

Because of this entry behavior, Excel would use the formula just shown to do the proper adjustment based on the default date when you enter a time (today's date) or a date you may explicitly enter.

The only drawback to this formulaic approach is that you must remember to change the daylight savings time boundary dates from year to year. (The ones in the formula are for 2003.) You could change the formula so that you actually stored the boundary dates in cells, such as E1 and E2, as follows:

=IF(AND(B7>=$E$1,B19<=$E$2),B7+7/24,B7+8/24)
While the formula is shorter, it still has a problem with the rather static determination of when daylight savings time begins and ends--you must remember to update that information manually. In addition, if you move to a different time zone, you must remember to modify the values by which the date and time are adjusted.

A really handy way around these drawbacks is to create a user-defined function that accesses the Windows interface and determines what the system settings are in your computer. Your system keeps track of daylight savings time automatically, as well as which time zone you are in. Accessing this information through a user-defined function means you will never need to worry about those items in your worksheet. You can use the following macro to do just that:

Option Explicit

Public Declare Function SystemTimeToFileTime Lib _
"kernel32" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long

Public Declare Function LocalFileTimeToFileTime Lib _
"kernel32" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long

Public Declare Function FileTimeToSystemTime Lib _
"kernel32" (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long

Public Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Public Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Public Function LocalTimeToUTC(dteTime As Date) As Date
Dim dteLocalFileTime As FILETIME
Dim dteFileTime As FILETIME
Dim dteLocalSystemTime As SYSTEMTIME
Dim dteSystemTime As SYSTEMTIME

dteLocalSystemTime.wYear = CInt(Year(dteTime))
dteLocalSystemTime.wMonth = CInt(Month(dteTime))
dteLocalSystemTime.wDay = CInt(Day(dteTime))
dteLocalSystemTime.wHour = CInt(Hour(dteTime))
dteLocalSystemTime.wMinute = CInt(Minute(dteTime))
dteLocalSystemTime.wSecond = CInt(Second(dteTime))

Call SystemTimeToFileTime(dteLocalSystemTime, dteLocalFileTime)
Call LocalFileTimeToFileTime(dteLocalFileTime, dteFileTime)
Call FileTimeToSystemTime(dteFileTime, dteSystemTime)

LocalTimeToUTC = CDate(dteSystemTime.wMonth & "/" & _
dteSystemTime.wDay & "/" & _
dteSystemTime.wYear & " " & _
dteSystemTime.wHour & ":" & _
dteSystemTime.wMinute & ":" & _
dteSystemTime.wSecond)
End Function
This may look imposing, as is often the case when working with system calls, but it works wonderfully. There are three system routines referenced (SystemTimeToFileTime, LocalFileTimeToFileTime, and FileTimeToSystemTime). By setting up the calls and using them in order, the date and time are automatically adjusted to GMT. To use the function, in your worksheet you would enter this to convert the time in cell B7:

=localtimetoutc(B7)
Format the cell as date/time, and the output is exactly what you wanted.



- Vignesh

Adding a Drop Shadow to a Text Box :

" Text boxes are a graphic element you can use in your workbooks to hold information that is ancillary to the main data in the workbook. For instance, you might use a text box to create a sidebar or other text object. To enhance your layout design using text boxes, you can add a drop shadow so the text box appears to float about the actual printed page.

To add a drop shadow to a text box, follow these steps:

> Make sure the Drawing toolbar is displayed. (You can click on the Drawing tool on the standard toolbar to display the Drawing toolbar.)

> Select the text box you want to format. Small selection handles should appear around the perimeter of the text box.

> Click on the Shadow tool on the Drawing toolbar. Excel displays a palette of available shadows.

> Click on the shadow desired.

Users of Excel 2007 will note that there is no longer a Drawing toolbar available. Instead, Excel 2007 users should follow these steps:

> Select the text box you want to format. Small selection handles should appear around the perimeter of the text box.

> Select the Format tab on the ribbon. (This tab is available only when you select the text box in step 1.)

> Click Shape Effects in the Shape Styles group. Excel displays a list of effects you can apply to the text box.

> Highlight the Shadow option and you will see a palette of available shadows.

> Click on the shadow desired.

- Vignesh

Adding AutoShapes :

" The following general steps detail how to do this for a series of twenty flowchart symbols. The steps assume that you are reasonably comfortable writing macros and customizing toolbars.

> Open a template workbook, and make sure it has only a single worksheet.

> Place all the flowchart graphics on the worksheet.

> Create a new toolbar, name it MyShapes, and make sure it is associated with the template workbook.

> Add twenty buttons to the toolbar, one for each flowchart graphic. The idea is that clicking a button will add the associated flowchart shape to the active worksheet.

> Edit each button face to show as closely as possible each flowchart graphic. (This is the toughest part of these steps).

> Change the ToolTip text for each button, as desired. This is helpful so the user can understand the purpose of each flowchart graphic.

> In turn, select and name each of the flowchart graphics. (You name the graphics by selecting them and entering a name in the Name box at the left of the Formula bar.) For the purposes of these steps, assume you use names such as FlowObj1, FlowObj2, etc.

> Write twenty macros (one for each flowchart graphic) of the following kind:
Sub AddFlowObj1()
ThisWorkbook.Sheets(1).Shapes("FlowObj1").Copy
ActiveSheet.Paste
End Sub

> Assign each of the macros to the corresponding toolbar button.

> In the Workbook module of the template, add the following procedures:
Private Sub Workbook_Open()
Application.CommandBars("MyShapes").Visible = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("MyShapes").Delete
End Sub
> Save the template as an Excel add-in.

> Restart Excel and use Tools | Add-ins to active your new add-in.


- Vignesh

Adding Data Labels to Your Chart :

" Data labels are used to indicate what the main part of the chart represents. Depending on the type of chart you are creating, data labels can mean quite a bit. For instance, if you are formatting a pie chart, the data can be more difficult to understand if you don't include data labels.

To add data labels, follow these steps:

> Activate the chart by clicking on it, if necessary.

> Choose Chart Options from the Chart menu. Excel displays the Chart Options dialog box.

> Make sure the Data Labels tab is selected. (Click here to see a related figure.) The left side of the dialog box shows the different types of data labels you can choose. (The available types will vary, depending on the type of chart you are using.)

> There are five different basic types of data labels from which you can choose. Each of them represents a different combination of using the data value, a percentage, or a label as the actual data label. Select the option that best reflects what you want to do. As you make choices, notice that the preview chart is updated according to your selections.

> Click on OK. Your chart is updated as you directed.


- Vignesh

Adding Drop Shadows :

" One helpful drawing tool you can use to format your drawings is known as the Shadow Style tool. Using this tool you can add a drop shadow behind an object. Adding drop shadows can help add more depth to your graphics, making them look like they are raised off the surface of the Excel worksheet.

To apply a drop shadow to an object, select the object, then click on the Shadow Style tool. (It is very close to the right end of the Drawing toolbar.) Excel displays a number of different shadow types and positions. You can also modify the shadow, once placed, by choosing the Shadow Settings option. This displays a toolbar that allows you to adjust the characteristics of the shadow, without affecting the original object. "




- Vignesh

Adjusting Your View of 3-D Graphs :

" Excel allows you to create some great looking three-dimensional graphs based on the information in your worksheets. For many purposes, the default method in which the graphs are created will be sufficient for your needs. However, you may want to adjust the angle at which you view your graph. Excel makes this easy by following these steps:

> Create and display your 3-D graph as you normally would.

> Select the 3-D View option from the Chart menu. Excel displays the 3-D View dialog box. (Click here to see a related figure.) (To display the dialog box in Excel 2007, display the Layout tab of the ribbon and then click 3-D Rotation in the Background group.)

> Using the appropriate boxes, specify an elevation, rotation, and perspective that are desired. You can also use the buttons to adjust the values in the boxes. As you make changes, Excel displays a frame outline of how a 3-D graph appears. (There is no frame outline in Excel 2007; the actual graph is adjusted immediately as you make changes.)

> When satisfied, click on OK. Your graph is updated with the adjusted view.


- Vignesh

Adjusting Picture Appearance :

" The toolbar has several tools on it that control the appearance of the selected graphic. These tools include the following:

> Image Control: This tool presents a menu of options you can use to specify how the graphic should be treated, i.e., as a black and white drawing or as a grayscale image.

> More Contrast: Increases the contrast between light and dark in the image.

> Less Contrast: Decreases the contrast between light and dark.

> More Brightness: Turns up the overall brightness of the image.
Less Brightness. Makes the image more dark, overall.

In order to use the adjustment tools, simply click on them. (When using the Image Control tool you will need to select an option from a menu first.) Remember that if you mess things up you can always click on the Undo tool on the regular Excel toolbar. If you mess things up too bad, you can click on the Reset Picture tool on the Picture toolbar. This sets you image back to its pre-change settings.


- Vignesh