Excel opening screen in Excel

How the first screen of MS Excel looks like and what are the options?

How to quickly open MS Excel on windows machine?

There few ways to find and quickly open MS Excel on the windows desktop or
laptop.
1. Go to Run dialog box in the Windows machine by pressing Ctrl+R and
type excel and hit OK button. This will instantly open the MS Excel on your
machine.

2. The other way to quickly open MS Excel on windows machine is to press

Windows button on the keyboard and start typing ms excel as shown in
the picture below. When Excel icon is visible and selected (background of
that icon will become deeper shown in the picture below) then hit Enter
key.
 This will immediately open MS Excel on the machine.

3. The last option is to navigate through the start menu and click on MS
Excel icon.

What are the options on opening screen of MS Excel?

When we open MS Excel, the opening screen looks like below. The option below
Open heading is to open the file either from Recent Workbooks (that is auto
selected and the recently opened files are visible right side), from OneDrive or
from Computer.
We can also add other location to open the MS Excel files from.
Clicking on
 OneDrive - shows the recent files from OneDrive
 Computer - shows the recent folders from where the Ms excel file was
opened.
All of above two has a Browse button that is used to show the Open dialog box
from where we can choose our desired files or files from a folder.
In the left panel of green color (This is basically New menu of the MS Excel), we
have options like New, Open, Save, SaveAs, Print, Share, Export and Close.
1. New - is used to open a new work book in MS Excel. This also displays
default templates to use to create new MS Excel file. These are online
templates and mostly free to use. Based on these templates we can
create a file and fill in our data.

To create blank and fresh MS Excel file, select Blank workbook that is
displayed in first column of the first row.
2. Open - the default screen that appears when MS Excel is opened and it is
already explained in the beginning of this topic.

3. Save / Save As - Save and Save As both opens the Save As screen that
is shown below. This let us save the file we are working on either on the
OneDrive, Computer or any other places we have added.
4. Print - Print shows the Print page that allows us to choose the printer on
which we want to print. We can also save our MS Excel file as .pdf through
this option by selecting Microsoft Print to PDF option from the Printer
dialogue box.

Apart from this, it also helps us in setting up the page size, margin,
orientations etc. while printing.

5. Share - let us share current file through Email (as an attachment) or

invite people to work alongside.
6. Export - Export let us export the file in other file format like PDF/XPS and
other file format like .xls (older version of MS Excel), .ods (Open source
version of MS Excel - Open Office), Template, csv, txt etc.

Be aware that all functionality of the MS Excel file and the look & feel of
the original MS Excel file may change based on what file type we are
exporting to.
7. Close - Close, simply closes the current work book.

8. Account - let us change our Account information we had used to install or

active MS Office software.

9. Options - helps us to customize the MS Excel like change the font, font
size, toolbars etc. We shall talk about this in details later in this tutorials.
Cell Range in Excel
What is range and how to select, fill, copy-paste, move, insert rows and columns in MS Excel?

What is Cell in MS Excel?

Cell in MS Excel is the intersection between a row and a column on spreadsheet.
Cell is denoted by its Column and Row number on the spread sheet.
Look at the picture below, the bordered (selected) cell is denoted by A1 (A is the
column and 1 is the row). Same applies to other cell as well.

To quickly know what cell you are on, simply look at the top-left side just above
the spreadsheet. In picture below, the cursor is on column D and row number 17
so 'D17' is being displayed. By default that cell is active and ready to be edited
with keyboard or mouse.

How to select a specific column in MS Excel?

To select a specific column in MS Excel, simply click on the column header (top
of the sheet). Look at the picture below for an example. To select D column,
move cursor to D column header (the mouse cursor would change to downward
black arrow) and click, the entire D column should get selected.

How to select a specific row in MS Excel?

To select a specific row, go to the header of the row (in the left side) and click
the row number. To select 6th row, click on 6th row header as shown in the picture
below.

How to select range of cells in MS Excel?

With mouse - To select range of cells (eg. B2 to C6), click on B2 first and then
drag the mouse to C6 (by holding the mouse key).
With keyboard - Instead of dragging with mouse, we can also select range of
cells using Keyboard. Simply use arrow keys to move to the starting cell and
while holding the shift key, use arrow keys to move left and right to select
desired range of cells.
How to select multiple individual cells in MS Excel?
To select multiple individual cells, click on first cell and then hold CTRL key and
then click on each cell to select.

How to automatically fill a value in range of MS Excel

cells?
To fill a particular value in range of cells, write down the value (in this case 8) in
the cell and then hold the bottom-right corner of the cell (the mouse cursor
becomes '+') and drag to rows or column you want to fill with this value.
The result would be like below.

How to fill pattern of values automatically in MS Excel

cells?
1. To automatically fill numbers in ascending order -

Write down first 2 numbers in 1st and 2nd row from where we want to start.
Select both cells and drag to rows or columns by holding the bottom-right
corner of the 2nd cell.
 The result would look like this.

2. To write table of a specific number automatically -

To write table of any number in MS Excel, write first 2 values as shown

below and then hold the bottom-right corner of the 2nd cell and drag.
 Here is the result.

3. To automatically write range of dates -

To automatically fill the range of dates, write the first date in a cell and
then hold the bottom-right corner of the cell and drag.

The result would be like this.

4. To writing day of week & month name automatically -

To automatically fill day and month in MS Excel, write a day or month in a

row as displayed below.

Now, hold the bottom-right corner of the selected cells and drag. The
result would be like this.
 We could have done this individually to week day and month name also.

Play more...
Can you try following
1. Writing the number in descending order - from 10 to -5
2. Writing dates in descending order - today to 1 week back
3. Writing repetitive values a (in C1) and b (in C2) to cell C3 (a), C4 (b), C5
(a), C6 (b) etc.

How to move range of cells in Excel?

To move range of cells in the Excel, select the cell by clicking and dragging.
Selection of cells can be achieved using Keyboard also (use Shift+Arrow keys).
Now click the border of the selection as shown in the picture below and drag to
the new location.
The result will be like this.

How to copy range of cells to new location in Excel?

To copy the range of cells values to new location, follow move approach as
above & hold the CTRL key while dragging to new location. This will keep the
original values as it is and copy those to the new location.
The same can be achieved using typical Copy-Paste approach also.
Select the range of cells to copy and press CTRL+C (or right click and select
Copy) and then go to the target location and Press CTRL+V (or right click and
select Paste) as shown in the picture below.

How to insert rows and columns in Excel?

Inserting row
To insert a row in between, select a row after which we want to insert a row
(click on row header to select the row) and right click and then select insert from
context menu.
The result will be like this.

To insert multiple rows in between, select multiple rows (by selecting a row and
dragging mouse over other rows) and then insert.
Inserting column
To insert a column, first select a column by clicking on the column header and
right click. Select Insert from the context menu.
The result would be like this.

To insert multiple columns, select multiple columns (by selecting a column and
dragging mouse over other columns) and then insert.

Functions and Formulas in Excel

How to work with functions and formulas in MS Excel?

What is Formula & Function in Excel?

A formula is a mathematical relationship or rule expressed in symbols that
calculates the value of a cell.
A function is a pre-defined formula available in Excel that is ready to use.
A formula in excel starts with '=' inside a cell.
For example, to show the addition of B2 and B3 value in B4, we have written
'=B3+B2' in B4 and hit Enter key. This gives the calculated value to B4.

The result will be shown like this.

If we try to add two string value instead of two integer values, we will get error
like shown in the image below

Operators in Formula
Excel understand the operators in formula as it is treated in normal
mathematical calculations. Look at the image below.
In B5, we wrote '=B2+B3*2' and this resulted as 96 because first it multiplied 8
by 2 and then added to 80.
In B5, we wrote '=(C2+C3)*2' and this resulted as 176 because first it added C2
and C3 and then multiplied by 2.

Similarly, the precedence of division, additions and subtraction work.

How to display a mathematical or expressive data as

string in Excel?
Notice the data in B6 and C6 in above picture; normally when we copy-paste
these strings in any cells, Excel tries to evaluate the values of those cells. But
what if you just want to display them as it is? In that case, we need to prefix the
formula or mathematical expression with single quote (') like displayed in the
picture below.

The result will be like

How to Copy-Paste a formula to other relevant cells in
Excel?
Let's assume a scenario as shown below where we want to copy the formula of
D2 to D3, D4 so that we get total of all the records.
There are two ways to copy the formula
1. Go to D2 and Copy (either right click and Copy or press CTRL+C). Now
select both D3 and D4 cells and Paste (right click and Paste or CTRL+V).

2. Go to D2 and hold the bottom-right corner of the cell (the mouse cursor
becomes + as shown below) and drag to D3 and D4.

The result would be like this

How to quickly know the sum & average of selected
cells value in Excel?
It is very easy, just select all the cells you want to know the sum & average of
and look at the right side of MS Excel status bar and you would be seeing them
like picture below.

Here
1. AVERAGE is the average value of selected cells (D2, D3 & D4)
2. COUNT is the number of cells selected
3. SUM is the sum of selected cells value.
Quickies
 Can we add multiple cell value by separating with + symbol (eg.
=A1+A2+A3+A4) ? - YES
 Can we add, subtract, multiply and divide at once using cells reference
(eg. =A1-A2*A3/A4) ? - YES
 Can we directly add, subtract, multiply and divide numbers in cells (eg.
=20+30-20*40/3)? - YES

How to select range of cells in MS Excel?

Range of cells is the selection of all the cells between starting and ending of the
cell. To select range of cells, we separate two cells using colon (:).
For example:
If we want to select B2 to B4, we can write B2:B4. This will select B2, B3 & B4
cells. Generally, range is used within the inbuilt functions of MS Excel.
How to insert a function in Excel?
To insert an inbuilt function in MS Excel, select a cell and then press Fx icon as
shown in the picture below (In this case, function will be inserted into D5 cell).

A dialog box opens with the list of functions available in MS Excel and by default
'Most Recently Used' function appears in the list. We can change the category
dropdown to choose a function available in that category.
For this demo, let's use AVERAGE function from the list (In case this function is
not available in the default list of your screen, simply use the Search for a
function box to search it).
After selecting AVERAGE from the list of functions, click OK. This opens another
dialog box that ask us the argument to pass into this function.
Average basically returns the average of its argument. So either we can write all
cells one by one in Number1, Number2 etc. boxes or we can specify them by
selecting the first cell (D2) and then start dragging (the screen changes as
below). Keep dragging till you reach to your target cell (D4). Excel automatically
surrounds selected cells with dashed border to notify that those cells have been
selected.
We will get the range of cells written in Number1 text box as displayed in below
picture. The result also appears on the dialog box. Now click OK.

