Excel Shortcuts

Dear All,
Today I am going to introduce some new excel shortcuts for you. So kindly not below mention excel shortcuts.



Shortcut Keys Description
F2 Edit the selected cell.
F5 Go to a specific cell. For example, C6.
F7 Spell check selected text and/or document.
F11 Create chart.
Ctrl + Shift + ; Enter the current time.
Ctrl + ; Enter the current date.
Alt + Shift + F1 Insert New Worksheet.
Shift + F3 Open the Excel formula window.
Shift + F5 Bring up search box.
Ctrl + A Select all contents of the worksheet.
Ctrl + B Bold highlighted selection.
Ctrl + I Italic highlighted selection.
Ctrl + K Insert link.
Ctrl + U Underline highlighted selection.
Ctrl + 5 Strikethrough highlighted selection.
Ctrl + P Bring up the print dialog box to begin printing.
Ctrl + Z Undo last action.
Ctrl + F9 Minimize current window.
Ctrl + F10 Maximize currently selected window.
Ctrl + F6 Switch between open workbooks / windows.
Ctrl + Page up Move between Excel work sheets in the same Excel document.
Ctrl + Page down Move between Excel work sheets in the same Excel document.
Ctrl + Tab Move between Two or more open Excel files.
Alt + = Create a formula to sum all of the above cells
Ctrl + ' Insert the value of the above cell into cell currently selected.
Ctrl + Shift + ! Format number in comma format.
Ctrl + Shift + $ Format number in currency format.
Ctrl + Shift + # Format number in date format.
Ctrl + Shift + % Format number in percentage format.
Ctrl + Shift + ^ Format number in scientific format.
Ctrl + Shift + @ Format number in time format.
Ctrl + Arrow key Move to next section of text.
Ctrl + Space Select entire column.
Shift + Space Select entire row.

Research into Excel use by Ed Bolton

in Computers / Software (submitted 2010-11-13)
Microsoft Excel has 400 million worldwide users and is so essential to business that people have stopped asking simple questions like "how much does it get used?" In fact, if you do a web search for Excel research, you are unlikely to find sites relating to the software itself.

This is particularly strange because there are so many people out there offering Excel training. It seems these people do not even feel they need to prove the benefits of such courses. It is just taken as blindingly obvious that employees could be using Excel more efficiently.

The best statistics out there come from a 2009 Dartmouth College study with the somewhat verbiose title "Comparison of Characteristics and Practices amongst Spreadsheet Users with Different Levels of Experience". They surveyed 1600 individuals but half of them were self-selecting experts. However, they have produced separate results for basic users.

From the subset of basic users, we can deduce that 5% of all employee time is spent on Excel. That's 2 hours a week. Every week. Even more interesting is the fact that 80% of employees use more than 1 spreadsheet a week. There are few people out there who never use Excel. The only significant sample bias is that 70% of interviewees were male. The average age was over 40 and no more than 20% worked in any single sector of the economy.

What is so fascinating about Microsoft Excel, is that nearly everyone (98% of respondents) will create spreadsheets from scratch and that they will use these spreadsheets week after week. Most spreadsheets are used for over a year.

The reason this research is of interest is that it suggests that people dedicate a significant proportion of their time to updating their own spreadsheets. If you've never updated a spreadsheet, you may not appreciate how tedious an activity this can be. In the vast majority of cases, users will simply transferring data from one place on their computer to another.

In an ideal world, this would all be automated and optimized. In the real world, it won't be. That's because most users won't know how much time they are wasting e.g. if you can cut a 20 minute weekly task down to 10, that's going to save a day a year. Sometimes you can realize those savings simply by adding a new formula to a sheet.

However, it is certainly the case that when a user's taking 2 days every month preparing monthly reports, they tend to question whether they could be doing it a better way. Mainly because the robotic nature of updating a spreadsheet is extremely boring. It's bad for employee motivation, it also wastes business time.

The solution is to provide users with on-demand Excel support. This can either come from within or outside the organization, although the latter should be assumed more efficient.

The fact there are solutions though, does not address the fundamental question of how much Excel gets used, and how much more efficiently it could get used. The answer to that will only come when more researchers pick up where the Dartmouth study left off. The simple truth is that, with 400 million users, we are probably wasting thousands of lifetimes every single day and don't even realize it.


About the Author
Ed Bolton is the founder of Excel4Business, providers of Excel Support.

