Author: Mehul Thakkar

Table of Contents Example From the given dataset, we want to remove transactions containing duplicate invoice numbers. Steps Select the complete data set from which you want to remove duplicates  Go to Data tab –> Remove Duplicates Select the column containing duplicate values and Click ok A dialogue box showing Number of Duplicate Values found…

Table of Contents Example In the below table, we can see there are extra spaces leading and trailing spaces appearing between the city and the State. To remove the extra spaces, we will use TRIM function of Excel.  The output after using TRIM function will appear as follows: With the help of trim function, we…

Table of Contents Example The commonly faced problem you face while working on excel sheets is that there exist various excel errors like #N/A,#REF,#NUM,#VALUE, #DIV0 as it can be seen in the table below. To select and delete all the errors appearing in an excel sheet, follow the steps mentioned below. Steps Select the columns…

Table of Contents Example In the above table, we can see there are duplicate invoice numbers. To identify and highlight such invoice numbers we will use Conditional formatting. Steps Identify the column in which duplicate values exist Select the column with duplicate values Goto Home Tab –> Conditional Formatting –> Highlight Cells Rules –> Duplicate…

Table of Contents Issue Often we face the issue where even after pressing “Enter”, the formula is visible instead of the results. Solution Press Ctrl ~ to switch back to normal mode.  Reason This happens due to the formula view being enabled by mistake at times by pressing Ctrl ~

Table of Contents Example We have a bank statement containing the following transactions. Output Required We want to extract the names between the first delimiter and the last delimiter. Steps Select the column from which the data has to be extracted. In our example, we have to extract data from Particulars column. Goto Data tab…

Table of Contents In this article, we will learn how to set the formula calculation to automatic in Excel.   Steps Go to the formula tab Click on the arrow near Calculation options Click on the Automatic option By enabling the automatic calculation option, all the formulas will be updated for any changes done in the…

Table of Contents Example We have to derive number of months between any two dates available in the data.   Formula Using the formula In a new column, type the following =DATEDIF(A2,B2,”M”) Using the above formula, we will get difference in number of months. Notes:1. DATEDIF formula will not appear in the by default list functions available…

Table of Contents In this article we will learn how to turn off GetPivotData option while referencing a pivot table in excel. This usually happens while referencing a cell from the pivot table. Creating a reference from another cell appears like =B4 but if the reference is done from a pivot table then GetPivotData function gets added…