and here is the result.

Quickies
1. Can we write the function directly into the D5 cell? - YES
2. If we can directly write functions into the Cell, why excel has given long
steps like above? - All users are not as intelligent as you are ......
OK, actually many functions have more than one arguments and it
might be difficult for many to remember those arguments.

Cell references (Relative, Absolute, Mixed) in

Excel
What are different types of cell references in MS Excel?

In previous post, we learnt about Functions and Formulas in Excel. In this post,
we shall learn about different types of Cell references to be used in formulas.

What is cell reference in Excel?

Cell reference is the name of a cell made by combining column and row names.
It is used to refer a particular cell in the spreadsheet.

Relative Cell Reference

The default cell reference in Excel is relative reference. Let's understand this by
an example.
Write '=D3*C3' in E3 cell, this simply multiply the values of these cells and
write. Now copy (either by dragging or copying) this formula to other cells (E4,
E5).
You would notice that even if we have just copied and pasted the formula to
other cells, Excel basically takes the relative reference of it's left side cells and
write value correct value into these cells.

Absolute Cell Reference

Generally, absolute cell reference in formula is given to that cell whose value is
constant. Below example show VAT% in E column and that is the 0.15 of the
Rate.
We have specified 'Vat Rate' in H3 cell and wrote '=C3*$H$3' to E3 cell.
Absolute reference of row and column is given by prefixing '$' symbol before row
and column name.
Notice the '$' symbol before column and row names.
This will calculate the 15% of 100 and write into E3 cell. If we copy this formula
to E4 and E5 column then we get the correct value.
and here is the result.

Quickies
Now, assume that instead of giving Absolute reference to H3 cell, you would
have given Relative reference in the E3 cell. So your formula would have been
'=C3*H3'. Now, if you copy-paste this to E4 and E5, you would have got
'=C4*H4' and '=C5*H5' respectively and that will result in wrong value.

Mixed Cell Reference

Mixed cell reference is the combination of Relative and Absolute cell reference.
For example,
1. Formula '=C3*H$3' says that we want Column H relative reference but
Row 3 absolute reference.
2. Formula '=C3*$H3' says that we want Column H absolute reference but
Row 3 relative reference.

Ribbon (Toolbars) in Excel

How to customize Ribbons, Toolbar in MS Excel?

What is Ribbon in MS Excel?

Ribbon is MS Excel is nothing but a placeholder on which Tabs exists. Look at
below picture, we have HOME, INSERT, PAGE LAYOUT.... etc. tabs on a panel
and that panel is called Ribbon.
Each tab has it's relevant commands in the form of Toolbar icon that does a
certain work. HOME tab has most frequently used toolbar icons.
What are display options of Ribbons?
In a way, Ribbon is better than traditional menu systems as most of frequently
used commands are just one click away. However sometimes it is annoying to
see the big horizontal ribbon bar that occupies almost 1/5 of the screen height.
To see the Ribbon display options, click on the icon (Ribbon Display Options) at
the top-right side of Excel screen as shown below.

that shows Ribbon Display Options menu as shown below.

Let's see what options are there
1. Auto-hide Ribbon - clicking this will hide the Ribbon (and maximize the
Excel window, if not already maximized) when you started working in the
spreadsheet. When you click on the top-bar as displayed in the picture
below, the ribbon appears.

2. Show Tabs - This only shows the tabs (not the commands icon).
However, clicking on Tab shows that tab commands. When you start
working on the spreadsheet, the command disappears.

This can also be achieved using 'Collapse the Ribbon' option from the
context menu (right click on the Ribbon) of the Commands bar.
 3. Show Tabs and Commands - This is the default display option of the
Ribbon. This displays the tabs and commands always (In India, we call it
24*7 ).

How to customize the Ribbon in MS Excel?

To customize the Ribbon, right click the ribbon and select 'Customize the
Ribbon'.

This shows below dialog box. Note that you would not be able to customize the
default tab and its group.

How to create a new tab?

To create a new tab, click on the 'New Tab' button and enter the Display name
(in this case 'Your Tab', look at the picture below) in the Dialog box that
appears. This will create a new tab. To rename the default name, click on
'Rename' button by selecting new tab.
How to create a group in the tab?
Now we need to create a custom group under that tab (we can't directly create
command under tab). Select the newly created tab and click on 'New Group' and
then click on 'Rename' button again and select an Icon and give custom name to
that group.
Now, select the commands to be added to that group from List of commands and
click 'Add >>' button. To remove, just click '<< Remove' by selecting the
command from right side List.

The newly created tab looks like below

The position of the tab can be changed by using Up and Down arrow at the right
side of the dialog box.

How to remove custom commands, group and tabs?

To remove custom added commands, group and tabs; simply select them and
click on '<< Remove' button.

How to remove all custom added tabs at once?

To remove all customization in the tabs, click on 'Reset' button and select 'Reset
all customizations' from the dropdown menu as shown in the picture below.

How to create a new group in custom or default tabs?

To create a new group in the default tabs, follow exactly the same approach as
explained above.
How to customize the Quick Access Toolbar?
Quick Access Toolbar is the top most bar, you see on the Excel screen.
Look at the first three options from context menu of the Ribbon.

1. Adding a command to Quick Access Toolbar

To add a command to Quick Access Toolbar, right click on the command
and select 'Add to Quick Access Toolbar'.
 That will add the command and it looks like this.

2. Removing a command from Quick Access Toolbar

To remove a command from Quick Access Toolbar, right click on
command of Quick Access Bar and select 'Remove from Quick Access
Toolbar'.

3. Customizing the Quick Access Toolbar

To customize the Quick Access Toolbar, simply choose the 2nd option
 from Ribbon Context menu 'Customize Quick Access Toolbar ...' and use
'Add >>' or '<< Remove' button to add or remove the commands to the
Quick Access Toolbar.

4. Shifting the position of Quick Access Toolbar

To shift the position of the Quick Access Toolbar, select 3 rd option (Show
Quick Access Toolbar Below the Ribbon) from the Ribbon context menu
and that shifts the position.
Vie Worksheet and Workbook in Excel
How to create a worksheet and a workbook in MS Excel?

Worksheet is a collection of rows and columns whose intersection makes cells in

which we keep and manipulate data.
Workbook is a collection of Worksheets. A workbook can contain many
worksheets.

How to create a new Workbook & Worksheet in Excel?

To create a new workbook in MS Excel, click on New at the top-left corner and
then select New menu and then click on 'Blank workbook' icon as shown below.

This opens up a new workbook and that looks like below.

By default, Excel opens up only one sheet in the workbook, however we are free
to add many other using + sign at the bottom-left corner of the workbook as
shown below.

Instead of following above steps, you can also right click on the existing sheet
name and select 'Insert...'
The following dialog box appears. Select Worksheet icon and then click OK.

Clicking OK will add a new worksheet after the last sheet.

How to rename a worksheet in MS Excel?

There are two ways to rename a worksheet.
1. Right click the sheet name and select Rename.
 2. The sheet name becomes editable like below, simply write new name
there.

After you are done with writing new name, simply press Enter key.

How to move/copy a sheet in Excel?

To move a worksheet, right click on the sheet you want to move and select
'Move or Copy...'.
That opens up below dialog box. Now select the sheet (from the list box) before
you want to move the selected sheet and click OK.

This moves the selected sheet as shown below.

To Copy a sheet: Follow all above steps of Move and before clicking OK, check
'Create a copy' checkbox.
The other way to move a sheet is to click on the sheet and drag to the desired
location (as shown below).

How to delete an existing sheet from the Excel

Workbook?
Right click the sheet you want to delete and select Delete.

You would get a confirmation box like below and then click Delete button.

This will delete the worksheet.

Cell formatting in Excel
How to format cell in MS Excel?

In the last post, we talked about Excel Worksheet and Workbook. In this post,
we shall learn about formatting and doing other formatting activities related with
cells of Excel worksheet.
The default data type of any cell in excel is 'General', to prove this write any
numeric data in a cell like this.

Now right click that cell and select 'Format Cells...' or simply press CTRL+1 (1
should not be pressed from numeric key pad).
and this shows a Format Cells dialog box. Notice below dialog box picture and
see that Category list has 'General' selected by default.

This means that any cell of the Excel worksheet can accept any type of data and
all are treated as string (however mathematical operations may parse the string
to integer automatically).

How to format data for decimal characters, currencies

values, percentage, and comma separation?
To format data into Number Formats, we can use either 'Number' group from the
Ribbon as displayed in below picture or Number, Currency and Accounting
category from the Number tab of Format Cells dialog box (as displayed above).
For example to keep decimal characters after a number, we can use 'Increase
Decimal' icon from the Ribbon group.

Same as above we can also use 'Decrease Decimal' to decrease the decimal
characters.
We can also use other icons from the group for Percent style, Comma Style,
Accounting Number Format.

Apart from Toolbar Icons, we can also use Format Cells dialog box to achieve
same thing.
How to change the color of the cells in Excel?
To change the color of the cells, first select the cell and then use Font group
icons as displayed below. To change the background color, use Fill color icon and
to change the font color use Font Color toolbar icon.

Apart from using Toolbar icons, we can also use Format Cells dialog box and
select Font tab. Look at the picture below.
We can also achieve following formatting using this
 Bold
 Italic
 Underline
 Font size
 Font style
 Strike through
 Superscript
 Subscript

How to change the border style of the Excel cells?

