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

Excel Shortcuts Ctrl A to Ctrl Z

By
Image
Use of keyboard shortcuts while working in excel will be a great help for us. It will save a lot of time. In this post we are going to see Excel Keyboard Shortcuts for alphabets A to Z while using Ctrl key. Below table will give you a brief idea for each shortcut and its functionality: Now, we will see in detail how each of above listed shortcut combinations will work: Ctrl + A :  It will select all content present on the worksheet. If active cell is in the table then it will select entire table. Ctrl + B :  It will convert selected text into Bold . Ctrl + C : It will copy the selected content. The content which is copied can be seen in the Clipboard. Ctrl + D :  It will fill down the content from upper cell. For example we have inserted a number “1” in the cell A1. If we select the cell range from A1:A5 and press Ctrl + D, then this number “1” will automatically fill down in the entire cell range . ...
Read more

Conditional Formatting: Highlighting Top 10 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 see how to highlight Top 10 Items in Excel?  But, before proceeding on this topic, first we should know where we can find “Conditional Formatting” option in Excel. We can find “Conditional Formatting” in “Home” tab and under Home Tab go to “Styles” group. We can find many sub categories under “Conditional Formatting”. But the one which we will focus in this blog post is to highlight “Top 10 Items ” . Below image will help you to find the exact option to highlight “Top 10 Items ” . Now you are familiar with the option where we can find the option to highlight “Top 10 Items ” . To explain this topic in very simple way, I have created a dummy data of 20 students and marks obtained by them in subjects Maths, English & Science. Also we have used SUM function to calculate the total marks obtained by each student. Now we will see how we can highlight “Top 10 Items ”  i...
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

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

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

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

Inserting Image into Shapes in Excel

By
Image
We have already learned about Inserting Shapes as well as Inserting Images in excel. Now, in this blog post we will learn about inserting images in shapes in excel. We will learn to insert an image (which is logo of our website “ Excel Tricks with Tejraj ”) into various shapes. First we will insert the shape in excel and then we will insert the logo into inserted shape. Insert Image in Oval Shape: First, click on “Insert” tab and then click on “Shapes” command. Once we click on “Shapes” command, we can see various shapes available in excel. Out of these, you can select any shape as per your choice. In this case, we want to select the “Oval” shape. If you are not aware about the names of these shapes, then you can just place the mouse cursor on any shape which will automatically show the name of that shape. Once we click on the “Oval” shape, the shape of mouse cursor will change from doctor symbol to plus symbol. As soon as the mouse cursor symbol changes to plus symbol, we can draw ...
Read more

Insert SmartArt Graphic in Excel

By
Image
In this blog post we will learn to insert SmartArt Graphic in excel. These SmartArt Graphics can be used to communicate information visually. SmartArt Command on Excel Screen: The main thing we should know is where we can find SmartArt command on our excel screen ? For this we have to go to “Insert” tab and under “Illustrations” group, we can find “SmartArt” command as highlighted in below image. Once we click on SmartArt command, one dialog box named as “Choose a SmartArt Graphic” will appear on our excel screen . All the SmartArt Graphics which are available in excel are divided in different categories. These categories we can see on the left hand side of dialog box. By default “All” category is selected, under which we can find all the SmartArt Graphics irrespective of their original category. SmartArt Graphic Categories: All the SmartArt Graphics are divided in various categories based on their designs. These categories are: 1. List 2. Process 3. Cycle 4. Hierarchy 5. Relationship ...
Read more

Number Filter in Excel

By
Image
In our previous posts we have seen about filters . In case you missed to read about what are filters and how to apply filter then click here to read in detail. In this blog post we will learn how to apply “Number Filters” in excel. We will see all the sub types of Number Filters in detail with below example. For this we have taken a data of some electronic products sold in East, West, South and North regions from 1st Aug 2019 to 1st Apr 2021 with us. Number filters can be applied to column which contain number values. So in our case we can apply number filters to column “No of Items Sold”. Click on drop down button which you will find on header cell “No of Items Sold” and you will see the “Number Filters” option as highlighted in below image. We can now filter the data present in this column based on below criteria’s: Equal Does Not Equal Greater Than Greater Than Or Equal To Less Than Less Than Or Equal To Between Top 10 Above Average Below Average Custom Filter Now, we will explai...
Read more