TIME CALCULATION

Time Calculation

Excel can work with time very easily.
Time can be entered in various different formats and calculations performed.
There are one or two oddities, but nothing which should put you off working with it.
See the TimeSheet example for an example.

Typing time
When time is entered into worksheet it should be entered with a colon between
the hour and the minutes, such as 12:30, rather than 12.30

1:30 12:30 20:15 22:45

Excel can cope with either the 24hour system or the am/pm system.
To use the am/pm system you must enter the am or pm after the time.
You must leave a space between the number and the text.

1:30 AM 1:30 PM 10:15 AM 10:15 PM

Finding the difference between two times
You can subtract two time values to find the length of time between.

Start End Duration
1:30 2:30 1:00 =D24-C24
8:00 17:00 9:00 =D25-C25
8:00 AM 5:00 PM 9:00 AM If the result is not shown correctly,
You may need to reformat the answer.
Look at the section about formatting
further in this worksheet.

Adding time
You can add time to find a total time.
This works well until the total time goes above 24 hours.
For totals greater than 24 hours you may need to apply some special formatting.

Start End Duration
1:30 2:30 1:00
8:00 17:00 9:00
7:30 AM 5:45 PM 10:15
20:15

Formatting time
When time is added together the result may go beyond 24 hours.
Usually this gives an incorrect result, as in the example below.
To correct this error, the result needs to be formatted with a Custom format.

Example 1 : Incorrect formatting
Start End Duration
7:00 18:30 11:30
8:00 17:00 9:00
7:30 17:45 10:15
Total 6:45 =SUM(E49:E51)

Example 2 : Correct formatting
Start End Duration
7:00 18:30 11:30
8:00 17:00 9:00
7:30 17:45 10:15
Total 30:45 =SUM(E56:E58)


How To Apply Custom Formatting
The custom format for time use a pair of square brackets [hh] on either side
of the hours indicators.

1. Click on the cell which needs the format.
2. Choose the Format menu.
3. Choose Cells.
4. Click the Number tag at the top right.
5. Choose Custom.
6. Click inside the Type: box.
7. Type [hh]:mm as the format.
8. Click OK to confirm.

Filename formula

There may be times when you need to insert the name of the current workbook
or worksheet in to a cell.

This can be done by using the CELL() function, shown below.
C:\DOCUME~1\SALEHK~1\LOCALS~1\Temp\Temporary Directory 2 for Excel function TUTOR.zip\[Excel function TUTOR.XLS] Filename formula
=CELL("filename")

The problem with this is that it gives the complete path including drive letter and folders.
To just pick out the workbook or worksheet name you need to use text functions.

To pick the Path.
C:\DOCUME~1\SALEHK~1\LOCALS~1\Temp\Temporary Directory 2 for Excel function TUTOR.zip\
=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

To pick the Workbook name.
Excel function TUTOR.XLS
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

To pick the Worksheet name.
Filename formula
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

AutoSum Shortcut Key

	Instead of using the AutoSum button from the toolbar,
	you can press Alt and = to achieve the same result.
	Try it here :
	Move to a blank cell in the Total row or column, then press Alt and =.
	or
	Select a row, column or all cells and then press Alt and =.
			Jan	Feb	Mar	Total
		North	10	50	90
		South	20	60	100
		East	30	70	200
		West	40	80	300
		Total

Microsoft Excel Tips

Dear All,

Hope you will be fine. I am starting new blog about Microsoft excel tips. In this blog I will provide you new excel tips & formulas on daily basis.
First note the index which i will teach you daily lesson by lesson.

Lesson NO: 01

Age Calculation

You can calculate a persons age based on their birthday and todays date.
The calculation uses the DATEDIF() function.
The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.
(Makes you wonder what else Microsoft forgot to tell us!)

Birth date :	12-Nov-03
Years lived :	6	=DATEDIF(C8,TODAY(),"y")
and the months :	3	=DATEDIF(C8,TODAY(),"ym")
and the days :	13	=DATEDIF(C8,TODAY(),"md")
You can put this all together in one calculation, which creates a text version.
Age is 6 Years, 3 Months and 13 Days
="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"
Another way to calculate age
This method gives you an age which may potentially have decimal places representing the months.
If the age is 20.5, the .5 represents 6 months.
Birth date :	1-Jan-60
Age is :	50.15	=(TODAY()-C23)/365.25