To change the border style of the cell, select the cells we want to format and
then click on the Border icon dropdown and then select the border style we want
to apply.
In this case, the applied border looks like this.

Instead of following above steps, we can do border formatting using Format

Cells dialog box and select Border tab and select desired border.
How to copy the format of a cell to other cells in MS
Excel?
To copy the format of a cell to other cells, we can use 'Format Painter' icon from
the Ribbon.
First select the cell you want to copy the format from and then click on 'Format
Painter' icon as displayed above.
The mouse icon changes like below. Now select the target cells you want to
paste the format into.

and the result looks like below.

How to clear the formatting of cells in MS Excel?
To clear the formatting of the cells, first select the cells you want to clear format
from. Now click on the Clear icon from editing group on the Ribbon as displayed
in the picture below and click on 'Clear Formats'. This clears the format of the
cell.

To clear the cell formats including the cell data, click on 'Clear All'.
This also helps in following
 Clear Contents - To only clear the content, not the formatting
 Clear Comments - To only clear the comment of that cell (we will learn
how to add comment for the cell later on)
 Clear Hyperlinks - In case a string has become hyperlink and we want to
remove the hyperlink, we can use this option.

Templates in Excel
What are templates and how to create a file based on templates in MS Excel?

What is templates in MS Excel?

Template in MS Excel is a readymade pattern / design / guide available to make
a certain type of MS Excel file. There are plenty of online/offline free templates
available in MS Excel.
How to create a file based on template in MS Excel?
To create a new MS Excel file based on a template, click on File menu (first and
green color), and choose New and you would see your screen like this.

Notice that the default screen shows some popular templates used however we
are free to search a new template based on our need.
To quickly find a template based on category, use the tags available just below
the Search box. For example, click on Business and you would see your screen
like below that shows list of templates under this category and list of sub
categories in the right panel.
Select any one of them (In this case, we selected Startup Expenses) that shows
another popup with Create button like below.

Clicking Create button creates a new file based on the template and it looks like
below
Now you can simply overwrite different cells value to save your data into this
template.

How to create our own custom template in MS Excel?

Creating our own custom template is very simple. Just follow these simple steps
1. Create a new Excel workbook and write the content you want to available
as template. Below are sample one for this tutorials.
2. Now, click on 'Save As' from File menu, you should be seeing something
like this.
 3. Now write the File name, change the 'Save as type' to 'Excel Template
(*.xltx)'. Notice the location where this file is being saved (you do not
need to change this). This is the default folder for the custom templates
created in Excel. This folder is used to display custom templates by Excel
in New screen. After you have done all that is displayed above, simply
click on Save button.

This should save the template. Now, to use this template first you will
need to close the Template file so close this file from top-right X icon.
4. To create a new MS Excel file based on this created template, go to File >
New and click on PERSONAL tab. This should show the template you have
just created. Simply choose this by clicking it.

5. You should be seeing a new file created based on this template. Now
change the data you want to change and Save this as new MS Excel file.

How to update or delete the custom template in Excel?

To update the custom template, simply opens this template file in Excel. In
general the path of the custom template files are 'C:\Users\<user>\Documents\
Custom Office Templates'. After opening the template file, change whatever you
want to change and save & close it.

You are ready to use the modified template now!

To delete the template file, simply go to folder as specified above and delete
the file.

Find and Replace in Excel

How to find & replace, select cells data, go to in MS Excel?

In previous post we learnt about Templates in MS Excel. In this post, we shall

learn how to find, replace, select cells based on data, conditions, formulas etc.
To learn those, we will take an example of below data.

How to find data in MS Excel?

To find data in MS Excel, we can use following approach
Click on 'Find & Select' icon from the 'Editing' group on the Ribbon and select
'Find...'.

This opens up Find and Replace dialog box as shown below. Alternatively, we can
also press CTRL+F to open up Find and Replace dialog box.
Enter the data you want to find in your current sheet and click 'Find Next'
button. If the data is found, it selects that particular cell otherwise displays a
message of not found.

: By default Excel is case insensitive that means that it doesn't check whether
the text is written in upper case or lower case.
In case, we need more control over how Excel finds the data we are looking for
then click on 'Options >>' button and it shows like this.

Here, we can select

1. 'Within:' - to select data within the current spreadsheet or in the entire
workbook.
2. 'Search:' - start searching record by rows or columns
3. 'Look in:' - whether to search in Formulas, Values or Comments (will learn
about it in coming posts)

How to find data into a certain formatted cells only in

Excel?
Sometimes, we want to find a specific text into a specific formatted cells only;
like only in header or footer etc.
To do this, click on 'Format...' dropdown button and select 'Choose Format From
Cell...'.
Notice the mouse cursor that changes like below.

Now click on the cell whose format you want to choose to find the data into.
See that the 'Preview' button changes to the chosen cell format. Now write the
data to find into the Text box and click Find Next button.
The data to find will be searched only to those cells that satisfies the chosen
format.
Quickies
To clear the find format on the Find and Replace dialog box, click on the
Format... dropdown again and then select last option (Clear Find Format).

How to find all occurrence of data in Excel?

To find all occurrence of data in Excel, we can use 'Find All' button.
All the matched cells references are listed at the bottom of the dialog box.
Clicking on each occurrence, selects that particular cells.

Quickies
To hide the list of occurrences, simply hold the bottom border of the dialog box
with mouse and drag up. To show again, hold the bottom border of the dialog
box again and drag down.

How to find case sensitive data or entire cell data only in

Excel?
To find case sensitive data use 'Match case' checkbox. Checking this check box
will find only those data that is exactly written in the 'Find what' textbox.
To match all the content of the cell (not partial content of the cell), check the
'Match entire cell contents' checkbox and then click on 'Find Next' button.

How to replace data in MS Excel?

There are three ways to open Replace dialog box.
1. Click on 'Replace...' of the 'Find & Select' dropdown from Editing group on
the Ribbon.
2. Press CTRL+H.
3. Click on Replace tab.
Replace dialog box looks like below

You may have noticed that almost all options of the Find are available here and
they work exactly the same way.
The only addition in the Replace dialog box is 'Replace with:' textbox, Replace All
and Replace buttons.
Let's try to replace 'FALSE' to '0' using Replace dialog box. Fill the data as it is
written in below dialog box.
Now we have two options to replace data
1. Replace All - to replace all occurences at once
2. Replace - to replace each occurences one by one

Quickies
To close this dialog box, simply press ESC key on the keyboard or press Close
button.
How to go to a specific cell in Excel?
'Go to' is used to go to a specific cell in the spreadsheet.
To open the Go to dialog box, choose 'Go To...' of the 'Find & Select' dropdown
from Editing group on the Ribbon.
This opens up Go To dialog box that looks like below.

Write the Reference of the cell in the text box and click OK. You will be sent to
that particular cell.

If the reference contains the range of cells, all those cells will be selected.
Clicking on Special button opens up 'Go To Special' dialog box.
Go To Special dialog box also helps us in finding a particular cells with specifics
as shown in the picture below.

How to find all the cells having Formulas in Excel

spreadsheet?
What if we want to select/view all the cells having Formula in it?
Okay, we have a way to find them. Select Formulas from the Find & Select
dropdown on the Ribbon as shown below (Do not worry about Find & Select icon
here, when you decrease the size of the Excel widow, the big icon & description
goes and small icon appears to accommodate all the commands on the Ribbon).
When you click on 'Formulas', you will see all the cells having formulas gets
selected. Notice the picture below (PPF field formula in this case is '=E4*0.15').

Same applies to other commands (Comments, Conditional Formatting etc.) of

the Find & Select dropdowns.
Data validation in Excel
How to validate data in MS Excel?

In previous post, we learnt about Find, Replace, Go To etc. in MS Excel. In this

post, we shall learn how to validate data in MS Excel.
To understand how validation is implemented, we have created a sample form.

Let's say, we have a requirement for age data that it should accept data
between 1 to 18 only (in C4 cell). First select C4 cell by clicking on that cell. Now
open 'Data Validation' dialog box by clicking on 'Data Validation' command icon
from 'DATA' tab on the ribbon as shown below and choose 'Data Validation...'

This opens up following dialog box.

Now select 'Whole Number' from 'Allow' dropdown.

select 'between' from 'Data' dropdown.

write 'Minimum' text box to 1 and 'Maximum' text box to 18 in the respective
text boxes. These values can be referenced by a specific cell value also by
clicking on the right side icon of these text boxes.

If we want to provide a hint to the user who is entering the data, we can use
'Input Message' tab and fill Title and Input Message text boxes with the hint we
want to show to the user.
Similarly, we can also provide Error message to the user if he/she has entered
wrong data into selected cell. To do this click on 'Error Alert' tab and fill
respective text boxes. Please note that if this tab is not used, Excel gives default
invalid data error alert.
Now, when you press OK.

You are done with the validation of C4 cell. Now this cell should accept only
Integer data between 1 to 18 only.
Let's try to fill this form. As soon as we go to C4 cell, we are presented with the
'Input Message' strings we had filled into the Data Validation dialog box. Look at
the picture below.
If you enter invalid data, you would be welcomed with Validation failed alert
that contains the data you had filled in 'Error Alert' tab.

If you are intelligent enough to enter correct data, you should be quitely
moved to the next cell.

How to provide dropdown to select data into the cell in

Excel?
Many a time we want to restrict the entry of data into a particular cell. Look at
the example below.
For the State field, we want to restrict the entry only to 4 states that is written
in the G5:G8 cells and want to give a dropdown hint in C7 cell.

To do this, open Data Validation dialog box and select 'List' from 'Allow'
dropdown. Keep the 'In-cell dropdown' checkbox checked. Now click on the right
side icon of the 'Source' text box.

you would get your screen like this. Now select (select and drag) the source
from where you want to create dropdown items and press Enter key or click on
the right side icon of the text box.
Now click OK on Data Validation dialog box. When you go back to C7 cell, you
would notice a dropdown appears as shown in the picture below that allows user
to select the data for this cell.

If user wants to write other data that is not in the dropdown, he/she gets error
alert.

