Microsoft Excel Tutorial 2013 2016 2019

What is Microsoft Excel or Excel?

Microsoft Excel is an electronic spreadsheet. You can use Excel to organize your data into rows and columns and to perform mathematical calculations. An Excel spreadsheet can contain workbooks and worksheets

What is a Spreadsheet ?

A spreadsheet is an electronic document that stores various types of data. There are vertical columns and horizontal rows. A cell is where the column and row intersect. A cell can contain data and can be used in the calculation of data within the spreadsheet.

How to Open MS-Excel 2013 , Excel 2016 or Excel 2019 or Excel

Press Win key From Keyboard and Type Excel and hit the Enter Key

Or

Press Win+R to open Run command Dialog Box then Type “Excel” and press Enter key.

MS-Excel 2013 / 2016 / 2019 Interface

Ms-Excel 2013 / 2016 / 2019 Screen Components

Showing and hiding the Ribbon

If you find that the Ribbon takes up too much screen space, you can hide it. To do this, click the Ribbon Display Options arrow in the
upper-right corner of the Ribbon, then select the desiredoption from the
drop-down menu.

Auto-hide Ribbon: Auto-hide displays your workbook in full-screen mode and completely hides the Ribbon from view. To show the Ribbon, click the Expand Ribbon command at the top of the screen.

Show Tabs: This option hides all command groups when they’re not in use, but tabs will remain visible. To show the Ribbon, simply click a tab.

Show Tabs and Commands: This option maximizes the Ribbon. All of the tabs and commands will be visible. This option is selected by default when you open Excel for the first time.

The Quick Access Toolbar

The Quick Access Toolbar lets you access common commands no matter which tab is selected. By default, it includes the Save, Undo, and Redo commands.

The Tell Me bar

The Tell me bar allows you to search for commands, which is especially helpful if you don’t remember where to find a specific command.

The Ribbon

The Ribbon contains all of the commands you will need to perform common tasks in Excel. It has multiple tabs, each with several groups of commands.

The Command Groups

Each group contains a series of different commands. Simply click any command to apply it. Some groups also have an arrow in the bottom-right corner, which you can click to see even more commands.

The Worksheet views bar

Normal view is the default view for all worksheets in Excel.

Page Layout view displays how your worksheets will appear when printed. You can also add headers and footers in this view.

Page Break view allows you to change the location of page breaks, which is especially helpful when printing a lot of data from Excel.

The Zoom bar

Click and drag the slider to use the zoom control. The number to the right of the slider bar reflects the zoom percentage.

The Status Bar

Status Bar locate at the bottom of Workbook window. Sum, Average, count are display auto after selecting cells . You can also find the CAPS Lock status and Number Lock status here.

Backstage view

Backstage view gives you various options for saving, opening a file, printing, and sharing your Workbook. To access Backstage view, click the File tab on the Ribbon.

Backstage view after click on File tab

Click on Back button to back to normal view

Backstage view Options

1	Return to Excel	You can use the arrow to close Backstage view and return to Excel.
2	Info	The Info pane will appear whenever you access Backstage view. It contains information about the current workbook.
3	New	From here, you can create a new blank workbook or choose from a large selection of templates.
4	Open	From here, you can open recent workbooks, as well as workbooks saved to your OneDrive or on your computer.
5	Save and Save As	Use Save and Save As to save your workbook to your computer or to your OneDrive.
6	Print	From the Print pane, you can change the print settings and print your workbook. You can also see a preview of your workbook.
7	Share	From here, you can invite people to view and collaborate on your workbook. You can also share your workbook by emailing it as an attachment.
8	Export	You can choose to export your workbook in another format, such as PDF/XPS or Excel 1997-2003.
9	Close	Click here to close the current workbook.
10	Account	From the Account pane, you can access your Microsoft account information, modify your theme and background, and sign out of your account.
11	Options	Here, you can change various Excel options, settings, and language preferences.

Worksheet Overall

Some Fact about Excel

Work Book:-

MS Excel File known as Workbook and extension is (.xlsx) . A workbook is collection of worksheets.

Worksheet :-

A Worksheet consists Columns and Rows and the intersection of Column and Row is know as cell. So a worksheet is group of cells.

Total Number of Column in a worksheet :- 16,384 & Last header is (XFD)

Total Number of Row in a Worksheet :- 10,48,576

Total Number of Cell in a worksheet :-16384 x 1048576 = 17,17,98,69,184 cells

Total Number of sheet in a workbook :- Unlimited

Default Number of worksheets when a workbook load :- 1 worksheet

Starting Work in Excel

