Excel Tricks with Tejraj

Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, “Create Pivot Table” dialog box will appear on screen. This is like as shown in below image. This “Create Pivot Table” dialog box is very important to create Pivot Table. There are two important options a...

In previous posts on Conditional Formatting, we have seen how we can: 1. Highlight Values Greater Than Given Value 2. Highlight Values Less Than Given Value 3. Highlight Values Between Two Given Values 4. Highlight Duplicate Values In this post we will see how we can highlight values which are equal to the provided value. YouTube Video We have created dummy data of sales of 05 employees in 04 quarters Q1, Q2, Q3 and Q4. Now as an example we will see how we can highlight the cells in which sales is exactly equal to 500. To do so, follow the below steps: 1. Select the cells from which we want to highlight the cells which are equal to 500. 2. Now, go to Home Tab, then select Conditional Formatting, then select Highlight Cells Rules and then select “Equals To” option. 3. You will see “Equal To” Dialog Box appeared on your screen. 4. As we want to highlight the cells which are exactly equal to the value of 500, we have to enter value “500” ...

If we want to highlight the values which are present between two given values then we can use Conditional Formatting. YouTube Video For example we have a database of 05 students for marks obtained by them in three subjects Maths, English and Science. Now we want to highlight the marks which are between "65" to "85". To do so we can use Conditional Formatting. Let us see detailed explanation as below: 1. Select all the cells from which we want to highlight the marks. 2. Go to Home Tab. Select "Conditional Formatting", then select “Highlight Cells Rules”, then select “Between” option from the list of appeared list. 3. Now, you will see “Between Window” on your screen. Enter 1st value and 2nd value between which we want to highlight the values. Also select the formatting style from the list of list of appeared style. Below is the image for your reference. 4. We have entered 1st value as “65” and 2nd value as “85” as ...

There might be question in your mind: “What is the difference between Paste and Paste Special?” If you really had this question then you are reading perfect blog. This blog will help you to answer your question. Also we will see how we can use each and every option from Paste Special to reduce our time and efforts.  We can say that Paste Special is subset of Paste. Both the Paste and Paste Special does the same thing (to paste the data) but “Paste Special” paste the data in a special way. So let’s see what these special options available in it are. YouTube Video Where we can find “Paste Special”? There are two ways by which we can reach up to Paste Special options. But for this we have to copy the data first which we want to paste. After we copy the data we can follow any of the below steps: 1. Click on the small downward arrow below “Paste” command in Clipboard group under Home Tab, we will see one option called as “Paste Special”. 2. Right click on any cell ...