Faysal Easy Excel

We know that, PivotTable helps you to find the unique values from a wide data range. But the problem is to find the frequency of each unique value in that range How to count the frequency of Unique values in PivotTable? Assume that, You have Student and Marks column.  Image 1: Students Table Now first step is to find the unique Student's name. To do this, drag the Student field in ROWS box in PivotTable.  Image 2: Students Table in ROWS Box This will show the unique students list. Now to find the frequency of each student, drag again the same Student field in COLUMNS box in PivotTable. This will count the students frequency: Image 3: Count of Students Finally the result is as below: Image 4: Frequency of Unique Values

Assume that, You have a worksheet with conditional formatting from A1:A23 cell. In many case you have to remove conditional formatting from that worksheet like, wrong conditional formatting applied, clear the worksheet for printing etc. To remove conditional formatting you should follow the below steps: How to remove conditional formatting from worksheet? Step 1: Select A1:A23 cell that contains the conditional formatting. Step 2: Click on Home | Conditional Formatting | Manage Rules.. Step 3: Conditional Formatting Rules Manager dialog box will appear like below: Image 1: Conditional Formatting Rules Manager dialog box Step 4: As you see that, Duplicate Values conditional formatting rules has already been selected. Just Click the Delete Rule button to delete this conditional formatting rules. Step 5: Now click on Ok. |||| Please SUBSCRIBE our YouTube Channel |||| https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw

IF Statement is a powerful tool in Excel VBA. Many logical operation being done through IF Statement. The basic term of IF Statement is doing something if the given logic is TRUE. The diagram of IF Statement are 3 types: (a) Easy IF Statement, where Logic is TRUE only: Image 1: Easy IF Statement (b) Medium IF Statement, where Logic is TRUE or FALSE: Image 2: Medium IF Statement (c) Nested IF Statement, Where another IF assigned under a logic: Image 3: Nested IF Statement Generally the above 3 types of IF Statement are found in Excel VBA. Remember all your IF Statement depends on 2 important features. These are Conditional Operators and Logical Operators. These are described as below: 1) Conditional Operators: To write logic in IF Statement you must use the Conditional Operators like, = (equal to). You can apply below 6 types of Conditional Operators. These are: Image 4: Conditional Operators 2) Logical Operators: To write...

As you have learn that, it is important to specify the cell format in Excel worksheet, so that Excel can calculate or look values smoothly and can ignore errors. Imagine that, a variable is a cell and to calculate or look values smoothly you have to specify the format of variables in Excel VBA. In variable there are different types of Variable Format. You need to set the matched variable format for the variable values you wish to store. For example if you wish a variable will store date, then set the variable type as Date. By this way you can use Long, Single, Boolean, Decimal, Currency etc. This discussion will help you to know about the types of Variable Format. Types of Variable: There are 11 different types of Variable format, which you can classified into 2 major groups. These are Numerical and Non-Numerical Variable formats. Image 1: Variable Types Numerical Variable Formats: There are 6 Numerical Variable formats are available in Excel VBA. These formats are allo...

When numbers are stored in variables, then you are allowed for mathematical calculation in Excel VBA, such as Addition, Subtraction, Multiplication and Division. Here are the examples of basic 4 types of mathematical operation: Addition calculation: Type the below codes and run it: Sub mathAdd()     Dim x As Integer     Dim y As Integer         x = 7         y = 8     Range("B4").Value = x + y End Sub When you enter these codes in VBA Code window, then it will looks like this: Image 1: Addition calculation in Excel VBA The mathAdd is new function you have created for running the Addition operation. You can rename this as you want but obviously following the rules of naming a Sub procedure in Excel VBA. Then Dim declared variable names as X and Y where 7 and 8 values are stored. The cell B4 has selected to show the result. And finally for calculating ...

First of all you need to clear what is Variable? Lets see from your end. In formula while you use =A1, then A1 is the variable name which contains some values. In computer programming sometimes you need to tell the computer to keep a calculated value in memory for later use. This calculated value your computer will store in a variable name which you will mention with a Dim command generally. Dim is the abbreviation of " Dimension ". For example: Sub keepinMemory()     Dim ValueInMemory as Text     ValueInMemory = "Hello World" End Sub This command will set a value "Hello World" in ValueInMemory word. If you write ValueInMemory, then computer will read "Hello World". This is the example of Variable. Image 1: Variables and Values Types of Variable? Variable are different types, such as String, Integer, Long, Date, Double, Object etc. It depends on which thing you want to add in memory. If it is a text, then use String, if it is ...

Real Life Problem: In a departmental store, the Store Manager needs to buy Onion in January month for getting discount from the Dealer. The Manager get the vegetable buying target in December. He applied a SUMPRODUCT() to get the total kilograms of Onion he must buy to get the discount from the Dealer in January. But the problem is, In Kilograms column, somehow text data entered. And that is why the SUMPRODUCT() is not working. It shows a #VALUE error. Now the question is, how can you help the Store Manager to get the correct total kilograms of Onion need to buy in January by ignoring the #VALUE Error?   Example Data Table: Below is the target for the Store Manager month wise got in December: Image 1: Target data Solution: In this situation There are 2 main things you can suggest to the Store Manager for getting the correct result. First one is by removing the text value from the Kilograms column. and you will get the result 321. The next one is handling the e...