Excel files are called workbooks. Whenever you start a new project in Excel, you’ll need to create a new workbook. There are several ways to start working with a workbook in Excel. You can choose to create a new workbook
either with a blank workbook 

or a predesigned template

or open an existing workbook.

To create a new blank workbook

Click the File tab. 
Backstage view will appear. Select New, then click Blank workbook. A new blank workbook will appear.

Entering Data in Worksheet

There are different ways to enter data in Excel

To enter data through active cell

Click in the cell where you want to the data
Begin typing

To enter data through the formula bar

Click the cell where you would like the data
Place the cursor in the Formula bar and begin typing

Project 1

To Save a workbook

Click the File tab. 
Backstage view will appear. Click on Save, Choose your location then type a name for your file and click on Save Button

To open an existing workbook

Click the File Tab -> Open than click on your file name form content window of Open dialog box and click on Open button.

Export as PDF or XPS

Sometimes we need to Save our Workbook as PDF or XPS format to share or Save for later.
To Export Your Workbook as PDF or XPS format

Click the File Tab -> Export than click on Create PDF/XPS button.

After clicking on Create PDF /XPS button choose your location and type a name for your Exported file From Export Dialog box. And click on Publish button to finish this task.

Edit Data in Cell

Excel provides three-way to edit data.
1.Double click on the cell which data you want to edit . or
2. Click on cell than press F2 from Keyboard or
3. Click on the cell which data you want to edit then click on formula bar to edit data

After edit press Enter to accept the change,

Press Esc to Reject the changes.

Selecting Cell

Click Hold and drag over the cell which you want to select. After selecting one cell or more than one cell you can able to format that cell like format font face, color, size, alignment, number format etc. at once.

Selecting Column or Row

Auto Fill

The Auto Fill feature fills cell data or series of data in a worksheet into a selected range of cells.

1.Click the fill Handle

2.Drag the fill handle to complete the cells

Excel Formulas

A formula is a set of mathematical instruction that can be used in Excel to perform calculations. Formula in excel is known as functions.

To create a basic formula in Excel

1. Select the cell for the formula
2. Type = (the equal sign) and the formula
3. Press Enter when finished.

You can also type a formula with help of formula bar.

Elements in a Formula

References :- The cell or range of cells that you want to use in your calculation.

Operators : Symbols (+ – / * etc.) that specify the calculation to be performed

Constants : numbers or text value that do not change

Functions : predefined formulas in Excel

Manual Calculation

In Excel You can also performing manual calculation with the following operators and starting with = (equal) sing in any cell.

Operators in Excel

+    Addition

–     Subtraction

*    Multiplication

/    Division

^    Exponential

%   Percentage

Calculation in Excel

Auto Functions

A function is a built in formula in Excel. A function has a name and arguments (the mathematical function) in parentheses (bracket). Some common function in Excel:-

Sum:  Calculate Sum of selected cell value (Alt+=)

Average : Calculates the average of the cells in the argument

Min : Finds the minimum value in a range

Max : Finds the maximum value in a range

Count : Finds the number of cells that contain a numerical value within a range of the argument.

Working with comments

Comments are Pop-up notes that you can insert into your worksheet.

Right click on a cell where you want to add comment.

Choose Insert Comment option

Type your comment

When finished click on another cell.

Inserting Row, Column or Cell

To insert cells, rows or columns in Excel

Place the cursor in the row below where you want the new row or in the column to the left of where you want the new column

Click the Insert Option  on the Cells group of the Home tab

Click the appropriate choice : Cell, Row or Column

Deleting Row, Column or Cell

To Delete cells, rows or columns in Excel

Place the cursor in the cell, which cell , row or column you want to delete

Click the Delete Option  on the Cells group of the Home tab

Click the appropriate choice : Cell, Row or Column

Project 2

Mathematical Function

SUM()

This function is used to add the numeric value in a range of cells.

Example : =sum(A1:A5)

AVERAGE()

This function is used to calculates the average of the numbers in a range of cells.

Example : =average(A1:A5)

MAX()

Returns the largest value in the range.

Example: =max(A1:A5)

MIN()

Returns the lowest value in the range.

Example: =min(A1:A5)

COUNT()

Counts the numbers of cells that contains numbers in the specified range

Example : =count(A1:A5)

COUNTA()

Counts the numbers of cells that are not
empty in the specified range.

Example : =counta(A1:A5)

COUNTBLANK()

Counts empty cells in a specified range of cells.

Example: =countblank(A1:A5)

Text Function

	A	B
1	ramratan sriwastav	=left(A1,5)
2	RAMRATAN SRIWASTAV
3	Ramratan Sriwastav
4	Ganesh Goutam
5	kumar sahaj

