Videos

By

Excel Tricks with Tejraj - YouTube Channel Introduction



Subscribe to our YouTube Channel "Excel Tricks with Tejraj" to watch more videos on excel. 

Click here to go to our YouTube Channel.






Comments

Post a Comment

Related Topics..

Color Scales in Conditional Formatting

By
Image
In this blog post we will learn about Color Scales from Conditional Formatting. Conditional Formatting option is available in Home Tab under Styles Group as shown in below image. Click on the Conditional Formatting option and we will find Color Scale option as shown in below image. There are total 12 Color Scales option available in Excel. These 12 options are divided in 02 groups. Each group contains 06 options.  3 Color Scales  2 Color Scales  The names on all the 12 groups as per their group is listed below: 3 Color Scales: Green - Yellow - Red Color Scale Red - Yellow - Green Color Scale Green - White - Red Color Scale Red - White - Green Color Scale Blue - White - Red Color Scale Red - White - Blue Color Scale 02 Color Scales: White - Red Color Scale Red - White Color Scale Green - White Color Scale White - Green Color Scale Green - Yellow Color Scale Yellow - Green Color Scale We will now see how to apply these color scales options on the data in excel. For this we ...
Read more

Data Bars in Conditional Formatting

By
Image
In continuation to series of posts on Conditional Formatting, we will learn how to use Data Bars in Conditional Formatting through this post. Conditional Formatting command can be found in Styles group under Home tab as shown in below image. Click on the Conditional Formatting option and select “Data Bars” option from the list of appeared options. We can now see all the “Data Bar” options available in excel. These “Data Bar” options are categorized into two groups.  1. Solid Fill 2. Gradient Fill: There are six different “Data Bar” colors available in each group. These colors are listed below: 1. Blue Data Bar 2. Green Data Bar 3. Red Data Bar 4. Orange Data Bar 5. Light Blue Data Bar 6. Purple Data Bar Each group with their respective colors are shown in below image: Solid Fill: Gradient Fill: Now we will see how we can apply these “Data Bars” on our data. For this we have created dummy data of few employees and Incentive received by them. Example with Gradient Fill: 1. Select all...
Read more

Fill Up, Down, Right & Left in Excel

By
Image
Excel has many great tricks which can help us to save our time while working on big data. In this blog post we are going to see one of those tricks where we can fill the data to the upwards of the selected cells, to the downwards of the selected cells, to the right of the selected cells and to the left of the selected cells. There are many situations where we may need to copy the data from selected cell to the upward, downward, left and right cells. But how can we do so?? In excel we can do so with the help of “Fill” command. This command we can found in: Home Tab --> Editing Group --> Fill Command. Let’s take an example. We have inserted a text “Tejraj” in a cell “D5”. Now we will see how to fill this text from cell D5 to Up, Down, Right & Left. Fill Down: 1. Let’s assume that we have to fill the data from cells D5 to D10. For this, select the Cell Range D5:D10. Keep in mind that the data from 1st cell of the selected Cell Range will fill down in entire selection. 2. Select ...
Read more

Icon Sets in Conditional Formatting

By
Image
In this blog post we will learn how to apply Icon Sets from Conditional Formatting to our data in excel. This Icon Sets will help us to improve visual presentation of our data.  Conditional Formatting option we can find in Home Tab under Styles group as shown in below image. If we click on Conditional Formatting then we can see multiple options available under this. Icon Sets is one of them. Click on this “Icon Sets” option to explore all the icons from this category.  All these icons are divided into 04 categories. 1. Directional 2. Shapes 3. Indicators 4. Ratings Then names of all the Icons from respective category are provided in below image. The sequence of the icons from image on the Left Hand Side is equal to the sequence of the names from the image from Right Hand Side. This will help you to select the proper icon sets when you are working in your daily tasks. We will now apply one-one icon set from each category on the below data. We have listed few numbers specificall...
Read more

Insert Multiple Rows in Excel Based On Cell Value

