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

Blank Cell - Color code and formatting This feature is mostly used to identify blank cells and if any time some one has accidentally  deleted any value in the cell which can be identified with this trick. How to do this – Step 1 - Please click on new rule (conditional formatting) under Home tab Step 2 - Select rule type as “Format only cells that contains” Step 3 - Under edit rule section choose blanks from the drop down and click on format and select fill with color of your choice, then click on OK , again click on OK

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.

Hold Ctrl Key in Formula (no typing of comma) There is no need for us to select one cell then type comma (,) and again another cell and typing comma (,) and repeat it. It is easy to select required cell in formula by using keyboard Ctrl key , so that we can save a time. Example: =Sum(J3,J7,J11)

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…

Set your default local location for saving excel file – Options Many of the people will save the excel files, but they forgot where the file was saved. When they want to retrieve it is difficult for them. To avoid that kind of difficulties, below is trick you use from excel Step 1: Please click on File menu, then click on Options Step 2: From the dialogue box “Excel Options”, go to save section then click on the check box “Save to computer by default” And we can change the path of the folder if required from “Default local file location”.

Pin the excel work books from Home There is no need for us every time to go the folders and open all the excel files which were used frequently or daily basis. We have trick which helps to save a lot of time - Step 1: Please click on File menu, then click on Home Step 2: Then click on “Recent”, select the excel file, then at the end of the file there appears a symbol of pin (), click on that symbol. That’s all… the file appears in the category of Pinned under Home (shown in image below)

Pin folders want to easily find later - Open Menu When we use regularly / frequently some folders, those can be pinned in excel. We have trick which helps a lot of time - Step 1: Please click on File menu, then click on Open Step 2: Then click on “Recent”, select the folder, then at the end of the folder there appears a symbol of pin (), click on that symbol. That’s all… the folder appears in the category of Pinned at the top (shown in image below)

Open second Window for excel In excel, we can open a second window so that we can work in different worksheets at a time (This will be mostly useful when we have dual monitor).  And we can arrange the windows as well based on the requirement like Horizontal or vertical etc. Step 1: Please click on New Window from View tab A new window of excel file will be opened with book name along with number

Convert note to comment in excel In excel, we have two varieties of highlighting of information – 1)    Notes - Under notes we can update the cell with a “NOTE” about a particular text to be displayed. 2)    Comments – In comments, where we can update a particular text same like note, but there is additional advantage of tracking of the comments updated by the others with the time stamp (it looks like how a mail chain having replies) Step 1: Please select a cell and enter a comment by clicking Notes from Review tab Step 2: Then click on “Convert to comments”, so that the note now turns into a comment. Very helpful when we have review comments. ( Preparer  and reviewer concept).

Selection of destination for text to columns Where we have data in different columns and you want to use text to columns, but it should not overlap on the immediate or adjacent columns, then here comes the solution. The advantage is that the data of original column will be remains same. Step 1: Please select the data that we want to do text to columns, click on Text to Columns from Data Tab Step 2: From the dialogue box Text to Columns Wizard, click of your choice Delimited or Fixed width Step 3: Click on next, select the delimiters then click on next again Step 4: Now select “Destination” and click the cell where you want to place your data, finally click on Finish

Insert shapes and linking cell contents in excel Wow great!!!   we can link a cell information to a shape. So that when we change the cell data it will be auto updated in the shape as well. Step 1: Please create or draw a shape of your choice Step 2: Select the shape, then click on the formula bar Step 3: Now type = and then select the cell which you want to link, press enter Now when we change cell data, Shape data updated automatically.

Generate serial numbers in excel - row formula This trick will help a lot for the people who enter serial numbers manually and a need for later to delete a record or records, then again needs to fix the serial numbers. Now we can automate the same with a formula, which updates automatically whenever the records deleted. Step 1: Enter formula into the cell from where you want to create numbering. Formulas – 1)    = rows ($A$2:z A2) or 2)    = row(A2)-row($A$1) Step 2: Drag or copy the above formula to the required range of cells Now when we delete the any of the records, serial numbers automatically updated. Thank you 😊

Text to columns do not import (skip) option When we have a data for text to columns, where we need only few columns rest you do not want to publish Here comes the solution in excel - Step 1: Please select the data that we want to do text to columns, click on Text to Columns from Data Tab Step 2: From the dialogue box Text to Columns Wizard, click of your choice Delimited or Fixed width Step 3: Click on next, select the delimiters then click on next again Step 4: Now select the column which you want to skip which will be highlighted in black then click on “Do not import column (skip), finally click on Finish Only columns which are not highlighted will be published .

Advanced filter in excel – unique records only This feature is very helpful to get the unique records out of the total list of records and used to get the criteria-based records as well. Step 1: Click on Advanced filter from Data Tab Step 2: From the dialogue box Advanced filter, choose “copy to another location” (it is always better to choose new location) Step 3: Select list range from your data base or table, criteria range keep it as blank and copy to select the cells where you want to place your data Step 4: Click on check box “Unique records only”, Then click on OK Only unique records will be published. When you want a criteria-based, just use criteria range and select data as shown below – So you can filter data records based on conditions aswell.

Pivot table - Empty cell text filled We can set the text to be displayed on the blank or empty cells in the pivot table. Step 1: Create pivot table and select the data for value field Step 2: Then right click and select PivotTable options Step 3: From the dialogue box PivotTable options, against “For empty cells show” - please enter text what you want to display. Step 4: Then click on OK

Moving of Pivot table - Existing or New worksheet When you want to make a different Pivot Tables based your requirements Then best way to do this is - How to use - Step 1: Create and select the pivot table, then go to Analyze tab Step 2: Then click on "Move PivotTable" Step 3: From the dialogue box Move PivotTable, choose an option either “New  Worksheet” or “Existing Worksheet” based on your requirement Step 4: Then click on OK

Find - Format as well via Find and Replace Where with Find and replace, we can also search and select the formatted cells. How to use - Step 1: Use Ctrl + F keyboard short cut or select find from Home tab Step 2: Then click on options, Step 3: Again, click on the Format which show a dialogue box Find Format from which you select the parameters of your choice or you can select an option “Choose Format From cell” which helps to find the exact replicate of cells Step 4: Then click on Find All or Find next based on your choice

Indirect function - used to avoid ref error When we deleted a data in cell or range of cells, then we the formula linked with that cells will shows the error which is called reference error (#REF!). Through this trick we can avoid those errors - Please use the formula =Indirect (ref_text) Just type the cell reference of particular cell in double quotations Examples: = Indirect(“A2”)

Get Pivot table data in regular or normal This trick will help us where we had a pivot table data which was used to link data to any cell. We always get value of cell with “=GetPivotData” and if we use copy and paste to below range of cells, information on the cells were looks bit strange. To avoid that situation to normalize the Pivot table data use the following trick – Step 1: Go to File tab and click on options Step 2: From the dialogue box Excel Options, go to Formulas section and uncheck the “Use GetPivotdata functions for PivotTable references" option, then click on OK Now Pivot table data used for linking as normal data

Using of Insert cut Cells Insert cut cells used as one step shortcut to cut and paste data at time rather than copying the data once and inserting a new row or column again pasting the data. Step 1: Select a cell or range of cells, then click on Cut from home tab or use keyboard short cut (Ctrl + X) Step 2: Please right click on the cell where you want to replace the data, then click on “Insert Cut cells” Now data will be moved.