Left()

Returns the specified numbers of characters from the left side of a text string.

Syntax: =left(text,[Num_chars]), Exp. =left(a1,5)

Right()

Returns the specified numbers of characters from the right side of a text string.

Syntax: =right(text,[Num_chars]), Exp. : =right(a1,5)

Proper()

Converts the first letter of each word in a text string to upper case.

Syntax: =proper(cell Address or Text) , Exp. : =proper(a1) 

Upper()

Converts all lower case letters in a text string to upper case.

Syntax: =upper (cell Address or Text), Exp. : =upper(a1)  

Lower()

Converts all uppercase letters in a text string to lowercase.

Syntax: =lower (cell Address or Text ), Exp. : =lower(a1)  

Rept()

Repeats the given text a specified numbers of times. Syntax: =rept(Text,[Num_Rept]), Exp. : =rept(a1,3)

Logical Function

	A	B	C
1	Name	Marks	Result
2	Ram	275	=if(b2>300,”Pass”,”Fail”)
3	Sam	360
4	Tina	800
5	Mina	180
6	Sita	560

IF()

The function IF is used to determine whether a condition is true or false.

Syntax: if(logical test, Value_if_True, Value_if_false)

Example : =if (B2>300,”Pass”,”Fail”)

Sumif and countif function

	A	B	C
1	POST	NAME	SALARY
2	DOCTOR	Ramesh	5500
3	ENGINEER	Dinesh	2500
4	DOCTOR	Deepak	3500
5	DOCTOR	Sanjay	2400
6	TEACHER	Pnakaj	1250
7	ENGINEER	Munna	1500
8	TEACHER	Rajnish	4500
9	DOCTOR	Shankar	7800
10	DOCTOR	Mahesh	6000

SUMIF()

It is used to find sum in a range of numeric cells based on a condition.

Syntax: =sumif(range,criteria,sum_range)

Example: =sumif(A2:A10, “DOCTOR”, C2:C10)

COUNTIF()

this function gives the count of the number of cells, which satisfies the condition

Syntax: =countif(range,criteria)

Example: =countif(A2:A10, “DOCTOR”)

Financial Function

FV() Function : Returns the future value of an investment based on periodic constant payments and a constant interest rate.

  Syntax: FV(interest/condition, term, installment)

  Example: =fv(11%/12,60,1000)  result will be  79518.08

PMT() Function: calculates the payment for a loan based on constant payments and a constant interest rate.

  Syntax : =PMT(rate,nper,pv,fv,type)

  Example: =pmt(11%/12,60,100000)  result will be 2174.24

PV() Function: Returns the present value of an investment.

  Syntax : =PV(rate, nper, pmt, fv, type)

  Example: =pv(11%/12,60,2174.24)  result will be 99999.89

Shorting Data

Name	Amount	Due Date
Ganesh Kumar	₹ 2,500.00	03/05/2017
Ajit Sen	₹ 5,200.00	06/05/2017
Manoj Mahato	₹ 1,900.00	06/09/2017
Babita Saran	₹ 6,500.00	05/02/2017
Suman Gupta	₹ 7,800.00	03/07/2017

The Sort command arrange worksheet data by text, number, dates or times.

Shorting Data:

Select a cell in the column used to sort

From the Data command tab, in the Sort & Filter group, click Sort A  to Z or Sort Z to A.

The selected column will be sorted.

Filtering  Data

Name	Amount	Due Date
Ganesh Kumar	₹ 2,500.00	03/05/2017
Ajit Sen	₹ 5,200.00	06/05/2017
Manoj Mahato	₹ 1,900.00	06/09/2017
Babita Saran	₹ 6,500.00	05/02/2017
Suman Gupta	₹ 7,800.00	03/07/2017

Using a filter allows you to work with a portion of the records in your data by extracting only records that match a set of criteria .

Filtering Data:
1. Click the column that contains the data you wish to filter
2. On the Home tab, click on Sort & Filter
3. Click Filter button
4. Click the Arrow at the bottom of the first cell
5. Set filter criteria
6. To clear filter click on Clear from Sort and Filter button.

Working with Pivot Table.

A PivotTable report provides a dynamic summary of an existing table or data range that can be quickly expanded, collapsed and rearranged to give you several different perspectives on your data

Using PivotTable

	A	B	C
1	POST	NAME	SALARY
2	DOCTOR	Ramesh	5500
3	ENGINEER	Dinesh	2500
4	DOCTOR	Deepak	3500
5	DOCTOR	Sanjay	2400
6	TEACHER	Pnakaj	1250
7	ENGINEER	Munna	1500
8	TEACHER	Rajnish	4500
9	DOCTOR	Shankar	7800
10	DOCTOR	Mahesh	6000

