Moving to the last or first cell in the rang

As you all know there are to ways that we could do any task in excel one with mouse and another with keyboard.

1. With Keyboard

• 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.

1. With Mouse

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

Office 2007 Ribbon Hide

Some times a user requirement of bigger view in excel to give it one thing we can do is hide the ribbon it is very simple to hide ribbon in excel all you have to do is Press Control + F1 and ribbon disappears and press again to reappear ribbon. You can also have it disappear so it will come back with a single click on any tab name. To do this, double click a tab name and the ribbon disappears. Single click a tab name and it reappears - click again on the tab or in the document, and it disappears. Repeat until you're tired of the magic! Double click or Control + F1 to go back to how it is.

Non Excel Tip : Invisible Folder

Hi All,

Today I Thought to give you some thing different than excel so here it is an Invisible folder to create an invisible folder you have to do following steps
1. Create a folder on desktop by right clicking your mouse and select New than folder

2. now your folder named NEW Folder is Created.

3. Now the tricky part comes in Right Click the New Folder and rename it with clicking Space and than pressing Alt key and hit 016 from num side of Keyboard. now your folder without name is in-front of you.

4. Now the final step again right click the unnamed folder and go to Properties-->Customize-->Change Icon and select any blank space.

Here presenting Invisible Folder enjoy.

Regards

Hemant

Shortcut Keys At A Glance