How to remove the data validation from a cell in MS

Excel?
To remove the data validation from a cell, go to the cell and open 'Data
Validation' dialog box.

Click on 'Clear All' button.

Print options in Excel
How to take print out from MS Excel and setup page properties?

In the previous post, we learnt about data validations in MS Excel. In this post,
we shall learn how to take print out of Excel worksheet and how to set different
printing settings.
To open the Print dialog box, go to File > Print and you would see a screen
similar to this.

If all the values are showing correct in your dropdown, simply hit Print (the bit
one) button and it goes straight to the printer.
If your system is connected to multiple printers then you have option to select
on which printer you want to send the print. Click on the Printer dialog box and
you should see the list of printers.
Apart from list of printers, we als have following options
1. Microsoft Print to PDF - this converts your sheet into .pdf format
2. Microsoft XPS Document Writer - this converts your sheet into XPS format
3. Send to OneNote XXXX - takes this sheet to OneNote.
To navigate through different pages in the Preview, click on page left and
right arrow that appears at the bottom of the screen.
To print multiple copies, increment the value of the 'Copies:' textbox.
To print specific pages only, you can use Pages: text boxes where you can
write from and to page numbers you want to print.

When you go back to your sheet from Print dialog box, you see a dashed vertical
and horizontal line as shown in the picture below. This indicates that how many
columns are being printed in a page, if you want to adjust the column width you
may do so and this vertical line will help you know whether your columns are
fitting in that page or not. The same applies to the horizontal dashed line. These
lines are just a page indicator to know what data is going in what page.

How to print entire worksheet, workbook and selected

cell in the Excel?
To get more control into what and how much we want to print, we can use 'Print
Active Sheets' dropdown.
 Print Active Sheets - print the sheet on which you are working currently
 Print Entire Workbook - prints the entire workbook (a workbook can
contains many worksheets)
 Print Selection - prints only those cells that are selected in the worksheet
How change the orientation of the page while printing
in MS Excel?
To print into portrait and landscape orientation, use this option. Click on the
'Portrait Orientation' dropdown and change the orientation you want to print
into.

In case you are printing multiple copies of the worksheet, you may want to use
Collated or Uncollated options.
What is Collated and Uncollated?
Let's assume, we want to print 8 copies of the worksheet.
Collated option will print 1st copy of the entire worksheet and then 2nd copy of
the entire worksheet .... and then 8th copy of the entire worksheet.
Uncollated option will print 8 copies of 1st page, 8 copies of 2nd page, 8 copies of
3rd page etc.
You can select Collated option from 'Collated' dropdown as per your
requirement.

How to change the Page size in Excel?

To change the page size, click on 'Letter' dropdown and you would be presented
with several standard page sizes. Select anyone that suits you.

If your page size is custom, click on 'More Paper Sizes...' appearing at the
bottom of this dropdown. This takes you to the Page Setup dialog box. Here
either you select the page size from 'Paper size' dropdown or click on 'Options...'
button that takes you to the Printer Properties dialog box where you can
completely customize the size of the paper.

How to setup the page margin in Excel?

To setup the paper margin in Excel, click on 'Normal' dropdown that shows few
pre-set margins. Select any one of them if that suits you.
If not, click on 'Custom Margins...' that appears at the bottom of this dropdown
options. This will open up again the Page Setup dialog box however this time,
the 'Margins' tab will be selected.

Here you have complete control over what value you give for different direction
margins. You can also specify margin of the header and footer.
Instead of setting up custom margin through dialog box, we can also do this
visually. Click on the 'Show Margins' icon at the bottom-right corner of the Print
dialog box.
this shows the margin notation on the Preview, use mouse cursor to drag the
margin. Notice that the Margin dropdown value has changed to 'Last Custom...'.

How to use scaling of pages in Excel?

Many a times we see that few of the columns gets printed on other pages. To
solve this problem, we can use this option.
Clicking on 'No Scaling' dropdown shows following options
 No Scaling - prints as it appears on the worksheet
 Fit Sheet on One Page - shrinks the data of worksheet so that it fits in one
page
 Fit All Columns on One Page - shrinks the column size to fit in one page
width
 Fit All Rows on One Page - shrinks all data to fit into one page height
How to setup header and footer in MS Excel?
To setup header and footer in MS Excel, click on 'Page Setup' link at the bottom
of the Print dialog box. Select Header/Footer tab. On this dialog box, you see
different options in header and footer dropdown. Select anyone of them based
on your requirement and press OK.
To customize header/footer, click on 'Custom Header...' and 'Custom
Footer...' respectively. Clicking on 'Custom Header...' shows below dialog box
where we can write custom header that may appear in left, center or right of the
page.
How to change the Page order in MS Excel?
By default, MS Excel marks the page number vertically (Down, then over). It
counts the number of pages down based on what is the last row in which at least
one cell is having data. To start marking the page horizontally, select 'Over, then
down' radio button under 'Page order' heading.
Quickies
To quickly see the actual size of the text going to appear in the print, click on
the right most icon (Zoon to Page) at the bottom-right of the Print dialog box.

that will show the acutal size of the text that will appear on the printer.
Views

Protect file in Excel

How to protect & unprotect MS excel file?

In the previous post, we learnt about how to take print out from MS Excel and
setup page properties. In this post, we shall learn about how to protect a excel
workbook (file) so that it requires a password to open.
To create a password protected file, first open that file in the MS Word and go to
File > Save As. Now click on Browse button that comes when you have selected
Computer in the left panel (Look at the picture below).
On the Save As dialog box, navigate to the folder where you want to save the
file. Give a new name and before you click on Save, click on 'Tools' dropdown
just beside Save button. Choose 'General Options...' from the 'Tools' dropdown.
that will open 'General Options' dialog box as shown below. Here we can create
two passwords
1. Password to open: will be asked when user tries to open the excel file
2. Password to modify: will be asked at the time of opening the file but after
Open password to make sure that this user has modify permission also to
the file. If modify permission is not supplied correctly, the file is opened in
Read Only mode.
You must have noticed a 'Read-only recommended' checkbox above OK button.
This is used to suggest the user that the author of the file has recommended to
open the file in read only mode.

How to open the protected file in MS Excel?

To open the protected file, use the same approach as if you are opening a
normal MS Excel file. However, before completely opening the file, Excel asks
you for the password dialog box as shown below.

Supply the password (if both Open and Modify password are given, two dialog
box appears) and click OK. Your file should be opened in the Excel.
In case you have checked the 'Read-only recommended' check box, user would
be presented with following alert also.

Other way of protecting MS Excel file

The other way of protecting the MS Excel file is by going to File menu and click
on Info tab in the left as shown in the picture below.
Click on Protect Workbook button to show the dropdown and then select 'Encrypt
with Password'.
that shows below dialog box. Enter password and click OK.

Next time, you try to open this file you will be presented with the password
dialog box and you will have to enter password to open this.
The benefit with the 1st approach was that you can ask for Open and Modify
password both. Here once the file is open, user will be able to modify the file
also.

Quickies
 What is the difference between Open and Modify password?
Answer: No difference as both are password . Okay, the Open
password is the combination of both; Modify and Open. The modify
password is just an extra layer of protection. In case you do not want
everyone to modify the file (for them, the file will open in Read Only
mode) but you want few user having password to modify the file.

How to unprotect the workbook in Excel?

Unprotecting the workbook is simple. Just follow the Protect approach and on
the password dialog box, remove the password and click OK.
: 21

Named range, named constant in Excel

How to define & use Named range, Named constant in MS Excel?

In the previous post, we learnt about how to protect a MS Excel file. In this post,
we shall learn how to created Named range, Named constant in MS Excel and
how to edit, delete and use them.

What is Named range or Named constant in MS Excel?

Named range is a range of cells that has been given a specific name so that it
is easy to understand in the Formula.
Named constant is a name given to a constant value so that the same can be
easily understood and can be reused at multiple places.

How to create Named range in MS Excel?

There are two ways to create Named ranges.
1. Select the range of cells you want to name, in this case we have selected
all Salary cells (E4:E17). Now go to the name box (top-left in below
picture) and write name and hit Enter key.

Salary name has been created.

2. The other way is to use the Ribbon bar and Dialog box. Click on 'Define
name' dropdown from 'Formula' tab after selecting the range of cells to
name.

You would see something like this. Now write the Name and hit OK.
How to create a Named constant in MS Excel?
Click on 'Define name' from the Formula tab, you would see 'New Name' dialog
box. Write the Name in the 'Name:' box and constant value in the 'Refers to:'
box.

Click OK. This will create a Named constant that can be used in the formulas.
How to edit, delete named range or named constant in
MS Excel?
To delete, edit named range or named constant click on 'Name Manager'.

that will open following dialog box.

To edit, select a Name (row) and click OK. Modify the respective textboxes
values and click OK.
To delete, select any of the formula and click on Delete button.

How to use Named range and Named constant in MS

Excel?
To use Named range or Named constant, press F3 at the edit mode of Cell and
you would see 'Paste Name' dialog box. Click OK and the selected name appears
in blue color in the cell.
or select a cell, click on 'Use in Formula' command dropdown from Formulas tab
on the ribbon and you would see list of Names, click on desired one to paste in
the cell.

The result looks like this

The other way of using Named range or constant is to start typing their name in
the Formula and you would see an intellisence like below. Select the formula
name and that particular name will be in the cell.

You can see that instead of writing '=(E4*15)/100', we have written

'=(E4*PPFRate)/100'. Here PPFRate is Named constant.

The benefit of using Named constant here is that if for some reason PPFRate is
changing, we can simply go to that Named constant and change it's value and all
the related cells value will be changed. If this were a direct value instead of
Named constant then we had to go to every cell to update the calculations.

Keyboard shortcuts in Excel

How to use keyboard shortcuts in MS Excel?

In the previous post, we learnt about Named range and Named constant. In this
post we shall learn about Keyboard shortcuts in Excel.
Keyboard shortcuts are a way to perform frequently used activities quicker with
the combination of keys rather than following few steps through mouse. This
helps in improving the productivity of the user.

