Category Archives: MS Office Excel
Working with Rows, Columns and Cells
Working with Rows, Columns and Cells
Working with Rows, Columns and Cells
In this step by step Excel tutorial, you will learn how to Insert and delete rows, Columns and cells in Microsoft Excel.
1. Inserting Row
There are two methods of inserting a row. The first method is to select the whole row where you want to add a new row by clicking its row label. Then right click on the row label and select “Insert” from the Context Menu. This will add a row above the selected row.
Another method is by right clicking the Cell where you want to add a row. You will then select “Insert” from the Context Menu. This time, an “Insert” pop will open with multiple options, you will select “Entire Row” and will click OK. This will add a new row.
2. Inserting Column
There are two methods of inserting a column. The first method is to select the whole column where you want to add a new column by clicking its column label. Then right click on the column label and select “Insert” from the Context Menu. This will add a column on the left of the selected column.
Another method is by right clicking the Cell where you want to add a column. You will then select “Insert” from the Context Menu. This time, an “Insert” pop will open with multiple options, you will select “Entire Column” and will click OK. This will add a new column.
3. Inserting Cell
To insert a cell, right click the Cell where you want to add a new cell. You will then select “Insert” from the Context Menu. An “Insert” pop will open with multiple options. Now, if you want to add a cell in vertical order (column wise), you will select “Shift cells down” and click OK. This will move the selected cell downward and your blank cell will be inserted. However, if you want to add a cell in horizontal order (row wise), you will select “Shift Cells Right” and click OK. This will move the selected cell towards right and your blank cell will be inserted.
4. Deleting Rows
The process for deleting a row is very much similar to the process of inserting a row. Here to, you will select the row, you want to delete, by right clicking its row label and selecting “Delete” from the Context Menu. This will delete your desired row.
5. Deleting Column
The process for deleting a column is very much similar to the process of inserting a column. Here to, you will select the column, you want to delete, by right clicking its column label and selecting “Delete” from the Context Menu. This will delete your desired column.
6. Deleting Cells
The process for deleting a cell is very much similar to the process of inserting a cell. Here to, you will click the cell, you want to delete, by right clicking the cell and select “Delete” from the Context Menu. A “Delete” pop will appear from which you will select the desired option and click OK.
- Shift Cells Up: By selecting this option, your cells will move upward within the column
- Shift Cells Left: By selecting this option, your cells will move to left within the row
- Entire Row: By selecting this option, you will delete the entire row containing the selected cell.
- Entire Column: By selecting this option, you will delete the entire column containing the selected cell.
Working with Ranges in MS Excel
Working with Ranges in MS Excel
Working with Ranges in MS Excel
The term Range is used in MS Excel for the selection of one or more than one cell either horizontally or vertically or even in a block.
There are various manners in which a range can be defined in MS Excel.
1. Selecting a complete single row
To select a complete row as a range, take your mouse to the row label and click. This will select the whole row
2. Selecting complete multiple rows
To select complete multiple rows as a range, take your mouse to the first row’s label that you want to select. Click on the label and drag your mouse vertically downwards on the rows labels, selecting all the desired rows.
3. Selecting a complete column
To select a complete column as a range, take your mouse to the column label and click. This will select the whole column
4. Selecting complete multiple columns
To select complete multiple columns as a range, take your mouse to the first column’s label that you want to select. Click on the label and drag your mouse horizontally to right side on the column labels, selecting all the desired columns.
5. Selecting a cell
You can select a specific cell by simply clicking on it.
6. Selecting a block of cells in 3 x 3 cells
Let’s assume that you want to select all the cell starting from “B2” and ends at “D4”. For this, you will click and drag your mouse from Cell B2 to Cell D4 diagonally, which will select all 9 cells falling within the range.
7. Selecting random cells
You can select random cells by keep pressing the “Ctrl” key and clicking on your desired cells.
8. Filling a range
If you have a value that replicates either vertically (within the same column) or horizontally (within the same row), instead of typing it again and again, you can use the range filling feature of MS Excel. You can do this by entering the value in one cell and then click and drag on the anchor point either vertically to the column or horizontally on the row. This action will apply the same value on selected range.
You can also use the same method to autofill the values. For example, you want to put serial number from 1 to 100 in Column A. Now, instead of typing numbers one by one, you will type 1 in Cell A1 and 2 in Cell A2. Then you will select both cells, and click and drag the anchor point vertically on Column A, till you reach Cell A100. You will notice that all range will automatically be filled with incremented numbers. This action will save you much of time and hassle.
9. Copying and Pasting a Range
To copy a range from one location and pasting it to another location is quite easy and simple in MS Excel. First, you need to select the range from where you want to copy the text. For example, you have a table having values from Cell A1 to Cell G6 and you have to make a copy of it on Cell J1 to P6. First of all, you will select the table by clicking on Cell A1 and dragging it diagonally towards Cell G6, defining your range. Then you will right click on the selected text and will select “Copy” from the Context Menu.
You will then right click on J1 and again, will select “Paste” from the Context Menu. Your range of select text will be copied to J1 to P6.
10. Cutting and Pasting a Range
To cut a range from one location and pasting it to another location is quite easy and simple in MS Excel. First, you need to select the range from where you want to cut the text. For example, you have a table having values from Cell A1 to Cell G6 and you have to remove it from its present location and place it on Cell J1 to P6. First of all, you will select the table by clicking on Cell A1 and dragging it diagonally towards Cell G6, defining your range. Then you will right click on the selected text and will click “Cut” from the Context Menu.
You will then right click on J1 and again, will select “Paste” from the Context Menu. Your range of select text will be move to J1 to P6.
11. Moving a Range
To move a select range to a new location, one method is explained under the “Cutting and Pasting a Range” section. However, there is another method, in fact a quick method of doing so. You will select the desired range and instead of placing you mouse on the anchor point, you will place it on the border line of your selection and click and drag the whole range to new location.
Workbook in MS Excel
Workbook in MS Excel
Workbook in MS Excel: How to Create, Open and Save workbook in Excel
Workbook in MS Excel
Workbook is considered as an Accounting or calculations oriented instrument that is used for record keeping and making calculations. A workbook contains multiple worksheets of account and other records. Now since, MS Excel is also associated with the record keeping and performs calculations therefore, the term workbook is used for file in MS Excel. When MS Excel is started, it automatically creates a Blank Workbook.
Creating a Workbook
By default, whenever you run MS Excel program, if automatically creates a blank workbook. However, if you want to create a new workbook you can do it in two simple ways.
Method 1: Using a Shortcut Key
The easiest and convenient way of creating a workbook is by pressing the shortcut key “Ctrl + N”. This command will quickly create a new MS Excel Workbook.
Method 2: Using File Menu
You can create new workbook by clicking the “File Tab” on the MS Excel Ribbon and clicking on “New” from the list.
Opening an existing Workbook in MS Excel
Opening an existing workbook is just like opening an existing file in any other program. You can open an existing workbook by using two methods.
Method 1: Using a Shortcut Key
You can open a workbook by pressing shortcut key “Ctrl + O”
This will open “Open File” dialogue box.
Select the location of the file and Press Open
Method 2: Using File Menu
You can open a file by clicking on File Tab and selecting Open from the list.
This will open “Open File” dialogue box.
Select the location of the file and Press Open
Saving a Workbook in MS Excel
Saving a workbook is just like saving any other file in any other program. You can save a workbook by using two methods.
Method 1: Using a Shortcut Key
You can save a workbook by pressing shortcut key “Ctrl + S”
If you are saving for the first time then it will open a “Save As” dialog box where you will enter the desired filename for your workbook.
If you have already saved then it will simply save the file without any prompt.
Method 2: Using File Menu
You can save a file by clicking on File Tab and selecting Save from the list.
If you are saving for the first time then it will open a “Save As” dialog box where you will enter the desired filename for your workbook.
If you have already saved then it will simply save the file without any prompt.
Ribbon in MS Excel
Ribbon in MS Excel
In Microsoft Office 2007, Microsoft introduced “Familiar User Interface” or “Familiar UI”, that replaced the traditional Menu bars and adjustable toolbars with a solitary “Office menu”, a smaller than usual toolbar known as “Quick Access Bar” and a Ribbon bar having multiple tabs. Every tab holds a toolbar bearing buttons for various operations. Toolbar controls have heterogeneous sizes and are grouped in a significant manner.
By default, the Ribbon contains 8 tabs in MS Excel 2013
File
Home
Insert
Page Layout
Formulas
Data
Review
View
Minimizing and Maximizing the Ribbon
By default, Ribbon is set to be fixed, however, it can be minimized or maximized by using the following methods.
Minimizing the Ribbon
Method 1
You can click the “^” symbol on bottom-right corner of the ribbon.
Method 2
Right click on the ribbon, and click on “Collapse the Ribbon”.
Method 3
The fastest way to minimize the ribbon is by pressing a shortcut key “Ctrl + F1”.
Maximizing the Ribbon
Method 1
Right click on the ribbon, and un-tick the “Collapse the Ribbon” option.
Method 2
The fastest way to pin the ribbon is by pressing the shortcut key “Ctrl + F1”.
Customizing the Ribbon
To customize the ribbon, right click on the Ribbon and click on “Customize the Ribbon” options
This will option the ribbon customization window
In this example, a new tab has been added.
And it is renamed as DEMO TAB
Now this tab is visible next to File Menu
Multiple handy options like All Charts type, Cut, Font Color and Format Cells have been added for demonstration purpose.
Worksheet in Excel
Worksheet in Excel
Worksheet in Excel: How to Select, Insert, Rename, Move, Copy and Delete Worksheet in Excel
Worksheet in Excel
Worksheet is the sub part of a Workbook. It is actual area where all the record keeping and calculation are performed. A worksheet comprises of Columns and Row and a point called Cell where a Column is intersected by a Row.
Worksheet plays a highly important role in MS Excel as all the automation in MS Excel requires worksheet as a key reference.
Selecting a Sheet
When MS Excel is started, it automatically creates a Workbook with 3 default sheets. The default naming convention for worksheets is Sheet 1, Sheet 2 and Sheet 3. You can select the desired sheet by simply clicking its tab below on the worksheet
You can select multiple sheets at time by keep pressing the CTRL key which clicking on the desired sheets.
Renaming Worksheets
The default naming conventions for worksheets in Sheet in MS Excel, however, this can be changed anytime you want. Simply follow the following steps:
- Right click on the tab below the sheet
- Select Rename form the context menu
- Rename the sheet
- Press enter to apply.
You can also rename a sheet by simply double clicking the sheet tab and this will put the name in editable mode. Now, you can change the name and press enter to apply.
Insert a Worksheet
Inserting a worksheet is also quite simple in MS Excel. Simply, click add new worksheet icon adjacent to Worksheet tabs.
Move a Worksheet
Moving a worksheet is quite simple. All you need to do is to select and drag the worksheet tab to the new position. For example, in this tutorial you will observer that Sheet 1 has been moved next to Sheet 4. You can do this by following the steps below.
1. Click and hold the mouse button on Sheet 1
2. Drag it towards Sheet 4
3. Drop it at the end of Sheet 4
Delete a Worksheet
To delete a worksheet, follow the following steps:
1. Right click on the desired tab below the sheet
2. Select Delete form the context menu
3. It will prompt for Delete Confirmation, click Delete and the worksheet will be deleted
Copy a Worksheet
While working in MS Excel, sometime, it happens that you need to perform the same tasks with the slight variations or maybe you need to experiment something new so the best strategy is to make a copy of your worksheet.
Making a copy is quite simple and very easy to manage in MS Excel. Follow the steps below to make a copy of your sheet.
1. Right click on the sheet and select “Move or Copy” from the context menu.
2. From the dialog box, check mark the “Create a Copy”
3. You may also select the position for this copy. In this tutorial, you will find the copy sheet at the end.
4. Click OK.
The output would look like the figure below
Important Terminologies in Microsoft Excel
Important Terminologies in Microsoft Excel
Important Terminologies in Microsoft Excel
Here you will find important terminologies that are used in Microsoft Excel. MS Excel is widely used all across the globe for its flexibility and high support for mathematical, statistical and financial calculations.
1. What is meant by a Row?
A row is a horizontal block that refers to a single record. In MS Excel 2013, there are 1,048,576 rows in one Worksheet or Sheet which means it can hold this much of records in a single Sheet.
2. What is meant by a Column?
A column is a vertical block that refers to all records falling under one field. There are multiple column within a single row or record. In MS Excel 2013, there are 16,384 columns in one Worksheet or Sheet which means it can hold a record having this much fields in it.
3. What is meant by a Cell?
An intersection point, where a row is intersected by a column is called a Cell. This block contains the specific data residing in a particular record (row) under a particular field (column). The label for a cell contains the IDs of both the row and the column. For example, the point where Column A is intersected by Row 1, will be marked as Cell “A1” where “A” is taken from the column label and “1” is taken from the row label. This new label will be unique throughout the sheet and hence will be used for formulas and functions.
4. What is meant by a Worksheet?
A worksheet is a container that contains multiple rows and columns.
5. What is meant by a Workbook?
A workbook is a container that contains multiple worksheets.
Cell References in Excel
Cell References in Excel
Cell references are very important in MS Excel. Cell referencing works in formulas and other automations performed within MS Excel. It is further classified into different type like relative, absolute and mixed references, and it is very important to understand their difference and purposes.
Relative Reference
By default, MS Excel uses relative reference. The formula shown in Cell D2 in the figure below. In this formula, Cell D2 references to Cell B2 and Cell C2. Both references are relative as they both are coming in a sequential manner
Let’s further observe it simply filling the formula from D2 to D5. You can do this in multiple manners as discussed in earlier tutorials like using Copy and Paste feature or Formula Fill feature.
Now, in this addition, Cell D3 is referencing Cell B3 and Cell C3. Cell D4 is referencing Cell B4 and Cell C4 and finally, Cell D5 is referencing with Cell B5 and Cell C5. In simple words: each cell references its two left side neighbors.
Absolute Reference
The formula shown in Cell E3 in the figure below is associated with absolute referencing. In this formula, Cell E3 references to Cell B3 and creates an absolute reference with Cell H3. The $ symbol represents the absolute reference of the cell.
1. To create an absolute reference with cell H3, place a $ symbol next to the column letter and row number of cell H3 (i.e. $H$3) in the formula of cell E3.
2. Now you can quickly fill this formula to the other cells.
It is evident that the reference to cell H3 is fixed and has not changed by dragging or filling. As a result, the correct lengths and widths in inches are calculated.
Mixed Reference
At times it happens that you need a combination of both relative and absolute references and this termed as Mixed Reference.
In the formula in cell F2 shown in the figure below, is the example of a Mix Reference.
Now, if you copy this formula to other cells or fill it. Drag cell F2 across one cell, and look at the formula in cell G2.
Well, the reference of the price should have been fixed to column B. The issue is that the formula is missing the $ symbol as described in the previous section.
Now, after putting the $ sign before Column Reference B, lets repeat the previous step again
Now, quickly drag this formula to the other cells.
You will notice that the references to column B and row 6 are fixed.
Cell References in Excel
Cell References in Excel
Cell References in Excel
Cell references are very important in MS Excel. Cell referencing works in formulas and other automations performed within MS Excel. It is further classified into different type like relative, absolute and mixed references, and it is very important to understand their difference and purposes.
Relative Reference
By default, MS Excel uses relative reference. The formula shown in Cell D2 in the figure below. In this formula, Cell D2 references to Cell B2 and Cell C2. Both references are relative as they both are coming in a sequential manner
Let’s further observe it simply filling the formula from D2 to D5. You can do this in multiple manners as discussed in earlier tutorials like using Copy and Paste feature or Formula Fill feature.
Now, in this addition, Cell D3 is referencing Cell B3 and Cell C3. Cell D4 is referencing Cell B4 and Cell C4 and finally, Cell D5 is referencing with Cell B5 and Cell C5. In simple words: each cell references its two left side neighbors.
Absolute Reference
The formula shown in Cell E3 in the figure below is associated with absolute referencing. In this formula, Cell E3 references to Cell B3 and creates an absolute reference with Cell H3. The $ symbol represents the absolute reference of the cell.
1. To create an absolute reference with cell H3, place a $ symbol next to the column letter and row number of cell H3 (i.e. $H$3) in the formula of cell E3.
2. Now you can quickly fill this formula to the other cells.
It is evident that the reference to cell H3 is fixed and has not changed by dragging or filling. As a result, the correct lengths and widths in inches are calculated.
Mixed Reference
At times it happens that you need a combination of both relative and absolute references and this termed as Mixed Reference.
In the formula in cell F2 shown in the figure below, is the example of a Mix Reference.
Now, if you copy this formula to other cells or fill it. Drag cell F2 across one cell, and look at the formula in cell G2.
Well, the reference of the price should have been fixed to column B. The issue is that the formula is missing the $ symbol as described in the previous section.
Now, after putting the $ sign before Column Reference B, lets repeat the previous step again
Now, quickly drag this formula to the other cells.
You will notice that the references to column B and row 6 are fixed.