The Quick Find Next (Repeat Search without Dialogue Box) – Excel Tip

 

In order to search a text in an Excel File  we need to invoke the “Find and Replace” tool in Microsoft Excel by hitting [Ctrl]+[F] as shown in figure 1.1 below…

Figure 1.1

To search the next occurrence of the text one has to again invoke the same “Find and Replace” dialogue box.

However, not many of us really know that Excel provides a quick way to repeat the search without invoking the Find dialogue. The quick Find Next works as stepped out below…

1. Invoke the “Find and Replace” dialogue box by pressing [Ctrl]+[F] key combination
2. Type the text that you intend to search and click “Find Next” button in “Find and Replace” dialogue box.
3. Press [Esc] key to close the “Find and Replace” dialogue box.
4. Press [Shift] + [F4] key combination to repeat the search without invoking “Find and Replace” dialogue.

I hope you found the tip useful and thank you for reading.

Recover the Deleted Items in Outlook without a recovery tool – Outlook Trick

 

If you don't know what a Hex editor is, you probably shouldn't be hex editing anything, but if you want to try, Google for "hex editor" - UltraEdit is perhaps the best and easiest one to use. Before doing anything to the PST with a Hex Editor, make a copy of the PST, or you may end up losing all of your email.

1. Open the PST in the Hex editor.
2. Delete positions 7 through 13 with the spacebar. Since you're using hexadecimal numbering, this actually clears 13 characters in the following positions:
   00007, 00008, 00009, 0000a, 0000b, 0000c, 0000d
   0000e, 0000f, 00010, 00011, 00012, 00013
   As you clear the characters, the editor displays the code “20” in their position.
3. Save the PST, it is now corrupted.
4. Run the Inbox Repair Tool, SCANPST.exe, to recover the file. Use Windows Search utility to find it. For more information on the Inbox Repair Tool, see KB article 287497
5. The Inbox Repair Tool creates a backup and repairs the damage and recreates the PST.

Open the new PST in Outlook. The Deleted Items folder should now contain the deleted messages, unless Outlook has already deleted them for good by compacting the PST.

I have recovered lost folders and mails using this method several times. I hope you all would be able to recover your lost emails.

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

How Outlook's Deleted Items folder works?

 

A PST is a database. Items are records within the database and there is an index that points to each item. When you empty the Deleted Items folder, Outlook doesn't actually delete the items, it just deletes the items' listings from the index. The item is still in the PST, but unrecoverable because Outlook has no idea where it is without the pointer in the index. The space the item takes up is called "whitespace".

When you Compact a PST, the item is finally removed permanently and the whitespace is recovered, often shrinking the PST by many megabytes. Once the PST has 20% "whitespace", Outlook begins compacting the PST. If the Deleted Items folder contained a lot of messages, Outlook may begin compacting the PST immediately and the items will be deleted forever within a few minutes.

To recover the items which are no longer in the index you need to force Outlook to rebuild the index by causing corruption. You can cause corruption by using a Hex editor to delete some characters from the beginning of the PST file. If you delete the wrong ones you'll cause corruption but not in the index and Outlook won't rebuild the index.

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

Convert Formula into Value. The Magic F9 key.

You all might have come across a situation where you used a formula in a cell and wanted to replace the output/formula result with the cell contents (i.e. the formula itself). e.g. If a cell contains a formula =SUM(A1:A10) and while entering you wanted the output of the above formula to be entered into the cell instead of the formula then what would you do?

• Perhaps you would calculate the output and enter it manually in the requisite cell.  OR
• You would copy the cell and user paste special feature to paste as value and thus convert the formula output into value.

There is a third better and short cut way of achieving the same result. Following is quick steps…

1. Select the cell containing the formula
2. Press [F2] key to edit the cell (Figure 1.1)
3. Press [F9] key to convert formula into value. (Figure 1.2).
4. Hit [Enter] and you are done.

The figure 1.1 and Figure 1.2 shows the same graphically.

Figure 1.1 (Step 2 above when you press [F2] key)

Figure 1.2 (Step 3 above when you press [F9] key)

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

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.