How to select entire range of data in Excel sheet?

Place your mouse in any cell of data (In this case, my cursor is in A3), now press
'CTRL+A' this will select the entire range of data as shown below.

To select the entire spreadsheet, press 'CTRL+A' twice.

How to select entire row of the ranage of data in Excel?
To know basic selection of cells, rows and columns read here.
Let's assume that we have to select the header row of below data.

Select the A3 cell by clicking on it and then press 'CTRL+SHIFT+→'

(CTRL+SHIFT+Right Arrow Key). The result would be
To select downwords rows, hold the SHIFT key and press ↓ key.
To select entire column of range of data (eg. A column data in this range) press
'CTRL+SHIFT+↓' (CTRL+SHIFT+Down Arrow Key).

To select right columns, hold SHIFT key and press → key.

How to quickly move to the last column and last row of

range of data in Excel?
Many a times when you may have loads of rows and columns in your
spreadsheet and you want to quickly jump to the last row or last column of your
data, scrolling column by column or row by row is a tough task.
To jump to the last column within range press 'CTRL+→' (CTRL+Right Arrow
Key)
To jump to the last row within range press 'CTRL+↓' (CTRL+Down Arrow Key)
Similarly to go to the last cell from the range of data press 'CTRL+END', to go to
the starting cell from the range of data press 'CTRL+HOME'.

How to quickly insert SUM function in MS Excel?

Take example of below data. Go to G4 cell and press 'CTRL+=' key, hit ENTER
key. This will automatically insert SUM function with range of numeric data in
that row.

The same can be achieved by press Auto sum icon from editing group on the
Ribbon as shown below.

To copy the same formula to other corresponding rows of the records in the
range, we can do any of following
1. Copy the first 'Total' cell that has the formula and hold the bottom-right
corner of the cell from the mouse and drag to other cells.
 All the dragged over cell will be filled with the formula and sum will be
displayed like below.

2. Copy (CTRL+C) the first cell where you have written the formula, now
press 'SHIFT+↓' (Down Arrow Key) to select other cells and then press
Enter key.

3. The other way to do this is select the first cell and press 'CTRL+C' and
then use 'SHIFT+↓' (Down Arrow Key) to select other cells and then press
'CTRL+D'.

How to bring currency symbol in MS Excel?

To bring currency symbol in MS Excel, select cells either through keyboard or
mouse.
Now open the Format Cells dialog box by pressing 'CTRL+1' (1 should not be
pressed from Numeric keypad). Go to 'Currency' category and then in the right
side from Symbol dropdown select your currency symbol.
Now press OK and here is the result.

Thanks for reading. Do share this post to your friends and colleagues.

Function Key Shortcuts

Function keys has a great benefit in Excel. Let's learn about it.

F1 Key
When you have not opened any dialog box and pressed F1 key, you would see a
default Excel help window. This allows your search for any help or read the
popular articles related with MS Excel.
However, if you happen to open a dialog box and need any help on that dialog
box active tab then pressing F1 would open Help window box focussed to that
active tab items only (Excel is intelligent, isn't it? ).
For example, I had opened Format Cells dialog box and Number tab was active,
when I pressed F1 here is what I see.

F2 key
F2 key brings the cell into Edit mode and moves the cursor to the end of the
content. If the cell contains formula, it shows the formula and selects cells on
which the formula is dependent on.
F3 key
F3 key brings the Paste Name dialog box provided you have already defined
Named range or Named constant.
F4 key
In Edit mode on the cell, it toggle through Absolute, Mixed and Related
references of the cell. For example, if we have to refer D8
1. Relative reference - D8
2. Absolute reference - $D$8
3. Mixed reference $D8 or D$8
We will learn about Cell references in forth coming posts.

F5 key
Opens the 'Go To' dialog box that let us quickly jump to a particular cell or
particular type of cell that contains specific type of data.

Clicking on 'Special...' button shows 'Go To Special' dialog box like below.

Let's think of a requirement where we want to select all the cells having formula
in them. You do not need to roam around all the cell and find them.
Just select 'Formulas' radio button from 'Go To Special' dialog box and hit OK.

You would see all the cells having formulas will be selected

F6 key
Pressing F6 navigates through the next split window in the spreadsheet.
F7 key
Pressing F7 key opens the Spelling dialog box and gives suggestions for the
content written in the selected cell.
F8 key
F8 key toggles the Extended mode in the excel. Extended mode is used to select
cells without dragging or pressing SHIFT key.
Press F8 and press Right Arrow key (→, any other arrow key) and you will notice
that right side cells will get selected. Pressing F8 key again and pressing Right
Arrow key (→, any other arrow key) again will not select cells but will select only
one right side cell.

F9 key
Re-evaluates all formulas of the workbook to ensure that all the cells calculated
value are correct. You must have noticed that MS excel automatically evaluates
the formula whenever any dependent cells value changes. However, we can
change this to 'Manual' mode by going to 'Calculation Options' command from
'Calculation' group under FORMULAS tab of the Ribbon.

If the Calculation Options is selected as 'Manual', changing the dependent cells

value doesn’t change the Formula cell (Total) value automatically.
To update the Total cells value, press F9 key.

Quickies
 To bring back the automatic calculation mode, select 'Automatic' from
'Calculation Options' command on the Ribbon.

F10 key
Works as if ALT key is pressed on the keyboard. This shows the shortcut keys to
select a particular tabs on the Ribbon.
As shown above, pressing 'M' key on the keyboard shows the 'FORMULAS' tab
with the shortcuts of each command under FORMULAS tab.

F11 Key
Pressing F11 creates a new Chart sheet based on selected data range. In my
case, I have selected LastName and Age columns.

When I pressed F11 key, I get a brand new sheet with chart.
F12 key
It simply brings 'Save As' dialog box that allows us to duplicate the file and save
with a different name.
Thanks for reading. If you liked it, do share with your friends and colleagues.

More Excel shortcuts

1. CTRL+; - insert today's date
2. CTRL+SHIFT+; - insert current time
: 11

Split spreadsheet window in Excel

How to split spreadsheet window in MS Excel?

Learn about Ribon in Excel here. In this post, we shall learn how to split/divide
the spreadsheet window. This helps us in viewing multiple distant contents of the
spreadsheet.
To split the spreadsheet, first select a cell from where you want to split. In this
case, we have selected D10.
Now go to 'VIEW' tab on the Ribbon and click on 'Split' command under 'Window'
group (as displayed below).

The result screen would be like this. Notice the cross mark (vertical and
horizontal bar) on the spreadsheet, now all 4 window will scroll separately.
How to vertically resize the split window in Excel?
To vertically resize the split window, place your mouse on the vertical bar. Your
mouse cursor would change like below. Now drag by holding the mouse key.
How to horizontally resize the split window in Excel?
Place your mouse cursor on the horizontal bar and your mouse cursor would
change like below. Hold the mouse key and drag and the split window will be
vertically resized.
Quickies
1. To quickly resize split window both vertically and horizontally,
move your mouse cursor at the intersection or horizontal and vertical bar
and your mouse cursor would become like below. Hold the mouse key and
drag.
The resultant screen would look like this.
 2. To un-split the window, simply click on the Split command under VIEW
tab on the Ribbon again.

Count, CountIf, CountIfs in Excel

How to use Count, CountIf and CountIfs functions in MS Excel?

In the previous post, we learnt about Keyboard shortcuts in MS Excel. In this

post, we shall learn how to count cells using Count, CountIf and CountIfs
function.

Count() function
Count() function is used to count the number cells between two ranges that
contains numberical data; it doesn't count the cell containing string data.
Let's take below as an example. In 'I4', we have written '=COUNT(A4:G4)', it
count 7 columns data.

The result is only 5 as shown below because only 5 columns (Sl. No., Age,
Salary, PPF, Total) columns having numerical data.

Similarly, we are trying to count the rows that contains numerical data.
As you can see that both A19 and B19 cell is using same formula to count row 3
to 17 but the result would be below.
As A column contains numeric data so it is giving number of rows, but B column
has all string data so the result is 0.

CountIf() function
CountIf function is used to count cells based on one criteria only (less than,
greater than, equal to etc.). The criteria can be either for numerical value
or string value.
Look at the below example. In D21 cell, the formula is written as
'=COUNTIF(D4:D17, "<18")', it means that Count only those cells having value
less than 18 and in this case only one cell D13 valid so the result we are getting
is 1.

We can even use wildcard characters in CountIf function.

This is counting cells whose string value starts with "S".

CountIfs() function
CountIfs function is used to count cells based on multiple criteria (separated by
comma). In below example, D21 cell contains '=COUNTIFS(B4:B17, "S*",
D4:D17, "<26")' formula that says
 count those cells from B4:B17 (cell range) whose string starts with "S*"
(this will give 3 records) and also
 D4:D17 cells whose value is '<26' (this will remove two records 'Sheo' &
'Sohan').
So if both criteria is met then only that particular record will be counted.
So the result is 1.

How to count cells with specific text in Excel?

To count cells with specific text in Excel, we can use CountIf() function. Select a
blank cell and write formula like this '=COUNTIF(B4:B17, "Sheo")'. In this case I
want to find 'Sheo' text.
and here is my result in B21.
As written earlier, we can use wild card characters also to count the
occurences, like
1. Count cells starting with 'Sh' - '=COUNTIF(B4:B17, "Sh*")'
2. Count cells ending with 'eo' - '=COUNTIF(B4:B17, "*eo")'
3. Count cells contains 'h' - '=COUNTIF(B4:B17, "*h*")'

How to count non blank cells in Excel?

To count Non Blank cell in excel, use the same COUNTIF function but the 2nd
parameter would be '<>'. So the formula to count non blank cells is
'=COUNTIF(G4:G19, "<>")' where the range of cells it is counting is G4 to G19.
See here non blank cell is 9.

How to count blank cells in Excel?

To count blank cells in Excel, use the COUNTIF function with 2nd parameter as
"". So here is the formula '=COUNTIF(G4:G19, "")'.
The number of blank cells between G4 to G19 is 7.

