Hi Friends,

After a long break, I again started to work on this blog with new time-saving techniques and tricks for making the day to day working on the most useful software as per my understanding of Microsoft Excel.

Expecting the same response from all of you.

Thanks
Hemant

Automatically numbering rows without error

A simple solution to numbering a row even if we delete or add a rows in between the serial numbers.

Formula for numbering the row is “=Row()”

What this formula do Row formula allow us to know the row no. in which we are working.

The question raise how we can use this formula for no the rows?

Answer is simple if you had start the counting from row no 10 then the formula will be “=Row()-9” than the cell will reflect value 1.

Use and enjoy working.

Select/Replace/Find Similar Format Cells

Some times people need to select similar format cells for analysis purpose or for editing the cells Now the question raise "How they can do this?" there is a simple process to this in excel same is defined here below. 1. Press "Ctrl + F" 2. Press "Options>>" 3. Click the arrow to the right of “Format” button (located at the right of the “Find what:” text box) 4. Select “Choose Format From Cell” (you can do it manually by using “Format” option instead) 5. Select the cell with the target format to select. The format is previewed at the right of “Find what:” text box 6. Press “Find All”. All the cells with the selected format are shown in the pane 7. Pick cells individually or press CTRL to select more than one to do the required action: replace, find, etc

one more important thing any one has to keep in mind while doing this is for using the find and replace command you have to select "clear find format".

Hope this tip will help you out some what.

By Numbers Excel 2003 v/s 2007

Download link for "21 Underground Excel Tips"

Hi all,

find the link of a good book for learning macros in Excel
http://www.4shared.com/office/bPq8kwot/21-underground-excel-tips-vol-.html
Regards

Microsoft Excel Bible 2007

Dear All

Here is the link to download Microsoft Excel 2007 Bible

http://www.4shared.com/office/bPq8kwot/21-underground-excel-tips-vol-.html

Regards
Hemant Koranne

Just past your name on single click

Here is the macro
Process is to use this is open your excel file click alt+F11 it will open one more window click on insert module & past this code in that module window. Replace your name with your own name
press save now close this window.
Sub yourname()
'
' yourname Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Application.WindowState = xlMinimized
Application.WindowState = xlNormal
ActiveCell.FormulaR1C1 = "yourname"
With Selection
.HorizontalAlignment = xlCenter
End With

ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Now just press Ctrl+Shift+N and see the magic.

Enjoy
Hemant

Remove Non Printable Items from Text

To Remove None Printable items from the cell containing text we just have to use a simple formula

=Clean(Cell number)

For Example In cell A1 you have text with some unwanted marks ahead of it just put a formula in Cell B1 =clean(A1) now you will see there are no non printable items are there they are gone now copy this and use past special and past value option your text is there without not printable items

Regards
Hemant

Multiple Row Filters in Same Sheet

I was working on my data sheet and countered with one problem

I required filters in two row ranges on being specific I want the first filter to be placed for data range from Row no. 1 to 200 and the second one I want for Row no. 210 to 350.

So I try to do it with filter options that are available in Excel 2007 & Excel 2010 but not getting it but whenever I put the filter on second range first filter goes disappear so with the help of search on various options I got the answer for this problem. And it is here for your help also this we can do without macros

First Go to Insert ribbon in MS Excel 2007
Click on Table A "Create Table" Window will open Select the data range and Mark My Table has headers option
And repeat the same function for another range you will get the desired results.

Regards
Hemant

for more you can refer this book.

https://amzn.to/2SFxDv4

Opening A Link Workbook

So many times we use another workbook data as a reference data by linking files to open those link or source files from the main file we could use following method

Go to Edit Menu open link and click on Open source

Or We can use Shortcut Key for this that is Ctrl+[

Regards
Hemant

Selecting cells that only contain Text in Microsoft Excel

By selecting cells that only contain text, you can distinguish between cells containing different types of data, which allows you to delete, fill or lock cells by type.
Technique 1

Press F5, or choose Edit, Go To…;
In the Go To dialog box, click Special.
In the Go To Special dialog box, select Constants.
Click OK.


Technique 2 - Conditional
Formatting

Select the data area.
From the Format menu, select Conditional Formatting.
In Condition 1, select Formula Is.
In the Formula Box, enter the formula =Istext(A1).
Click Format..., choose any format from the Format Cells dialog box, and click OK.
Click OK.

Using keyboard shortcuts to open the Insert Function dialog box:

Select an empty cell and press Shift+F3.
To open a Function Arguments dialog box:
Select a cell containing a formula and press Shift+F3.

To insert a new Formula into a cell using the Function Arguments dialog box:

1. Select an empty cell, and then type the = sign.
2. Type the formula name and press Ctrl+A.

To insert a formula by typing it while being guided by the formula syntax tooltip:

1. Select an empty cell, and then type the = sign followed by the formula name and a ( sign.
2. Press Ctrl+Shift+A (in Excel version 2003 the syntax appears immediately after step 1 above)

Change slash separator in date with period in Microsoft Excel

Some times most people love to use period instead of / and would like to change the default setting for the date format, perform the following steps:

From Windows, choose Start, Settings, Control Panel, Regional Options.

Select the Date tab. In the Date separator box, change the slash (/) to a period (.).

Click Apply and OK

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.