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

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

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

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

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

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

Insert Shapes in Excel

By
Image
In this blog post we will learn to Insert Shapes in excel. For this we have to click on “Insert” tab and under “Illustrations” group we can find a command “Shapes”.  Once we click on the “Shapes” command, we can see large variety of shapes which are available in excel will appear on our screen. These shapes are divided into 09 different categories: 1. Recently Used Shapes 2. Lines 3. Rectangles 4. Basic Shapes 5. Block Arrows 6. Equation Shapes 7. Flowchart 8. Stars and Banners 9. Callouts Recently Used Shapes : All the recently used shapes will appear in this category. If you are using only few shapes repetitively then you don’t need to search for those shapes in their individual category. You can easily find them in Recently Used Shapes. Lines : We can find various types of Lines, Arrows, and Connectors etc. under this category. Rectangles : We can find various types of Rectangles under this category. Basic Shapes : We can find various types of basic shapes like triangle, oval, ...
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

SUBTOTAL Function in Excel

By
Image
In this blog post we will learn to about SUBTOTAL function in excel. SUBTOTAL function will return subtotal from the list or database. Where to find SUBTOTAL function on Excel Screen: SUBTOTAL 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. SUBTOTAL function is highlighted in blue in below image. Once we click on SUBTOTAL option as highlighted above, we will get the function argument dialog box as shown in below image. Syntax of SUBTOTAL Function: The Syntax of SUBTOTAL function is as below: =SUBTOTAL(function_num,ref1, ref2, …) Arguments of SUBTOTAL Function: SUBTOTAL function takes arguments as listed below: function_num : In this argument we have to enter 1 to 11. Each number represents different function as listed below: 1: AVERAGE 2: COUNT 3: COUNTA 4: MAX 5: M...
Read more