Count blank and non blank cells in Excel

How to count blank and non blank cells in MS Excel?

In the previous article, we learnt about Count, CountIf, and CountIfs function in
MS Excel. In this article, we shall learn COUNTBLANK and COUNTA function that
is used to count blank and non-blank cells within given range.

How to count blank cells in MS Excel?

We have already learnt other way of counting blank cells in MS Excel in the
previous article using CountIf() function.
MS Excel also provides a dedicated function called COUNTBLANK to count the
blank cells in the given range.
In G22 cell below, we have written '=COUNTBLANK(G4:G19)' that will count
blank cells from G4 to G19 cells.
The result is 6.

How to count non blank cells in MS Excel?

We have already learnt other way of counting non blank cells in MS Excel in
the previous article using CountIf() function.
MS Excel also provides a dedicated function called COUNTA to count non blank
cells withing a given range.
In H22 cell, I have written '=COUNTA(G4:G19)' that counts all non blank cell
from G4 to G19 cell and gives the result.
The result is 10.

Quickies
Do remember that if the cell is having a blank space (space bar key), Excel
counts that as non blank cell.

Count Logical and Bit values in Excel

How to count logical and mixed logical values in Excel?

In the previous post, we learnt about counting blank and non blank cells in
Excel. In this post, we shall learn how to count logical and mixed logical values
in MS Excel.

How to count logical values in Excel?

To count logical values (TRUE/FALSE) in MS Excel, we can use COUNTIF function
as explained in previous post. Instead of giving 2nd parameter as "FALSE" (in
double quote, we can also give just FALSE as Excel understand these logical
values).
So the formula would be '=COUNTIF(H5:H18, FALSE) OR =COUNTIF(H5:H18,
"FALSE")'

The result is 6.

How to count mixed logical (also bit) values in Excel?

Let's take example of above data, few records 'Active' field data is TRUE and few
are 1 ie. bit value (both are treated as true/yes). If this is the scenario, how to
get the correct count of all True/Yes records?
The solution of this problem is to have two COUNTIF functions and add both
function's return value.
So the formula would be '=COUNTIF(H4:H17,"TRUE")+COUNTIF(H4:H17,1)'.
The 1st part is getting all records count having 'TRUE' and 2nd part is getting all
records count having 1. The count of both functions are being added and the
result is being displayed in H20 cell.
Sum, SumIf, SumIfs functions in Excel
How to use Sum, SumIf, SumIfs function in Excel?

In the previous post, we learnt about counting logical and bit values in Excel. In
this post, we shall learn about sum range of cells value in Excel.

Sum function
To sum/add a range of cells in MS Excel, we can use SUM function. SUM function
can either by used by directly writing in the cell or by clicking on Sum command
from the 'Editing' group under 'HOME' tab on the Ribbon.
So the formula to sum E2 to E5 columns values are '=SUM(E2:E5)'.
SUM BY CLICKING ON THE RIBBON
SUM BY DIRECTLY WRITING IN THE CELL

Here we are adding the values of E2 to E5 cells and the result is shown below.

SumIf function
Like CountIf function, SUMIF function is also used to sum the range of cells
based on one criteria. In below example, we are adding value of E column only
when the cell value is more than 350.
The criteria is written in double quote as 2nd parameter. Instead of passing 2nd
parameter as greater than, less than or equal to, we can also put criteria based
on other cell range.
For example, we want to add the E column value only when its corresponding B
column value is "Ram". To do this, we have written formula as '=SUMIF(B2:B5,
"Ram", E2:E5)' ie. Add the value from E2 to E5 only when B2 to B5 value
contains "Ram".

and the result is below.

SumIfs function
If our requirement is to use multiple criteria while summing the range of cells,
we can use SumIfs function.
So in below example, we want to sum the value of E column only if B column
value is "Ram" and C column value is "Sita". So our formula will be
'=SUMIFS(E2:E5, B2:B5, "Ram", C2:C5, "Sita")'.
Here
1. E2 to E5 is sum range
2. B2:B5 is criteria 1 range
3. "Ram" is the criteria value of B2:B5
4. C2:C5 is criteria 2 range
5. "Sita" is the critera value of C2:C5

As this criteria is met by only 1 record, so the result is below.

Similarly, we can specify more than 2 criteria also to sum range of cells.
Thanks for reading, if this helps do share to your friends and colleagues.

Sumproduct, SumsQ functions in Excel

How to sum the product of two ranges and square of given range in Excel?

In previous post, we learnt about Sum, SumIf and SumIfs function in Excel. In
this post, we shall learn about SUMPRODUCT, SUMSQ function in MS Excel.
SumProudct function
SUMPRODUCT function in Excel is used to sum the products (multiplication) of
corresponding ranges.
In below example, we want to get the total amount spent, so in D6 cell we have
written '=SUMPRODUCT(C3:C5,D3:D5)' so it does following
1. multiply C3 to D3 and add that value to
2. multiple of C4 to D4 and add that to
3. multiple of C5 to D5
So it basically multiplies the corresponding value of C columns to D columns and
gives the result.

and here is the result.

The benefit we got is that we do not need to have a separate column (We have
kept E column just for clarity purpose) to multiple Rate and Qty and then add it.
Thanks for reading, if you liked it do share to your friends and colleagues.

SumsQ function
SUMSQ function is used to sum the square of the given range. In below
example, we have written '=SUMSQ(D3:D5)' formula in D6 cell that squares the
value of D3 to D5 cells and add.
Here is the result.
The E column is just for reference purpose.
Like to SUMSQ we have
1. SUMX2MY2 - used to sums the difference between the squares of two
corresponding given ranges
2. SUMX2PY2 - used to return the sum total of the sums of the squares of
numbers in two corresponding given ranges
3. SUMXMY2 - used to return the sum of squares of the differences in two
corresponding given ranges
As there are not very frequently used so no explanations are provided in this
post.

If, And, Or functions in Excel

How to work with If, And and Or functions in MS Excel?

In the previous post, we learnt about Sumproduct, SumsQ function in Excel. In

this post, we shall learn about Logical functions such as If, And and Or in Excel.

If Function
IF is a conditional function that returns one value based on whether the
condition is valid or not. In below example, F column should have a value (Adult
or Minor) based on E column data (Age), so the formula we have written in F
column is '=IF(E3>18, "Adult", "Minor")'.
i.e.
For row 3, it checks if E3 column value is more than 18 then writes 'Adult' else
'Minor'. The same formula has been copied for other rows. The result is below.
Nested IF Function
IF function can be nested within another IF. Look at below example.
In I3 cell, the 1st parameter is the IF function is again an IF function that returns
TRUE/FALSE based on E3 value and then writes 'Major' or 'Minor' in the cell.
When we copy the same formula to other rows, the result would be like this.

AND Function
AND function returns
1. TRUE if all conditions are valid
2. FALSE even if one condition is invalid
Look at the example below. Here we have written '=AND(E3>18,F3>18)' in F3
cell that checks the value of E3 and F3. If both are greater than 18 then writes
TRUE else FALSE.
The return value of 'AND' function can be used inside another function as well.
Look at the example below.
In G3 cell we have written '=IF(AND(E3>18,F3>18), "Correct", "Incorrect")', it

 first checks E3>18 and F3>18, if both are valid then returns TRUE
otherwise FALSE
 IF function then checks the return value of AND function and if it is TRUE,
writes 'Major' otherwise 'Minor'.
The same formula has been copied into G column cells as well and here is the
result.

OR Function
OR function returns

 TRUE if any of the condition is valid

 FALSE if all conditions are invalid
In below example, G3 cell contains '=OR(E3>18,F3>18)' formula. This checks
the value of E3 and F3 and if both are greater than 18, it returns TRUE otherwise
FALSE.
The result is below.
The return value of OR can also be used within another function. Look at below
example.
Cell H3 contains '=IF(OR(E3>18,F3>18), "Correct", "Incorrect")' formula. Here
the return value of OR function is used in IF condition and then 'Correct' or
'Incorrect' value is returned.
The result is below.

Look at H6 cell, here E6 returns TRUE but F6 returns FALSE. Even if 1 condition
is FALSE, OR function returns TRUE and the result is 'Correct'.

Date and Time functions in Excel

How to work with date and time in MS Excel?

In previous post, we learnt about If, And, Or functions in Excel. In this post, we
shall learn about Date and Time functions in MS Excel.

How to write date and time in MS Excel?

Date in Excel can be written separated by either '/' (forward slash) or '-' (dash).
Time in Excel can be written separated by ':' (Colon).
Date and Time can be written in a single cell separated by a single space.
The format of the date & time depends on the culture/zone you have selected
while installing the operating system.

How to get current date and time in Excel?

To get current date and time in MS Excel, we can use 'NOW()' function. Look at
the example below. In B3 cell, we have written '=NOW()' that writes current
date and time in Excel.

How to get Day, Month and Year from a Date in Excel?

Day, Month and Year can be extracted from a valid date.
DAY function
DAY function is used to return the day of month (1 to 31) from a valid date. D3
cell contains '=DAY(B3)' that returns '9' as day from B3 cell value.

MONTH function
MONTH function returns the month number from 1 (January) to 12 (December)
from a validate date. E3 contains '=MONTH(B3)' formula that returns '8' as
month from B3 cell value.
YEAR Function
YEAR function is used to return year (1900 to 9999) from a valid date. F3 cell
contains '=YEAR(B3)' formula that returns '2016' as year from B3 cell value.

How to get Hour, Minute and Second from a Time in

Excel?
HOUR function
HOUR function is used to return hour 0 (12:00 AM) to 23 (11:00 PM) from a
valid time or date time. G3 cell contains '=HOUR(B3)' formula that returns '14'
as hour from B3 cell value.

MINUTE function
MINUTE function is used to return minute 0 to 59 from a valid time or date time.
H3 cell contains '=MINUTE(B3)' formula that returns '16' as minute from B3 cell
value.

