Microsoft Excel Tutorial 2013 2016 2019

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

How to Open Excel with Start Menu
How to Open Excel with Start Menu

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

Or

How to Open Excel with Run Command
How to Open Excel with Run Command

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

MS-Excel 2013 / 2016 / 2019 Interface

microsoft Excel Interface

Ms-Excel 2013 / 2016 / 2019 Screen Components

Excel Screen Components

Showing and hiding the Ribbon

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

Quick Access Toolbar location
Quick Access Toolbar Menu

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

The Tell Me bar

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

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

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

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

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

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

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

Backstage view after click on File tab

Click on Back button to back to normal view

Backstage view Options

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

Worksheet Overall

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

New with Blank worksheet

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

Entering Data in Worksheet

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

Excel Project 1

To Save a workbook

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

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

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.

 Export Dialog box

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 data in cells

Selecting Column or Row

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

Auto Fill in excel

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

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.

Auto Sum function

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

Inserting Row, Column or Cell

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

Deleting Row, Column or Cell

Project 2

Excel Project 2

Mathematical Function

sample data mathematical functioan

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

AB
1ramratan sriwastav=left(A1,5)
2RAMRATAN SRIWASTAV
3Ramratan Sriwastav
4Ganesh Goutam
5kumar sahaj
Sample Data for Text function

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

ABC
1NameMarksResult
2Ram275=if(b2>300,”Pass”,”Fail”)
3Sam360
4Tina800
5Mina180
6Sita560
sample data for logical function

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

ABC
1POSTNAMESALARY
2DOCTORRamesh5500
3ENGINEERDinesh2500
4DOCTORDeepak3500
5DOCTORSanjay2400
6TEACHERPnakaj1250
7ENGINEERMunna1500
8TEACHERRajnish4500
9DOCTORShankar7800
10DOCTORMahesh6000
Sample Data for sumif , countif

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

NameAmountDue Date
Ganesh Kumar ₹ 2,500.0003/05/2017
Ajit Sen ₹ 5,200.0006/05/2017
Manoj Mahato ₹ 1,900.0006/09/2017
Babita Saran ₹ 6,500.0005/02/2017
Suman Gupta ₹ 7,800.0003/07/2017
Sample Data for Data shorting

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

NameAmountDue Date
Ganesh Kumar ₹ 2,500.0003/05/2017
Ajit Sen ₹ 5,200.0006/05/2017
Manoj Mahato ₹ 1,900.0006/09/2017
Babita Saran ₹ 6,500.0005/02/2017
Suman Gupta ₹ 7,800.0003/07/2017
Sample Data for Filter

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

ABC
1POSTNAMESALARY
2DOCTORRamesh5500
3ENGINEERDinesh2500
4DOCTORDeepak3500
5DOCTORSanjay2400
6TEACHERPnakaj1250
7ENGINEERMunna1500
8TEACHERRajnish4500
9DOCTORShankar7800
10DOCTORMahesh6000
Sample Data for Pivot Table

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

Working with Pivot Table

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

Using shapes in Excel

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.

Customize shapes in Excel

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.

Working with Photo offline in Excel
offline Picture option
working with Picture online
working with Picture online option

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

Picture customization

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 smart Art in Excel

Using WordArt

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

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

using Wordart in Excel

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

Chart in Excel

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 option in excel

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 (Number Tab)

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 (Alignment Tab)

Format Cell (Font Tab)

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

Format Cell (Font Tab)

Format Cell (Border Tab)

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

Format Cell (Border Tab)

Format Cell (Fill Tab)

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

Format Cell (Fill Tab)

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

Paste Special (Transpose)

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

Paste Special (Transpose)

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

Freeze Row or column

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

Hide / Unhide Worksheet

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

Workbook Views Setup

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

How to Print a workbook

Final Project-1

Final Excel Project-1
Final Excel 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. Net Profit = Profit – Income Tax.

Final Project -2

Final Excel Project-2
Final Excel 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

Leave a Comment