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

Blank Cell – selection and fill with required data from above cells This feature is mostly used, for to select the blank cells 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   Blanks Step 3: Click on  OK You can also press F5 in keyboard to get Goto Menu. Once blank cells are selected use formula, press = (is equal to) and selected the upper cell and press Ctrl + Enter. So that blank cells are filled with required data.

Conditional formatting - Highlight Duplicate Values This feature is mostly used to identify duplicate values in a range of cells or array, it also identify the any error of data of unique records like ID numbers, serial numbers etc . How to do this – Step 1 - First select the range of cells, then  click on Highlight cell rules (conditional formatting) under Home tab Step 2 - Select rule type as   “Duplicate Values” Step 3 - Then click on  OK

Report Publishing via Pivot Table in different sheets This feature is mostly helpful to publish the data in sheet wise Step 1: Create pivot table with Report filter , go to analysis tab or Options tab from "PivotTable options" Step 2: Click on options drop down Step 3: Then click on  “Show report filter pages” Now we can see the new excel sheet tabs names along with data, published based on the report filtered data.

Grouping in Pivot table This feature is mostly helpful when row label data is in numerical values or dates. Step 1: Create pivot table, go to row label data and right click Step 2: Select option “Group” Step 3: From the dialogue box Grouping enter the number against within range of start and End values, then click on OK Now we can see the data in group wise or bucketed data

Evaluate the formula - step by step process This is the best trick to debug the complex formulas, each part of the formula can be evaluated individually. Step 1: Please select a cell with formula , then click on Evaluate formula under Formulas menu or tab Step 2: Dialogue box appear, click on  evaluate  which shows the result of the formulas in step by step process Once done click on close button.

Evaluating the result of Particular formula With F9 Key This is the best trick to know the result before publishing into cell. Step 1: Select the section of the formula for which we want to know the result Step 2: Press F9 , now we can see the result If we press the enter the result will be published into the cell. Note : But we want to see the formula back to the cell, then how to go ahead? Here is the answer – Just press Ctrl + Z (Undo command) , we were now back to formula mode.