SECOND function
SECOND function is used to return second 0 to 59 from a valid time or date
time. I3 cell contains '=SECOND(B3)' formula that returns '25' as second from
B3 cell value.
Even if B3 cell doesn't show second segment, Second is returned from the
current time while writing the formula as B3 cell contains '=NOW()'.

Date and Time functions

DATE function
To add simply days into a date, write the formula as if you are trying to add a
number into another number. Eg. C4 cell contains '=B4+7' that i.e. adding 7
days into '9/24/77' and giving the result.

To create a new date based on existing date segments, we can use DATE
function. In D4 cell, we have written '=DATE(YEAR(B4)+5, MONTH(B4)+3,
DAY(B4)+2)' formula that
1. adds 5 into B4 year
2. adds 3 into B4 month
3. adds 2 into B4 day
and gives a fresh date '12/26/82' based on '9/24/77'.

like DATE, we have TIME function as well. TIME function can be used to get time
based on other time values.
TIME function
Time function can be used to create a valid time based on integer numbers
within time range. For example, G4 cell contains '=TIME(G3+2, H3+3, I3+3)'
that
1. adds 2 into G3 value
2. adds 3 into H3 value
3. adds 3 into I3 value
and gives a fresh time '5:27 PM'.

How to write different formats of date in Excel?

To write different date formats in Excel, we can use TEXT function. TEXT function
accepts two parameters
 1st - the value
 2nd - the format

When the value is a valid date and format is valid format, TEXT function is
intelligent enough to derive corresponding names of those date segments.

Other DATE functions

1. TODAY() - returns today's date

2. EOMONTH() - returns the serial number of last day of the month, if 2 nd

parameter is not 0, it adds current month with that value and returns the
last day of that month.
 You can see that C1 has serial number of the last day of the current
month. To convert this serial number to date, I have used '=TEXT(C1,
"dd-mmm-yyyy")' formula, and here is the result.

To get next month last day, use '=EOMONTH(B1, 1)' that will return last
day of September ie. 30-Sep-2016.

3. WEEKDAY() - returns the day of the week

4. DATEDIFF() - used to calculate the difference between two dates in

days, months and years. In F2 cell, '=DATEDIF($B$2, $A$2, "d")' formula
is written that gets the difference between B2 and A2 cell (Notice that we
have used Absolute reference while referencing the cell).

Parameters are following

1st - lower date
2nd - higher date
3rd - "d" for day difference, "m" for month difference, "y" for year
difference

Join, Left, Mid, Right, Find, Substitute

functions in Excel
How to use Join, Find, Left, Right, Mid, Substitute functions in Excel?
In the previous post, we learnt about Date & Time functions in Excel. In this
post, we shall learn about Text manipulation in Excel.

Join strings in Excel

To join two strings in Excel, we an use '&' operator.
In below example, D3 has formula '=B3 & " " & C3' that concatenates B3 and C3
cell values.

To insert more than 1 space use multiple blank space between double quotes
like " ".

LEFT function
LEFT function in Excel is used to return the specified number of characters from
start of a string. See E3 cell, we have written '=LEFT(D3, 5)' that returns 'Sheo '
(with space).

The same formula has been copied to other E cells to bring above result.

RIGHT function
RIGHT function in Excel is used to return specified number of characters from
the end of string. E3 cell in below example has '=RIGHT(D3, 5)' formula that
returns 5 characters from the end of string of D3 cell.
The same formula has been copied to other E cells.

MID function
MID function in Excel is used to return the characters from the middle of string
by giving starting position and length of characters to return.
E3 cell in below example has '=MID(D3, 3, 5)' that returns string starting from
3rd position to next 5 characters.

Copy, the same formula in E cells to get above result.

FIND function
FIND function in Excel is used to return the starting position of the 1 st occurence
of a substring within another string. FIND is a case-sensitive function ie. "an" is
difference than "AN".
E3 cell in below example contains '=FIND("an", D3)' formula that finds "an" at
the 11th position, so the result is 11.
If FIND function can't find the specified substring, it throws "#VALUE!" error as
shown above.

SUBSTITUTE function
SUBSTITUTE function in Excel is used to replace a part of text with new text in a
string. E3 cell in below example contains '=SUBSTITUTE(D3, " ", "|")' formula
that replaces " " (blank space) with "|" (pipe sign).
SUBSTITUTE function is also case-sensitive.

The same formula has been copied to other E cells.

Count words in Excel

How to count number of words in a cell of Excel worksheet?

In the previous post, we learnt about LEFT, MID, RIGHT, FIND and SUBSTITUTE
function of Excel. In this post we shall learn how to count the number of words
in a cell of Excel worksheet.
Let's solve this problem three simple step.
Step 1: Count the total length of string
To count the total length of string we shall use the LEN function of Excel. In D3
cell we have written '=LEN(TRIM(C3))' function that first trims any space before
and after the cell data and then gives the length of the string.

Step 2: Count the length of string without space

To count the length of the string without space, we have entered
'=LEN(SUBSTITUTE(C3, " ", ""))' formula in E3 cell that first replaces the " "
(blank space) with "" (nothing) and then counts the length of string.

Step 3: Deduct the length without space from total

length of string
Now deduct the length of string without space from the total length of string and
add 1. Here is the correct word count in F3 cell (formula '=D3-E3 + 1').
Copy-paste the same formula to other cells and below is the result.
View

Upper/Lower/Proper case in Excel

How to change string to upper/lower/proper case in Excel?

In the previous post we learnt about how to count number of words in Excel. In
this post, we shall learn how to change the case of a string (upper, lower or
proper case) in Excel.

UPPER function
To change string to upper case, we can use UPPER function. In B3 cell, we have
'=UPPER(A3)' formula that changes the A3 cell string to upper case.
Here is the result.

LOWER function
To change string to lower case, we can use LOWER function. In C3 cell, we have
'=LOWER(A3)' formula that changes the A3 cell string to lower case.
Here is the result.
PROPER function
To change string Proper case (1st character of each word will be in capital letter),
we can use PROPER function. In D3 cell, we have written '=TRIM(PROPER(A3))'
that first removes empty space from start and end of the string and then
changes the string into Proper Case.
Here is the result.

Views: 17

Compare strings in Excel

How to compare two strings in Excel using EXACT (Case sensitive) and equal (Case insensitive)
operator?

In the previous post, we learnt about how to change string to upper, lower and
proper case in Excel. In this post, we shall learn how to compare two strings
(case sensitive and case insensitive) in Excel.

Case Sensitive comparison (EXACT function)

To compare two string (case sensitive comparison), we can use EXACT function.
In C3 cell, we have written '=EXACT(A3, B3)' formula that compares A3 and B3
cells and returns FALSE (Notice there is blank space before A3 cell string).
EXACT function returns
 1. TRUE - if both strings are exactly same
2. FALSE - if both strings are not exactly same (even blank space matters!)

When we copy the same formula to other C cells, we get above result.

Case Insensitive comparison (= operator)

To compare two strings just for their value, not with their case we can use '='
operator. In D3 cell below we have written '=A3=B3' formula that compares only
the string value of both A3 and B3 cells (The blank space matters!).
As A3 has blank space in the start of the string so the result is FALSE, rest all
are TRUE as the data are same (Upper case and lower case doesn't matter).

When we copy the same formula to other D cells, we get above result.
6 | Post O

Separate string in Excel

How to separate string from a specific characters in Excel?
In the previous post, we learnt about how to compare two strings (Case
sensitive and Case insensitive) in Excel. In this post, we shall learn how to
separate string from a specific character in Excel.
Let's take example of below data.
In B column we have Full Name of people separated by Comma (,). We need
First Name and Last Name separately into two different columns.
To get First Name, we have written '=LEFT(B3, (FIND(",",B3) - 1))' formula in
C3 cell that
 finds the position of "," from B3 cell (using FIND function) and deduct 1
from that position
 takes the resultant number of characters from starting of the string
using LEFT function.
The result is all characters from starting of the string till comma (,), this gives
the First Name of the person.

The same formula has been copied to other C column cells that gives above
result.
To get Last Name, we have written '=RIGHT(B3,LEN(B3)-FIND(",",B3)-
1)' formula in D3 cell that
 finds the position of "," and deduct 1 and then deduct the resultant
number from total length of the string
 takes the resultant number of characters from end of the string
using RIGHT function.
The result is all characters from ending of the string till comma (,), this gives the
Last Name of the person.
The same formula has been copied to other D column cells that gives above
result.

Text to columns in Excel

How to convert csv data into columns in MS Excel?

In previous post, we learnt about separating string from specific characters in

Excel. In this post, we shall learn about converting CSV (comma separated
values) data into columns in Excel.
This post is equally helpful in case data is not comma separated but semicolon,
space, tab or any other characters separated.
In below example, we have a comma separated data from A1 to A5. Select all
the rows and select 'Text to Columns' command from DATA tab on the ribbon.

This opens up 'Convert Text to Columns Wizard' as shown below. In this wizard,
select the file type/data type we have to convert to. We have comma delimited /
separated data so let the 1st radio button selected.
Now select the delimited character from 'Delimiters' list. As our data is 'Comma'
separated so check the 'Comma' checkbox. We get preview of our final data
below under 'Preview' box. If it is looking good press 'Next >' else adjust the
data or Delimiters.
Now, choose the data type of each column. Select column heading and then
choose the data type from 'Column data format' heading to format that column
data.

Now press 'Finish' button to show the formatted data at the same place like
below.

Quickies
If we want formatted data to appear at some other location, click right side icon
of 'Destination' textbox and select the target cell. Now, clicking 'Finish' will bring
the formatted data to the new location.
Find vs Search function in Excel
What is the difference between Find and Search function in Excel and how to use them?

In previous post, we learnt about Text to Columns in Excel for CSV data. In this
post, we shall learn the difference between FIND and SEARCH function in Excel
and where to use what?

FIND function
Look at below example. In B2, we have '=FIND("JOHN", A2)' formula that
returns '#VALUE' as FIND couldn't find the exact value ('JOHN') in A2 cell.
(FIND is case-sensitive function).

No support for wildcard characters

Look at below example, we are trying to find 'J?hn' where I am looking for any
character in place of '?'. FIND gives '#VALUE' error.