By
Image
In this blog post we will learn to Insert Multiple Rows in excel based on cell value with the help of VBA Code. For this we have prepared dummy data as shown in below image. In this data we have listed few “Products”, their “Quantities” and their “Unit Price”. Now we want to insert multiple rows based on their “Quantities”. For example, “Product 1” has 04 quantities so we have to add 04 rows below the Product 1, “Product 2” has 02 quantities so we have to add 02 rows below the Product 2 and so on. To avoid wasting our time by manually inserting rows one by one, we can use VBA code which will do our work within seconds. To do this, just follow the below steps: 1. Add “Developer Tab” on the Ribbon if it is not already added. If you are not aware about how to add Developer Tab on the Ribbon then click here . Once added on the Ribbon, “Developer Tab” will looks like as highlighted with red in below image. 2. Now, click on the “Visual Basic” command as highlighted in below image. 3. Once ...
Read more

Text Orientation in Excel

By
Image
Whenever we want to adjust our text in a narrow cell, we can use Orientation command in excel. With the help of this command, we can rotate the text clockwise, counterclockwise, upward downward etc.  YouTube Video We can found this command in Alignment group under Home tab. If click on the orientation command icon. You will see a dialogue box with below options appear on your screen. There are 05 options we can see in the above dialogue box. 1. Angle Counterclockwise 2. Angle Clockwise 3. Vertical Text 4. Rotate Text Up 5. Rotate Text Down So let's see how to use this command.  First select the text where we want to apply the orientation. As an example, we have inserted a text "Excel Tricks with Tejraj" in a cell and we will apply all these orientation styles to these text: 1. Angle Counterclockwise: This option will help us to rotate the text counterclockwise within a cell. Select the text to which we want to apply Orientation st...
Read more

Select all cells highlighted with specific color at a time

By
Image
Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s...
Read more

Customize SmartArt Graphic in Excel

By
Image
In this blog post we will learn to customize SmartArt Graphic in excel. Previously we have seen to insert SmartArt Graphic in excel but in this post we will learn to customize them to give beautiful effects. If you are not familiar about how to insert SmartArt Graphic in excel then click here before reading this blog post. We will take same example which we have taken in our previous blog post about inserting SmartArt Graphic in excel. In that blog post, we have inserted below SmartArt Graphic. Where to find commands to Customize SmartArt Graphic on Excel Screen: The main thing we should know is where we can find commands to customize SmartArt Graphics on our excel screen ? For this we have to click on the SmartArt Graphic which we have created. Once we click on this SmartArt Graphic, two additional tabs as listed below will get enabled on Ribbon . 1. Design Tab 2. Format Tab We will use these two tabs to Customize SmartArt Graphic in excel. Note : These two tabs will get enabled if...
Read more

Insert Object in Excel

By
Image
Many time we may need to insert word file, pdf file or presentation in excel. But many people don’t know how to add these pdf files, word files or PPT files in excel. For those people we will learn to insert objects in excel through this blog post. 1. For this we have to go to “Insert” tab and under “Text” group and click on the command “Object” 2. Once we click on the “Object” command, one dialog box names as “Object” will appear on excel screen. 3. Click on the “Create from File” tab as highlighted in below image. 4. Click on “Browse” button, which will provide the option to select the file which we want to insert in excel. 5. Once we select the file, we can see the file name along with its path will reflect in dialog box as shown in below image. 6. Also we have to check the checkbox for “Display as icon” as shown in below image. 7. Once we click on “OK” button, selected file is now inserted as an object in our excel file. To Change the Caption of Inserted Object: We can now insert o...
Read more

SUMIF Function in Excel

By
Image
In this blog post we will learn to about SUMIF function in excel. Whenever we want to calculate the SUM based on certain criteria or condition, we can use the SUMIF function. Where to find SUMIF function on Excel Screen: SUMIF function can be found under “Math & Trig” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Math & Trig” function category, we can see list of various Math & Trig functions available in excel. SUMIF function is highlighted with blue color in below image. Once we click on SUMIF option as highlighted above, we will get the function argument dialog box as shown in below image. Syntax of SUMIF Function: The Syntax of SUMIF function is as below: =SUMIF(range, criteria, [sum_range]) Arguments of SUMIF Function: SUMIF function takes three arguments as listed below: range : It is cell range which you want to evaluate or in other words, it is cell range from which we want to apply condition or ...
Read more