BEST EXCEL TIPS AND TRICKS | TOP 100 EXCEL TIPS AND TRICKS

Selection of Column differences This feature is mostly used, for to select the Column differences in the excel. How to do this – Step 1 - Please click on GoTo under Home tab or press F5 , then click on special will open a new dialogue box Step 2 - Choose the radio button Column differences Step 3 - Then click on   OK How it works - First select the range of cells, then if we want to select the cells which are different from the active cell, this feature or trick will helps you a lot. Remember - Active cell is the cell which will be displayed on Name box at the time of selection, if you want to change the active cell use tab key on your key board to move around the range selected.  

Selection of Row differences This feature is mostly used, for to select the Row differences in the excel. How to do this – Step 1 - Please click on GoTo or press F5 under Home tab, then click on special will open a new dialogue box Step 2 - Choose the radio button  Row differences Step 3 - Then click on  OK How it works - First select the range of cells, then if we want to select the cells which are different from the active cell, this feature or trick will helps you a lot. Remember - active cell is the cell which will be displayed on Name box at the time of selection, if you want to change the active cell use tab key on your key board to move around the range selected.

Sorting Data - Row Wise Step 1: Select data first, then click on Custom Sort under Home tab Step 2: Click on Options, then choose Sort left to right under Orientation Step 3: Click on OK button, again click on OK Now we can sort the data in row wise.

Consolidation Helps to consolidate the different worksheets data.  Whenever there is update on the individual sheet tabs, we can update that sheet tab to consolidated sheet with this trick. Step 1: Created sales sheet tab for Jan, Feb with Data - Click on   Consolidate button   from Data tab or menu. Select the range of data from reference and then click on   add . (Repeat for all sets of data Example: Jan Sales, Feb Sales) Step 2: Click the check box Top Row and Left column, then click OK . So that we can publish the consolidated data where ever we needed Note : If you click the check box "Create links to sources data" will help us to show the result along with formula links. With Browse option we can select and add from external excel files as well.

Hide cell contents by changing the cell format (in Excel) Useful in situations where you have a small amount of data you want to hide but still want to be able to work with (and you’re not required to hide the entire column or row). And when you print the data as well the cell hided will not be printed. Step 1 – Select the cell or cells you want to hide. In this example, cell C3 to C6 is selected. Step 2 – Select the option Format Cells or click Ctrl + 1  Step 3 – The Format Cells options will appear; select Custom Step 4 – Type a three semi-colon marks (;) under  Type Step 5 – Select  OK See below, Cell C3 to C6 will appear as hidden (and hidden for printing) but included in the formulas.

Watch window - to know changes on your sheet tab This will be used to observe any particular cell or range of cells – One way is to see the result of a formula of cell which can be observed from watch window, no need to move around the worksheet. Second way is we can use this as a selecting and moving to the required cell from watch window. Step 1: Please click on Watch window from Formula tab Step 2: From the dialogue box “Watch Window”, click on Add watch and select the cells or range, then finally click on Add (based on your requirement) So that we can watch the result or move through via watch window, which can also be added to your screen at formula bar (whenever is required click on watch window)

Data entry - Move data always towards right – Advanced Always you see that when we press enter in excel, it always goes down. Can we change it? Yes then the question is How? See below Step 1: Please click on File menu, then click on Options Step 2: From the dialogue box “Excel Options”, go to Advanced section then select the option we required from “Direction” drop down under Editing options. We have all four options – Down, up, left and Right… Now go ahead and choose…