Making your Excel 2007 files backward compatible

 

While working with Excel 2007 several users have been inadvertently creating files that are not compatible with the older version of MS Excel e.g. 2003, 97 etc. One way to ensure that you workbook is compatible with the older version you need to Save your workbook in appropriate Excel format that recipient of the file is expected to have or use the compatibility checker tool on your workbook so that you can fix potential issues.

To ensure that a MS Office Excel 2007 workbook does not have compatibility issues that may cause loss of some or several functionality in an earlier version of Excel, you must run the Compatibility Checker tool unless you are already working on a file created in earlier version of MS Excel. The Compatibility Checker tool discovers any potential compatibility issues and can create a report for you. Follow the below instructions to use Excel 2007’s Compatibility Checker tool…

• In Office Excel 2007, open the workbook that you want to check for compatibility.
• Click Microsoft Office Button ( )
• Click Prepare
• Click Run Compatibility Checker.
• To ensure the Compatibility is checked for every time you save the workbook, select the Check compatibility when saving this workbook check box.
• To create a report in a separate worksheet of all the issues that are listed in the Summary box, click Copy to New Sheet.
• Clicking Fix button may resolve some simple compatibility issues.

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

Files Types and Excel 2007

Excel 2007 supports several file type for editing or opening workbook files created in other program. It can also convert excel files into other file types compatible with such programs. Below tables lists various files type supported by MS Excel 2007 under following categories….

• MS Excel Formats
• Text Formats
• Clipboard Formats
• Other Formats

Note: If you save a workbook in Text, CSV, Image type or any other format there may be loss for functionality available in the original file.

Excel Format

Format	Extension	Description
Excel Workbook	.xlsx	XML-based file format. Note: It cannot store VBA macro code or MS Excel 4.0 macro sheets (.xlm).
Excel Workbook (code)	.xlsm	XML-based, macro-enabled file format. Unlike .xlsx file format it can store VBA macro code or Excel 4.0 macro sheets (.xlm).
Excel Binary Workbook	.xlsb	MS Excel 2007 Binary file format (BIFF12).
Template	.xltx	Excel 2007 file format for an Excel template. It does not store VBA macro code or Excel 4.0 macro sheets (.xlm).
Template (code)	.xltxm	Excel 2007 macro-enabled Excel template file format that can store VBA macro code or Excel 4.0 macro sheets (.xlm).
Excel 97- Excel 2003 Workbook	.xls	The Excel 97 - Excel 2003 Binary file format (BIFF8).
Excel 97- Excel 2003 Template	.xlt	The Excel 97 - Excel 2003 Binary file format (BIFF8) for an Excel template.
MS Excel 5.0/95 Workbook	.xls	The Excel 5.0/95 Binary file format (BIFF5).
XML Spreadsheet 2003	.xml	XML Spreadsheet 2003 file format (XMLSS).
XML Data	.xml	XML Data format.
Excel Add-In	.xlam	Excel 2007’s XML-based macro-enabled Add-In (a supplemental program that is designed to run additional code) that supports VBA projects & Excel 4.0 macro sheets (.xlm).

 

Text Formats

Format	Extension	Description
Formatted Text (Space-delimited)	.prn	Lotus space-delimited format. It can store only the active sheet.
Text (Tab-delimited)	.txt	Saves a workbook as a tab-delimited text file. It can store only the active sheet.
Text (Macintosh)	.txt	Saves a workbook as a tab-delimited text file. It can store only the active sheet.
Text (MS-DOS)	.txt	Saves a workbook as a tab-delimited text file. It can store only the active sheet.
Unicode Text	.txt	Saves a workbook as Unicode text, a character encoding standard that was developed by the Unicode Consortium.
CSV (comma delimited)	.csv	Saves a workbook as a tab-delimited text file. It can store only the active sheet.
CSV (Macintosh)	.csv	Saves a workbook as a tab-delimited text file. It can store only the active sheet.
CSV (MS-DOS)	.csv	Saves a workbook as a tab-delimited text file. It can store only the active sheet.
DIF	.dif	Data Interchange Format. Saves only the active sheet.
SYLK	.slk	Symbolic Link Format. Saves only the active sheet.

