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.