Auto Complete Functions in Excel 2007/2010

In Excel 2010 you must have noticed that while typing the formula in a cell excel displays a list of functions starting with the characters typed in the cell as shown in figure 1.1 below…

Figure 1.1

Excel 2007/2010 provides auto complete functionality to complete the function without the need to type it. To auto complete the desired function you can click the desired function name in the auto complete list or hit [TAB] key on the keyboard. The function name along with open parenthesis appears i the cell for you proceed with entering the function parameters as shown in figure 1.2 below.

Figure 1.2

Thus Auto complete makes life a bit easier for the power users.

I hope you found the information useful and Thank you for reading.

Auto Complete Functions in Excel 2010

In Excel 2010 you must have noticed that while typing the formula in a cell excel displays a list of functions starting with the characters typed in the cell as shown in figure 1.1 below…

Figure 1.1

Excel 2010 provides auto complete functionality to complete the function without the need to type it. To auto complete the desired function you can click the desired function name in the auto complete list or hit [TAB] key on the keyboard. The function name along with open parenthesis appears i the cell for you proceed with entering the function parameters as shown in figure 1.2 below.

Figure 1.2

Thus Auto complete makes life a bit easier for the power users.

I hope you found the information useful and Thank you for reading.

Did You Know ? You could Quickly Delete several Blank Rows in Excel

Guys if you have a huge worksheet with several blank rows, what do you do? The answer is simple.. Delete Them so that data is organized and can be used more meaningfully.

Question now is how do you delete all such blank row in single go? Do you write a macro? or Do you select each row manually and delete them? But what if the blank rows are in hundreds?

There is one simple and quick way to handling such spread sheets. Guess what? The best way to handle such scenario is to sort your data. Depending on your sort criteria the blank rows would either appear on top or at bottom. If they are at bottom you simply have no issues otherwise you could simply delete the blank rows.

But there could be an issue. What if you wanted all your data to be restored back in original order post deleting blank rows. There is a solutions to this. Below steps would be helpful…

1. Insert a column before the first column in data or use column next to the last column.
2. Enter numbers in the ascending order in first cell and drag it down till the last row containing data.
3. Sort the data on column other than the one you inserted in step 1.
4. Delete blank Rows in bulk
5. Sort the data back on column you inserted in step 1 above.

I hope you found the information useful and Thank you for reading.

Did You Know ? You could Quickly Delete several Blank Rows in Excel

Guys if you have a huge worksheet with several blank rows, what do you do? The answer is simple.. Delete Them so that data is organized and can be used more meaningfully.

Question now is how do you delete all such blank row in single go? Do you write a macro? or Do you select each row manually and delete them? But what if the blank rows are in hundreds?

There is one simple and quick way to handling such spread sheets. Guess what? The best way to handle such scenario is to sort your data. Depending on your sort criteria the blank rows would either appear on top or at bottom. If they are at bottom you simply have no issues otherwise you could simply delete the blank rows.

But there could be an issue. What if you wanted all your data to be restored back in original order post deleting blank rows. There is a solutions to this. Below steps would be helpful…

1. Insert a column before the first column in data or use column next to the last column.
2. Enter numbers in the ascending order in first cell and drag it down till the last row containing data.
3. Sort the data on column other than the one you inserted in step 1.
4. Delete blank Rows in bulk
5. Sort the data back on column you inserted in step 1 above.

Microsoft Office 2010 in Beta State

 

For all office lovers, users and those who hate as well there is a news about the new version of Microsoft Office. Yes you heard it right. Though many of us do have developed the comfort with the Office 2007 Microsoft is about to release Office 2010.

But not to worry. Microsoft Office is in Beta State as of now and is available for download in case you wish to evaluate and contribute to the features of the Microsoft Office. The download is available in two flavors as listed below…

1. Microsoft Office 2010 Developers download
2. Microsoft Office 2010 IT Professionals download