1.Select that data which you want use to create PivotTable
2.Insert Tab > PivotTable > PivotTable

Using Shapes

To create shapes in Excel click on

1. Insert tab
2. Click on shapes tool in Illustration Group
3. Click on any shapes that you want to draw on your page.
4. Left Click hold and drag mouse over the page where you want to draw the shapes

Shape Customization

You can customize your shape color, size, angel etc. from format tab.

To customize a shape click on that shape then choose your desire tool to customize. To add text on shapes , click on shape then type from keyboard.

Working with Picture

MS-Excel provides two ways to insert Image or Picture in your worksheet. •Offline: Insert Picture or Image from Your PC storage.
•Online: Insert Picture from the Internet.

Picture customization 

Use Format tab to customize your Picture like Picture Style, Shape, Color etc.

To activate format tab click on your Image or Picture or shape

Using Smart Art

1.Click on Insert Tab -> Illustration ->Smart Art

2. The Choose a SmartArt Graphic dialog box will appear, choose a SmartArt then click ok.

Using WordArt

1. Click on Insert Tab -> Text -> Word Art

2. Choose a style from dropped list of Word Art.

Chart in Excel

Excel can build a chart automatically based on existing data.

To Create Chart
1.Select the data
2.Insert Tab > Chart Group ( choose your chart type like column, Pie, Bar, etc.)

Format Cell

You can change Fonts, Number style, Alignment with help of Format cell option

To Format a Cell
1. Right Click on that  cell or cells
2. Choose  Format Cells  from the popup menu
3. Customize format for your cell

Format Cell (Number Tab)

Format Cells dialog box provide Six tabs to format your cell
1.Number tab:- Allow for the display of different number types and decimal places

Format Cell (Alignment Tab)

2.Alignment tab :- Allows for the horizontal and vertical alignment of text, wrap text, shrink text, merge cells and the direction of text

Format Cell (Font Tab)

3.Font tab :- Allows for control of font style, size, color and additional features

Format Cell (Border Tab)

4.Border tab :- Allows for control border styles and colors

Format Cell (Fill Tab)

5.Fill tab :- Allows for control of cell fill colors and styles

Paste Special

MS-Excel Provide different types of Paste Mode. Paste Mode are known as Paste Special in Excel. Some Paste Mode are listed bellow.

Paste Special (Transpose)

Transpose is Paste Special mode in Excel to Paste Row Data in Column Format and vise-Versa.

Freeze row or column

To Freeze a row or column
1. Click the Freeze Panes button on the View Tab.
2. Click on Top Row or First Column as your need
3. To unfreeze, click the Unfreeze from Freeze Panes button

Hide / Unhide Worksheet

To hide a worksheet
1.Select the tab of the sheet you wish to hide
2.Right-click on the tab
3.Click Hide

To Unhide a worksheet
1.Right click on any worksheet tab
2.Click unhide
3.Choose the worksheet to unhide

Workbook Views Setup

Excel Provide following way to setup your Page View for Print or Work 1.Normal View
2.Page Break View
3.Page Layout View and
4.Custom View

How to Print a workbook

To Print Your work from workbook first select the cells and then click on Page Layout->Print Area -> Set Print Area

Than click on Print from File Tab

Final Project-1

Question of final Project -1 

1. Cost of Purchases = 35% of Sales.
2. Gross profit(GP) = Sales – Cost of Purchase.
3. Advertisement =18% of Gross Profit (GP).
4. Total Expenses = Advertisement + Salaries + Elec. + Telephone + Misc
5. Profit = Gross Profit – Total Expenses.
6. Income Tax = 35% of Profit.
7. Net Profit = Profit – Income Tax.

Final Project -2

Question of final Project -2 

1. DA = 75% OF BASIC ,      HRA = 40% OF BASIC,          PF = 10% OF BASIC
2. Total salary = BASIC + HRA + DA – PF
3. Loan AMT = 30 times of total salary for employees whose total is above Rs 4500. {  =if(basic > 4500, basic*30,0)  }
4. Loan AMT paid = calculate loan repaid per months if interest rate is 5% and term =  180 months  {  = PMT(5%/12,180,loan AMT)  }
5. Rename the sheet 1 to as January.
6. Insert a FBONUS column after the DA column
7. FBONUS = 25% of basic if Basic is more than Rs. 2700.
{  = if(basic> 2700, 25%*basic, 0)  }
8. Net salary = Total + Fbonus – Loan AMT Paid Format Amount as two decimal place

Click to Watch how to solve this Project