Excel Tricks with Tejraj

In this blog post we will learn to protect the workbook structure in excel. First of all let’s understand what it means by protecting workbook structure. Once we protect the workbook structure: 1. User cannot insert new worksheet . 2. User cannot delete existing worksheet. 3. User cannot rename worksheet . 4. User cannot hide & unhide the worksheet . 5. User cannot change the tab color . 6. User cannot copy the existing worksheet . 7. User cannot move the existing worksheet . Now, we will see how to protect worksheet structure in excel: 1. Click on “File” option on Ribbon. 2. Click on “Info” option as highlighted in below image. 3. Click on “Protect Workbook” option as highlighted in below image. 4. It will appear further options under “Protect Workbook”. Out of these options we have to click on “Protect Workbook Structure”. 5. Once we click on “Protect Workbook Structure” option, one dialog box will appear on our excel screen as shown in below image. Under this we have to check th...

When we are working with formulas or functions in excel we have to deal with Absolute or Relative Reference or combination of these two in Excel. These reference styles are basically associated with cell address of the cells where we are using any formulas or function.  We will see these reference style one by one.  In excel “Relative Reference” style is a default reference style. Relative Reference: To explain “Relative Reference” we will use formula to calculate sum of two numbers. In “Column A” we have listed 1st number and in “Column B” we have listed 2nd number. To add the number listed in “Column A” and “Column B” we have used a formula in “Column C” as shown in below image. The output of this formula is also shown beside. Now, copy the formula from cell “C2” to the cells C3, C4, C5 and C6. The output of addition will be seen as shown in below image. Now select Range “C2:C6” where we have copied the formula to add the numbers from cell “C...

Till now we have seen many things related to worksheets like 1. How to Insert New Sheet in Excel? 2. How to Copy Sheet in Excel? 3. How to Move Sheet in Excel? 4. How to Change Tab Color in Excel? Now, in this blog post we will see How to Rename Sheet in excel. We can rename the sheets by using three various methods: 1. By using Format Command 2. By using Mouse Right Click 3. By using Mouse Left Double Click We will see all these three methods in detail one by one. For this we will open an excel sheet. This excel sheet contains three sheets in it with name Sheet1, Sheet2 and Sheet3 respectively. Method 1: By using Format Command 1. Select the sheet which we want to rename. In our case we have selected Sheet1. 2. Go to Home Tab. Click on Format Command under “cells group”. Select option “Rename Sheet” from list of appeared options. 3. Old Sheet Name will get highlighted with grey color. 4. Type the new sheet name ...

In this blog post, we will see how we can copy sheet in excel. To explain in detail, we will open new excel file which have 03 worksheets in it. There are two methods by which we can copy the sheets: 1. By using Format command 2. By using Mouse Right Click 1. By using Format command: In this method we use format command to copy the selected sheet. Following steps will give you clear idea about how we can copy the sheet in excel. 1. Select the sheet which we want to copy. (In this case “Sheet1”). 2. Go to “Home Tab”. Select format command under “Cells group”. Select option “Move or Copy Sheet”. 3. “Move or Copy” dialog box will open. Select the sheet in this dialog box before which we want to copy selected sheet. For example if we want to copy selected sheet before sheet3 then select “Sheet3” and Check the checkbox for “Create a Copy”. 4. We can see Selected Sheet (i.e. Sheet1) is copied before “Sheet3”. 5. Selec...

Many times, while working in excel, we may face a situation where, we have a data with specific formatting style and we want to copy same formatting style in another location. We can do it manually but it will be very critical while working on a large data also it is time consuming. To do this task within a seconds, there is one command available in excel: “Format Painter”. We can find this command in Home Tab under Clipboard Group. So let’s have a look on how we can use this command effectively to save our time and efforts. Here we will take an example of Marks scored by students in Maths and Science. In Table 1, we have listed marks scored by five students in Maths. We have applied some formatting to this table as below: 1. We made text in the first row of this table to Bold. 2. We have applied border to entire table. 3. We have changed background color of all the cells from table to Light Green. Now we have another data in Table 2, in which we have listed marks scor...

Copy and Paste are the most basic but very frequently used commands in excel. These commands can be found in Home Tab under Clipboard group. Names of these commands itself explains their functionality. Copy means copy the things and paste means paste the things which are already copied. Things we can copy here can be anything like text, numbers or they can be formulas also. YouTube Video Copy : This command copy the selected content and put it on the Clipboard so you can paste it on desired location. Copy command does not delete the data from original location. It just copy the data from original location to desired location, keeping data from original location unchanged. Paste : This command paste the data which is already available on Clipboard (i.e. the data which we have putted on the Clipboard by using Copy options). If we want to see the data in Clipboard then just click on the launcher (downward arrow situated at the bottom right corner of Clipboard group). T...