Needless to describe which download is intended for which audience. For Microsoft lovers this is certainly a good news. And for all those anti-Microsoft the new opportunity to find holes and vulnerability in the new program.

To grab a beta testing copy of Microsoft Office 2010 please visit www.microsoft.com or Click here.

I hope you found the information useful and Thank you for reading.

Using Tab Scrolling Buttons in MS Excel

 

Tab Scrolling Button are nothing but the sheet navigation buttons and are used to to scroll through the available worksheets in a Microsoft Excel Workbook. When you start the MS Excel application you see three worksheets by default. However, if your workbook includes several worksheets, you would be forced to use the Sheet Navigation Button for viewing the hidden worksheets. The sheet navigation button is located at bottom left of the Excel application interface as shown Figure 1 below.

Figure 1

The Tab Scrolling Buttons are used to scroll through the list of available sheets in an Excel Workbook File. If you are able to see all worksheets in current view then clicking it will have no visual change in current view.

The Tab Scrolling buttons can also be used to jump to a particular worksheet without scrolling through all the sheets. In order to jump to a sheet directly right click on any of the Sheet Navigation Buttons. A list of available sheets in the Workbook is displayed in Figure 2 below.

Figure 2

You can click on a sheet to directly jump to that. This tip becomes handy when working on an excel workbook with several worksheets.

I hope you found the information useful and Thank you for reading.

MS Excel Editing Tips and Tricks

 

Most of us work on excel. Data entry using excel in cell at times becomes very tedious and therefore most of us prefer word when it come to editing text for the data entry. There are several quick tips and tricks available to make one’s life easy while working with MS Excel for data entry and manipulation…

Task / Objective	Shortcut / Key Sequence
New Workbook	[Ctrl] + [N]
Edit Current Cell	[F2]
Enter multiple lines of data with line breaks the way you can type in MS Word Tables. To insert carriage return use..	[Alt] + [Enter]
Wrapping Text enter in the cell	[Ctrl] + [1] + {Click Alignment Tab} + {Wrap Text}
Change Relative Cell Ref (e.g. A1) to Absolute ref. (e.g. $A$1, A$1,$A1). Relative references change when copying data from one cell to another. Absolute reference do not change.(Toggle Key)	[F4]
Toggle between Show Formula Mode/Normal Mode.	[Ctrl] + [`]
Close Current Workbook. Will prompt to save before closing in case of unsaved changes	[Ctrl] + [w] / [Ctrl] + [F4]
Create Table and Set filters on table headers	[Ctrl] + [L] Or [Ctrl] + [T]
Insert Hyperlink Dialog	[Ctrl] + [K]
Auto Sum	Alt + [-]
Current Date	[Ctrl] + [;]
Current Time	[Ctrl] + [:]
Complete Formula with parenthesis and parameters	[Ctrl] + [Shift] + [A]
Delete Selected Cell	[Ctrl] + [-]
Insert Blank Cell	[Ctrl] + [Shift] + [=] or [Ctrl] + [+]
Insert Cut Cells above current selection	[Ctrl] + [Shift] + [=] or [Ctrl] + [+]
Insert Copied Cells above current selection	[Ctrl] + [Shift] + [=] or [Ctrl] + [+] Select appropriate option and click ok
Copy data from Cell above current selection	[Ctrl] + [']
Copy data from Cell above current selection along with formatting	[Ctrl] + [d]
Copy Contents from Cell to the Left of current selection	[Ctrl] + [R]

Happy Editing… Cheers

I hope you found the information useful and Thank you for reading.

MS Excel Quick Formatting Tips

 

Formatting Data in Excel take hell lot of time. It is therefore necessary for every one to know the quick key sequences. The table below lists such short cut to enable you with some quick data formatting key sequences.

Task / Objective	Shortcut / Key Sequence
Cell Format Cell Dialog (Gives option for various formatting options including fonts, border, color, protection, data type etc.)	[Ctrl] + [1]
Bold / Normal (Toggle Key)	[Ctrl] + [2] / [Ctrl] + B
Italic / Normal (Toggle Key)	[Ctrl] + [3] / [Ctrl] + I
Underline / Normal (Toggle Key)	[Ctrl] + [4] / [Ctrl] + U
Strike Out / Normal (Toggle Key)	[Ctrl] + [5]
Box the Current Selection	[Ctrl] + [Shift] + [7] or [Ctrl] + [&]

I hope you found the information useful and Thank you for reading.

MS Excel Navigation Shortcuts

There are several navigational shortcuts that can help one expedite the work by allowing quick Navigation within worksheet and across worksheets/workbook in Microsoft Excel. Below tables lists such know short-cuts. Some shortcuts are likely to be specific to a particular version of Microsoft Excel and however, in general should be applicable to all.

Task / Objective	Shortcut / Key Sequence
Jump to Cell A1	[Ctrl]+[Home]
Jump to the last cell in the worksheet that contains Data.	[Ctrl]+[End]
Jump to Next Sheet (To the right of current sheet)	[Ctrl]+[PgDn]
Jump to Previous Sheet (To the left of current sheet)	[Ctrl]+[PgUp]
Go To a Cell	[F5]
Toggle between open workbooks. More than one workbook (excel file) must be open	Ctrl + TAB
Hide Column	[Ctrl] + [0]
Unhide Column	[Ctrl] + [Shift] + [0]
Hide Row	[Ctrl] + [9]
Unhide Row	[Ctrl] + [Shift + [9]
Show/Hide Chart	[Ctrl] + [6]
Select Multiple Cells (Hold [Ctrl] key while clicking cell. You can click random cells in the worksheet.)	[Ctrl] + [Click]
Select Range of Cells (Hold Shift Key while selecting cells to select a range)	[Shift] + [Click] or [Shift] + {Arrow Keys}
Select current data block (cell containing data including and adjacent to current selection)	[Ctrl] + [*] or [Ctrl] + [Shift] + [8]

I hope you found the information useful and Thank you for reading.

MS Excel Shortcuts

You may want to keep below set of key sequences handy as it can definitely improve your productivity while working with Excel.

Task	Key Sequence
Hide Column	[Ctrl] + [0]
Unhide Column	[Ctrl] + [Shift] + [0]
Hide Row	[Ctrl] + [9]
Unhide Row	[Ctrl] + [Shift] + [9]
Refresh	[Ctrl] + [6]
Strike Out	[Ctrl] + [5]
Underline	[Ctrl] + [4]
Italic	[Ctrl] + [3]
Bold	[Ctrl] + [2]
Format Cell Dialog	[Ctrl] + [1]
Change Relative Cell Ref to Absolute ref. (Toggle Key)	[F4]
Edit Current Cell	[F2]
vzGo To a Cell	[F5]
Toggle between Show Formula/Normal Mode in whole worksheet (Toggle Key)	[Ctrl] + [`]
Close Current Workbook	[Ctrl] + [w]
Copy Contents of Cell to Left	[Ctrl] + [R]
Create Table and set filters	[Ctrl] + [L] Or [Ctrl] + [T]
Insert Hyperlink Dialog	[Ctrl] + [K]
Current Date	[Ctrl] + [;]
Current Time	[Ctrl] + [:]
Copy Contents of Cell above current selection	[Ctrl] + [']
New Workbook	[Ctrl] + [N]
Delete Selected Cell	[Ctrl] + [-]
Insert Blank Cell	[Ctrl] + [=]
Auto Sum	Alt + [=]
Complete Formula with parenthesis and parameters	[Ctrl] + [Shift] + [A]
Toggle between open workbooks	[Ctrl] + [TAB]
Open Worksheet to left of current worksheet	[Ctrl] + [PgUP]
Open Worksheet to right of current worksheet	[Ctrl] + [PgDn]
Box the Current Selection	[Ctrl] + [Shift] + [7] or [Ctrl] + [&]

I hope you found the information useful and Thank you for reading.