SEARCH function
When we pass the same parameters in SEARCH function, we get '1'. So our
formula in C2 is'=SEARCH("JOHN", A2)'. As SEARCH function is case-
insensitive, as long as text to find is same (either upper or lower or mixed
case), it returns the position of the text found in A2 cell.
See the similar examples below for FIND

and SEARCH

Support for wildcard characters

SEARCH function supports wildchard characters, you can see that 'J?
hn' parameter still in SEARCH still returns '7'.

Quickies
Can we also use '*' wildcard character instead of '?' - Yes

VLookup, HLookup functions in Excel

How to work with VLOOKUP & HLOOKUP functions in Excel?

In previous post, we learnt the difference between Find and Search in Excel. In
this post, we shall learn about VLOOKUP and HLOOKUP functions in Excel.

VLOOKUP function
VLOOKUP (Vertical lookup) function is used to look for a value in the main table
and return corresponding value from the lookup table.
In this example, we want to write the 'Emp Type Name' in C column based on B
column value from the Lookup table from E2 to F6.
The formula in C2 is '=VLOOKUP(B2,$E$3:$F$6,2,FALSE)' that executes in
following way
 1st parameter - lookup value
 2nd parameter - lookup table range (notice that the range is in absolute
reference)
 3rd parameter - column index to use to write the value
 4th parameter - approximate match (TRUE) or exact match (FALSE)
Notice that EmpTypeId = 1 is 'Manager' in the Lookup table so the VLOOKUP
function returns 'Manager' in C2 cell.

When we copy the same formula to other C cells, we get above result.

HLOOKUP function
This is almost same as VLOOKUP except the fact that the Lookup table values
are horizontally arranged.
In C11 cell, we have '=HLOOKUP(B11,$F$10:$I$11, 2,FALSE)' formula that does
following
 1st parameter - lookup value
 2nd parameter - lookup table range (notice that the range is in absolute
reference)
 3rd parameter - row index to use to write the value
 4th parameter - approximate match (TRUE) or exact match (FALSE)
The EmpTypeId (B11) is 1 so the Lookup table EmpTypeId = 1 is 'Manager of
CEO' so we have this in the C11 cell.
When we copy the same formula to other C cells, we get above result.

Max & Min value in Excel

How to locate maximum and minimum value in Excel cell range?

In previous post, we learnt about VLOOKUP and HLOOKUP function in Excel. In

this post, we shall learn about MAX, MIN, MATCH and ADDRESS function of
Excel.

MAX function
MAX function in Excel is used to return the largest value in the set of values. It
ignores the textual or logical values.
In C2 cell, the formula is '=MAX(B2:B6)' that gets the maximum values between
B2 to B6 by ignoring any textual and logical values.

The result is below.

MIN function
MIN function in Excel is used to return the smallest value in the set of values. It
ignores the textual or logical values.
In D2 cell, the formula is '=MIN(B2:B6)' that gets the minimum values between
B2 to B6 by ignoring any textual and logical values and the result is below.

Getting the relative position of a value using MATCH

function
To get the relative position of the maximum value withing given range, we can
use MATCH function.
In below example, C4 cell contains '=MATCH(MAX(B2:B6), B2:B6,0 )' formula
that does following
1. 1st parameter gives the maximum value withing B2 to B6
2. 2nd parameter specifies the range within which to match the value
3. 3rd parameter specified whether to find exact value, or less than or greater
than value.
As the mximum value is 55 that is coming at the 3rd position in the range so we
get 3 in C4.
Getting the cell reference of maximum value using
ADDRESS function
To get the cell reference of the maximum value within the given range, we can
use ADDRESS function.
In C6 cell, we have '=ADDRESS(MATCH(MAX(B2:B6), B2:B6,0 ),2)' formula that
does following
1. The 1st parameter gets and Maximum value and then its position
2. 2nd parameter specifies column number
and the result we get is $B$3.

Calculate monthly installment of a loan in

Excel
How to calculate payment for a loan based on constant payment and interest rate in Excel?
In the last post, we learnt about getting maximum and minimum value from set
of values in Excel. In this post, we shall learn how to calculate payment for a
loan based on monthly payment and to a specific interest rate. This can be done
using PMT function of Excel.
To understand how it works, we shall calculate the monthly payment of a term
loan of 14,50,000 for a period of 20 years on 9.75% interest rate.

PMT function
Get monthly installments for loan
The PMT function helps in getting payment amount (installment) of a loan. It
accepts at least 3 parameters and they are
1. rate - interest rate, as the yearly interest rate is 9.75 so monthly will be
(0.75/12)% ie. 0.008125.

2. nper - total number of periods, as the payment term is monthly so total

pay period will be 20*12 ie. 240.

3. pv - present value, total amount borrowed ie. 14,50,000

4. [fv] (Optional) - the future value of the loan, we want to completely pay
off so it will be 0

In A2 cell write '=PMT(B2, C2, D2, E2)' formula where

 B2 is rate of interest per month (not year)

 C2 is period in months to replay
 D2 is the present value ie. amount borrowed
 E4 (optional) is the future value ie. after 240 months, how much value is
left after paying
and this gives following result (notice the 2nd row)
The same calculation if we do for Nper 180 (ie. 15 years) we get different result
(notice 3rd row). Just for the sake of knowing how much you end up paying
more, we have kept the F and G column.

RATE function
Get interest rate of the loan
Similar to above, if we want to know what would be the interest rate, if we want
to borrow certain amount, for certain period of time and we want to pay a
certain amount every month then we can use RATE function.
In B4 cell, we have '=RATE(C2,A2, D2, E2)' formula where
 C2 is Nper (number of months we want to pay)
 A2 is monthly payment we want to make
 D2 is the amount of money we want to borrow
 E2 is the value left after paying

The result would look like this. In general result come in full number, simply
use cell formatting to display decimal numbers.

NPER function
Get period (in months) of a loan
Let's say we want to know the period (in months) we will have to pay if we
borrow certain amount at certain interest rate and we want to pay certain
amount every month, then we can use NPER function.
In C4 cell, we have '=NPER(B2,A2,D2,E2)' formula where
 B2 is rate of interest
  A2 is monthly installment we want to pay
 D2 is amount we want to borrow
 E2 is the amount left after paying installments
Here is the result.

PV function
Get how much we can borrow
Let's assume that you want to know that if you pay certain amount every
month, till certain period of months with certain interest rate then how much
amount you can borrow? We will need to use PV function.
In D4 cell we have '=PV(B2,C2,A2,E2)' formula where
 B2 is the rate of interest
 C2 is the number of months we want to pay
 A2 is the amount we want to pay every month
 E2 is the left over amount after paying
Here is the result.

FV function
Get if you can pay off loan after paying only certain amount every
month
Let's assume a scenario where you want to know that what if you pay only
certain amount every month, with a certain interest rate for a certain amount of
loan, whether you will be able to pay off the entire loan? To know this we can
use FV function
In E4 cell, we have '=FV(B2,C2, A2,D2)' formula where
  B2 is the loan rate of interest
 C2 is the period in months we want to repay
 A2 is the amount we are willing to pay every month
 D2 is the loan amount we want to borrow
and here is the result.

Notice that slight change (only 753.49 less amount) in the Payment amount
(installment) results in huge left over amount (5,53,971.31).

Here you must be knowing the magic of cummulative interest. The same applies
when you save money every month !

What if I had invested calculation in Excel

How to do what if I had invested calculations in MS Excel?

In previous post, we learnt about Calculate monthly installment of a loan in

Excel. In this post, we shall learn 'What if I had invested scenario' in Excel. We
will learn what If I would have invested a certain amount for certain period
assuming to get certain interest rate then how much amount we would end up
getting.
Before we go ahead with this calculations, we need to understand the behaviour
of Excel.

Important: How excel treat an amount

Excel treat all payment as negative amount and all receipt as positive amount.
So when we are paying monthly installment, its negative amount in the formula.
What if I had invested
To get the maturity amount again, we shall use FV function of Excel. Look at
below scenario, where we are assuming that I would
 invest 1000 a month (this should be negative amount as we are paying)
 for 10 years
 and assuming I will get 8.75% interest per year
In B9, we have entered formula '=PV(B6/12,B5*12,B4)' where

 1st parameter is B6/12 that is interest rate (B6/12 as 8.75% is the yearly
rate of interest)
 2nd parameter is B5*12 that is term in year (B5*12 as term is in year and
installment is monthly)
 3rd parameter is B4 is monthly payment
and the result is 190,802.98.
Total amount invested is '12*10*1000=120,000' and you end up gaining
'79,791.42'.

Annuity investment
Let's say that we want to buy an annuity plan that will pay us 1000 every month
for next 10 years at the rate of 8.75%, how much we need to invest?
To calculate the annuity amount, enter '=PV(B6/12,B5*12,B4)' formula in B12,
here
 1st parameter is B6/12 that is interest rate per month
 2nd parameter is B5*12 that receipt per month
 3rd parameter is B4 that is payment per month
and the result is 79,791.42.
Thanks for reading, if this helps do share to your friends and colleagues.

Combine multiple workbooks to one workbook with Move or

Copy command

If you are a rookie of Microsoft Excel, you have no choice but only have
to copy the data of every sheet and paste them in to a new workbook
one by one and applying the Move or Copy command. Using the Move
or Copy command will help you export or copy one or several
worksheets to a new workbook quickly.
1. Open all workbooks that you want to merge into a single workbook.
2. Select all of the worksheet names of a workbook in tab bar. You can
select multiple with holding down Ctrl key or Shift key. Right click
the worksheet name, and click the Move or Copy from context menu.
3. In Move or Copy dialog box, select the master workbook that you
want to merge other workbooks into from the drop down list of Move
selected sheets to book. And then specify the location of the merged
worksheets. See screenshots:

4. Then click OK. The selected worksheets have been moved to the
master workbook.
5. Repeat the steps from 2 to 4 to move other workbooks to the master
workbook. Then it combines all worksheets of opened workbooks into
a single workbook. See screenshots: