Microsoft Excel 2013 Level 1

Version 196.07.01

207, Lok Center, Marol-Maroshi Road, Marol, Andheri (East), Mumbai 400 059.
Tel: +91-22-2920 1583, 30910000 (100 lines). [Link]
Table of Contents
Chapter 1: Exploring the Excel Environment ....................................................................... 1
What is Microsoft Excel 2013................................................................................................... 1
Starting Microsoft Excel 2013 .................................................................................................. 1
Interface of Microsoft Excel 2013 ............................................................................................ 2
Row, Column, Cell and Worksheet........................................................................................... 5
Navigation of Spreadsheet ....................................................................................................... 5
Paste Options with Live Preview Icons..................................................................................... 6
Cut Copy and Paste Operation ................................................................................................. 7
Customize the Excel Interface .................................................................................................. 9
Exercise .................................................................................................................................. 10
Chapter 2:Extending the spreadsheet and cell Referencing ................................................11
Cell Style ................................................................................................................................. 11
Cell Alignment ........................................................................................................................ 12
Changing Column Width and Row Height .............................................................................. 12
Merging Cells and Word Wrap ............................................................................................... 14
Undoing and Redoing Action ................................................................................................. 14
Formatting Numbers .............................................................................................................. 15
Entering Formulas .................................................................................................................. 17
Copying the Data and the formulas ....................................................................................... 18
Using Auto Sum ...................................................................................................................... 18
Using Simple Aggregate Functions ......................................................................................... 19
Cell References ....................................................................................................................... 20
Exercise .................................................................................................................................. 21
Chapter 3: Excel Charts ....................................................................................................22
Creating Charts using Chart Tools .......................................................................................... 22
Selecting Chart Styles ............................................................................................................. 23
Including Titles and Values in Charts using Chart Tools ......................................................... 23
New Feature of Excel 2013 .................................................................................................... 24
Recommended Charts ............................................................................................................ 25
Exercise .................................................................................................................................. 26
Module 4:Organizing and formatting Data in Excel ............................................................27
Formatting the Structure of a List .......................................................................................... 27
Sorting the List and Sorting the Data on Color ...................................................................... 28
Filter the List........................................................................................................................... 28
Chapter 5: Working with Functions ...................................................................................29
Entering Formulas .................................................................................................................. 29
Copying the Data and the formulas ....................................................................................... 30
Using Auto Sum ...................................................................................................................... 30
If Function .............................................................................................................................. 31
Vlookup .................................................................................................................................. 32
 Hlookup .................................................................................................................................. 33
Exercise .................................................................................................................................. 35
Chapter 6: Sorting and Filtering Data ................................................................................36
Sorting the Database .............................................................................................................. 36
Sorting by Multiple Columns .................................................................................................. 36
Subtotal .................................................................................................................................. 37
Auto- filter .............................................................................................................................. 37
Filtering a List using Advanced Filter...................................................................................... 38
Filtering Unique Records ........................................................................................................ 39
Exercise .................................................................................................................................. 40
Chapter 7: Data Validation and Data Protection ................................................................41
Setting Data Validation Rules ................................................................................................. 41
Methods of Data Validation ................................................................................................... 41
Exercise .................................................................................................................................. 43
Chapter 8: Working with Reports ......................................................................................44
Examining PivotTables ........................................................................................................... 44
Format a PivotTable report .................................................................................................... 46
Top/ Bottom Report ............................................................................................................... 46
Sparklines ............................................................................................................................... 48
Customize Sparklines ............................................................................................................. 49
Change the Style of Sparklines ............................................................................................... 49
Freezing Rows and Column .................................................................................................... 50
Split Window .......................................................................................................................... 52
Inserting Page Break .............................................................................................................. 52
Chapter 8:- Previewing and Printing ..................................................................................54
Previewing Worksheet ........................................................................................................... 54
Printing of Worksheet in Multiple Pages ............................................................................... 55
Repeating Rows and Columns for Multiple Pages ................................................................. 55
Chapter 9: Simple Database Operations ............................................................................56
Sorting the Database .............................................................................................................. 56
Sorting by Multiple Columns .................................................................................................. 56
Auto- filter .............................................................................................................................. 57
Subtotal .................................................................................................................................. 58
Pivot Table.............................................................................................................................. 58
Exercise .................................................................................................................................. 60
Chapter 9: More functions ................................................................................................61
Functions ................................................................................................................................ 61
Chapter 1: Exploring the Excel Environment
Objective:
After completing this chapter, you will be able to know
 What is Microsoft Excel 2013
 How to start Microsoft Excel 2013
 Interface of Microsoft Excel 2013

What is Microsoft Excel 2013

Microsoft Excel is a powerful spreadsheet application from Microsoft Corporation. It makes it easy for
you to create various kinds of spreadsheets, tables and statements along with the graphical
representation of data. While working in Excel, you can make use of its most important feature of
automatic recalculation, to save time and effort.

In Excel, you work with worksheets, which consist of rows and columns that intersect to form cells.
Cells contain various kinds of data that you can format, sort, and analyze. You can also create charts
based on the data contained in cells. An Excel file is called a workbook, which by default contains
three worksheets.

Starting Microsoft Excel 2013

Starting Excel with the Start Menu

1) Click on Start Button
2) Select Program
3) Select Microsoft Office
4) Click on Microsoft Excel

Starting Excel by creating Shortcut on Desktop

1) Click on Start Button
2) Select Program
3) Select Microsoft Office
4) Right Click on Microsoft Excel
5) Select the option, Send to Desktop (shortcut)

Starting Excel by Using Run Option

1) Click on Start Button
2) Select Run (a dialog box will appear)
3) Type Excel

Tips: We can also create short cut key by right clicking on MS Excel and assigning the shortcut key

Pragati Software Pvt. Ltd. 1

Microsoft Excel 2013 Level 2.
 Interface of Microsoft Excel 2013

File Quick
Backstage Access
View Toolbar Tabs Tabs Commands

Name
Box

Fig : 1.1
The components of the Excel window interact with the program or display information about what
you are working on Fig 1.1 .Some of these components are explained below:

Back Stage View:

The Backstage view is where you manage your documents and related data about them — creates,
save, and send documents, inspect documents for hidden metadata or personal information, set
options such as turning on or off AutoComplete suggestions, and more.
The File tab replaces the Microsoft Office Button and the File menu used in earlier
releases of Microsoft Office.

Pragati Software Pvt. Ltd. 2

Microsoft Excel 2013 Level 2.
 Details about the file i.e. Versions, Created date, Last modified

New, Open, Save, Save

As

Print Preview, Page setup, Print

Save and share the files on cloud

Create a PDF/XPS Documents and also change File Types

Close the Excel Application

To make change in default setting of Excel or to customize the Ribbon or to

create new ribbon

Fig:1.2

Pragati Software Pvt. Ltd. 3

Microsoft Excel 2013 Level 2.
Quick Access Tool Bar

Quick Access Toolbar is found at the upper left corner of the window, next to Office Button
It is a customized toolbar to which you may add frequently used commands. By default it contains
Save, Undo and Redo commands.
Quick Access Toolbar can be placed above (default location) or below the Ribbon. You may right click
on the ribbon and choose to place the Quick access toolbar above or below the ribbon.

You may add command or gallery to the Quick Access Toolbar. To do this, right Click on the Tab or
command and Click on Add to Quick Access Toolbar

Figure 1.1

Ribbon:

Figure 1.4

Groups, commands & Galleries

Menus and toolbars in the previous versions of Excel have been replaced by the Ribbon. Ribbons
contain Tabs such as Home, Insert, Page Layout, Formulas, Data, Review, and View. Each tab consists
of commands and galleries grouped according to their function into Ribbon Groups.

Pragati Software Pvt. Ltd. 4

Microsoft Excel 2013 Level 2.
 Row, Column, Cell and Worksheet

Row
Row runs horizontally in Excel Worksheet. They are identified by numbers starting from 1. In Excel
2013, there are 1048576 rows in a worksheet.

Column
Column runs vertically in an Excel Worksheet. They are identified by alphabets starting from A in the
column header. In Excel 2013, there are 16384 columns in a worksheet. As there are only 26
alphabets,Excel has given column heading as A to Z and then continues with AA to XFD.

Cell
Intersection of Row and Column is called a Cell or a Grid. Each and every cell in a worksheet is
identified by a Cell Reference or Cell Name. Cell reference is a combination or Column Name and Row
Number which indicates the column and row that intersects to make the cell.

Worksheet
A Worksheet or spreadsheet is a collection of Rows and Columns that intersects to form Cells. The
formation of a worksheet enables us to store related data effectively in it.

Navigation of Spreadsheet
While entering or editing the data in a worksheet, we will need to navigate from one place to another
inside the worksheet. The keys used to navigate inside the worksheet See below Table
Action Result
Click the cell Make Active cell
Enter Moves active cell one cell down
Shift + Enter Moves active cell one cell Up
Tab It Move to right side
Shift + Tab It Move to left side
Arrow key It move Up, Down, Left And Right
Shift + Arrow Will help to select the range
Home It Move to the first cell in the Row
Ctrl + Home To Move the cell to the first row & first column (A1)
Moves active cell to the top or bottom of the current column
with data. Pressing one of the directional arrow keys will
End
move the active cell to the left-most or right-most cell in the
current row with data
Ctrl + End To Move the cell to the last row & last column of the data
Ctrl +Page Up &Ctrl + Page Down Will help you, to move from one sheet to another

Pragati Software Pvt. Ltd. 5

Microsoft Excel 2013 Level 2.
 Note: Do not use Arrow key to navigate in the selected range as data will get deselected

Tips: To navigate in the selected range we have to use Enter key or Tab key.

Paste Options with Live Preview Icons

The options available in the gallery are as follows:

Paste
This is the standard paste that you would get using Ctrl+V.

Formulas
Pastes only formulas, with no formatting. This is common when you are copying
down from the first row of a table that has an outline border. To prevent the top
border from copying, you can paste formulas. You then find that you have to reapply
the number formatting. Figure 1. 2

Formulas & Number Formatting

Copies formulas as previous formulas, along with the number formatting.

Keep Source Formatting

This is particularly useful when copying from another application such as a web page. The formatting
from the other application is pasted along with the values.

No Borders
Pastes everything but the borders.

Column Widths
Includes the column widths from the copied area.

Transpose
Turns the data on its side. A 12-row-by-1-column copied range would paste as 1 row by 12 columns.
Values

Converts formulas to values.

Values and Number Formatting

Converts the formulas to values and includes the number formats from the copied data.

Pragati Software Pvt. Ltd. 6

Microsoft Excel 2013 Level 2.
Values & Source Formatting
Converts the formulas to values and includes all formatting such as cell styles, font color, number
formatting, and borders.

Formats
Paste only the formatting of the cell.

Paste Link
Creates a link to the source file.

Paste as Picture
Pastes as a picture.

Paste as Linked Picture

Pastes a live picture of the original cell in this location. This is the elusive Camera tool from Excel
2003.

Open Paste Special

Used to access the old Paste Special dialog. The Paste Special dialog still offers some choices not
available in the Paste Options gallery: Comments, Validation, All using Source Theme, Add, Subtract,
Multiply, Divide, and Skip Blanks.

Cut Copy and Paste Operation

Cut and Paste Operation

While working with excel, many a times we have to move data from one place to
another. In excel, we can easily move the value of the cell by using cut and paste
options.
Cut and Paste options allows us to move values, formulas, functions etc from one cell
to another. While moving the formulas or functions, cell reference does not get
change. We can also move the value from one worksheet to another worksheet or
from one workbook to another workbook.

To Move data from one place to another,

Select the cell or range (source)Home RibbonClipboard Cut
Select the cell or range (destination) Home Ribbon Clip Board Paste

Copy and Paste Operation

While working with excel, many a times we have to type the same data again and
again. Instead of doing this, we may use the Copy and Paste options.
Copy and Paste option allows us to copy data, formulas, function from one cell to
another cell or from one worksheet to another worksheet or from one workbook to

Pragati Software Pvt. Ltd. 7

Microsoft Excel 2013 Level 2.
another workbook. We can copy the data from one worksheet and can paste it in any another
workbook and in any location.
To copy data from one place to another,

Select the cell or range (source)Home RibbonClipboard Copy

Select the cell or range (destination) Home Ribbon Clip Board Paste

Tips: Ctrl + C (copy) Ctrl + X (Cut) Ctrl + V (paste)

Paste Special
We all know the basic copy and paste or cut and paste feature where we copy or move the data as it
is in other cell. But what if we want to copy only the data (without format) or want to copy only the
format not the data or want to copy only the value not formula etc so, excel has provided us paste
special option. Below is a list of all paste special options in Excel.

Paste Action
Paste cell value, format, formula,
All
function
Paste cell values & formulas without
Formulas
formatting
Paste cell values without formatting &
Values
formulas
Paste only the format of the cell
Formats
without values
Paste only the comment (if any)
Comments
without values
Paste only validation so same
Validation
validation will apply
Paste everything, but uses the
formatting from the current document
All using Source
themes of the sources. This option is
theme
useful to copy the data from one
workbook to another workbook.
Paste cell values, without cell borders
All except border
(if any)
Paste only column width without the
Column Widths
values
Formulas and Paste value, formula & number format
number formats (but no other formatting)
Values & number Paste value, number format (but no
format other format and no formulas)

Pragati Software Pvt. Ltd. 8

Microsoft Excel 2013 Level 2.
Customize the Excel Interface
Use Customizations to personalize the ribbon the way you want it. For Example, you can create
custom tabs and custom groups to contain the frequently used commands.

To Add New tab

1) Click on File BackStage View
2) Options  Customize Ribbon
3) Under Main Tabs, select the tab that you want the new ribbon tab to come here
4) Click on new tab button below the customize the ribbon list box
5) Add Commands to this custom group, by selecting them, in the Choose Commands From list box
and then clicking the Add button.
6) To rename the custom tab or group, select it and click the Rename button, type the new group
name in the Display Name text box, and click OK.
7) (Optional) To add another group to the same custom tab, click the New Group button below the
list box, and then add all its command buttons before renaming it (refer to Steps 5 and 6).
8) Click OK

Note: You can only add commands to custom groups on the Ribbon

Pragati Software Pvt. Ltd. 9

Microsoft Excel 2013 Level 2.
 Exercise

Select the Appropriate answer for the following

1) __________ has replaced the Menu and Toolbar

1) Office Button
2) Ribbons
3) Tabs
4) Quick Access
2) ___________ is the intersection of Row and Columns
1) Cell
2) Groups
3) Tabs
4) Commands
3) ___________ number of rows in a worksheet
1) 16384
2) 65653
3) 1048576
4) 256
4) ___________ to make the first cell of the row active
1) Ctrl + Home
2) Select the cell
3) Home
4) Shift+ Home
5) __________ number of column in a worksheet
1) 16384
2) 65653
3) 1048576
4) 256

Pragati Software Pvt. Ltd. 10

Microsoft Excel 2013 Level 2.
Chapter 2:Extending the spreadsheet and cell Referencing
Objective
After completing this chapter, you will be able to know;
 Formatting, alignment and merging the cell
 Changing the height and width of the row and column
 Inserting and Deleting the row, column and worksheet
 How to keep cell constant?

Cell Style
Cell Style is a group of predefined styles that you may use to present data properly. To apply a cell
style or cell format, we can use cell style option in Home Tab,Style Group.

Cell Styles Gallery

Figure 2.0.1

Figure 2.0.2

Pragati Software Pvt. Ltd. 11

Microsoft Excel 2013 Level 2.
Cell Alignment
When we type data in the spreadsheet, by default, text
is left aligned and numeric data is right aligned. But if
we wish to manuallyalign data to right, left or center,
we may use Cell Alignment.

In the figure, Data has been entered in the sheet,

where text data is left aligned and numeric data is right
aligned. To align all the data to center, we will select
the data or cell and follow any one of the following Figure 2.0.3
steps.
Select the Cell or data  HOME TAB CELL GROUP FORMATCell
Format Option
Select the Cell or data  Right on the cell  Select Cell Format Option

Figure 2.0.4

Tips: Select a cell and Press Ctrl +1 for theFormat Cell Dialog Box

Changing Column Width and Row Height

Changing Column Width

On a worksheet, we can also change the width
of the column according to the size of data to
be fit. By default, the width of any column in a
worksheet is 8.43 characters. Column width
can vary from 0 (Hidden column) to 255
characters.
As Per the figure, in cell C2, some value has Figure2.0.5

Pragati Software Pvt. Ltd. 12

Microsoft Excel 2013 Level 2.
been entered but because of column width, the value cannot be displayed. So, we need to change the
width of the column.
To Change the width of the Column, do the following

Select the Cell Go to Home Tab Cell Group Column Width

Figure 2.0.6
Figure 2.0.7

Changing Row Height

We can also change the row height by dragging with the mouse. But we need to decide the height in
points where maximum row height can be 409 points.

Figure 0.9 Figure 0.8

As per the above figure, we need to increase the row height so that it would look clearer. To do this,
Select the Cell Go to Home Tab Cell Group Row Height

Pragati Software Pvt. Ltd. 13

Microsoft Excel 2013 Level 2.
 Merging Cells and Word Wrap
Sometimes we may want to make space for larger amount of data that does not fit into a single cell.
In this case, we may merge two or more cells to act like a single cell. When a group of cells out of
which more than one cell contains dataismerged,only value in thefirstcell in selection ispreserved. We
can merge the cell horizontally or vertically

Figure 0.10

To mergecell, select the range of the Cell Home TabAlignment GroupMerge &Center

Wrapping the Text

If user wants to type multiple lines in a particular cell,we can format the cell to wrap text so that text
will be automatically broken if it exceeds the width of the cell.
To wrap the cell, select the Cell  Home Tab Alignment GroupWrap Text
Tips: Press Alt Enter while entering the data in the cell for Wrap Text directly

Undoing and Redoing Action

In Excel, user can also undo and redo the last action up to 100 times even after worksheet has been
saved.
Quick Access Toolbar  Undo and Redo

Pragati Software Pvt. Ltd. 14

Microsoft Excel 2013 Level 2.
Formatting Numbers
By applying format to the number, we can change the appearance of the number without changing
the number. Original value we can view in formula bar.
To Format Number
Select Cell Home  Number Group

Figure 0.11

General:
General is the default format for any cell. When you enter a number into the cell, Excel will guess the
number format that is most appropriate.
For example, if you enter "1-5", the cell will display the number as a Short Date, "1/5/2010".

Number
Number formats numbers with decimal places.

Pragati Software Pvt. Ltd. 15

Microsoft Excel 2013 Level 2.
For example, if you enter "4" into the cell, the cell will display the number as "4.00"

Currency
Currency formats numbers as currency with a currency symbol.
For example, if you enter "4" into the cell, the cell will display the number as "$4.00".

Accounting
Accounting formats numbers as monetary values like the Currency format, but it also aligns currency
symbols and decimal places within columns.
This format makes it easier to read long lists of currency figures.

Short Date
Short Date formats numbers as M/D/YYYY.
For example, August 8th, 2013 would be "8/8/2013".

Long Date
Long Date formats numbers as Weekday, Month DD, YYYY.
For example, "Monday, August 14, 2013".

Time
Time formats numbers as HH/MM/SS and notes AM or PM.
For example, "[Link] AM".

Percentage
Percentage formats numbers with decimal places and the percent sign.
For example, if you enter "0.75" into the cell, the cell will display the number as "75.00%".

Fraction
Fraction formats numbers as fractions separated by the forward slash.
For example, if you enter "1/4" into the cell, the cell will display the number as "1/4". If you enter
"1/4" into a cell that is formatted as General, the cell will display the number as a date, "4-Jan".

Scientific Format
Scientific formats numbers in scientific notation.
For example, if you enter "140000" into the cell, then the cell will display the number as "1.40E+05".
Note: by default Excel will format the cell in scientific notation if it contains a large integer. If you do
not want Excel to format large integers with scientific notation, use the Number format.

Text
Text formats numbers as text, meaning that what you enter into the cell will appear exactly as it was
entered.
Excel defaults to this setting if a cell contains both text and numbers.

Pragati Software Pvt. Ltd. 16

Microsoft Excel 2013 Level 2.
Tips: In excel, by default all text are left aligned and numbers are right aligned

Tips: Ctrl + Z for Undo and Ctrl + Y for Redo.

Entering Formulas

Formulas are equations which helps us to calculate the values in a worksheet. Each and every formula
has to start with = (equal to sign). Excel calculates the formula mathematically.

For example:
= 2+3*5 will display us the 17 as the answer.

Formulas can be made up of reference operator, arithmetic operators, and relational operators
Operators Types
Reference , (commas) : (colon)
+ (Addition) * (Multiple)
Arithmetic (Subtract) ^ (Exponential)
/ (Divide) % (Percentage)
> (Greater than) <= (Less than equal to)
Relational < (Less than) = (Equal to)
>= (Greater than equal to) <> (not equal to )
Figure 0.12

Hierarchy for the formula (How formula will work)

First Operator ^ (Exponent will get solved first) = 5*2^2 It will display 20
=10*2/5 It will display 4
Second Operator * / (which ever come first)
=10/2*5 It will display 25

Third Operator + - (which ever come first ) = 50/(7+3) It will display 5

Figure 0.13

Pragati Software Pvt. Ltd. 17

Microsoft Excel 2013 Level 2.
Copying the Data and the formulas
As displayed in the figure, HRA is calculated at 12% of B2
(Basic salary) now, to calculate the same for the rest, instead
of writing the formula again and again we will copy the
formula down. As we all know that, while copying the
formula horizontally or vertically, cell references get
changed. We copy the formula by our normal way or we can
just simply double click on fill handle which appears to the
right side of the active cell.
Figure 0.14

Figure 0.16
Figure 0.15

As we can see in the above image, when we double click on the fill handle formula will get copied
down till the last cell of the data in the same column.

Now suppose we need to copy only the data not the formula.
Select the range Home Tab  Clipboard Group  Copy PastePaste Values

Using Auto Sum

AutoSum is the shortcut given by Excel to add values which are

entered in rows or columns. We need to select rows or column along
with the cell where you require the output. Then use one of the
following methods to use AutoSum.

Figure 0.17
Select the rangeHome Ribbon  Editing Group Auto sum

Select the range Formula Ribbon Function GroupAuto

sum
Figure 0.18

Pragati Software Pvt. Ltd. 18

Microsoft Excel 2013 Level 2.
Understanding Functions
Functions are formulas predefined in excel. Functions start with = (equal to) sign. Every function has a
name; this name is called the Function name. Function name itself tell us what that function would
do. For example Sum(), average(), Count()…. Etc. Functions are always with circle brackets in which we
need to input the values. The values that are to be input into a function are called the function
parameters. Parameters in an excel function are separated by comma(,)..

Using Simple Aggregate Functions

Let see some of the basic functions

SUM ()
Purpose : It adds all the value in the cell.
Syntax : SUM (number1, number2 …)
SUM (range1, range2……..)

Example : =SUM (B2, B4, B6)

=SUM (B2:B7) Figure 0.19
COUNT ()
Purpose : It counts only number in the range.
Syntax : COUNT (number1, number2 …)
COUNT (range1, range2……..)

Example : =COUNT (B2, B4, B6)

=COUNT (B2:B6) Figure 0.20

AVERAGE ()
Purpose : It gives average of the series.
Syntax : AVERAGE (number1, number2 ….)
AVERAGE (range1, range2 …..)

Example : AVERAGE (B2,B4,B6)

AVERAGE (B2:B7) Figure 0.21

MAX ()
Purpose : It is gives the highest value in range
Syntax : MAX (number1, number2 ….)
MAX (range1, range2 …..)
Example : MAX (B2, B4, B6)
MAX (B2:B7)

Pragati Software Pvt. Ltd. 19

Microsoft Excel 2013 Level 2. Figure 0.22
MIN ()
Purpose : It is gives the lowest value in range
Syntax : MIN (number1, number2 ….)
MIN(range1, range2 …..)
Example : MIN(B2, B4, B6)
MIN (B2:B7)

SUMIF ()
Purpose : It is gives the sum based on the
criteria
Syntax : SUMIF (Range,Criteria,Sum_range)
Example : SUMIF(A2:A11,C2, B2:B11)

COUNTIF ()
Purpose : It is gives the count based on the
criteria
Syntax : SUMIF (Range,Criteria)
Example : COUNTIF(A2:A11,C2)

Tips: select the range with one blank cell, row or column and press Alt = for auto sum

Cell References
Cell reference is the name given to the cell on a worksheet which helps excel to look for the values
when we write the formulas or function. By default every cell has a unique name which is column
letter and row number e.g. B2 if we use B2 in any formula then B2 will be the cell reference for excel.
Types of Cell references
 Relative Reference
 Absolute Reference

Relative References
Relative References are the default cell reference in Excel and
they make it easy for us to create formulas once and then
copy them. When we copy the formula to other cells it
changes itself relationally as per the reference of the
destination cell reference.

Pragati Software Pvt. Ltd. 20

Microsoft Excel 2013 Level 2.
Absolute References
In some case while creating the formula we do not want our cell reference to change. In this case, we
will freeze or lock the row and column so that while copying the formula to another cell, our cell
reference does not change. This type of Cell Reference where, neither the column number nor the
row number changes, when a formula is copied, is called as Absolute Reference. We need to use $
(dollar sign) to freeze or lock the cell reference, instead of using B2 which is relative reference in the
formula, we will use $B$2 so that while coping the formula B2 will not change. So, when we use $ sign
to lock row and column in a cell reference, it is called as absolute cell reference.

Tips: Press F4 in cell reference by writing formula it became absolute reference

It is very important to know how cell reference gets changed while copying the formula. While
copying the formula to the next cell, reference will get change.
For example
When =B2*12% in enter in the cell C2 it will display the value and when same formula is copy to C3
then formula will change to =B3*12% or when copy the formula to D2 it will change to =C3*12%.

Exercise
1) Currently we are working in a worksheet containing, Name of the customer, principal amount,
number of years and rate of Interest for 10 employees. Calculate Simple Interest in E column

2) Currently we are working in a worksheet containing, Name of the customer, principal amount,
and rate of Interest for 10 employees. Calculate Simple Interest in D column where number of
year is fixed which is entered in B13.
3) Currently we are working in a worksheet containing, Code (employee code), Name, Department,
Region and Basic Salary for 200 employee where first row containing heading and other contains
the data. Calculate HRA which is 22% of basic, if basic is more than 2500 otherwise HRA is 0. DA
is 28% of basic for EAST region and 25% of basic to others, PF which is 12.33% of basic, and Net
salary (Net salary must be rounded off to two decimal places).
4) Currently we are working in a worksheet where Customer name and revenue MTD is being
entered for 20 customers in sheet1 and in sheet2 we have customer name and their new
revenue MTD. So create one more column in sheet1 as new revenue and write the formula
using Vlookup so that we can get new revenue of customer.

Pragati Software Pvt. Ltd. 21

Microsoft Excel 2013 Level 2.
Chapter 3: Excel Charts
Objective
After completing this chapter, you will be able to know;
 How to create Charts
 Different type of charts
 Formatting the charts

Creating Charts using Chart Tools

To represent our data especially numerical data graphically, we may use chart. For example, if we
need to study a trend in data, we need to graphically present it. This makes it easy for the analyst.
There are many types of charts like column, line, Pie, Bar, Area, scatter etc

To insert a chart in excel, we may

Select the dataInsert TabCharts

We can select any type of charts or click on All Chart Types….. (Insert Chart dialog box will appear)
select the desired chart. The chart will be created on the same worksheet

Pragati Software Pvt. Ltd. 22

Microsoft Excel 2013 Level 2.
Selecting Chart Styles
There are large galleries of designs for each of the chart types in Excel. The styles can be selected
from the design tab after creating a chart. Design is a contextual tab that appears only when a chart is
created on a worksheet and clicked.

Including Titles and Values in Charts using Chart Tools

There are certain predefined layouts available in the design tab that can be applied to achieve desired
results. However, we may wish to change these layouts or create our own. This can be done from the
layout tab. Layout is another Contextual tab that appears as we select a chart. This tab lets us decide
the position of chart title, axis Title, legend, Data Label, data table etc and lets us keep them off if
need be.

Pragati Software Pvt. Ltd. 23

Microsoft Excel 2013 Level 2.
 Formatting the Charts

Before After

To change the style, look or feel Format is another Contextual Tool as we select the chart. This tool is
useful to us to give the various shape styles, Word art Styles, to arrange and rotate the charts or the
shape.

New Feature of Excel 2013

Excel 2013 helps you to create healthy assortment of new charting elements and features that will
save lot of time .Charts that used to require specialized knowledge and a lot of time and
experimentation have been reduced to a few clicks. The emphasis is on generating quick and
appropriate charts, from the get-go.

Pragati Software Pvt. Ltd. 24

Microsoft Excel 2013 Level 2.
Recommended Charts

Choosing the appropriate chart type to represent your data's story and applying that chart based on
the users requirement. Recommended Charts takes a bit of the pain out of this process.

Pragati Software Pvt. Ltd. 25

Microsoft Excel 2013 Level 2.
Exercise
There exist a worksheet, containing Product name from A1 to A10 and sales value of that product
from January to May B1 to G10.
1) Create a Line Diagram for all the product
2) Create a Pie diagram for March Sales
3) Create a 3D column diagram (give proper Chart Tile, Axis tile)Module 4: Organizing and
formatting Data in Excel

Pragati Software Pvt. Ltd. 26

Microsoft Excel 2013 Level 2.
Module 4:Organizing and formatting Data in Excel
Objective
After completing this chapter, you will be able to know;
 To format the table automatically
 To arrange the format table
 To filter the format table

Formatting the Structure of a List

Excel provides us a numerous of predefined table styles that we can

use to format the table quickly. If the predefine style does not meet
our need, we can create our own style.
We can also perform sorting and filter function with this format as
table

How to format the table

Select the range Home Ribbon Style Group Format Table

After getting the gallery of format table we can select the format of the table from the Light, Medium,
and Dark Groups listed in the Format Table gallery.

Pragati Software Pvt. Ltd. 27

Microsoft Excel 2013 Level 2.
Sorting the List and Sorting the Data on Color
When a range is formatted as a table, we get a filter
automatically applied on the table. This filter would
allow us to rearrange or filter our data according to
our requirement.

Filter the List

As the table has been formatted by format

table option we will get drop down arrow
on the heading of the table. Drop down
arrow also help us to filter the list where
we can give the condition as which data we
would like to display.

Pragati Software Pvt. Ltd. 28

Microsoft Excel 2013 Level 2.
Chapter 5: Working with Functions
Objective
After completing this chapter, you will be able to know;
 How to create a formula.
 Using functions
 Different types of Cell reference
 What is if function and Vlookup.

Entering Formulas

Formulas are equations which helps us to calculate the values in a worksheet. Each and every formula
has to start with = (equal to sign). Excel calculates the formula mathematically.

For example:
= 2+3*5 will display us the 17 as the answer.

Formulas can be made up of reference operator, arithmetic operators, and relational operators
Operators Types
Reference , (commas) : (colon)
+ (Addition) * (Multiple)
Arithmetic (Subtract) ^ (Exponential)
/ (Divide) % (Percentage)
> (Greater than) <= (Less than equal to)
Relational < (Less than) = (Equal to)
>= (Greater than equal to) <> (not equal to )

Hierarchy for the formula (How formula will work)

First Operator ^ (Exponent will get solved first) = 5*2^2 It will display 20
=10*2/5 It will display 4
Second Operator * / (which ever come first)
=10/2*5 It will display 25

Third Operator + - (which ever come first ) = 50/(7+3) It will display 5

Pragati Software Pvt. Ltd. 29

Microsoft Excel 2013 Level 2.
Copying the Data and the formulas
As displayed in the figure, HRA is calculated at 12% of B2
(Basic salary) now, to calculate the same for the rest, instead
of writing the formula again and again we will copy the
formula down. As we all know that, while copying the
formula horizontally or vertically, cell references get
changed. We copy the formula by our normal way or we can
just simply double click on fill handle which appears to the
right side of the active cell.

Fill Handle is used to fill

series of numbers

As we can see in the above image, when we double click on the fill handle formula will get copied
down till the last cell of the data in the same column.

Now suppose we need to copy only the data not the formula.
Select the range Home Tab  Clipboard Group  Copy PastePaste Values

Using Auto Sum

AutoSum is the shortcut given by Excel to add values which are

entered in rows or columns. We need to select rows or column
along with the cell where you require the output. Then use one of
the following methods to use AutoSum.
Select the rangeHome Ribbon  Editing Group Auto sum

Select the range Formula Ribbon Function GroupAuto sum

Pragati Software Pvt. Ltd. 30

Microsoft Excel 2013 Level 2.
If Function
We can use the IF function to evaluate a condition. The IF function returns different values depending
on whether the condition is true or false.
The first argument is the condition that you want the function to evaluate. The second argument is
the value to be returned if the condition is true and the third argument is the value to be returned if
the condition is false. Second and third parameters are optional.
Purpose : It returns true value or false value depend on the condition given by user
Syntax : IF(CONDITION, TRUE VALUE, FALSE VALUE)
Example : IF(100>500, “YES”,”NO”)  NO

Example 1: Calculate HRA as 20% of salary only for sales department otherwise 0.

Sales is the condition

20% of salary is True value
0 is false value

As D column contains Dept, D2= “sales”, As H column contains Salary, H2*20%

(Text will be in double quotes while creating formulas

Pragati Software Pvt. Ltd. 31

Microsoft Excel 2013 Level 2.
Vlookup
Purpose : It searches the values from the first column from the data and returns the value
in the same row from specified column.
Syntax : VLOOKUP(Lookup Value, Table Array, Column Number, Range Lookup)
(What to Find, Where to Find, Which Value, How to Display)

Example :

Microsoft Excel lookup is a Microsoft Excel function that

searches for values in a column or row of a spreadsheet
list or table. The V in VLOOKUP stands for vertical
(column).

We have a simple example, to understand the working

of v-lookup. In this figure we have two sheets consisting
of table having unique Part Number, description, price
and their availability.

Now, what are we looking for Price based on the Part Number. In another sheet there is Part Number
but no Price. It can be obtained from the sheet that has the Price Column. The main use of Vlookup is
to compare different worksheets, same worksheets or different workbooks.

In order to find the price from another

sheet we use vlookup.
Price is calculated by using vlookup
function.
The main source data is in Sheet 1. The
function is as follows

=vlookup(B2,sheet1!$B$2:$E$7,3,0)

Pragati Software Pvt. Ltd. 32

Microsoft Excel 2013 Level 2.
Lookup_Value
is the lookup value, this is the parameter where the part number is entered

Table_array
The table_array is the lookup table from where the value can be fetched mapping on the unique
values(in this example it is Part Number)of both the sheets

Column_index_number
Vlookup does not rely on column heading, so you must count from left to right, how many columns
you want to return the data
False

Exact or approximate:
The range looks up for an exact or approximate value. In our example, we want to get the price of the
part number

Tip: The lookup value must be in the left-most column of where the lookup table begins or it will not
work.

Once the above formula has been entered into cell C2 any valid part number that is entered into C2
returns the price for that part. For example, if we entered P79 into G1, it would return $12.00.

Hlookup
Purpose : It searches the values from the first row of the data and returns the value
from the specified column.
Syntax : HLOOKUP(Lookup Value, Table Array, Column Number, Range Lookup)
(What to Find, Where to Find, Which Value, How to Display)

Example :

Microsoft Excel lookup is a Microsoft Excel function that searches for values in a column or row of a
spreadsheet list or table. The H in HLOOKUP stands for horizontal(row).
Consider the below example where we have order ID, Unit Price and Quantity. Based on the order ID
we need to extract the quantity.

=HLOOKUP(B7,$B$1:$H$3,3,0)

Pragati Software Pvt. Ltd. 33

Microsoft Excel 2013 Level 2.
Lookup_Value
is the lookup value, this is the parameter where the part number is entered

Table_array
The table_array is the lookup table from where the value can be fetched mapping on the unique
values (in this example it is Part Number) of both the sheets

row_index_number
Vlookup does not rely on rowindex, so you must count from top to bottom, how many rows you want
to return the data
False

Exact or approximate:
The range looks up for an exact or approximate value. In our example, we want to get the price of the
part number

Tip: The lookup value must be in the top-most row of where the lookup table begins or it will not
work.

Once the above formula has been entered into cell B7 any valid part number that is entered into C2
returns the price for that part. For example, if we entered P79 into G1, it would return $12.00.

Pragati Software Pvt. Ltd. 34

Microsoft Excel 2013 Level 2.
Exercise
1) Currently we are working in a worksheet containing, Name of the customer, principal amount,
number of years and rate of Interest for 10 employees. Calculate Simple Interest in E column

2) Currently we are working in a worksheet containing, Name of the customer, principal amount,
and rate of Interest for 10 employees. Calculate Simple Interest in D column where number of
year is fixed which is entered in B13.
3) Currently we are working in a worksheet containing, Code (employee code), Name, Department,
Region and Basic Salary for 200 employee where first row containing heading and other contains
the data. Calculate HRA which is 22% of basic, if basic is more than 2500 otherwise HRA is 0. DA
is 28% of basic for EAST region and 25% of basic to others, PF which is 12.33% of basic, and Net
salary (Net salary must be rounded off to two decimal places).
4) Currently we are working in a worksheet where Customer name and revenue MTD is being
entered for 20 customers in sheet1 and in sheet2 we have customer name and their new
revenue MTD. So create one more column in sheet1 as new revenue and write the formula
using Vlookup so that we can get new revenue of customer.

Pragati Software Pvt. Ltd. 35

Microsoft Excel 2013 Level 2.
Chapter 6: Sorting and Filtering Data
Objective
After completing this chapter, you will be able to know;
 To arrange the database in ascending or descending order
 To filter the data
 To summarize the data
 To make the report

Sorting the Database

After entering data in excel, we may want
to arrange it in the alphabetical order (A-
Z) or smallest to largest values. For this,
we may use the sort functionality in Excel.
In excel, we can re-arrange data on values,
cell color, font color, or cell icon. When we
sort on values it may be in the order A to Z
, Z to A (Text) , smallest to Largest, largest
to smallest (Numbers), oldest to newest, newest to oldest (Date) or custom list.

Data Ribbon Sort & filter Group Sort

Sorting by Multiple Columns

In Excel, we can also arrange the data by
multiple columns, by clicking on Add level
so that one more column will get added in
the sorting list. Second and subsequent
sorting parameters help us sort one field
on top of another. Example, if we sort
data first region wise and then
department wise, first the data is
arranged region wise in the sorting order
selected and then for each region, data

Pragati Software Pvt. Ltd. 36

Microsoft Excel 2013 Level 2.
would be arranged department wise. We can perform up to 64 levels of sorting in Excel.

Subtotal

Many a times we need to find total, average, total number of records,

highest value etc. in a selected range so inserting subtotal in our data
will make our work easy. Subtotal is in Data Ribbon  Outline Group
 Subtotal

Why Subtotal
Before Clicking on Subtotal, we will see to that our select range or
data where want total, average etc must be arrange in ascending or
descending order.

Inserting Subtotal
After click on Subtotal, a dialog box will appear and in that we need
to select the column name where data is arrange in ascending order
and then select which function need to operator (Sum, average,
count etc) and then on which column subtotal need to be added.

Subtotal is performed on visible items.

Auto- filter

Using AutoFilter to filter data is a

quick and easy way to find and
work with a subset of data in a
range of cells or table column.

Filtered data displays only the

rows that meet criteria that you
specify and hides rows that you
do not want displayed. After you
filter data, you can copy, find,
edit, format, chart, and print the
subset of filtered data without
rearranging or moving it.

Ribbon Sort & filter Group 

Filter

Pragati Software Pvt. Ltd. 37

Microsoft Excel 2013 Level 2.
When we click on auto-filter option a drop-arrow key will get display on each heading on the data so,
that we can filter the list as per our condition. We just need to uncheck the value which we do not
want to view.

To use Auto filter effectively, our database must have a heading and data must be ideal. An ideal
database contains no blank row or blank column and no merged cells.
Auto -Filter can be done in any type of data i.e. text values, numeric values and date values where we
have various type of criteria which is already mention in the list or we have to select the custom filter
option which last option in the list of Text filter,
Number filter or Date Filter.

Custom filter is use when we have two criteria at a

time. Auto-filter has the limitation that we can give
only two criteria with custom filter and only one
criterion without custom filter. If we have more than
two criteria or we multiple criteria then we have to
use Advance Filter Option.

Filtering a List using Advanced Filter

If you wish to filter your data such that only the records of employees of Sales and admin
departments from north and south region who earns between 7000-12000 or 15000-20000 are
displayed, auto filter will not serve the purpose. This is because one number filter cannot be applied
over another in Auto Filter. However, the above query requires us to do just the same on salary field.
So to solve this query, we may have to use Advanced-Filter.

While using Advanced Filter, we need to have a criteria range and a list range.

List range is your database. To create a criteria range, we need to make a copy of the column header
of the database.

Criteria Range

Pragati Software Pvt. Ltd. 38

Microsoft Excel 2013 Level 2.
 1) Enter a comparison criterion below the cell that contains the
criteria label. You may use same row for "AND" criteria and
different rows for "OR" criteria. For example, the criteria given in
can be used to display only records of people in north or south
regions.
2) Activate the Data tab.
3) In the Sort & Filter group, click Advanced to open the Advanced
Filter dialog box.
4) In the List Range box, select the cell range you want to filter. The
cell range must include the associated column headings.
5) In the Criteria Range box, select the cell range that contains your criteria.
6) Click OK.
Tip: While designing the criteria range, it is better to copy and paste the column header of the entire
database as the heading of the criteria range.
For better visibility, keep the criteria range and list range on different rows .

The Advanced Filter command filters your list in place, as Auto Filter does, but it does not display
drop-down lists for columns. Instead, you have to select the List Range i.e. your data, type criteria in a
criteria range on your worksheet and select the Criteria Range and in output range type the cell
address where you want to display the output. It is optional.

Filtering Unique Records

Advanced filter can also be used to filter out unique values in a list at a separate location. Though
remove duplicates functionality of excel can help in creating a list of unique values in a list, you would
need to copy paste the unique values, if you need it at a different location. To avoid this, use the
advance filter option as follows.
1) Select the column or click a cell in the range or list you want to filter.
2) On the Data Tab, Click Filter, and then click Advanced Filter.
3) Do one of the following.
 To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place.
 To copy the results of the filter to another location, click Copy to another location. Then, in the
Copy To box, enter a cell reference.
 To select a cell, click Collapse Dialog to temporarily hide the dialog box. Select the cell on the
worksheet, and then press Expand Dialog.
 Select the Unique records only check box.

Pragati Software Pvt. Ltd. 39

Microsoft Excel 2013 Level 2.
 Tips: Advanced filter, Copy to option copies on a same worksheet, if you want to copy the Filter data
in to different worksheet, and then select the Advanced Filter command while you are atthe
worksheet where you want the data to be placed.

Exercise
1) Open the sheet named Filter. Use Auto filter to display only records of
1) People working in sales or admin
2) People from North or South
2) Display records of people working in sales or admin, north or south whose salary is between 7000
and 12000.
3) Display records of people working in sales or admin, north or south whose salary is between 7000
and 12000 or between 15000 and 20000.

Pragati Software Pvt. Ltd. 40

Microsoft Excel 2013 Level 2.
Chapter 7: Data Validation and Data Protection
Objective
This chapter
 Helps you understand how to restrict data entry in a cell or a worksheet.
 Discusses the different Data Validation techniques in excel.

Suppose you do not want the user to enter a non text value in a cell or you want to restrict data entry
to certain values. You may use Data Validation for these.
Data Validation is a process which restricts the users from entering invalid data for individual cells or
cell ranges. It limits the data entry to a particular type, such as whole numbers, decimal numbers or
text and sets limit on valid entries.

Setting Data Validation Rules

To create a set of rules for data validation, do the following.
3) Select the cells for which you want to create a
validation rule.
4) On the Data tab, in the Data Tools group, click Data
Validation to open the Data Validation dialog box
(Figure 4.1).
5) Activate the Settings tab.
6) From the Allow list, select a data validation option.
7) From the Data list, select the operator you want.
Then complete the remaining entries.
8) Enter the Input Message if required in Input
Message tab.
9) Enter the error message if required in Error Alert
tab.
10) Click OK to set the validation rule and close the dialog box.
Figure 4.1
Methods of Data Validation

Creating a List
A list is an effective form of data validation where the user is allowed to
select an option from a drop-down list which is built-in to the cell
(Figure 4.2). The data source may be written manually by the user or
selected from the same sheet.
Steps are as follows.
1) Select a blank cell
2) Select Data Tab
3) Select Data Validation from Data Tool group
4) Select List Figure 4.2
5) In Source, select the cell with values, or type the data with comma.

Pragati Software Pvt. Ltd. 41

Microsoft Excel 2013 Level 2.
Tips: If the source is from a different sheet, create a named range for all the values and use the name
in the Source field for Data Validation.

Allow Numbers within Limits

1) In the Allow box, click Whole Number or Decimal.
2) In the Data box, select the type of restriction you want. For example, to set upper and lower
limits, select Between.
3) Enter the minimum, maximum, or specific value to allow.

Allow Dates or Times within a Timeframe

1) In the Allow box, select Date or Time.
2) In the Data box, select the type of restriction you want. For example, to allow dates after a
certain day, select greater than.
3) Enter the start, end, or specific date or time to allow.

Allow Text of a Specified Length

1) In the Allow box, click Text Length.
2) In the Data box, click the type of restriction you want. For example, to allow up to a certain
number of characters, click less than or equal to.
3) Enter the minimum, maximum, or specific length for the text.

Calculate What is Allowed Based on the Content of another Cell

1) In the Allow box, select the type of data you want.
2) In the Data box, select the operator (for the criteria) you want.
3) In the box or boxes below the Data box, click the cell that you want to use to specify what's
allowed. For example, to allow entries for an account only if the result won't go over the budget,
click Decimal for Allow, select less than or equal to for Data, and in the Maximum box, click the
cell that contains the budget amount.

Use a Formula to Calculate What is Allowed

1) In the Allow box, click Custom.
2) In the Formula box, enter a formula that calculates a logical value (TRUE for valid entries or
FALSE for invalid). For example, to give an incentive only if the dept is sales and the region west,
you may use the following custom formula =and(d2="sales",e2="west").

To display an optional input message when the cell is clicked, click the Input Message tab, and make
sure that, the-Show Input Message When Cell is Selected - check-box is selected and fill in the title
and text for the message.
Specify how you want Microsoft Excel to respond when invalid data is entered:
1) Click the Error Alert tab, and make sure the Show Error Alert After Invalid Data is Entered check
box is selected.
2) Select one of the following options for the Style box:
 To display an information message that does not prevent entry of invalid data, select
Information.

Pragati Software Pvt. Ltd. 42

Microsoft Excel 2013 Level 2.
  To display a warning message that does not prevent entry of invalid data, select Warning.
 To prevent entry of invalid data, select Stop.
Fill in the title and text for the message (up to 225 characters).

If you do not enter a title or text, the title defaults to "Microsoft Excel" and the message to "The
value you entered is not valid. A user has restricted values that can be entered into this cell."

Exercise
1) Open Advance Excel Assignment workbook. In the sheet named Validation, so the following data
validations.
1) No duplicates should be allowed in emp_code.
2) Only text should be allowed in emp name.
3) Age should be only numeric data.
4) Salary should be between 5000 and 50000.
5) Joining Date should be less than current Date.

2) In the “emp_inf” sheet create a dropdown list of all the employee codes in cell B3.

Pragati Software Pvt. Ltd. 43

Microsoft Excel 2013 Level 2.
Chapter 8: Working with Reports
Objectives:
This chapter would help you learn how to
 Create Pivot Tables.
 Make different reports using Pivot Tables.
 Use advanced features of Pivot Tables.

A Pivot Table is an interactive worksheet based table that quickly summarizes large amounts of data
using the format and calculation methods you choose. It is called a Pivot Table because you can
rotate its row and column headings around the core data area to give you different views of the
source data. As source data changes, you can update a pivot table. It resides on a worksheet thus;
you can integrate a Pivot Table into a larger worksheet model using standard formulas. You can use a
PivotTable to analyze data in an Excel workbook or from an external database such as Microsoft
Access or SQL Server.

Examining PivotTables

The data on which a PivotTable is based is called the Source Data. Each column represents a field or
category of information, which you can assign to different parts of the PivotTable to determine how
the data is arranged. You can add four types of fields, as shown in figure 9.1. The fieldsareexplained in
the following table:

Field Description
Filters the summarized data in the PivotTable. If you select an item in
the report filter, the view of the PivotTable changes to display only the
Report
summarized data associated with that item. For example, if Region is a
Filter
report filter, you can display the summarized data for North, West, or
all regions.
Displays the items in a field as row labels. For example given below, the
Row Labels row labels are values in the Quarter field, which means that the table
shows one row for each quarter.
Displays the items in a field as column labels. For example, given below,
Column
the column labels are values in the Product field, which means that the
Labels
table shows one column for each product.
Contains the summarized data. These fields usually contain numeric
Σ Values data, such as sales and inventory. The area where the data itself
appears is called the data area.

Pragati Software Pvt. Ltd. 44

Microsoft Excel 2013 Level 2.
 R
eportFilte
r Colum n
list L
abelslis
t

R
epo
rtF
ilte
r

R
owL
abe ls
list

ΣVa
lue
s

Figure 9.1
1) Select any cell in a data range that includes a heading for each column in the top row.
2) Activate the Insert tab.
3) In the Tables group, click the PivotTable button, or click the PivotTable list and select PivotTable
to open the Create PivotTable dialog box.
4) In the Table/Range box, select the range that contains the data to be used in the PivotTable.
5) Select the location for the PivotTable. You can place the PivotTable in a new or existing
worksheet. Click OK to create the PivotTable.

Add fields
You can add fields to a PivotTable to specify the data you want to display. The fields of the source
data appears in the "PivotTable Field List" task pane.
To add fields, drag the relevant field from the top of the PivotTable Field List to one of the four areas
at the bottom. You can add more than one field to an area, and you donot need to add all fields to the
table. To display data and not just headings, you need to place at least one field in the Σ Values area.
After the fields are in place, you can filter the information that appears in the table by selecting from
the Filter columns, Filter rows, or report filter lists. For example, you can show all data values, or
restrict the PivotTable to summarizing only a couple of them.

Pragati Software Pvt. Ltd. 45

Microsoft Excel 2013 Level 2.
Or simply select the data to create pivot table, click on button at bottom right corner and select table
option to create pivot report as below,

Format a PivotTable report

You can modify the format of a PivotTable by
using styles and the Field Settings dialog box. You
can use styles to format an entire PivotTable in
one step. You can use the Field Settings dialog
box to change number formats, specify how data
is summarized, and show or hide data.
6) Activate the Design tab
7) In the PivotTable Styles group, select a style Figure 9.14
that you want to apply to your Pivot Table
Calculate the Percentage of the field
You can change field settings to alter how data appears or is summarized in a PivotTable. To change
field settings:
8) Activate option Tab.
9) Click on the Field setting From Activate Field Group.
10) Form the Given dialog box change the custom name to % of
Salary.
11) Select the Tab Show vales as and from the drop down select the
% of Total.
Figure 9.15

Top/ Bottom Report

12) Select the Field on the Pivot.

Pragati Software Pvt. Ltd. 46

Microsoft Excel 2013 Level 2.
13) Select Value Filters and Select Top 10.
14) Apply the condition according to your requirement.

Figure 9.16

Pragati Software Pvt. Ltd. 47

Microsoft Excel 2013 Level 2.
Sparklines
Sparklines are tiny charts that fit in a cell - to visually summarize trends alongside data. Sparklines
show trends in a small amount of space, they are especially useful for dashboards or other places
where you need to show a snapshot of your business in an easy-to-understand visual format.
For example, the following picture shows how a Sparkline is used to analyze the trend of the product
Month wise.

Create a Sparkline:
1) Select an empty cell or group of empty cells in
which you want to insert one or more sparklines.
2) On the Insert tab, in the Sparklines group, click the
type of Sparkline that you want to create: Line,
Column, or Win/Loss.
3) In the Data box, type the range of the cells that
contain the data on which you want to base the
sparklines.
4) In earlier scenario, we have a line Sparkline, but Excel 2013 comes with some others such as
“column Sparkline” and “win/loss Sparkline” shown below for the same dataset:

When one or more sparklines are selected, the Sparkline Tools appear, displaying the Design tab. On
the Design tab, you can choose one or more of several commands from among the following groups:
Sparkline, Type, Show/Hide, Style, and Group. Use these commands to create a new Sparkline, change
its type, format it, show or hide data points on a line Sparkline, or format the vertical axis in a
Sparkline group.

Pragati Software Pvt. Ltd. 48

Microsoft Excel 2013 Level 2.
Customize Sparklines
After you create sparklines, you can control which value points are shown (such as the high, low, first,
last, or any negative values), change the type of the sparkline (Line, Column, or Win/Loss), apply styles
from a gallery or set individual formatting options, set options on the vertical axis, and control how
empty or zero values are shown in the sparklines.

Change the Style of Sparklines

Use the Style gallery on Design tab, which becomes available when you select a cell that contains a
sparklines

Select a single sparkline or a sparkline group.

To apply a predefined style, on the Design tab, in the Style group, click a style or click the arrow at the
lower right corner of the box to see additional styles.
To apply specific formatting to a sparkline, use the Sparkline Color or the Marker Color commands.

Pragati Software Pvt. Ltd. 49

Microsoft Excel 2013 Level 2.
Objective
After completing this chapter, you will be able to know how
 To freeze row and column
 To split the windows
 Insert page break for printing

Freezing Rows and Column

In order to view a large data more effectively, we
may wish to keep some part of the data visible all
the time while scrolling. Freezing panes can be used
to achieve this result

To freeze first row or first column

Select any cell  View RibbonWindow
Groupfreeze pane
Select Freeze top row or Select freeze First column

Pragati Software Pvt. Ltd. 50

Microsoft Excel 2013 Level 2.
To freeze row and column both
Freeze panes option will always freeze the rows
which are above the active cell and left columns
of active cell.
So if we want to freeze 1st row and 1st column
then
Select the B2 View ribbonFreeze Panes

Pragati Software Pvt. Ltd. 51

Microsoft Excel 2013 Level 2.
Split Window
In excel we can split the window into separate panes and scroll the worksheet in each pane so that we
can easily compare data from two separate worksheet locations. To split a worksheet or data into two
(upper and lower) horizontal panes, we can drag the split bar

Inserting Page Break

To print parts of a large worksheet is a bit
tricky. It is possible that we make mistake
while printing.
Page break helps us easily set how much data
we will require in each page

To insert the page break

Page break will always get inserted to the rows
which are above the active cell and columns to
the left of active cell.
To insert a page break for A1 to G18

Pragati Software Pvt. Ltd. 52

Microsoft Excel 2013 Level 2.
Select H19Page layoutpage setupbreakinsert page break

Pragati Software Pvt. Ltd. 53

