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.

Selected row with Context Menu

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.

Select Column with Context Menu

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

Selected Cell with Context Menu

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.

Insert Menu with Shift Cells right

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.

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

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

Cell with Context Menu

  • 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

Delete Menu

  • 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

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

Insert Multiple 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

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

Insert Multiple Column

5.     Selecting a cell

You can select a specific cell by simply clicking on it.

Selecting Cell

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.

Selecting Block Range

7.      Selecting random cells

You can select random cells by keep pressing the “Ctrl” key and clicking on your desired cells.

Custom Cell Selection

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.

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

Menu showing copy and pasting

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.

Menu showing cut and paste options

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.

Moving Cells

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.

new

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

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.

save as

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.


AddFreeStats

 

 

 

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

file

Home

Home

Insert

insert

Page Layout

page layout

Formulas

formulas

Data

data

Review

review

View

veiw

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.

arrow symbol

Method 2

Right click on the ribbon, and click on “Collapse the Ribbon”.

Collapse the Ribbon

Method 3

The fastest way to minimize the ribbon is by pressing a shortcut key “Ctrl + F1”.

Ctrl + F1

Maximizing the Ribbon

Method 1

Right click on the ribbon, and un-tick the “Collapse the Ribbon” option.

Collapse the Ribbon

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

Customize the Ribbon

This will option the ribbon customization window

ribbon customization

In this example, a new tab has been added.

new tab

And it is renamed as DEMO TAB

DEMO TAB

Now this tab is visible next to File Menu

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.

Worksheet in Excel

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

Selecting a Sheet

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:

  1. Right click on the tab below the sheet
  2. Select Rename form the context menu
  3. Rename the sheet
  4. Press enter to apply.

Renaming Worksheets

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.

sheet 1

Insert a Worksheet

Inserting a worksheet is also quite simple in MS Excel. Simply, click add new worksheet icon adjacent to Worksheet tabs.

plus icon

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

button on Sheet 1

2. Drag it towards Sheet 4

3. Drop it at the end of Sheet 4

Drop it

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

Delete

3. It will prompt for Delete Confirmation, click Delete and the worksheet will be deleted

click Delete

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.

move or copy

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.

copy sheet at the end

4. Click OK.

The output would look like the figure below

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.

row

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.

Column

 

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.

Cell

4.      What is meant by a Worksheet?

A worksheet is a container that contains multiple rows and columns.

worksheet

5.      What is meant by a Workbook?

A workbook is a container that contains multiple worksheets.

workbook

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

Cell B2 and Cell C2

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.

D2 to D5

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.

formula of cell E3

2. Now you can quickly fill this formula to the other cells.

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.

cell F2

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.

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.

column B

Now, after putting the $ sign before Column Reference B, lets repeat the previous step again

Now, quickly drag this formula to the other cells.

drag this formula

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

Cell B2 and Cell C2

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.

D2 to D5

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.

formula of cell E3

2. Now you can quickly fill this formula to the other cells.

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.

cell F2

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.

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.

column B

Now, after putting the $ sign before Column Reference B, lets repeat the previous step again

Now, quickly drag this formula to the other cells.

drag this formula

You will notice that the references to column B and row 6 are fixed.