Clipboard Formats

Format	Extension	Clipboard type Identifiers
Picture	.wmf or .emf	Pictures in Windows Metafile Format (WMF) or Windows Enhanced Metafile Format (EMF).
		Note    If you copy a Windows metafile picture from another program, Excel pastes the picture as an enhanced metafile.
Bitmap	.bmp	Pictures stored in Bitmap format (BMP).
Microsoft Excel file formats	.xls	Binary file formats for Excel versions 5.0/95 (BIFF5), Excel 97-2003 (BIFF8), and Office Excel 2007 (BIFF12).
SYLK	.slk	Symbolic Link Format.
DIF	.dif	Data Interchange Format.
Text (tab-delimited)	.txt	Tab-separated text format.
CSV (Comma-delimited)	.csv	Comma-separated values format.
Formatted text (Space-delimited)	.rtf	Rich Text Format (RTF). Only from Excel.
Embedded object	.gif, .jpg, .doc, .xls, or .bmp	Microsoft Excel objects, objects from properly registered programs that support OLE (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.)
Linked object	.gif, .jpg, .doc, .xls, or .bmp	OwnerLink, ObjectLink (ObjectLink: An OLE data format that describes a linked object, identifying the class, document name, and name of an object. Each of these data items; is a null-terminated string.), Link, Picture, or other format.
Office drawing object	.emf	Office drawing object format or Picture (Windows enhanced metafile format, EMF).
Text	.txt	Display Text, OEM Text.
HTML	.htm	Hypertext Markup Language.
		Note    When you copy text from another program, Excel pastes the text in HTML format, regardless of the format of the original text.

Other Formats

Format	Extension	Description
Quattro Pro 5.0 (Win)	.wb1	Quattro Pro version 5.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.
Quattro Pro 7.0 (Win)	.wb3	Quattro Pro version 7.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.

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

Grouping Worksheets in MS Excel

I am not able to imagine the why one would want to group or ungroup worksheets in an Excel Workbook. Also have not tested this on Office 2003 platform so do not know the applicability there as of now.

Grouping Worksheet together.

One can group multiple sheets in MS Excel by selecting one or more sheet together.

• To select a single worksheet simply click on the worksheet.
• To select multiple adjacent sheets click on the first worksheet; Press and Hold the [Shift] key and then click the last worksheet. This will group all the worksheets together. You might have to use Tab Scrolling Buttons to navigate through worksheets tabs.
• To select multiple non-adjacent worksheets; press and hold [Ctrl] key and click one or more non-adjacent worksheets.

Note: When you select two or more adjacent or non-adjacent worksheets together they are grouped by Excel. The status of grouped sheets are appears as [Group] in the title bar of the open worksheet.

Impact of Grouping Sheets

Grouping worksheets together changes the way excel treats grouped worksheets when editing, printing or other such operations.

• Data being entered or edited in the active worksheet in group is reflected in all selected sheets.
  Note: The changes in active worksheet in the group apply to all worksheets that constitute the group. Hence editing a cell would replace data on the active sheet and on other sheets in group.
• Data that you copy or cut in grouped sheets cannot be pasted on another sheet, because the size of the copy area includes all layers of the selected group and is therefore different from the paste area in a single sheet outside the group.
  Note: Make sure that only one sheet is selected before you copy or move data to another worksheet.

Ungrouping the Worksheets

To ungroup the selected worksheets right click on one of the sheet in the group and then click Ungroup Sheets in the context sensitive menu.

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.

How to Open System Properties without minimizing current Window?

 +  =  System Properties dialog box

In order to find out details about RAM, OS, Processor speed we all have been doing below set of steps…

1. Minimize All Application Window to view Desktop
2. Right Click on My Computer
3. Left Click on Properties in the context sensitive menu.

But there is a smarter way of doing this. To quickly view the system properties simply hold “Win” Key on your keyboard and then press “Pause/Break” key.

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.