Microsoft Excel 2013 Level 2.
Chapter 8:- Previewing and Printing
Objective
After completing this chapter, you will be able to know;
 How to view the page before printing the page
 To set the page margin for printing
 To print long worksheet in multiple pages
 To print same rows and columns in multiple pages

Previewing Worksheet
In excel, we just have worksheet not the pages so cannot make out how the print will come and how
much data will get printed on a page. So, before giving print in excel we must see the preview of the
sheet so that before giving it to print we can do last min changes. Here, excel has gave us Print
Preview options so that we can see how much data will print on a page.
File  Print Print Preview

Pragati Software Pvt. Ltd. 54

Microsoft Excel 2013 Level 2.
Page setup

Margins
In excel, we can also set the margins for printing the page.
To customize the margin setting click on small arrow icon,
this is right corner of page setup group in Page Layout
ribbon

Orientation
Next command in Page setup group is Orientation which allows us
to change the page orientation which can be portrait or landscape.

Size
Third command in Page Setup group is Size which allows us to
change the size of the page. There is various size of page for e.g. we
normal use Letter size page for printing or A4 size so, to fix the size
of the page this command will use.

Printing of Worksheet in Multiple Pages

Breaks
This option allows us to break our long worksheet in multiple sheets. This is inserting page break.

Repeating Rows and Columns for Multiple Pages

Print Area
This Print Area allows us to print same row and column to print in multiple page spreadsheets.

Pragati Software Pvt. Ltd. 55

Microsoft Excel 2013 Level 2.
Chapter 9: Simple Database Operations
Objective
After completing this chapter, you will be able to know;
 To arrange the database in ascending or descending order
 To filter the data
 To summarize the data
 To make the report

Sorting the Database

After entering data in excel, we may want
to arrange it in the alphabetical order (A-
Z) or smallest to largest values. For this,
we may use the sort functionality in Excel.
In excel, we can re-arrange data on values,
cell color, font color, or cell icon. When we
sort on values it may be in the order A to Z
, Z to A (Text) , smallest to Largest, largest
to smallest (Numbers), oldest to newest, newest to oldest (Date) or custom list.

Data Ribbon Sort & filter Group Sort

Sorting by Multiple Columns

In Excel, we can also arrange the data by
multiple columns, by clicking on Add level
so that one more column will get added in
the sorting list. Second and subsequent
sorting parameters help us sort one field
on top of another. Example, if we sort
data first region wise and then
department wise, first the data is
arranged region wise in the sorting order
selected and then for each region, data

Pragati Software Pvt. Ltd. 56

Microsoft Excel 2013 Level 2.
would be arranged department wise. We can perform up to 64 levels of sorting in Excel.

Auto- filter

Using AutoFilter to filter data is a

quick and easy way to find and
work with a subset of data in a
range of cells or table column.

Filtered data displays only the

rows that meet criteria that you
specify and hides rows that you
do not want displayed. After you
filter data, you can copy, find,
edit, format, chart, and print the
subset of filtered data without
rearranging or moving it.

Ribbon Sort & filter Group 

Filter

When we click on auto-filter

option a drop-arrow key will get
display on each heading on the
data so, that we can filter the list as per our condition. We just need to uncheck the value which we
do not want to view.

To use Auto filter effectively, our database must have a heading and data must be ideal. An ideal
database contains no blank row or blank column and no merged cells.
Auto -Filter can be done in any type of data i.e. text values, numeric values and date values where we
have various type of criteria which is already mention in the list or we have to select the custom filter
option which last option in the list of Text filter,
Number filter or Date Filter.

Custom filter is use when we have two criteria at a

time. Auto-filter has the limitation that we can give
only two criteria with custom filter and only one
criterion without custom filter. If we have more than
two criteria or we multiple criteria then we have to
use Advance Filter Option.

Pragati Software Pvt. Ltd. 57

Microsoft Excel 2013 Level 2.
Subtotal

Many a times we need to find total, average, total number of records,

highest value etc in a selected range so inserting subtotal in our data will
make our work easy. Subtotal is in Data Ribbon  Outline Group 
Subtotal

Why Subtotal
Before Clicking on Subtotal, we will see to that our select range or
data where want total, average etc must be arrange in ascending or
descending order.

Inserting Subtotal
After click on Subtotal, a dialog box will appear and in that we need
to select the column name where data is arrange in ascending order
and then select which function need to operator (Sum, average,
count etc) and then on which column subtotal need to be added.

Subtotal is performed on visible items.

Pivot Table
A pivot table is a great reporting tool that sorts and summarizes
independent of the original data layout in the spreadsheet. Pivot
table is used to summarize data in a tabular report form.
Pivot-table tools can automatically sort, count, total or give the
average of the data stored in spreadsheet. It displays the results in
report form in the same sheet or new sheet as we specify.

We can create report as per our expectation. It organizes our data according to the fields we specify.
To insert pivot table into a workbook, go to Insert Ribbon Tables Group Pivot table

Pragati Software Pvt. Ltd. 58

Microsoft Excel 2013 Level 2.
This gives the Create pivot Table dialog Box, where we may specify the range for data source (the
base data) and also the position to place the pivot table. We can either place the pivot table in the
same worksheet or on a new worksheet.

Once we insert a pivot table, we will get a blank report

and then we need to select the field that we want in Row
Label, Column Label, report filter and values field
When we click on any text field, it will automatically go to
Row Label and Numeric field will go to Values. By default
the sum function applied to a numerical field that is
added to the Values Field. To do any changes in the
positioning, you may just drag the field names to one of
the four boxes according to the report you wish to create.

Pragati Software Pvt. Ltd. 59

Microsoft Excel 2013 Level 2.
Exercise

Create a worksheet containing Empcode, name of the employee, gender, department,

designation, date of joining and salary and solve the following commands
1) Arrange the data in alphabetical order of name
2) Arrange the salary in descending order and then within name must be arrange in ascending
order
3) To display only the data of MANAGER
4) To display only those records, whose salary is between 10000 to 150000
5) To display total number of employee in each dept along with total salary
6) To create a report, display average salary in department gender wise.

Pragati Software Pvt. Ltd. 60

Microsoft Excel 2013 Level 2.
Chapter 9: More functions

Functions

Text functions

Text Function Description

Upper Converts a text into uppercase
Lower Converts a text into lowercase
Proper Converts a text into First character as capital
Left Extracts characters from left
Right Extracts characters from right
Mid Extracts characters from middle
Concatenate Join text
Len Find the length of string
Exact Checks for two text are same or not
Text Changes value to text format

Date & Time function

Date Function Description
Day Return day of the month, anumber from 1 to 31
Return month number of the year, a number
month
from 1 to 12
year Return year of the date
Now() Display current date and time
Today() Display the current date

Database Functions
Database Function Description
Adds the number in the column of records in
Dsum database that match the condition
Count the number of cell in the column of
Dcount
records in database that match the condition
Average the number in the column of records in
Daverage
database that match the condition
Returns the largest number in the column of the
DMax
records in database that match the condition
Returns the smallest number in the column of
DMin the records in database that match the
condition

Pragati Software Pvt. Ltd. 61

Microsoft Excel 2013 Level 2.
Shortcuts In Excel
Shortcut Command

CTRL combination shortcut keys Microsoft Excel 2007

CTRL+SHFT+( Unhide any hidden rows within the selection.

CTRL+SHFT+) Unhide any hidden columns within the selection.

CTRL+SHFT+& Applies the outline border to the selected cells.

CTRL+SHFT_ Removes the outline border from the selected cells.

CTRL SHFT ~ Applies the General number format in Microsoft Excel.

Applies the Currency format with two decimal places (negative numbers in
CTRL+SHFT+$
parentheses).

CTRL+SHFT+% Applies the Percentage format with no decimal places.

CTRL+SHFT+^ Applies the Exponential number format with two decimal places.

CTRL+SHFT+# Applies the Date format with the day, month, and year.

CTRL+SHFT+@ Applies the Time format with the hour and minute, and AM or PM.

Applies the Number format with two decimal places, thousands separator,
CTRL+SHFT+!
and minus sign (-) for negative values.

Selects the current region around the active cell (the data area enclosed by
CTRL+SHFT+* blank rows and blank columns). In a PivotTable, it selects the entire PivotTable
report.
CTRL+SHFT+: Enters the current time.
Copies the value from the cell above the active cell into the cell or the Formula
CTRL+SHFT+
Bar.
CTRL SHFT Plus ( ) Displays the Insert dialog box to insert blank cells in Microsoft Excel.

CTRL+Minus (-) Displays the Delete dialog box to delete the selected cells.

CTRL+; Enters the current date.