ctrl+shift+( Unhides any hidden rows within the selection.
ctrl+shift+) Unhides any hidden columns within the selection.
ctrl+shift+& Applies the outline border to the selected cells.
ctrl+shift_ Removes the outline border from the selected cells.
ctrl+shift+~ Applies the General number format.
ctrl+shift+$ Applies the Currency format with two decimal places (negative numbers in
parentheses).
ctrl+shift+% Applies the Percentage format with no decimal places.
ctrl+shift+^ Applies the Exponential number format with two decimal places.
ctrl+shift+# Applies the Date format with the day, month, and year.
ctrl+shift+@ Applies the Time format with the hour and minute, and am or pm.
ctrl+shift+! Applies the Number format with two decimal places, thousands separator,
and minus sign (–) for negative values.
ctrl+shift+* Selects the current region around the active cell (the data area enclosed by
blank rows and blank columns).
In a PivotTable, it selects the entire PivotTable report.
ctrl+shift+: Enters the current time.
ctrl+shift+” Copies the value from the cell above the active cell into the cell or the
Formula bar.
ctrl+shift+plus (+) Displays the Insert dialog box to insert blank cells.
ctrl+minus (–) Displays the Delete dialog box to delete the selected cells.
ctrl+; Enters the current date.
ctrl+` Alternates between displaying cell values and displaying formulas in the
worksheet.
ctrl+’ Copies a formula from the cell above the active cell into the cell or the
Formula bar.
ctrl+1 Displays the Format Cells dialog box.
ctrl+2 Applies or removes bold formatting.
ctrl+3 Applies or removes italic formatting.
ctrl+4 Applies or removes underlining.
ctrl+5 Applies or removes strikethrough.
ctrl+6 Alternates between hiding objects, displaying objects, and displaying
placeholders for objects.
ctrl+8 Displays or hides the outline symbols.
ctrl+9 Hides the selected rows.
ctrl+0 Hides the selected columns.
ctrl+a Selects the entire worksheet.
If the worksheet contains data, ctrl+a selects the current region. Pressing
ctrl+a a second time selects the current region and its summary rows.
Pressing ctrl+a a third time selects the entire worksheet.
When the insertion point is to the right of a function name in a formula,
displays the Function Arguments dialog box.
ctrl+shift+a inserts the argument names and parentheses when the
insertion point is to the right of a function name in a formula.
ctrl+b Applies or removes bold formatting.
ctrl+c Copies the selected cells.
ctrl+c followed by another ctrl+c displays the clipboard.
ctrl+d Uses the Fill Down command to copy the contents and format of the
topmost cell of a selected range into the cells below.
ctrl+f Displays the Find and Replace dialog box, with the Find tab selected.
shift+f5 also displays this tab, while shift+f4 repeats the last Find action.
ctrl+shift+f opens the Format Cells dialog box with the Font tab selected.
ctrl+g Displays the Go To dialog box.
f5 also displays this dialog box.
ctrl+h Displays the Find and Replace dialog box, with the Replace tab selected.
ctrl+i Applies or removes italic formatting.
ctrl+k Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit
Hyperlink dialog box for selected existing hyperlinks.
ctrl+n Creates a new, blank workbook.
ctrl+o Displays the Open dialog box to open or find a file.
ctrl+shift+o selects all cells that contain comments.
ctrl+p Displays the Print dialog box.
ctrl+shift+p opens the Format Cells dialog box with the Font tab selected.
ctrl+r Uses the Fill Right command to copy the contents and format of the
leftmost cell of a selected range into the cells to the right.
ctrl+s Saves the active file with its current file name, location, and file format.
ctrl+t Displays the Create Table dialog box.
ctrl+u Applies or removes underlining.
ctrl+shift+u switches between expanding and collapsing of the Formula bar.
ctrl+v Inserts the contents of the clipboard at the insertion point and replaces any
selection. Available only after you have cut or copied an object, text, or cell
contents.
ctrl+w Closes the selected workbook window.
ctrl+x Cuts the selected cells.
ctrl+y Repeats the last command or action, if possible.
ctrl+z Uses the Undo command to reverse the last command or to delete the last
entry that you typed.
ctrl+shift+z uses the Undo or Redo command to reverse or restore the last
automatic correction when AutoCorrect Smart Tags are displayed.

Web Page from MS Excel

If you need to analyze data from a Web site, you might be in for a bunch of tedious retyping. Under the right circumstances, however, Excel 97 can save them a lot of time by letting them open an HTML page in Excel directly from the Web.
The procedure is just a slight modification of the standard file-opening procedure:
1. Choose Open from the File menu.
2. In the File Name text box, type the URL of the Web file to open. Note: Be sure to include the prefix http://.
3. From the Files Of Type drop-down list, select HTML Documents (*.html, *.htm).
4. Click Open.
Excel opens HTML pages based on the HTML table structure of the page, so some Web design treatments may lead to some weird results in Excel. For pages that cleanly present data in a table format, however, this approach can be a real time-saver.

Hidden text in formula

To put hidden tax in formula person have to use N() Function

Example : =100000+50000+N("Party X+Party Y")
Output := 150000

It is helpful when you want to display any information along with formula in single Cell.

Creat Backup of your Excel File

To Create back up of your excel file automatically

Office Button ----> Save As --> Tools --> General Option

Or Click F12 and Than Tools --> General Option

And Mark Always Create Back up

Click Ok and replace the existing file with the new one.

How to apply different passwords or permissions to separate ranges in worksheets in Excel

To apply group-level protection to a worksheet, follow these steps:
1.Start Excel, and then open a blank worksheet.
2.On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.

Note If you are running Excel 2007, click Allow Users to Edit Ranges in the Changes group on the Review menu.
3.In the Allow Users to Edit Ranges dialog box, click New.
4.In the New Range dialog box, click Collapse Dialog, select the range B2:B6, and then click Collapse Dialog again.
5.In the Range password box, type rangeone, and then click OK twice. When prompted, retype the password.
6.Repeat steps 3 through 5, selecting the range D2:D6 and typing rangetwo as the password for that range.
7.In the Allow Users to Edit Ranges dialog box, click Permissions, and then click Add in the Permissions for Range2 dialog box.
8.In the Select Users, Computers, or Groups dialog box, type Everyone.
9.Click OK in the Select Users, Computers, or Groups dialog box, and then click OK in the Permissions for Range2 dialog box.
10.In the Allow Users to Edit Ranges dialog box, click Protect sheet, type ranger in the Password to unprotect sheet box, and then click OK twice. When prompted, retype the password.
11.Select cell B3, and then start to type Dataone. A password is still required. Click Cancel in the Unlock Range dialog box.
12.Select cell D3, and then type Datatwo.

When you apply different passwords to separate ranges in this way, a range that has been unlocked remains unlocked until the workbook is closed. When you unlock another range, you do not relock the first range. Likewise, when you save a worksheet, you do not relock a range.

You can use existing range names to identify cells that are to be protected with passwords, but if you do, Excel converts any relative references in the existing name definitions to absolute references. Because this may not give you the results you intended, it is preferable to use the Collapse Dialog button to select the cells, as described earlier in this article.

Inserting Current Date & Time

Here is the shortcut for entering Current Date in Selected Cell

=Ctrl+

And to Insert Current Time it will be

=Ctrl+Shift++

Tips to look more profession in Excel

Excel provides several underline formats to give your spreadsheets a professional look and to make them easier to read at a glance. For example, labels and values are often underlined to distinguish them from the rest of the data. To access these formats, click on the cell you want formatted and press [Ctrl]1. Click on the Font tab and then click on the drop-down arrow of the Underline combo box. This box offers four different types of line formatting. While the Single and Double format are appropriate for text labels, they are not appropriate for indicating totals and subtotals. For Totals, you should choose the Double Accounting format; for Subtotals, choose the Single Accounting format.

Another way you can make your worksheets look more professional is to remove zero values. For example, you've just copied a formula down a column, and now you have blocks of cells containing 0.00 or 0.00%. You could go back and delete the formulas from those particular cells, but an easier method would be to change the worksheet to avoid displaying or printing the zero values. To do so, follow these steps:

Go to Tools | Options.
Click the View tab.
Under the Window Options section, clear the Zero Values check box.
Click OK.

10 TIPS TO WORK FAST

#1: Exploit defined names

Defined names aren't just for ranges. You can use a defined name to define a constant value, such as a discount amount. Use the feature as you normally would, entering the literal value or expression that evaluates to the desired value into the named cell. For instance, select a cell and choose Name from the Insert menu. Then, select Define. Enter the descriptive name Discount and click OK. Now, in the same cell, enter the actual discount amount, say 3 percent (just enter .03). Now, you can use the defined name, Discount, in your formulas instead of entering the literal value .03. For instance, Excel would use .03 for Discount when evaluating the following formula:

=TotalPrice - (TotalPrice * Discount)

This quick tip has two benefits: It makes updating much simpler, as you can quickly change the value in Discount and Excel will automatically update all dependent formulas. And it eliminates data entry errors.

#2: Quick copy to noncontiguous cells

Copying data or a formula is simple. You just drag the source cell's fill handle and Excel copies the data or formula from the source cell to the cells you select using the fill handle. But copying isn't always a nice, neat, contiguous package. Sometimes you need to copy data or a formula to a series of noncontiguous cells. You could paste the source data into each individual cell, but that's the hard way. Instead, you can copy data into a noncontiguous block.

First, copy the source data. Then, hold down the Ctrl key while you click each cell in the noncontiguous destination range. Once you've highlighted each target cell, press Ctrl+V, and Excel will copy the source data into each of the highlighted cells. Formulas copied this way obey referencing rules, in regard to absolute and relative addresses.

An alternate method is to right-click in the cell that contains data you want to copy and choose Copy from the resulting submenu. Then, right-click a destination cell and choose Paste. At this point, the source cell is still highlighted, which means you can copy the contents again. Right-click another destination cell and choose Paste. Continuing selecting destination cells until you've completed the copy task. Press Esc to clear the selection of the source cell.

#3: Customize defaults

Excel uses template files to control default settings in new workbooks and sheets. For most of us, the settings are adequate. However, if you find yourself resetting the same defaults for each new workbook or sheet, consider changing the defaults permanently.

To change default settings for a workbook, open Book.xlt, make changes, and then save the file. Don't change the file's name; you're just updating it. (It's a good idea to create a copy of the original Book.xlt so you can revert to Excel's original settings if necessary. Name the copy BookOriginalSettings.xlt or something similarly descriptive.) After changing Book.xlt, all new workbooks will use the custom settings you applied. To change a sheet's default settings, open Sheet.xlt, make the necessary changes, and save it.

If you don't have one or both files, simply create your own. Just be sure to save them in Excel's XLStart folder (\Program Files\Microsoft Office\XLStart).

#4: Enter repetitive data quickly

Tip #2 shows you how to copy existing data into noncontiguous cells. You can also use this technique to enter data into a series of noncontiguous cells. Hold down the Ctrl key and click all the cells into which you want to enter data. Then, type the text you want to enter and press Ctrl+Enter. Excel will enter the typed text into all of the cells in the noncontiguous selection.

#5: Create custom lists

Most of us work with sets of data that seem to repeat themselves throughout our projects. That means we can enter the same values in numerous spots. If you frequently enter the same dataset, consider creating a custom list. To do so, choose Options from the Tools menu and then click the Custom Lists tab. In the List Entries control, enter each item in the list, one entry per line, in the order in which you want it to appear. When you've completed the list, click Add. Excel will copy the list to the Custom Lists control. Click OK to close the Options dialog. To enter the list, select a cell and enter any name in the list. Then use the fill handle to complete the list.

If you want a partial list, enter the item you want to begin with and then pull down the fill handle. Excel will fill in the remaining names.

If the list already exists in the sheet, you don't have to retype it to create a custom list. Simply select the list before choosing Options from the Tools menu. Then, click Import on the Custom Lists tab.

#6: Customize movement

By default, the cell pointer moves down when you press Enter. Selecting the cell immediately below the current one won't always be what you need. For instance, some people enter data from column to column. You could press the Right Arrow key instead of Enter, but out of habit, most of us reach for Enter. Even if you can retrain yourself (or users) to use the arrow keys, they're far enough away from the main keys to slow down data entry.

Fortunately, you can change the cell pointer's default direction. Chose Options from the Tools menu and then click the Edit tab. Select the Move Selection After Enter check box (if necessary) and then choose a direction from the option's drop-down list. For instance, to move from column to column, you might choose Right instead of Down.

While entering data, you can temporarily force the cell pointer to move in the opposite direction by holding down the Shift key while you press Enter.

#7: Hide everything but the working area

You usually hide a column or row to conceal or protect data and formulas. You can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area.

To hide unused rows, select the row beneath the sheet's last row. (Select the row header to select the entire row.) Next, press Ctrl+Shift+Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl+Shift+Right Arrow and then choose Column from the Format menu instead of Row.

Before you hide anything, make sure you don't inadvertently hide an obscure area by pressing Ctrl+End to find the last cell in the sheet's used range. Unhide the rows and columns by selecting the entire sheet. Then, select Row or Column from the Format menu, and choose Unhide.

#8: View formulas, or not, quickly

You probably know that you can view all the formulas in a sheet by choosing Options from the Tools menu and selecting Formulas on the View tab. Doing so displays formulas instead of their evaluated results. But there's a quicker way. Press Ctrl+~ (the tilde character to the left of the number 1 on your keyboard). The keyboard combination toggles between formulas and normal view. When you're finished viewing the formulas, simply press Ctrl+~ again to return to normal view.

#9: Identify printed sheets

Printing a sheet is a common task. Some users find it useful to print the name of the workbook in the header or footer. In Excel 2003, you can accomplish this by choosing Page Setup from the File menu and clicking the Header/Footer tab. Then, choose the appropriate item from the Header control's drop-down list. Versions prior to 2003 can use the following VBA procedure to print the full file's pathname:

Sub FormatHeader()
With ThisWorkbook
ThisWorkbook.Worksheets(sheetname)PageSetup.LeftHeader = .FullName
End With
End Sub

where sheetname is the sheet's name as a string value. To make the procedure more dynamic, use ActiveSheet.Name instead. That way you can run it against any sheet in the workbook.

#10: Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula. (See Tip #1 for an alternate suggestion.)
Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.

Excel Shotcuts

Navigating in Excel Shortcut

Switch between Worksheets CTRL-PageUp/CTRL-PageDown

Switch between Workbooks CTRL-Tab

Move one character up, down, left, or right. Arrow keys

Go to end of a contiguous range CTRL-Arrow Keys

Select a cell range SHIFT+Arrow keys

Highlight a contiguous range SHIFT-CTRL-Arrow Keys

Select entire worksheet CTRL+A

Move to the beginning of the line. HOME

Go To F5

Move a Sheet/Copy a Sheet Alt-E-M

Change Zoom Sizing Alt-V-Z

Entering and editing data Shortcut

Complete a cell entry and select…

...the cell below. ENTER

...the previous cell above. SHIFT+ENTER

...the next cell to the right. TAB

...the previous cell to the left. SHIFT+TAB

Delete cell and then get inside the cell BACKSPACE

Delete cell/selection. DELETE

Edit inside a cell (edit cell mode) F2

Once inside edit cell mode (F2)…

...Start a new line in the same cell. ALT+ENTER

...Highlight individual characters within cells SHIFT+Arrow keys

...Highlight contiguous string within cells SHIFT+CTRL+Arrow keys

...Delete the preceding character. BACKSPACE

...Delete the character to the right of the insertion point. DELETE

...Cancel a cell entry. ESC

Spell Check. F7

Insert a comment. SHIFT+F2

Fill down. CTRL+D

Fill to the right. CTRL+R

Undo the last action. CTRL+Z

Redo the last action. F4 or CTRL+Y

Hiding / Unhiding Rows and Columns Shortcut

Hide the selected rows. CTRL+9

Unhide any hidden rows within the selection. CTRL+SHIFT+( (opening parenthesis)

Hide the selected columns. CTRL+0 (zero)

Unhide any hidden columns within the selection. CTRL+SHIFT+) (closing parenthesis)

Selecting, grouping, inserting, and deleting cells Shortcut

Highlight Entire Row SHIFT+SPACEBAR

Highlight Entire Column CTRL+SPACEBAR

Group Rows or Columns SHIFT+ALT+RIGHT ARROW KEY

Ungroup Rows or Columns SHIFT+ALT+LEFT ARROW KEY

Clear the contents of the selected cells. DELETE

Delete the selected cells. CTRL+MINUS SIGN

Insert blank cells. CTRL+SHIFT+PLUS SIGN

Comaring Two Workbook

To compare two sheets from the same workbook, choose "Window->New Window" and then the side-by-side comparison should work.

Also use Window --> Arrange -->Select(Horizontal,Vertical,Tiled,Cascade) to compare more easily.

Format All Wroksheets in a Workbook

If you need to format all the worksheets in a workbook the same way, you can hold down the Shift or Control button and select the worksheets. Now that you have the worksheets selected, you can change things like cell or page properties that will apply to all the worksheets selected. After you're done, click on an inactive worksheet (if you've selected all the worksheets) or click on an unselected worksheet (if you did not select all the worksheets) to release the hold.

F4 Function

The 'F4' is easily the most useful shortcut key in Excel. Basically it repeats your last command.
So say you changed the color of a font in a cell. Now click in a new cell and hit 'F4' and it will change the font in the new cell to the same color.Wonderful, right? Well what's great about this is that it works for nearly everything else in Excel too.Highlight a row, right-click and choose 'Delete'. Now highlight another row and hit 'F4'. It deletes that row.Whatever you did last, Excel will attempt to repeat the same command until you do something else. This works great for things that require you to go to the menus or right-click and make choices or click buttons that bring up more dialogs. It can be huge time saver once you get used to using it. As the help says, it doesn't work in every situation but most times 'F4' works great.

No to Word Converter Add In

Today I Wanna give you all Number to word convertor add in

you can get it from the following link

http://cid-8debefc810167f39.skydrive.live.com/self.aspx/.Public/No%20in%20word%20Excel%20Addin.xla.xls

all you have to do is to download this from the following link and add it to your add-in

for those who don't know how to add a add-in in Excel here is the details.
(Detail are for Excel 2007)
First go to Office Button on the top of Excel-file (Round One)
Than go to Excel Options-> Add in-> Manage Excel Add-in (Go)->
A new window will appear called Add In
go to Browse and pick up the file from the specified location (Where you stored the addin)
mark the addin name appear in the list and click ok

Now how to use it

It is so simple just put the formula on the cell (=Spellnumber(cell no.))

And your value will convert in words for exp.

If in Cell No. A1 Contains 1000 than put the formula in cell B1 (=spellnumber(A1))
It will provide you value of "Rupees One Thousand"

Thanks & Regards
Hemant

Name of last Month

Dear Friends,


When someone prepare some reports and wanna pun headings like “For the month of December, 2009”
“Report for the month of January, 2010”

Than the question comes in mind that "Is there any way so the month will be automatically updated?"

Answer is yes there is a simple way for this first put the formula of today in the cell that is "=Today()" and than do the small work as described under

GO to format cells--->Custom-->"For the month of" mmmm-yy--->Click Ok

and you will get the desired result.


Regards
Hemant

Hi All

Hi All,

Well first of all this is my first interaction with all of you in this manner here is my small intro. I am Hemant Koranne work in Middle Management in a reputed company. I think now you people wanna know why I am here well to describe it I would like to give an example.

On guy you can name him any thing as you like I Will call him Sachin so What Sachin do well Sachin is guy working with a firm XYZ Ltd. as an Executive and he use to submit his reports buy using MS-Excel. But he was not so confident and proficient with excel. So he use to work on simple formulas and work hard to get results from his own prepared files or you can say data. Well there was on more guy on the same post and same working profile named Piyush well piyush is also a hard working person but he also no to use excel smartly. He used to work on the same kind of files but got better results and analysis of the data so he used to the one in the management.

By analyzing the both persons capabilities and capacities we can get that now a days we required more on the Hard work than Smart Work.

So Here I am to provide you the tricks and tips on the same part of our official life that is MS Excel.

So be good in excel & get better in life.