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..

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

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

Title Bar in Excel

By
Image
Title Bar is the bar holding title of the excel file. But apart from showing title, it also contains many other important functionalities. so let's get familiar with Title Bar in this blog post. Title Bar is divided in 03 parts: 1. Quick Access Toolbar 2. File Name 3. Minimize, Maximize/Restore, Close buttons Let’s see in detail about this. Quick Access Toolbar:  The commands which we are using frequently can be added to Quick Access Toolbar. Following steps will help you to add desired commands here. 1. Right click on the desired command. (In below screenshot we have selected “ Format Painter ” as a desired command) 2. Select option “Add to Quick Access Toolbar”. 3. Now, you will see “ Format Painter ” command is added in the Quick Access Toolbar. 4. To remove desired command from Quick Access Toolbar, just right click on the command and then select option “Remove from Quick Access Toolbar”. 5. No...
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

Conditional Formatting:Highlighting Above Average and Below Average Values

By
Image
This blog post is continuation to our series of posts on “Conditional Formatting”. In this blog post, we will learn about highlighting values which are “Above Average” and “Below Average” of the selected values. To learn this post in an easy way, I have randomly listed few numbers as shown in below image. We will learn how to highlight the numbers which are above the average of all the selected numbers and which are below the average of all the selected numbers. For your easy understanding I have already calculated average value of all these numbers numbers (Which is 65). This will help us to easily cross check our output. Highlighting Above Average Values: Select all the values from which we wish to highlight the “Above Average Values”. Go to Conditional Formatting (which is available in Home Tab under Styles group) and select “Above Average” option as shown in below image. “Above Average” dialog box will appear on screen. Select the Formatting style from available list and hit OK but...
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

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

Adjusting Row Height & Column Width in Excel

By
Image
In this blog post we will see how we can adjust Row Height and Column Width of cells in excel sheet. The options to adjust Row Height and Column Width in excel are available in Home Tab under cells group. Once we go to Cells group, click on “Format” command and then under Cell Size we can find all the options to adjust Row Height and Column Width. Let’s learn more about this with real time example. We have a dummy data of marks obtained by five students with us. But the data is not well organized. The Row Height and Column Width of cells which contain this dummy data is not equal which giving an ugly look to our data. So let’s adjust Row Height and Column Width for these cells so that our data will look more attractive. Row Height: Select all the cells for which we want to adjust Row Height. Select "Row Height" option under “Format” command as shown in below image. Row Height dialog box will appear on screen. Here we can enter the value to adjust Row Height. We have provided ...
Read more

Insert WordArt in Excel

By
Image
In this blog post we will learn to insert WordArt in excel. WordArt can be used to provide attractive look while creating templates or dashboards. The WordArt command can be found in “Insert” tab and under “Text” group as shown in below image. Once we click on this WordArt command, we can find around 20 different WordArt options which we can use in our excel file. Out of these 20 different WordArt options, we can select any one of our choice. In this case we have selected the WordArt which is highlighted with red in below image. Once we click this red highlighted WordArt option, we can see the this WordArt is now appeared on our excel screen with message in textbox “Your text here”. We can edit the text in this WordArt just by selecting it and typing the letters which we want to enter. In this case we have inserted the name of our blog “ Excel Tricks with Tejraj ”. Providing Text Effects to WordArt: We can also provide special effects to the text in WordArt. When we click on the insert...
Read more

Conditional Formatting: Highlighting Top 10 Percent Values in Excel

By
Image
This blog post is in continuation to our series of posts on “Conditional Formatting”. In this blog post we will learn how to highlight “Top 10%” values from selected set of values with the help of “Conditional Formatting”. Conditional Formatting Top 10% option we can find in Styles group under Home Tab.  Now the main question is what does it mean by highlighting “Top 10%” values from selected set of values? It means “Conditional Formatting” will highlight Top 10% values from total no. of values.  If our total values are 10 then excel will highlight Top 1 values. If our total values are 20 then excel will highlight Top 2 values. If our total values are 30 then excel will highlight Top 3 values. This is the basic concept behind this option. So let’s learn how we can highlight these Top 10% values using “Conditional Formatting” in excel.  To explain this topic in detail, I have listed total 50 values as shown in below image. We will see how we can highlight Top 10% values fr...
Read more