Alternates between displaying cell values and displaying formulas in the
CTRL+`
worksheet.
Copies a formula from the cell above the active cell into the cell or the
CTRL+'
Formula Bar.
CTRL+1 Displays the Format Cells dialog box.

Pragati Software Pvt. Ltd. 62

Microsoft Excel 2013 Level 2.
CTRL+2 Applies or removes bold formatting.

CTRL+3 Applies or removes italic formatting.

CTRL 4 Applies or removes underlining in Microsoft Excel.

CTRL+5 Applies or removes strikethrough.

Alternates between hiding objects, displaying objects, and displaying

CTRL+6
placeholders for objects.

CTRL+8 Displays or hides the outline symbols.

CTRL+9 Hides the selected rows.

CTRL 0 Hides the selected columns in Microsoft Excel.

Selects the entire worksheet. If the worksheet contains data, CTRL+A selects
the current region. Pressing CTRL+A, a second time selects the current region
and its summary rows. Pressing CTRL+A a third time selects the entire
CTRL+A worksheet. When the insertion point is to the right of a function name in a
formula, displays the Function Arguments dialog box. CTRL+SHFT+A, inserts
the argument names and parentheses when the insertion point is to the right
of a function name in a formula.
CTRL+B Applies or removes bold formatting.
Copies the selected cells. CTRL+C followed by another CTRL+C, displays the
CTRL+C
Clipboard.
Uses the Fill Down command to copy the contents and format of the topmost
CTRL+D
cell of a selected range into the cells below.
Displays the Find and Replace dialog box, with the Find tab selected. While
CTRL+F SHFT+F5 also display this tab, SHFT+F4,repeats the last Find action.
CTRL+SHFT+F, opens the Format Cells dialog box with the Font tab selected.
CTRL+G Displays the Go To dialog box. F5 also displays this dialog box.
CTRL+H Displays the Find and Replace dialog box, with the Replace tab selected.
CTRL I Applies or removes italic formatting in Microsoft Excel.
Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit
CTRL+K
Hyperlink dialog box for selected existing hyperlinks.
CTRL+N Creates a new, blank workbook.
Displays the Open dialog box to open or find a file. CTRL SHFT O selects all cells
CTRL O
that contain comments in Microsoft Excel.
Displays the Print dialog box. CTRL+SHFT+P, opens the Format Cells dialog box
CTRL+P
with the Font tab selected.
Uses the Fill Right command to copy the contents and format of the leftmost
CTRL+R
cell of a selected range into the cells to the right.
CTRL+S Saves the active file with its current file name, location, and file format.

Pragati Software Pvt. Ltd. 63

Microsoft Excel 2013 Level 2.
CTRL T Displays the Create Table dialog box in Microsoft Excel.
Applies or removes underlining. CTRL+SHFT+U switches between expanding
CTRL+U
and collapsing of the formula bar.
Inserts the contents of the Clipboard at the insertion point and replaces any
CTRL+V selection. Available only after you have cut or copied an object, text, or cell
contents.
CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL Y Repeats the last command or action, if possible in Microsoft Excel.
Uses the Undo command to reverse the last command or to delete the last
entry that you typed. CTRL+SHFT+Z, uses the Undo or Redo command to
CTRL+Z
reverse or restore the last automatic correction when AutoCorrect Smart Tags
are displayed.

Function keys
Displays the Microsoft Office Excel Help task pane. CTRL+F1 displays or hides the
Ribbon, a component of the Microsoft Office Fluent user interface. ALT+F1
F1
creates a chart of the data in the current range. ALT+SHFT+F1, inserts a new
worksheet.
Edits the active cell and positions the insertion point at the end of the cell
contents. It also moves the insertion point into the Formula Bar when editing in
F2
a cell is turned off. SHFT+F2, adds or edits a cell comment. CTRL+F2, displays the
Print Preview window.
Displays the Paste Name dialog box. SHFT+F3, displays the Insert Function dialog
F3
box.
Repeats the last command or action, if possible. CTRL+F4, closes the selected
F4
workbook window.
Displays the Go To dialog box. CTRL F5 restores the window size of the selected
F5
workbook window in Microsoft Excel.
Switches between the worksheet, Ribbon, task pane, and Zoom controls. In a
worksheet that has been split (View menu, Manage This Window, Freeze Panes,
Split Window command), F6 includes the split panes when switching between
F6
panes and the Ribbon area. SHFT+F6 switches between the worksheet, Zoom
controls, task pane, and Ribbon. CTRL+F6 switches to the next workbook
window when more than one workbook window is open.
Displays the Spelling dialog box to check spelling in the active worksheet or
selected range. CTRL+F7, performs the Move command on the workbook
F7
window when it is not maximized. Use the arrow keys to move the window, and
when finished, press ENTER, or ESC to cancel.

Pragati Software Pvt. Ltd. 64

Microsoft Excel 2013 Level 2.
 Turns extend mode on or off. In extend mode, Extended Selection appears in
the status line, and the arrow keys extend the selection. SHFT+F8, enables you
to add a nonadjacent cell or range to a selection of cells by using the arrow keys.
F8
CTRL+F8 performs the Size command (on the Control menu for the workbook
window) when a workbook is not maximized. ALT+F8 displays the Macro dialog
box to create, run, edit, or delete a macro.
Calculates all worksheets in all open workbooks. SHFT F9 calculates the active
worksheet in Microsoft Excel. CTRL ALT F9 calculates all worksheets in all open
workbooks, regardless of whether they have changed since the last calculation.
F9
CTRL ALT SHFT F9 rechecks dependent formulas, and then calculates all cells in
all open workbooks, including cells not marked as needing to be calculated.
CTRL F9 minimizes a workbook window to an icon.
Turns key tips on or off. SHFT F10 displays the shortcut menu for a selected item
in Microsoft Excel. ALT SHFT F10 displays the menu or message for a smart tag.
F10 If more than one smart tag is present, it switches to the next smart tag and
displays its menu or message. CTRL F10 maximizes or restores the selected
workbook window.
Creates a chart of the data in the current range. SHFT+F11, inserts a new
F11 worksheet. ALT+F11 opens the Microsoft Visual Basic Editor, in which you can
create a macro by using Visual Basic for Applications (VBA).
F12 Displays the Save As dialog box.
7)
Other useful shortcut keys
Move one cell up, down, left, or right in a worksheet. CTRL+ARROW KEY moves to
the edge of the current data region (data region: A range of cells that contains
data and that is bounded by empty cells or datasheet borders.) in a worksheet.
ARROW KEYS SHFT+ARROW KEY, extends the selection of cells by one cell. CTRL+SHFT+ARROW
KEY extends the selection of cells to the last nonblank cell in the same column or
row as the active cell, or if the next cell is blank, extends the selection to the next
nonblank cell
Deletes one character to the left in the Formula Bar in Microsoft Excel. Also clears
BACKSPACE the content of the active cell. In cell editing mode, it deletes the character to the
left of the insertion point.
Removes the cell contents (data and formulas) from selected cells without
DELETE affecting cell formats or comments. In cell editing mode, it deletes the character
to the right of the insertion point.
Moves to the cell in the lower-right corner of the window when SCROLL LOCK is
turned on. Also selects the last command on the menu when a menu or submenu
is visible. CTRL+END moves to the last cell on a worksheet, in the lowest used row
END of the rightmost used column. If the cursor is in the formula bar, CTRL+END,
moves the cursor to the end of the text. CTRL+SHFT+END, extends the selection
of cells to the last used cell on the worksheet (lower-right corner). If the cursor is
in the formula bar, CT
ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below

Pragati Software Pvt. Ltd. 65

Microsoft Excel 2013 Level 2.
 (by default). In a data form, it moves to the first field in the next record. Opens a
selected menu (press F10 to activate the menu bar) or performs the action for a
selected command. In a dialog box, it performs the action for the default
command button in the dialog box (the button with the bold outline, often the
OK button). ALT+ENTER starts a new line in the same cell. CTRL+ENTER fills the
selected cell range
Cancels an entry in the cell or Formula Bar in Microsoft Excel. Closes an open
menu or submenu, dialog box, or message window. It also closes full screen mode
ESC
when this mode has been applied, and returns to normal screen mode to display
the Ribbon and status bar again.
Moves to the beginning of a row in a worksheet. Moves to the cell in the upper-
left corner of the window when SCROLL LOCK is turned on. Selects the first
HOME command on the menu when a menu or submenu is visible. CTRL+HOME moves
to the beginning of a worksheet. CTRL+SHFT+HOME, extends the selection of cells
to the beginning of the worksheet.
Moves one screen down in a worksheet. ALT+PAGE DOWN moves one screen to
the right in a worksheet. CTRL+PAGE DOWN, moves to the next sheet in a
PAGE DOWN
workbook. CTRL+SHFT+PAGE DOWN, selects the current and next sheet in a
workbook.
Moves one screen up in a worksheet. ALT+PAGE UP moves one screen to the left
PAGE UP in a worksheet. CTRL+PAGEUP, moves to the previous sheet in a workbook.
CTRL+SHFT+PAGEUP, selects the current and previous sheet in a workbook.
In a dialog box, performs the action for the selected button, or selects or clears a
check box. CTRL+SPACEBAR, selects an entire column in a worksheet.
SHFT+SPACEBAR, selects an entire row in a worksheet. CTRL+SHFT+SPACEBAR,
SPACEBAR selects the entire worksheet. If the worksheet contains data,
CTRL+SHFT+SPACEBAR, selects the current region. Pressing
CTRL+SHFT+SPACEBAR a second time selects the current region and its summary
rows. Pressing CTRL+SHFT+SPACEBAR a third time selects the entire worksheet.
Moves one cell to the right in a worksheet. Moves between unlocked cells in a
protected worksheet. Moves to the next option or option group in a dialog box.
TAB SHFT TAB moves to the previous cell in a worksheet or the previous option in a
dialog box in Microsoft Excel. CTRL TAB switches to the next tab in dialog box.
CTRL SHFT TAB switches to the previous tab in a dialog box.

Pragati Software Pvt. Ltd. 66

Microsoft Excel 2013 Level 2.
 Pragati Software Pvt. Ltd. 67
Microsoft Excel 2013 Level 2.
 Pragati Software Pvt. Ltd. 68
Microsoft Excel 2013 Level 2.