Unit -I

UNIT - I
Basics of MS Excel : Features of MS Excel , Worksheets and Workbooks: Definition of
Worksheets and Workbooks, Opening, Labeling and Naming Worksheets and Workbooks ,
Adding, Deleting and Saving Worksheets and Workbooks, Format Worksheet Tabs,
Reposition Worksheets, Inserting, Deleting, and Renaming Worksheets, Copy Worksheets, Printing
a Workbook: Set Print Titles, Headers/Footers, Page Margins, Page Orientation, Page Breaks.
Formatting a Worksheet: Cell, Cellpointer, Cell address, Change Font Styles and Sizes, Adding
Borders and Colors to Cells, Changing Column Width and Row Height, Merge Cells.

Introduction
Microsoft Excel is a spreadsheet program that is used to record and analyze numerical data. Think of
a spreadsheet as a collection of columns and rows that form a table. MS Excel is a Windows-based
application package that can be used to automate tasks such as calculation and analysis of data,
automate financial statements, business forecasting, transaction registers, inventory control, etc. It
provides multiple facilities, such as making graphs and charts, analyzing situations, and helps users
at the managerial level in taking decisions.

FEATURES OF MS EXCEL
The features provided by Excel are very useful for managers as well as the supervisors in any
type of organization and help them to execute their complex tasks with a minimum effort. It is
extensively used in financial organizations. The features of MS Excel are as follows:

1. Functions and formulas: The built-in formulas are called functions. MS Excel provides
analyzing data and manipulating text by using different functions. Users can easily calculate sum,
interest, average, etc. by using built-in functions. This can be done either by typing in the
function-based formulas or by using function wizards. Formulas are widely used in simple
computing (such as addition, subtraction, multiplication, division) and advanced computing.
They provide the power to analyze data extensively.
2. Auto-calculation: MS Excel spreadsheet allows a user to automatically recalculate the whole
worksheet every time a change is made in a single cell.
3. Charts & Graphs: One of the most important features of MS Excel is a chart. MS Excel allows
users to view data entered as tables in a graphical form as charts, which helps a user to easily
understand, analyze, and compare data.

1
 Unit -I

4. Entering Data in Series: A user can fill a range of cells either with the same value or with the
series of values. This can be done using the Autofill handle (small square on the bottom- right
corner of the active cell).
5. Sorting: This feature allows the data to be sorted either in ascending or descending order.
6. Filtering: 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
7. Pivot Table & Pivot Charts: Use a PivotTable report to summarize, analyze, explore, and
present summary data. Use a PivotChart report to visualize this summary data in a PivotTable
report, and to easily see comparisons, patterns, and trends. Both a PivotTable report and a
PivotChart report enable you to make informed decisions about critical data in your enterprise.
8. Data Validation: Prevents invalid data entry in a worksheet.

Basic Terms:
Workbook: when you open Excel, a new file is created called Book 1 (until you name it differently). It
is called “workbook” and it is initially made up of three Worksheets. When MS Excel is opened, by
default a workbook is opened with three worksheets named sheet1, sheet2, sheet3.
Worksheet: A worksheet consists of cells organized into columns and rows and is always a part of
workbook also called as spread sheet. In Microsoft excel a workbook is a file in which we can store
the data.
C ell: The worksheet is divided into number of columns and rows with labels. The intersection of a
column and a row is a cell. A cell can be identified by its cell reference composed of the column
name followed by the row number. Eg: D4 where D is column name and 4 is row number.
 A workbook contains 3 worksheets by default. We can add any number of worksheets to a
workbook.
 There are 16,384 columns (from A to XFD) and 1,048,576 rows (from 1 to 1048576) in an Excel
worksheet.
 Cells = 16384*1048576
 Column width is 255 characters and row height can be up to 409 points.
 A cell can contain 32,767 characters.

2
 Unit -I

Spreadsheet: A spreadsheet is a grid of rows and columns in which you enter text, numbers, and the
results of calculations. In Excel, a computerized spreadsheet is called a worksheet. The file used to
store worksheets is called a workbook.

PARTS OF MS EXCEL WINDOW

The Application Window
The Excel window is like other Office applications.

The Title bar displays the name of the application running and the name of the file you have open. On
the far right you will see the Minimize, Restore or Maximize, and Close buttons to control the
window.

Excel Menus
Along the top of the screen is the Menu Bar. Each word represents a different menu of Excel commands.
If a command is also on the toolbar then the toolbar icon appears alongside it. Some commands will
also feature the keyboard shortcut alongside it.

3
 Unit -I

Excel Toolbars
Excel displays the Standard and Formatting toolbars by default. These are found below the menu bar
at the top of the screen and provide a faster method of performing commands.
(i) The Standard Toolbar

(ii) The Formatting Toolbar

(iii) Picture Toolbar

(iv) Chart Toolbar

SAVING, OPENING AND CLOSING WORKBOOK

Creating a new workbook
 On starting Excel 2003 you will be presented with a new blank workbook named Book1. The
new workbook will contain 3 sheets identified by the sheet tabs in the bottom left of the
worksheet.
 To create a new blank workbook, click the new icon on the Standard toolbar or press
Ctrl + N.
 Every time a new blank workbook is created it is named Book1, Book2, Book3 and so on
until saved under a more appropriate name.

S a v i n g a Wo kbook
 It is good practice to save your workbook as soon as you can, and then to regularly save it to
protect against data loss.
 To save your document click Save under the File menu or press Ctrl + S. Select a location to
save the workbook in and an appropriate name and click Save.

Opening an Existing Workbook

If you have a workbook that you have already been working on, you can open it from Excel. You can
do so in three ways;

4
 Unit -I

 Click File > Open

 Click the Open toolbar icon
 Press Ctrl + O

C l o s i n g E x c e l a n d t h e w o r k b o o k : There are 3 ways to close a workbook:

 Click File > Close

 Click the lower X in the top right corner of the window
 Or Press Ctrl + W

These methods will close the workbook and leave Excel open.

There are also 3 ways to exit Excel:

 Click File > Exit
 Click the upper X in the top right corner of the window
 Or press Alt + F4

These methods will close Excel and all open workbooks.

E nte r i ng and Editing Data:

To enter data into Excel, click on the cell, type in your data and press Enter.

After pressing Enter the cell below the current one then becomes the active cell. Other alternatives
are:

Tab key: Enters the data and the cell to the right of the current cell becomes the active cell

Arrow keys: Enters the data and the cell dependent on the direction of the arrow key pressed
becomes the active cell

Mouse click: Enters the data and the cell clicked becomes the active cell

Esc key: Cancels the entry

Adding, Deleting, Labeling Worksheet:

When you open a new workbook, or add new worksheets to an existing workbook, Excel uses a
generic name for each sheet. Sheet1, Sheet2, Sheet3, and so on.

Adding/Inserting a worksheet
 To insert a new worksheet in front of an existing worksheet, select the existing worksheet and
then, click Home > click on Insert option in cells group > select Insert Sheet option.

5
 Unit -I

The new worksheet is named according to the number of worksheets you currently have. For
example if you have 3 sheets, then the new worksheet is named sheet4.
 Another way to insert a worksheet - click the “New Sheet” button (+) at the right end of the
spreadsheet name tabs.
 Alternatively, Right-click a sheet tab, choose the Insert... command from the shortcut
menu, select Worksheet from the Insert dialog box, and then click OK.

Deleting a worksheet

 Select the worksheet that you wish to delete. Click the “Delete” button in the “Cells” button
group on the “Home” tab of the Ribbon - Then choose the “Delete Sheet” choice in the button’s
drop-down menu.
 Another way is Right click on the desired sheet tab which you wish to delete - Select Delete
option form the context menu.

Rename a worksheet
 The easiest way to rename a worksheet is to double click its name. This will highlight the text
of the name, and you can then type a new name. Press enter to confirm the change.
 You can also right click on a worksheet and choose Rename from the menu.
 Select the worksheet that you wish to rename. Click the “format” button in the “Cells” button
group on the “Home” tab of the Ribbon - Then choose the “rename sheet” choice in the button’s
drop-down menu.

Reposition a worksheet

 Moving a worksheet is quite simple. All you need to do is to select the worksheet, drag the
worksheet tab to the new position and drop it.
 Alternatively click on “format” option in cells group of the home button on the ribbon and
choose “move or copy sheet “option from that.
 Or right click on the sheet tab – select “move or copy” option from the context menu.

Copying a worksheet Format

Worksheet Tabs:

6
 Unit -I

To change worksheet tab color

 One way is - In Excel, to change worksheet tab color select the worksheet
tab whose tab color you wish to change. Click the “Home” tab in the Ribbon then click the
“Format” button in the “Cells” button group. Then roll your mouse pointer down to the “Tab
Color” command. In the side menu that appears, then click the color you want to apply to the
worksheet tab.
 The other way is – right click on sheet tab and choose “Tab color” option”, select any color
from color theme.

Copy of a worksheet
 We can create a copy of the worksheet by selecting copy option from shortcut menu of
sheet tab.
 We can also move worksheet from its position (reposition). Right click on sheet tab and
choose move option from shortcut menu.

INSERTION AND DELETION OF ROWS

Inserting rows on a worksheet

 Select the row, or a cell in the row below where you want the inserted row to appear. For
example, if you wanted to insert a row between rows 7 and 8, select row 8.
 Click Insert > Rows
Inserting columns on a worksheet
 Select the column, or a cell in the column to the right of where you want the inserted
column to appear. For example, if you wanted to insert a column between columns C and
D, select column D.
 Click Insert > Columns
Inserting cells on a worksheet
 Select the cell, or the range of cells where you want to insert the new cells. Select the
same number of cells as you would like to insert
 Click Insert > Cells
 In the dialogue box that appears select the direction in which to shift the surrounding cells
Deleting rows, columns, and cells
 Select the rows or columns to be deleted.
 Click Edit > Delete

7
 Unit -I

 The rows or columns are deleted and all other rows and columns are shifted up and to the
left
To delete data in a cell
 Select the cell or cells to be deleted.
 Click Edit > Delete
 In the dialogue box that appears select the direction in which to shift the surrounding cells
Note: Pressing the delete key only removes the contents from the cells and will not delete the rows,
columns, or cells.
AUTO FILL
An amazing and often underutilized feature of Excel is the Auto fill. Auto fill is the use of the fill handle
to copy data and sequences across a range of cells.
To fill a range of cells:
 Select the cell with the content you wish to copy
 Point at the black square that appears in the bottom right corner of the cell, until your
mouse pointer becomes the fill handle
 Click and drag in the direction of the range you wish to fill.
 Excel will copy the contents of the cell across the range that you select. This will also work
with dates.
 This feature becomes very powerful when used with cells containing formulas. A lot of time can
be saved by copying formulas across a range of cells.

Printing a Workbook:
Select the worksheet or worksheets that you want to print. On the Page Layout tab, in the Page Setup
group,
 Page
Orientation – is the data wide? Landscape might work better. Tall? Try portrait Scaling – I
always use the fit to option, if it will fit on one page, great. If not, play with the pages until it
makes sense
Paper size – if 8.5×11” is too small, maybe the data will fit nicely on one legal size page

8
 Unit -I

 To specify Margins
To use predefined margins, click Normal, Wide, or Narrow. To specify custom page margins,
click Custom Margins and then—in the Top, Bottom, Left, and Right boxes—enter the margin
sizes that you want.
 To set header or footer margins, click Custom Margins, and then enter a new margin size in
the Header or Footer box. Setting either the header or footer margins changes the distance from
the top edge of the paper to the header or from the bottom edge of the paper to the footer
 To center the page horizontally or vertically, click Custom Margins and then, under Center on
page, select the horizontally or vertically check box.

 To use print titles:

Imagine how difficult it would be to read a worksheet if the column and row headings only
appeared on the first page. The Print Titles command allows you to select specific rows and
columns to appear on each page.

1. Click the Page Layout tab.

2. Select the Print Titles command.
3. The Page Setup dialog box appears. Click the icon at the end of the Rows to
repeat at top field.
4. Your mouse becomes the small selection arrow . Click the rows you want
to appear on each printed page. The Rows to repeat at top dialog box will
record your selection.
5. Click the icon at the end of the Rows to repeat at top field.
6. Repeat for Columns to repeat at left, if necessary.
7. Click OK. You can go to Print Preview to see how each page will look
when printed.

 To insert a break:
1. Click the Page Layout tab.

9
 Unit -I

2. Determine the placement of the break by clicking the row below, cell
below, or column to the right of where you want the break to appear. For
example, select column C, and a break will appear after column B.
3. Select the Insert Page Break command from the Breaks drop-down
menu.
4. The break is inserted. You can go to Print Preview to confirm that it
appears in the correct place on the page.

 To print active sheets:

If you have multiple worksheets in your workbook, you'll need to decide if you want to print the entire
workbook or specific worksheets. Excel gives you the option to Print Active Sheets. A worksheet is
considered active if it is selected.

1. Select the worksheets you want to print. To print multiple worksheets,

click the first worksheet, hold down the Ctrl key, then click the other
worksheets you want to select.

2. Click the File tab.

3. Select Print to access the Print pane.
4. Select Print Active Sheets from the print range drop-down menu.

5. Click the Print button.

(iii) To print the entire workbook:

1. Click the File tab.
2. Select Print to access the Print pane.
3. Select Print Entire Workbook from the print range drop-down menu.

10
 Unit -I

4. Click the Print button.

(iv) To print a selection or set the print area:

Printing a selection—sometimes called setting the print area—lets you choose which cells to print, as
opposed to the entire worksheet.

1. Select the cells you want to print.

2. Click the File tab.

3. Select Print to access the Print pane.
4. Select Print Selection from the print range drop-down menu.

11
 Unit -I

5. You can see what your selection will look like on the page in Print
Preview.
6. Click the Print button.

(v) To fit a worksheet on one page:

1. Click the File tab.
2. Select Print to access the Print pane.
3. Select Fit Sheet on One Page from the scaling drop-down menu.

4. Your worksheet is reduced in size until it fits on one page. Remember that if it
is scaled too small, it might be difficult to read.

Formatting a Worksheet:
Cell: In Excel, the intersection of a column and a row is called as a cell. Each cell has a name or
cell address. A cell is basic block of MS Excel. It holds data and formulas in tabular format. In a
worksheet there has 65536 row and 256 columns.
How many sheets, rows, and columns can a spreadsheet have?
Version Rows Columns

Excel 2013 1,048,576 16,384 XFD

Excel 2007 1,048,576 16,384 XFD

Excel 2003 and earlier 65,536 256

The total cells in a worksheet are 256*65536 =16777216

Cell Address: Each cell has a name or a cell address. The cell address consists of the column letter
and row number. For example, the first cell is in first column and first row. First column

12
 Unit -I

name is A and first row number is 1. Therefore, the first cell address is A1. Similarly, the
address of last cell is IV65536 i.e. column IV and row number is 65536.

Cell pointer: The cell pointer in Excel is the active cell or the selected cell and is highlighted by a
bolder rectangle. The location of the cell pointer is listed below the tool bar to the left of the formula
bar. By using the arrow keys on the keyboard or pointing and clicking on the desired cell, you can
move the pointer.

Cell range: A group of selected cells is called a range. The range is identified by its range reference,
for example, A3:C5.
Adjacent range: In an adjacent range, all cells touch each other and form a rectangle. To select an
adjacent range, click the cell in a corner of the range, drag the pointer to the cell in the opposite
corner of the range, and release the mouse button.
Non-adjacent range: A nonadjacent range includes two or more adjacent ranges and selected
cells. To select a nonadjacent range, select the first adjacent range or cell, press the Ctrl key as you
select the other cells or ranges you want to include, and then release the Ctrl key and the mouse
button

Change Font Styles and Sizes, Adding borders and Colors to Cells
In Microsoft Excel, a user can change the properties of text in any cell, including font type, size,
color, as well as make it bold, italic, or underlined. A user can also change the color of a cell's
background and the border around a cell. The following picture is a graphic illustration of the font
and cell format bar in Excel, as well as a description of each of the tools contained within it.

13
 Unit -I

 Changing font type

To change the text font within a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell containing the text you want to change.
2. Click the down arrow next to the font field on the format bar. (If you want to change the
font to bold, italic, or underlined, click on the B, I, or U on the format bar.)
3. After clicking the down arrow for the font, you should be able to select from each of the
installed fonts on your computer. Click the font you want to use, and the text in the
selected cell will change.
Note: If the selected cell does not contain any text, the font type will change as soon as you type new
text into the cell.

 Changing font size

To change the text size within a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell containing the text you want to change.
2. Click the down arrow next to the size box on the format bar. Typically, the default
size is 11 or 12, as shown in the above example.
3. After clicking the down arrow for the size, you should have a selection of different
sizes to choose. Some fonts may not scale properly, so they may have limited size
options.

14
 Unit -I

Note: If the selected cell does not contain any text, the font size will change as soon as you type new text
into the cell.

 Changing font color

To change the text color within a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell containing the text you want to change.
2. Click the down arrow next to the text color icon. It is usually displayed as the letter "A"
with a red underline, as shown in the example above.
3. After clicking the down arrow for the text color, select the color you want to make the
text.
Note: If the selected cell does not contain any text, the font color will change as soon as you type new
text into the cell.

 Changing cell background color

To change the cell background color within a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell for which you want to change the background color.
2. Click the down arrow next to the cell color icon. It is usually displayed as tipping
paint can with a yellow underline, as shown in the example above.
3. After clicking the down arrow for the cell color, select the color you want to make the
cell background.

 Changing cell border

By default, a cell does not have a border. Adding borders to a worksheet are a great way to help readers
categorize data – use of borders allows you to help focus the headings for rows and columns and
define what categories are and what data is. To change the cell border within a Microsoft Excel
spreadsheet, follow the steps below.
The methods are:
 Add a border around all data: Select range of cells like C5 to G17 -> Right Click the selected
range -> Select Format Cells -> Click on the Border Tab -> Click on the single thick line on
the right of the Style box ->Click on the Outline in the Presets box -> Click OK (Do the same
for ranges C5 to C17, D6 to D17 and D5 to G6)

15
 Unit -I

 Add a colored border: Select range of cells like C5 to G5 -> Right Click the selected range ->
Select Format Cells -> Click on the Border Tab -> Click on the single thick line on the right of
the Style box -> Change the color to Red, Accent 2 (hover over colors to see definition)
-> In the Border section click the bottom area of the box (a red line should appear) -> Click OK

 Merge Cells and Align Text:

With the ability to spread headings over columns, wrap text, change the orientation of the text and align
the text within cells you are able to provide as much information as possible by using simple text.
The methods are:
 Wrapping text within a single cell: Right Click on Selected Cell -> Select Format Cells->
Click on the Alignment Tab -> Click the check box next to Wrap Text -> Click OK

 Aligning and centering text over several columns: Select range of cells like D5 to G5 ->
Right Click the selected range -> Select Format Cells -> Click on the Alignment Tab ->
Click the check box next to Merge Cells -> Change the Horizontal alignment to Center -
> Click OK

Changing Column Width and Row Height:

By default, when you create a new workbook in Excel, the row height and column width is always the
same for all cells. However, you can easily change the height and width for one or more rows and
columns. If you find yourself needing to expand or reduce Excel's row widths and column heights,
there are several ways to adjust them. The table below shows the minimum, maximum and default
sizes for each based on a point scale.

Type Min Max Default

Column 0 (hidden) 255 8.43
Row 0 (hidden) 409 15.00

 Set a column to a specific width:

1. Select the column or columns that you want to change.
2. On the Home tab, in the Cells group, click Format.

16
 Unit -I

3. Under Cell Size, click Column Width.

4. In the Column width box, type the value that you want.
5. Click OK.

 Change the column width to automatically fit the contents (AutoFit)

1. Select the column or columns that you want to change.

2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click AutoFit Column Width.

 Change the width of columns by using the mouse

Do one of the following:
1. To change the width of one column, drag the boundary on the right side of the column
heading until the column is the width that you want.
2. To change the width of multiple columns, select the columns that you want to change, and
then drag a boundary to the right of a selected column heading.
3. To change the width of columns to fit the contents, select the column or columns that you
want to change, and then double-click the boundary to the right of a selected column
heading.
4. To change the width of all columns on the worksheet, click the Select All button, and
then drag the boundary of any column heading.

 Set a row to a specific height

1. Select the row or rows that you want to change.

2. On the Home tab, in the Cells group, click Format.

3. Under Cell Size, click Row Height.

4. In the Row height box, type the value that you want, and then click OK.

 Change the row height to fit the contents

1. Select the row or rows that you want to change.

2. On the Home tab, in the Cells group, click Format.

3. Under Cell Size, click AutoFit Row Height.

17
 Unit -I

 Change the height of rows by using the mouse

Do one of the following:

1. To change the row height of one row, drag the boundary below the row heading until the
row is the height that you want.

2. To change the row height of multiple rows, select the rows that you want to change, and then
drag the boundary below one of the selected row headings.

3. To change the row height for all rows on the worksheet, click the Select All button, and then
drag the boundary below any row heading.

18
 Unit - II

Unit – II
Adding Elements to a Workbook: Adding, Modifying, cropping an image, and rotating an
Images, compressing a Picture, Adding WordArt, Inserting AutoShapes, Adding Clip Art,
Adding a Hyperlink.
Protection: Protect worksheet, protect workbook, share workbook, and track Changes. Charts:
Chart elements: Titles, legend, data labels, creating a New Chart, Formatting the Chart, Types
of charts, Using Chart Templates.

Adding Elements to a Workbook:

While Microsoft Excel is primarily used as a calculation program, in some situations
you may want to store different elements like pictures, shapes, clipart etc., along
with data. You can enter these elements into Excel simply to associate them with a
particular piece of information.

Adding Images/Inserting Pictures:

All versions of Microsoft Excel allow you to insert pictures stored anywhere on your
computer or another computer you are connected to. In Excel 2016 and Excel 2013,
you can also add an image from web pages and online storages such as
OneDrive, Facebook and Flickr.

Insert an image from a computer

Inserting a picture stored on your computer into your Excel worksheet is easy by
following these steps:

1. In your Excel spreadsheet, click where you want to put a picture.

2. Switch to the Insert tab > Illustrations group, and click Pictures.

1
 Unit - II

3. In the Insert Picture dialog that opens, browse to the picture of interest, select it,
and click Insert. This will place the picture near the selected cell, more precisely,
the top left corner of the picture will align with the top left corner of the cell.
 To insert several images at a time, press and hold the Ctrl key while selecting
pictures, and then click Insert.

Add picture from the web, OneDrive or Facebook (Online pictures)

In the recent versions of Excel 2016 or Excel 2013, you can also add images from
web-pages by using Bing Image Search. To have it done, perform these steps:

1. On the Insert tab, click the Online Pictures button:

2. A window will appear, you type what you're looking for into the search box, and
hit Enter:
3. In search results, click on the picture you like best to select it, and then click
Insert. You can also select a few images and have them inserted in your
Excel sheet in one go.

Add/insert a picture in Excel cell

Normally, an image inserted in Excel lies on a separate layer and "floats" on the
sheet independently from the cells. If you want to embed an image into a cell,
change the picture's properties as shown below:

1. Resize the inserted picture so that it fits properly within a cell, make the cell
bigger if needed, or merge a few cells.
2. Right-click the picture and select Format Picture…
3. On the Format Picture pane, switch to the Size & Properties tab, and
select the Move and size with cells option.

Modifying Images

2
 Unit - II

As we mentioned earlier, whenever you insert a picture into MS Excel, the

Picture toolbar automatically opens. This gives you all the tools necessary to
modify your image in one convenient place. You can apply changes to images in
size, crop it, compress it, and adjust the color and contrast and more.
Note: All of the function available on the toolbar are also available in the Format
Picture dialog box. To access it, double click on the picture.
Adjusting Color and Contrast
Select the Color button on picture toolbar, it gives you different ways to adjust the
color of a picture:
To the right of the Color button on the Picture toolbar are the Contrast buttons,
they adjust the contrast up or down, just as the Brightness buttons right beside them
adjust the brightness up or down.

Cropping an image:
In MS Excel, you drag the Crop marks at the edges of the picture.
 Select a picture in MS Excel and click the Crop button in the Picture toolbar.
You can see the black crop marks at each corner of the picture.
 To crop the picture, click one of the crop marks and drag it to the desired location.
In this case, we will crop the picture to include only a selected part.
 To turn the crop function off, click the Crop button again.
 You can also use values to crop a picture by using the Format Picture dialog. To
access the Format Picture dialog, double click on the picture. To crop a picture
by entering values, select the Picture tab. Enter Values, then click Ok.

Rotating an image:

3
 Unit - II

 To rotate an image, we'd click the Rotate left 90° button. This rotates the picture
90° to the left. You can also rotate an image by selecting it and placing your
mouse over the handle at the top.
 When the mouse pointer turns into a circular arrow P, just click and drag the
top of the picture either right or left to rotate it.
 You can also rotate the picture by selecting the size tab in the Format Picture
dialog box and entering a value.
 Ex: If we enter 12, this will rotate the picture 12 degrees to the right. If we
wanted to rotate to the left, we would enter a negative number, such as -12.

Changing the Borders of a Picture or an Image

 The next button on the Picture toolbar is the Line Style button. This refers to
the border around the outer edge of a selected element.
 To change the border of an image, you can click this button in the toolbar, and
then select the desired weight (thickness) of the line.
 You can also change the border style in the Colors and Lines tab of the Format
picture dialog box. Using this method, you can also easily change the color
and style of the line as well.

Compressing a Picture

 You can reduce the file size of a picture by using the Compress Picture command.
 This reduces the resolution of the picture for quicker downloading and removes
unnecessary information. For instance, when you crop a picture, the cropped
portions of a picture are still stored in the file, they have only been "hidden."

4
 Unit - II

 Find the Compress Picture button on the toolbar, to the right of the Line Style
button. When you press it, you will see the Compress Picture dialog box.
 Choose the options you want, and click OK. The Web/Screen option reduces
picture resolution to 96 dpi, or dots per inch. This helps increase the loading
speed of your document when you view it on the web or when you open it.
 With the Select Transparent Color button, you create a transparent area in a
picture. To do this, click the button and select the color you want to make
transparent by clicking on it in the picture.
 Use the Reset Picture button on the toolbar to reset the picture to its original
size and format.

Adding WordArt:

WordArt is a program that allows you to treat text as a graphic. You can use the program to
add special effects and flourishes to text, and then insert the text—as a graphic—in your
worksheet. It is useful for creating special text elements such as logos, mastheads, or
titles.
 You an insert WordArt into Excel much like in ms-word. Click Insert >
WordArt from Text tab.
 Or select the WordArt icon in the Draw toolbar at the bottom of the
worksheet window.
 You will then see the WordArt gallery. Select a style and click OK.
 Another dialog box will open where you will enter your own text.
 Select a font and a font size, and then click the text box and begin typing.
Click OK when finished.
 When you insert WordArt into MS Excel, the WordArt toolbar will
automatically open. Just like the Picture toolbar, this gives us easy access to
all the functions associated with WordArt.

5
 Unit - II

 When we click it, the Edit WordArt Text dialog box reopens. If we wanted to
change our text, we'd simply select the Text box, make our changes, and click
OK.
 We can use Format button to see the Format WordArt dialog box.
 As you can see it is almost identical to the Format Picture box. From here you
can alter the WordArt's size, color, adjust properties, etc.
 Use the WordArt Shapes button to change the shape of your WordArt.
 Use the Same Letter Height button to make all of the characters in your text
the same height. The Vertical Text button, reorients the text so it runs
vertically.

Inserting AutoShapes:

 AutoShapes are basic shapes such as rectangles and circles and lines. They can
also be arrows, connectors, flowchart symbols, and stars.
 Click insert > shapes button in illustrations. Or Click the AutoShapes button in
the draw toolbar in the bottom left corner of Excel to see a list of AutoShape
categories.
 We will then drag our shape to the desire size and release the mouse button.
 After you insert an AutoShape Excel, you can manipulate it like any other
object. You can resize it, rotate it, and change the transparency and stroke
size.
 To change any modifications you can select Format button in drawing tools.
The Format AutoShape dialog is nearly identical to the Format Picture dialog.
 Click the Size tab to set sizing options. You can set a specific (absolute)
measurement, or you can set the measurement as a percentage of the

6
 Unit - II

document page or margin. On the Size tab, you can also set the shape’s
rotation and scale.
 If your AutoShape is an arrow, you can set the beginning style for the arrow and
the ending style. Depending on the styles that you select, you can also change the
size for the beginning and the end of the arrow.
 To fill your shape with a solid color, click the color that you want. To add a
multiple-colored background, click Fill Effects.
 If you fill an AutoShape with a picture, you adjust that picture by making
changes on the Picture tab of the Format AutoShape dialog box.

Adding Clip Art

In Office 2013 or Office 2016 there's no longer a clip art library, but Office still
helps you insert clip art.
 From the Insert tab, in the Illustrations group, click Online Pictures.
 In the Insert Pictures dialog box that appears:
o Type in your search word(s) and press enter.
o View the results of your search.
 Select a picture. Click on Insert.
 Resize the image and/or cell to fit the image into your worksheet.
 Format menu in picture tools will open when you select the picture, then you
can format the picture with layout, borders, size, color etc.

Creating a Hyperlink:

Hyperlinks are widely used on the Internet to navigate between web-sites. In your Excel
worksheets, you can easily create such links too. An Excel hyperlink is a reference
to a specific location, document or web-page that the user can

7
 Unit - II

jump to by clicking the link. Microsoft Excel enables you to create hyperlinks for
many different purposes including:
 Going to a certain location within the current workbook
 Opening another document or getting to a specific place in that document,
e.g. a sheet in an Excel file or bookmark in a Word document.
 Navigating to a web-page on the Internet or Intranet
 Creating a new Excel file
 Sending an email to a specified address

Hyperlinks in Excel are easily recognizable - generally this text is highlighted in

underlined blue.

The most common way to put a hyperlink directly into a cell is by using the
Insert Hyperlink dialog, which can be accessed in 3 different ways. Just select the
cell where you want to insert a link and do one of the following:
 On the Insert tab, in the Links group, click the Hyperlink
Right click the cell, and select Hyperlink… from the context menu.
 Press the Ctrl + K shortcut.
And now, depending on what sort of link you want to create, proceed with one of
the following examples:
 Hyperlink to another document
 Hyperlink to web-page (URL)
 Hyperlink to a new workbook
 Hyperlink to an email address

8
 Unit - II

Create hyperlink to another document

To insert a hyperlink to another document such as a different Excel file, Word document
or PowerPoint presentation, open the Insert Hyperlink dialog, and perform the steps
below:
1. On the left-hand panel, under Link to, click the Existing File or Web Page
2. In the Look in list, browse to the location of the target file, and then select the
file.
3. In the Text to display box, type the text you want to appear in the cell.
4. Optionally, click the ScreenTip… button in the upper-right corner, and enter
the text to be displayed when the user hovers the mouse over the hyperlink. In
this example, it's "Goto file3 in My Documents".
5. Click OK.

The hyperlink is inserted in the selected cell.

 To link to a specific sheet or cell, click the Bookmark… button in the right- hand
part of the Insert Hyperlink dialog box, select the sheet and type the

9
 Unit - II

target cell address in the Type in the cell reference box, and click OK.

Add a hyperlink to a web address (URL)

To create a link to a web page, open the Insert Hyperlink dialog, and proceed with
the following steps:
1. Under Link to, select Existing File or Web Page.
2. Click the Browse the Web button, open the web page you want to link to, and
switch back to Excel without closing your web browser.
3. Excel will insert the web site Address and Text to display for you
automatically. You can change the text to display the way you want, enter a
screen tip if needed, and click OK to add the hyperlink.
4. Alternatively, you can copy the web page URL before opening the Insert
Hyperlink dialog, and then simply paste the URL in the Address box.

Hyperlink to a sheet or cell in the current workbook

To create a hyperlink to a specific sheet in the active workbook, click the
Place in this Document icon. Under Cell Reference, select the target

10
 Unit - II

worksheet, and click OK.

To create an Excel hyperlink to cell, type the cell reference in the Type in the cell
reference box.

Insert a hyperlink to open a new Excel workbook

Besides linking to existing files, you can create a hyperlink to a new Excel file.
Here's how:
1. Under Link to, click the Create New Document icon.
2. In the Text to display box, type the link text to be displayed in the cell.
3. In the Name of new document box, enter the new workbook name.
4. Under Full path, check the location where the newly created file will be
saved. If you want to change the default location, click the Change button.
5. Under When to edit, select the desired editing option.
6. Click OK.

How to remove hyperlink in Excel

Removing hyperlinks in Excel is a two-click process. You simply right-click a link,
and select Remove Hyperlink from the context menu. This will
remove a clickable hyperlink, but keep the link text in a cell. To delete the link text
too, right-click the cell, and then click Clear Contents.

11
 Unit - II

Protecting a Workbook:
Excel gives you the ability to protect your work, whether it’s to prevent someone
from opening a workbook without a password, granting Read-Only access to a
workbook, or even just protecting a worksheet so you don’t inadvertently delete any
formulas.

Workbook-level: To control how users should work with worksheets inside your
workbook’s structure, use workbook-level protection. You can lock the structure of
your workbook by specifying a password. Locking the workbook structure prevents
other users from adding, moving, deleting, hiding, and renaming worksheets.
 To prevent other users from viewing hidden worksheets, adding, moving,
deleting, or hiding worksheets, and renaming worksheets, you can protect the
structure of your Excel workbook with a password.
 If you protect the workbook structure, users cannot insert, delete, rename, move,
copy, hide or unhide worksheets anymore.
1. Open a workbook.
2. On the Review tab, in the Changes group, click Protect Workbook. Or
click on file menu > info>protect workbook.
3. Check Structure, enter a password and click OK.
4. Reenter the password and click on OK.
5. Users cannot insert, delete, rename, move, copy, hide or unhide worksheets
anymore. If you forget or lose your password, you can’t retrieve it.

Worksheet-level: To control how users should work within an individual worksheet,

use worksheet-level protection. With sheet protection, you can

12
 Unit - II

control how a user can work within worksheets. You can specify what exactly a
user can do within a sheet, thereby making sure that none of the important data in
your worksheet are affected. For example, you might want a user to only add
rows and columns, or only sort and use AutoFilter. Once sheet protection is enabled,
you can protect other elements such as cells, ranges, formulas, and ActiveX or Form
controls.
 To protect a sheet in Excel perform the following steps.
 To password protect your Excel sheet, type a password in the corresponding field.
Be sure to remember the password or store it in a safe location because you will
need it later to unprotect the sheet.
 Right click a worksheet tab at the bottom of your screen and select
Protect Sheet… from the context menu. Or, click the Protect Sheet button
on the Review tab, in the Changes group.
 In the Protect Sheet dialog window, do the following:
 Select the actions you allow the users to perform.
o By default, the following 2 options are selected: Select locked cells
and Select unlocked cells. If you leave only these two options selected,
the users of your sheet, including yourself, will be able only to select
cells (both locked and unlocked).
o To allow some other actions such as sorting, filtering, formatting cells,
deleting or inserting rows and columns, check the corresponding boxes.
o If you don't check any action, users will only be able to view the
contents of your Excel sheet.
 Click the OK button.
 To unprotect a worksheet, Right-click the sheet tab, and select Unprotect
Sheet… from the context menu.

13
 Unit - II

Sharing a workbook:
These days more and more people are using Microsoft Excel for team work. We can
also export and share workbooks with others directly from Excel. The recent
versions of Excel 2010, 2013 and 2016 make it easy to share and collaborate on
workbooks. By sharing an Excel file, you are giving other users access to the
same document and allow them to make edit simultaneously. Here we will learn
how to share an Excel workbook for multiple users by saving it to a local network
location where other people can access it and make edits. You can keep track of
those changes and accept or reject them.

With the workbook open, perform the following steps to share it:

1. On the Review tab, in the Changes group, click the Share Workbook button.

2. The Share Workbook dialog box will appear, and you select the Allow
changes by more than one user at the same time. This also allows
workbook merging check box on the Editing tab.
3. Optionally, switch to the Advanced tab, select the desired settings for
tracking changes, and click OK.
For example, you may want to have changes updated automatically every n
number of minutes (all the other settings on the screenshot below are the
default ones).
4. Save you Excel file to a network location where other people can access it
(the fastest way is by using the Ctrl + S shortcut).

14
 Unit - II

5. If done correctly, the word [Shared] will appear to the right of the
workbook's name

6. When the teamwork has been completed, you can stop sharing the
workbook in this way:

Open the Share Workbook dialog box (Review tab > Changes group). On the
Editing tab, clear the Allow changes by more than one user at the same time…
check box, and click OK.

Track Changes in workbook:

When collaborating on an Excel workbook, you may want to keep track of the changes
that have been made to it. This could be especially useful when the document is
almost finished and your team is making the final revisions. In an Excel file, you
can review, accept or reject changes electronically by using the Track Changes
feature specially designed for it. By using the built-in Track Changes in Excel, you
can easily review your edits directly in the edited worksheet or on a separate sheet,
and then accept or reject each change individually or all changes at a time. To use the
Excel tracking feature most effectively, there are a few points for you to remember.
1. Track Changes is only available in shared workbooks
2. Track Changes cannot be used in workbooks that contain tables
3. It's not possible to undo changes in Excel
4. Not all changes are tracked in Excel
5. Change history is kept for 30 days by default

Turn on Excel Track Changes feature

To view the changes made to a given workbook by you or other users, perform these
steps:

15
 Unit - II

1. Be certain that you want to use this method before continuing. Shared
Workbooks have limitations, and therefore we highly recommend co-
authoring, which is the replacement for Shared Workbooks.
2. Click Review > Share Workbook.
Note that in newer versions of Excel, the Share Workbook button has been
hidden. Here's how to unhide it.
3. In the Share Workbook dialog box, on the Editing tab, select the Allow
changes by more than one user at the same time check box.
4. Click the Advanced tab.
5. Under Track changes, click Keep change history for and, in the days box,
type the number of days of change history that you want to keep. By default,
Excel keeps the change history for 30 days and permanently erases any change
history that is older than this number of days. To keep change history for a
longer than 30 days, enter a number that is larger than 30.
6. Click OK and, if you are prompted to save the workbook, click OK to save
the workbook.

Charts in Excel:
A chart is a tool you can use in Excel to communicate data graphically. Charts
allow your audience to see the meaning behind the numbers, and they make
showing comparisons and trends much easier. A chart is a visual representation of
data, in which the data is represented by symbols such as bars in a bar chart or
lines in a line chart.

To create charts for the data by below mentioned steps.

 Select the data for which you want to create the chart.

16
 Unit - II

 Choose Insert Tab » Select the chart or click on the Chart group to see
various chart types.
 Select the chart of your choice and click OK to generate the chart.

Chart Elements
The different parts that make up a chart are referred to as chart elements. Chart
elements give more descriptions to your charts, thus making your data more
meaningful and visually appealing.

Follow the steps given below to insert the chart elements in your graph.
Step 1 − Click the chart. Three buttons appear at the upper-right corner of the
chart.

Step 2 − Click the Chart Elements icon. A list of available elements will be
displayed.

The following chart elements are available −

Excel charts have a handful of elements. Some of these elements are displayed by
default, others can be added and modified manually as needed.

1. Chart area 6. Axis title

2. Chart title 7. Data points of the data series
3. Plot area 8. Chart legend
4. Horizontal (category) axis 9. Data label
5. Vertical (value) axis

17
 Unit - II

1. Chart area 6. Axis title

2. Chart title 7. Data points of the data series
3. Plot area 8. Chart legend
4. Horizontal (category) axis 9. Data label
5. Vertical (value) axis

 Chart Area: The chart area is everything within the outside border. This is the
area that makes up the chart. All charts have a chart area. This includes the plot
area and the space outside it. You need to select the chart area when you want
to make changes to the whole chart.
 Chart titles: When you create a chart, a Chart Title box appears above the chart.
 Plot Area: The plot area is the area which is enclosed by the two axis. This area
can have its own border as well as a background color. The plot area is everything
enclosed by the axes. This does not include titles, legend, etc. This is the area
enclosed by the two axes. All charts have a plot area.
 Axes: Charts typically have two axes that are used to measure and categorize the
data. A vertical axis (also known as value axis or y axis), and

18
 Unit - II

A horizontal axis (also known as category axis or x axis) 3-D Column charts have a
third axis, the depth axis.
 Axis titles: Axis titles give the understanding of the data of what the chart is
all about. You can add axis titles to any horizontal, vertical, or the depth axes in
the chart. You cannot add axis titles to charts that do not have axes (Pie or
Doughnut charts).
 Data Series: A data series is a collection of data points (or markers) and normally
corresponds to the data within a single row or column. This could be a series of
columns, bars or a series of squares or crosses joined together by a line.
 Data labels: Data labels make a chart easier to understand because they show the
details about a data series or its individual data points. You can change the
location of the data labels within the chart, to make them more readable.
 Legend: When you create a chart, the Legend appears by default. You can hide a
Legend by deselecting it from the Chart Elements list.

Creating Charts with Insert Chart

To create a chart in Excel, you start by entering the numeric data on a

worksheet, and then continue with the following steps.

1. Prepare the data to plot in a chart

 You can organize the data in rows or columns, and Microsoft Excel will
automatically determine the best way to plot the data in your graph

 The data in the first column (or columns headings) is used as labels along the
X axis of your chart.

 The numerical data in other columns are used to create the labels for the Y
axis.

19
 Unit - II

 Either the column headings or data in the first column are used in the chart
legend. Excel automatically chooses the data for the legend based on your
data layout.

2. Select data to include in the chart

 Select all the data you want to include in your Excel graph. Be sure to
select the column headings if you want them to appear either in the chart
legend or axis labels.
3. Inset the chart in Excel worksheet
 To add the graph on the current sheet, go to the Insert tab > Charts group, and
click on a chart type you would like to create.

To use the option Recommended Charts, follow the steps given below −
Step 1 − Select the data.
Step 2 − Click the Insert tab on the Ribbon. Step
3 − Click Recommended Charts.

Formatting Charts in Excel

Once you create a chart it's easy to format and enhance your chart using Excel's menus
and commands. To change chart style in Excel, simply right click or double click
on the chart item you want to format to view the formatting options for that item.
Just a few of the chart items you can format are:
 Chart Titles, Axis Titles, and Data Labels
 Chart Lines
 Axis Labels
 Plot Area / Chart Area

Overall, there are 3 ways to customize charts in Excel 2016 and Excel 2013.

20
 Unit - II

1. Select the chart and look for the needed options on the Chart Tools tabs on the
Excel ribbon.

2. Right-click an element on the chart and select the corresponding context menu
item. For example, here's the right-click menu for customizing the chart title:

3. Use on-object chart customization buttons. These buttons appear in the top
right corner of your chart as soon as you click on it.

Chart Elements button. It launches the checklist of all the elements you
can modify or add to your graph, and it only shows those elements that are
applicable to the selected chart type. The Chart Elements button supports
Live Preview, so if you are not sure what a certain element is, hover the mouse
on it and you will see what your graph would look like if you select that option.

Chart Styles button. It lets you quickly change the chart styles and colors.

Chart Filters button. It allows you to show or hide data displayed in your
chart.

21
 Unit - II

For more options, click the Chart Elements button, find the element you want to
add or customize in the checklist, and click the arrow next to it. The Format
Chart pane will appear on the right of your worksheet, where you can select the
options you want:

Format your chart using the Ribbon

1. In your chart, click to select the chart element that you want to format.
2. On the Format tab under Chart Tools, do one of the following:
 Click Shape Fill to apply a different fill color, or a gradient, picture, or texture to
the chart element.

 Click Shape Outline to change the color, weight, or style of the chart
element.

 Click Shape Effects to apply special visual effects to the chart element, such as
shadows, bevels, or 3-D rotation

3. To apply a predefined shape style, on the Format tab, in the Shape Styles
group, click the style that you want. To see all available shape styles, click the
More button

22
 Unit - II

4. To change the format of chart text, select the text, and then choose an option on
the mini toolbar that appears. Or, on the Home tab, in the Font group, select
the formatting that you want to use.

Types of Charts:

Excel provides you different types of charts that suit your purpose. Based on the type of
data, you can create a chart. You can also change the chart type later.
Excel offers the following major chart types −

 Column Chart
 Bar Chart
 Pie Chart
 Doughnut Chart
 Line Chart
 Area Chart
 XY (Scatter) Chart
 Bubble Chart
 Stock Chart
 Surface Chart
 Radar Chart

Column Chart

A Column Chart typically displays the categories along the horizontal (category) axis
and values along the vertical (value) axis. To create a column chart, arrange the
data in columns or rows on the worksheet.
A column chart looks as follows −

23
 Unit - II

Line Chart

Line charts can show continuous data over time on an evenly scaled Axis.
Therefore, they are ideal for showing trends in data at equal intervals, such as
months, quarters or years.
In a Line chart −
 Category data is distributed evenly along the horizontal axis.
 Value data is distributed evenly along the vertical axis.
To create a Line chart, arrange the data in columns or rows on the worksheet. A
line chart looks as follows:

24
 Unit - II

Pie Chart
Pie charts show the size of items in one data series, proportional to the sum of
the items. The data points in a pie chart are shown as a percentage of the whole pie.
To create a Pie Chart, arrange the data in one column or row on the worksheet.
A Pie Chart has the following sub-types −
 Pie
 3-D Pie
 Pie of Pie
 Bar of Pie

Doughnut Chart

A Doughnut chart shows the relationship of parts to a whole. It is similar to a Pie

Chart with the only difference that a Doughnut Chart can contain more than one
data series, whereas, a Pie Chart can contain only one data series. A Doughnut
Chart contains rings and each ring representing one data series. To create a
Doughnut Chart, arrange the data in columns or rows on a worksheet.

25
 Unit - II

Bar Chart

Bar Charts illustrate comparisons among individual items. In a Bar Chart, the
categories are organized along the vertical axis and the values are organized
along the horizontal axis. To create a Bar Chart, arrange the data in columns or
rows on the Worksheet.
A Bar Chart has the following sub-types −
 Clustered Bar, Stacked Bar, 100% Stacked Bar, 3-D Clustered Bar, 3-D
Stacked Bar, 3-D 100% Stacked Bar etc.

Area Chart

Area Charts can be used to plot the change over time and draw attention to the
total value across a trend. By showing the sum of the plotted values, an

26
 Unit - II

area chart also shows the relationship of parts to a whole. To create an Area Chart,
arrange the data in columns or rows on the worksheet.

XY (Scatter) Chart

XY (Scatter) charts are typically used for showing and comparing numeric values,
like scientific, statistical, and engineering data.
A Scatter chart has two Value Axes −
 Horizontal (x) Value Axis
 Vertical (y) Value Axis
It combines x and y values into single data points and displays them in irregular
intervals, or clusters. To create a Scatter chart, arrange the data in columns and
rows on the worksheet.
Place the x values in one row or column, and then enter the corresponding y values
in the adjacent rows or columns.
Consider using a Scatter chart when −
 You want to change the scale of the horizontal axis.
 You want to make that axis a logarithmic scale.
 Values for horizontal axis are not evenly spaced.

27
 Unit - II

 There are many data points on the horizontal axis.

 You want to adjust the independent axis scales of a scatter chart to reveal
more information about data that includes pairs or grouped sets of values.
 You want to show similarities between large sets of data instead of differences
between data points.
 You want to compare many data points regardless of the time.
o The more data that you include in a scatter chart, the better the
comparisons you can make.

28
 Unit - II

Bubble Chart
A Bubble chart is like a Scatter chart with an additional third column to specify
the size of the bubbles it shows to represent the data points in the data series.
A Bubble chart has the following sub-types −
 Bubble
 Bubble with 3-D effect

Surface Chart

A Surface chart is useful when you want to find the optimum combinations between
two sets of data. As in a topographic map, colors and patterns indicate areas
that are in the same range of values.
To create a Surface chart −
 Ensure that both the categories and the data series are numeric values.
 Arrange the data in columns or rows on the worksheet. A
Surface chart has the following sub-types −

29
 Unit - II

 3-D Surface
 Wireframe 3-D Surface
 Contour
 Wireframe Contour

Radar Chart

Radar charts compare the aggregate values of several data series. To create a Radar
chart, arrange the data in columns or rows on the worksheet.

Combo Chart

Combo charts combine two or more chart types to make the data easy to
understand, especially when the data is widely varied. It is shown with a

30
 Unit - II

secondary axis and is even easier to read. To create a Combo chart, arrange the
data in columns and rows on the worksheet.

Using Templates in Excel:

A template is a predesigned spreadsheet you can use to create new spreadsheets
with the same formatting and predefined formulas. With templates, you
don't need to know how to do the math, or even how to write formulas—these are
already integrated into the spreadsheet.
How to create a chart template
 In Excel 2013 and 2016, to save a graph as a chart template, right-click the
chart and pick Save as Template in the pop-up menu:
 Clicking the Save As Template option brings up the Save Chart Template
dialog, where you type the template name and click the Save
button.
 By default, the newly created chart template is saved to the special
Charts folder. All chart templates stored to this folder are automatically
added to the Templates folder that appears in the Insert Chart and Change
Chart Type dialogs when you create a new or modify an existing graph in
Excel.
How to apply the chart template

31
 Unit - II

 To create a chart in Excel based on a specific chart template, open the

Insert Chart dialog by clicking the Dialog Box Launcher in the
Charts group on the ribbon. On the All Charts tab, switch to the
Templates folder, and click on the template you want to apply.
 To apply the chart template to an existing graph, right click on the graph and
choose Change Chart Type from the context menu. Or, go to the
Design tab and click Change Chart Type in the Type group.
 Either way, the Change Chart Type dialog will open, you find the desired
template in the Templates folder and click on it.
How to delete a chart template in Excel
 To delete a graph template, open the Insert Chart dialog, go to the
Templates folder and click the Manage Templates button in the bottom left
corner.

32
 Unit - III

Unit – III
Data Sorting: Sorting by One Column, Sorting by Colors or Icons, Sorting by Multiple Columns, Sorting by a
Custom List.
Filtering Data: Creating a Custom AutoFilter, Using an Advanced Filter.
Data Outline: Group, Ungroup and Subtotals.
Tables: Creating a Table, Entering Data into a Table, Sorting Data into a Table, Using Filters to Sort Tables,
Data Tools: Data Validation, Consolidation

Data Sorting:

Sorting data is an integral part of Data Analysis. You can arrange a list of names in alphabetical
order, compile a list of sales figures from highest to lowest, or order rows by color or icons.
Sorting data helps you quickly visualize and understand your data better, organize and find the
data that you want, and ultimately make more effective decisions.
You can sort by columns or by rows. Most of the sorts that you use will be column sorts. You can
sort data in one or more columns by
 text (A to Z or Z to A)
 numbers (smallest to largest or largest to smallest)
 dates and times (oldest to newest and newest to oldest)
 a custom list (E.g. Large, Medium, and Small)
 format, including cell color, font color, or icon set

Sort by one Column

 To sort on one column, execute the following steps.
 Select the Column by which you want to sort data.
 To sort in ascending order, on the Data tab, in the Sort & Filter group, click AZ.
 To sort in descending order, on the Data tab, in the Sort & Filter group, click ZA.
 We can sort numerical data either ascending or descending order, we can also sort
alphabetical data in A to Z or Z to A.

Note: If you want to sort data based on a selected column, Choose Continue with the selection or if you
want sorting based on other columns, choose Expand Selection.

1
 Unit - III

The following table has information about employees in an organization.

Sort by more than one Column or Row

You can sort a table by more than one column or row.
 Click the Table.
 Click Sort.
 In the Sort dialog box, specify the column by which you want to sort first.
In the screen shot given below, Sort By Title, Sort On Values, Order A – Z are chosen.

 Click Add Level in the Sort dialog box. The Then By dialog appears.

2
 Unit - III

 Specify the column by which you want to sort next.

 In the screen shot given below, Then By HireDate, Sort On Values, Order Oldest to
Newest are chosen.
 Click OK.

The data will be sorted for Title in the ascending alphanumeric order and then by HireDate. You will
see the employee data sorted by title, and in each title category, in the seniority order.

Sort by Cell Color

To sort the table by the column total marks that contains cells with colors (Conditionally
Formatted)
 Click the Header of the column. Ex: Total Marks.

3
 Unit - III

 Click Data tab.

 In the Sort & Filter group, click Sort. The Sort dialog box appears.
 Choose Sort By as Total Marks, Sort on as Cell Color and specify the color green in
Order. Click Add Level.
 Choose Sort By as Total Marks, Sort on as Cell Color and specify the color Yellow in
Order. Click Add Level.
 Choose Sort By as Total Marks, Sort on as Cell Color and specify the color Red in Order.

The column – Total Marks will be sorted by the cell color as specified in the Order.

Sort by Font Color

To sort the column Total Marks in the table, that contains cells with font colors (conditionally
formatted) −
 Click the header of the column – Total Marks.
 Click Data tab.
 In the Sort & Filter group, click Sort. The Sort dialog box appears.
 Choose Sort By as Total Marks, Sort On as Font Color and specify the color green in
Order. Click Add Level.

4
 Unit - III

 Choose Sort By as Total Marks, Sort On as Font Color and specify the color yellow in
Order. Click Add Level.
 Choose Sort By as Total Marks, Sort On as Font Color and specify the color red in Order.

The column – Total Marks is sorted by the font color as specified in the Order.

How to Apply Icons to cells (Conditional Formatting)

You can use the icon sets to visualize numerical differences. The following icon sets are available

5
 Unit - III

As you observe, an icon set consists of three to five symbols. You can define criteria to associate an
icon with each value in a cell range. For example, a red down arrow for small numbers, a green up
arrow for large numbers, and a yellow horizontal arrow for intermediate values.
 Select the range to be conditionally formatted.
 Click Conditional Formatting in the Styles group under Home tab.
 Click Icon Sets from the drop-down menu. The Icon Sets options appear.
 Click the colored three arrows.
Colored Arrows appear next to the Data based on the Values in the selected range.

 Repeat the first three steps. The Icon Sets options appear.
 Select 5 Ratings. The Rating Icons appear next to the data based on the values in the
selected range.

Sort by Cell Icon

To sort the table by the column Total Marks that contains cells with Cell Icons (Conditionally
Formatted), follow the steps given below −
 Click the Header of the column – Total Marks.
 Click Data tab.
 In the Sort & Filter group, click Sort. The Sort dialog box appears.

 Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order. Click Add
Level.

6
 Unit - III

 Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order. Click Add
Level.

 Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order.

The column – Total Marks will be sorted by Cell Icon as specified in the Order.

Sort by a Custom List

You can create a custom list and sort the table by the custom list.
In the table given below, you find an indicator column with title – Position. It has the values high,
medium and low based on the position of total marks with respect to the entire range

7
 Unit - III

Now, suppose you want to sort the column - Position, with all High values on top, all low values
at bottom, and all medium values in between. That means the order you want is low, medium and
high. With Sort A to Z, you get the order high, low and medium. On the other hand, with
Sort Z to A, you get the order medium, low and high.

You can resolve this is to create a custom list.

 Define the order for the custom list as high, medium and low in a range of cells as shown
below.
 Select that Range.

 Click the File tab.

 Click Options. In the Excel Options dialog box, Click Advanced.

8
 Unit - III

 Scroll to the General.

 Click Edit Custom Lists.

The Edit Custom Lists dialog box appears. The select range in worksheet appears in the Import list from
cells Box. Click Import.
Your custom list is added to the Custom Lists. Click OK. The
next step is to sort the table with this Custom List.
 Click the Column – Position. Click on Sort. In the Sort dialog box, ensure Sort By is
Position, Sort On is Values.
 Click on Order. Select Custom List. Custom Lists dialog box appears.
 Click on the High, Medium, Low Custom List. Click on OK.

9
 Unit - III

In the Sort dialog box, in the Order Box, High, Medium, Low appears. Click on OK.

The table will be sorted in the defined order – high, medium, low.

You can create Custom Lists based on the following values −

 Text
 Number
 Date
 Time
You cannot create custom lists based on format, i.e. by cell / font color, or cell icon.

10
 Unit - III

Filtering the data:

Data filtering helps to display the rows that satisfy the criteria subject to specification of a
column. It is the easy way of finding the required data in a list.

For Ex: To show the data out the marks who got between 60% and 70%, then data filtering helps
to show the required data.

There are two commands for filtering data in M.S.Excel, Viz.

1. Auto Filters: It is used for filtering for simple criteria like the sorting of above 60% to 70%
marks of students. It quickly filters a data set based on selection, specified text, number or
other such criteria.

2. Advanced Filter: Excel's Advanced Filter is really helpful when it comes to finding data
that meets two or more complex criteria such as extracting matches and differences between
two columns, filtering rows that match items in another list, finding exact matches including
uppercase and lowercase characters, and more. For example, if the user wants to filter
according to age, qualification, place and highest marks of students, then the user need to
apply advanced filters.

Custom Auto Filter: Excel Auto Filter supports filtering of data in number filter and text filter. The
flowing are the steps to create a custom auto filter.

1. Click any single cell inside a data set.

2. On the Data tab, in the Sort & Filter group, click Filter.

Arrows in the column headers appear. Number

Filter
To apply a number filter, execute the following steps.
3. Click the arrow next to selected column.
4. Click Number Filters (this option is available because the Sales column contains
numeric data) and select custom filter option from the list.

11
 Unit - III

5. Enter filter criteria in terms of mathematical operations greater than, less than etc and
values from shows rows where block of custom auto filter dialog box and then click OK.

Text Filter:
1. Click any single cell inside a data set.
2. On the Data tab, in the Sort & Filter group, click Filter.

Arrows in the column headers appear. Text

Filter
To apply a number filter, execute the following steps.
3. Click the arrow next to selected column.
4. Click Text Filters (this option is available because the Last Name column contains text data)
and select Equals from the list.

12
 Unit - III

5. Enter filter criteria in terms equal, does not-equal, begins with, contains etc and values for
that criteria from “shows rows where” block of custom auto filter dialog box and then click OK.

We can also display records that begin with a specific character, end with a specific character,
contain or do not contain a specific character, etc. We can use make use of meta characters like:
? to represent single character in criteria
* to represent a series of zero or more characters in criteria. Ex:“?
a*”

To apply regular filter (Basic Filter) on data follow the given steps:

STEPS:
1. The data should include header row
2. Data Menu – Sort & Filter Ribbon – Filter
3. A drop-down arrow will appear in the header cell for each column.
4. Click the drop-down arrow for the column the user wants to filter. In our example, we
will filter column B to view only certain types of data.

5. The Filter menu will appear.

6. Uncheck the box next to Select All to quickly deselect all data.
7. Check the boxes next to the data to filter, then click OK. In this example, it get
displayed Laptop and Tablet to view only those types of equipment.

13
 Unit - III

8. The data will be filtered, temporarily hiding any content that doesn't match the
criteria.

9. Filtering options can also be accessed from the Sort & Filter command on the
Home tab.

To clear a filter:

After applying a filter, to remove—or clear—it from the worksheet:

1. Click the drop-down arrow for the filter to clear.

2. The Filter menu will appear.

3. Choose Clear Filter from [COLUMN NAME] from the Filter menu.
4. The filter will be cleared from the column. The previously hidden data will be
displayed.
5. To remove all filters from your worksheet, click the Filter command on the
Data tab.

Advanced filtering:
If the user need to filter for something specific, basic filtering may not give enough options. Excel
includes many advanced filtering tools, including search, text, date,
and number filtering, which can narrow the results to help find exactly. Advanced text filters
can be used to display more specific information, such as cells that contain a certain number
of characters, or data that excludes a specific word or number.

14
 Unit - III

Extracting a Unique list: You can use Excel Advanced Filter to quickly extract unique records
from a data set (or in other words remove duplicates). Let’s see how to use advanced filter to get a
unique list. Suppose you have a dataset as shown below:

As you can see, there are duplicate records in this data set (highlighted in orange). These could be
due to an error in data entry or result of data compilation. In such a case, you can use Excel
Advanced Filter tool to quickly get a list of all the unique records in a different location (so that
your original data remains intact). Here are the steps to get all the unique records:

 Select the entire data set (including the headers).

 Go Data tab –> Sort & Filter –> Advanced. This will open the Advanced Filter dialog box.

In the Advanced Filter dialog box, use the following details:

 Action: Select the ‘Copy to another location’ option. This will allow you to specify the location
where you can get the list of unique records.
 List Range: Make sure it refers to the dataset from which you want to find unique records.
Also, make sure headers in the data set are included.
 Criteria Range: Leave this empty.
 Copy To: Specify the cell address where you want to get the list of unique records.
 Copy Unique Records Only: Check this option.
 Click OK.

15
 Unit - III

This will instantly give you a list of all the unique records.

Using Criteria in Excel Advanced Filter

Getting unique records is one of the many things you can do with Excel advanced filter. Its primary
utility lies in its ability to allow using complex criteria for filtering data. Here is what I mean by
complex criteria. Suppose you have a dataset as shown below and you want to quickly get all the
records where the sales are greater than 5000 and the region is the US.

Here is how you can use Excel Advanced Filter to filter the records based on the specified criteria:

 The first step when using Excel Advanced Filter with complex criteria is to specify the criteria. To
do this, copy the headers and paste it somewhere in the worksheet.
 Specify the criteria for which you want to filter the data. In this example, since we want to get all
the records for the US with sales more than 5000, enter ‘US’ in the cell below Region and
>5000 in the cell below Sales. This would now be used as an input in Advanced Filter to get the
filtered data (as shown in the next steps).

16
 Unit - III

 Select the entire data set (including the headers).

 Go Data tab –> Sort & Filter –> Advanced. This will open the Advanced Filter dialog box. In
the Advanced Filter dialog box, use the following details:
 Action: Select the ‘Copy to another location’ option. This will allow you to specify the location
where you can get the list of unique records.
 List Range: Make sure it refers to the dataset from which you want to find unique records.
Also, make sure headers in the data set are included.
 Criteria Range: Specify the criteria we constructed in the steps above. In this example, it
would be F1:I3.
 Copy To: Specify the cell address where you want to get the list of unique records.
 Copy Unique Records Only: Check this option.
 Click OK.

This would instantly give you all the records where the region is the US and the sales are more
than 5000.

Data Outline:
Outlining Data
Outlining data makes your data easier to view. Outline includes grouping, ungrouping & subtotal.
With the help of outline data options the user can hide and unhide rows and columns for temporary
purpose and also to calculate automatic mathematical options with the help of subtotal.

Grouping the data:

With the help of grouping the data, the user can group the selected columns and rows. STEPS:

SUBTOTAL

1. First, sort the data on the Company column.

17
 Unit - III

2. . On the Data tab, in the Outline group, click Subtotal.

3. Select the Company column, the column we use to outline our worksheet.
4. Use the Count function.
5. Check the Company check box.
6. Click OK.

Result:

18
 Unit - III

7. To collapse a group of cells, click a minus sign. You can use the numbers to collapse or expand
groups by level. For example, click the 2 to only show the subtotals.

Note: click the 1 to only show the Grand Count, click the 3 to show everything. To
collapse a group of columns, execute the following steps.
8. For example, select column A and B.
9. On the Data tab, in the Outline group, click Group.

10. Click the minus sign above column C (it will change to a plus sign).
Result:

11. To remove the outline, click any cell inside the data set and on the Data tab, in the Outline
group, click Subtotal, Remove all.
19
 Unit - III

Outline (group) data in a worksheet:

If you have a list of data that you want to group and summarize, you can create an outline of up to
eight levels, one for each group. Each inner level, represented by a higher number in the outline
symbols, displays detail data for the preceding outer level, represented by a lower number in the
outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail
data for each group. You can create an outline of rows (as shown in the example below), an outline of
columns, or an outline of both rows and columns.

1. To display rows for a level,

click the appropriate
outline symbols.

2. Level 1 contains the total

sales for all detail rows.

3. Level 2 contains total sales

for each month in each region.

4. Level 3 contains detail rows

An outlined row of sales data grouped by geographical regions— in this case, rows 11 through 13.
and months with several summary and detail rows
displayed. 5. To expand or collapse data in
your outline, click
the and outline symbols.

Create an outline of rows

1. Make sure that each column of the data that you want to outline has a label in the first row,
contains similar facts in each column, and that the range has no blank rows or columns.
2. Make sure your detail rows also have a summary row—a subtotal. Do one of the following:

20
 Unit - III

Insert summary rows by using the Subtotal command

o Use the Subtotal command, which inserts the SUBTOTAL function immediately below or
above each group of detail rows and automatically creates the outline for you
o Insert your own summary rows
o Insert your own summary rows, with formulas, immediately below or above each group of
detail rows. For example, under (or above) the rows of sales data for March and April, use the
SUM function to subtotal the sales for those months. The table later in this topic shows you an
example of this.
3. Specify whether the summary rows are located below or above the detail rows. On the
Data tab, in the Outline group, click the Outline dialog box launcher.

4. If your summary rows are above your detail rows, clear the Summary rows below
detail checkbox. Otherwise, leave the checkbox alone.
5. Outline your data. Do one of the following:

Outline the data automatically

o If necessary, select a cell in the range of cells you want to outline.

o On the Data tab, in the Outline group, click the arrow under Group, and then click Auto
Outline.
o Outline the outer group.

Outline the data manually

Important: When you manually group outline levels, it's best to have all data displayed to
avoid grouping the rows incorrectly.

Ungroup:

 If you want to ungroup rows/columns, select the rows/columns, and then on the Data tab, in the
Outline group, click Ungroup.
 You can also ungroup sections of the outline without removing the entire outline. Hold down
SHIFT while you click the or for the group, and then on the Data tab, in the
Outline group, click Ungroup.

21
 Unit - III

 Important: If you ungroup an outline while the detail data is hidden, the detail rows may
remain hidden. To display the data, drag across the visible row numbers adjacent to the
hidden rows. On the Home tab, in the Cells group, click Format, point to Hide & UnHide,
and then click Unhide Rows.

Create an outline of columns

1. Make sure that each row of the data that you want to outline has a label in the first column,
contains similar facts in each row, and the range has no blank rows or columns.
2. Insert your own summary columns with formulas immediately to the right or left of each group
of detail columns. The table listed in step 4 below shows you an example.

Note: To outline data by columns, you must have summary columns that contain formulas
that reference cells in each of the detail columns for that group.

3. Specify whether the location of the summary column is to the right or left of the detail
columns. On the Data tab, in the Outline group, click the Outline dialog box launcher.

How to specify the summary column location

a. On the Data tab, in the Outline group, click the Outline dialog box launcher.

b. To specify a summary column to the left of the details column, clear the Summary columns to right
of detail check box. To specify a summary column to the right of the details column, select the
Summary columns to right of detail check box.
c. Click OK.
4. To outline the data, do one of the following:

Outline the data automatically

a. If necessary, select a cell in the range.

b. On the Data tab, in the Outline group, click the arrow below Group and click Auto Outline.

Outline the data manually

Important: When you manually group outline levels, it's best to have all data displayed to
avoid grouping columns incorrectly.

c. Outline the outer group.

22
 Unit - III

Show or hide outlined data

1. If you don't see the outline symbols , , and , click the Microsoft Office
Button and then click Excel Options (Excel 2007), OR, click the File tab (other versions), and then
click Options, click the Advanced category, and then under the Display options for this worksheet
section, select the worksheet, and then select the Show outline symbols if an outline is applied
check box.
2. Click OK.
3. Do one or more of the following:

Show or hide the detail data for a group

o To display the detail data within a group, click the for the group.
o To hide the detail data for a group, click the for the group.

Expand or collapse the entire outline to a particular level

o In the outline symbols, click the number of the level that you want. Detail data at lower
levels is then hidden.

For example, if an outline has four levels, you can hide the fourth level while displaying the rest of the
levels by clicking .

Show or hide all of the outlined detail data

o To show all detail data, click the lowest level in the outline symbols. For example, if
there are three levels, click .
o To hide all detail data, click .

Remove an outline

1. Click the worksheet.

2. One the Data tab, in the Outline group, click Ungroup and click Clear Outline.

Important: If you remove an outline while the detail data is hidden, the detail rows or columns
may remain hidden. To display the data, drag across the visible row numbers or column
letters adjacent to the hidden rows and columns. On the Home tab, in the Cells group,
click Format, point to Hide & Unhide, and then click Unhide Rows or Unhide Columns.

Tables in Excel:

In Excel 2007, and later versions, you can use the Table command to convert a list of
data into a formatted Excel Table. Tables have many features, such as sorting and
filtering that will help you organize and view your data. Tables allow you to analyze your

23
 Unit - III

data in Excel quickly and easily. An Excel Table makes an excellent source for a pivot
table, so you should use this feature if you plan to create a Pivot Table from the data.

Preparing Your Data

Before you create the formatted Excel Table, follow these guidelines for organizing your data.

 The data should be organized in rows and columns, with each row containing
information about one record, such as a sales order, or inventory transaction.
 In the first row of the list, each column should contain a short, descriptive and
unique heading.
 Each column in the list should contain one type of data, such as dates, currency, or
text.
 Each row in the list should contain the details for one record, such as a sales order. If
possible, include a unique identifier for each row, such as an order number.
 The list should have no blank rows within it, and no completely blank columns.
 The list should be separated from any other data on the worksheet, with at least one
blank row and one blank column between the list and the other data.

Creating an Excel Table

After your data is organized, as described above, you're ready to create the formatted Table.

1. Select a cell in the list of data that you prepared.

2. On the Ribbon, click the Insert tab.

24
 Unit - III

3. In the Tables group, click the Table command.

4. In the Create Table dialog box, the range for your data should automatically appear,
and the My table has headers option is checked. If necessary, you can adjust the
range, and check box.
5. Click OK to accept these settings.

Entering new data into an Excel Table

By default an Excel table will expand automatically, and fill formulas down to the last row.
For example:

 Add new data in the row immediately below a table, or in the column to its
immediate right, and the table expands automatically, to include that new data.
 Enter a formula in the first row of a blank column, that formula fills down to all the
remaining rows, as soon as you press Enter

Rename an Excel Table

When it is created, an Excel table is given a default name, such as Table 3. You should
change the name to something meaningful, so it will be easier to work with the table later.
To change the table name:

1. Select any cell in the table

2. On the Ribbon, under the Table Tools tab, click the Design tab.

3. At the far left of the Ribbon, click in the Table name box, to select the existing name

25
 Unit - III

4. Then, type a new name, such as Orders, and press the Enter key

Sort and Filter the Data

Your list is now an Excel Table, and is automatically formatted with a default Table
Style, which you can change.
The heading cells have drop down arrows that you can use to sort or filter the data.

Create an Excel Table with Specific Style

When you create a table with the Table command on the Ribbon's Insert tab, the table retains
any formatting that it currently has, and the default Table Style is applied.
If you want to apply a specific table style when creating an Excel Table:

1. Select a cell in the list of data that you prepared.

2. On the Ribbon, click the Home tab.
3. In the Styles group, click Format as Table
4. Click on the Style that you want to use
5. OR, to apply a Style and remove any existing formatting, right-click on a Style, and
click Apply and Clear Formatting.
6. In the Create Table dialog box, the range for your data should automatically appear,
and the My table has headers option is checked. If necessary, you can adjust the
range, and check box.
7. Click OK to accept these settings.

A formatted Excel Table is created, with the selected Table Style.

26
 Unit - III

Show Totals in a Table

After you create an Excel table, it's easy to show the total for a column, or for multiple
columns, using a built-in Table feature.
To show a total:

1. Select any cell in the table

2. On the Ribbon, under the Table Tools tab, click the Design tab
3. In the Table Style Options group, add a check mark for Total Row

Change and Add Totals

In addition to the automatically created totals, you can select totals for other columns.

1. Click in the Total cell for one of the columns.

2. From the drop down list, select the function that you want to use in the current
column.

Data Validation:

What is data validation in Excel?

Data Validation is a very useful and easy to use tool in Excel with which you can set data
validations on the data that is entered that is entered into your Worksheet. Excel Data
Validation is a feature that restricts (validates) user input to a worksheet. Technically, you
create a validation rule that controls what kind of data can be entered

27
 Unit - III

into a certain cell. In Excel, the data validation feature helps you control what can be entered
in your worksheet. For example, you can:
 Allow only numeric or text values in a cell.
 Allow only numbers within a specified range.
 Allow data entries of a specific
 Restrict dates and times outside a given time frame.
 Restrict entries to a selection from a drop-down list.
 Validate an entry based on another cell.
 Show an input message when the user selects a cell.
 Show a warning message when incorrect data has been entered

How to do data validation in Excel

To add data validation in Excel, perform the following steps.

1. Open the Data Validation dialog box

Select one or more cells to validate, go to the Data tab > Data Tools group, and click the
Data Validation button.

You can also open the Data Validation dialog box by pressing Alt > D > L, with each
key pressed separately.

2. Create an Excel validation rule

On the Settings tab, define the validation criteria according to your needs. In the
criteria, you can supply any of the following:

 Values - type numbers in the criteria boxes like shown in the screenshot below.
 Cell references - make a rule based on a value or formula in another cell.
 Formulas - allow to express more complex conditions like in this example.

28
 Unit - III

As an example, let's make a rule that restricts users to entering a whole number between
1000 and 9999:

With the validation rule configured, either click OK to close the Data
Validation window or switch to another tab to add an input message or/and error alert.

3. Add an input message (optional)

If you want to display a message that explains to the user what data is allowed in a given
cell, open the Input Message tab and do the following:

 Make sure the Show input message when cell is selected box is checked.
 Enter the title and text of your message into the corresponding fields.
 Click OK to close the dialog window.

As soon as the user selects the validated cell, the following message will show
up:

4. Display an error alert (optional)

In addition to the input message, you can show one of the following error alerts when invalid
data is entered in a cell.

 Alert in stop box

29
 Unit - III

 Alert Warning box

 Alert in Information box

To configure a custom error message, go to the Error Alert tab and define the following
parameters:

 Check the Show error alert after invalid data is entered box (usually selected by
default).
 In the Style box, select the desired alert type.
 Enter the title and text of the error message into the corresponding boxes.
 Click OK.

Date and time validation in Excel

To validate dates, select Date in the Allow box, and then pick an appropriate criteria in the
Data box. There are quite a lot of predefined options to choose from: allow only dates
between two dates, equal to, greater than or less than a specific date, and more.

Similarly, to validate times, select Time in the Allow box, and then define the required criteria.

For example, to allow only dates between Start date in B1 and End date in B2, apply this
Excel date validation rule:

To validate entries based on today's date and current time, make your own data validation
like:

Validate dates based on today's date: In many situations, you may want to use today's date as the
start date of the allowed date range. To get the current date, use

30
 Unit - III

The TODAY function, and then add the desired number of days to it to compute the
end date.

For example, to limit the data entry to 6 days from now (7 days including today), we are
going to use the built-in Date rule with the formula-based criteria:

 Select Date in the Allow

 Select between in the Data

 In the Start date box, enter =TODAY()

 In the End date box, enter =TODAY() + 6

Validate times based on current time: To validate data based on the current time, use the
predefined Time rule with your own data validation formula:

 In the Allow box, select Time.

 In the Data box, pick either less than to allow only times before the current time, or
greater than to allow times after the current time.

 In the End time or Start time box (depending on which criteria you selected on the
previous step), enter one of the following formulas:

 To validate dates and times based on the current date and time:
=NOW()

 To validate times based on the current time:

=TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

Text length
To allow data entry of a specific length, select Text length in the Allow box, and choose the
validation criteria in accordance with your business logic.

For example, to limit the input to 10 characters, create this rule:

31
 Unit - III

Excel data validation list (drop-down)

To add a drop-down list of items to a cell or a group of cells, select the target cells and do
the following:

1. Open the Data Validation dialog box (Data tab > Data Validation).
2. On the Settings tab, select List in the Allow
3. In the Source box, type the items of your Excel validation list, separated by commas.
For example, to limit the user input to three choices, type Yes, No, N/A.
4. Make sure the In-cell dropdown box is selected in order for the drop-down arrow to
appear next to the cell.
5. Click OK.

Note. Please be careful with the Ignore blank option, which is selected by default. If you
are creating a drop-down list based on a named range that has at least one blank cell,
selecting this check box allows entering any value in the validated cell. In many

32
 Unit - III

situations, it is also true for validation formulas: if a cell referenced in the formula is blank,
any value will be allowed in the validated cell.

Custom data validation rules

In addition to built-in Excel data validation rules, you can create custom rules with
your own data validation formulas. Here are just a few examples:

 Allow numbers only

 Allow text only
 Allow text beginning with specific characters
 Allow only unique entries and disallow duplicates

How to remove data validation in Excel

The standard approach designed by Microsoft and the mouse-free technique devised by Excel
geeks who would never take their hands off the keyboard unless absolutely necessary (e.g. to
take a cup of coffee :)

Method : Regular way to remove data validation

Normally, to remove data validation in Excel worksheets, you proceed with these steps:

1. Select the cell(s) with data validation.

2. On the Data tab, click the Data Validation button.
3. On the Settings tab, click the Clear All button, and then click OK.

Tips:

33
 Unit - III

1. To remove data validation from all cells on the current sheet, use the Find & Select
feature to select all of the validated cells.
2. To remove a certain data validation rule, select any cell with that rule, open
the Data Validation dialog window, check the Apply these changes to all other cells with
the same settings box, and then click the Clear All button.
As you see, the standard method is pretty fast but does require a few mouse clicks, no big
deal as far as I'm concerned. But if you prefer working with the keyboard over a mouse,
you may find the following approach appealing.

Data Consolidation:
Excel 2016 allows you to consolidate data from different worksheets into a single worksheet.
Using the program’s Consolidate command button on the Data tab of the Ribbon, you can
easily combine data from multiple spreadsheets. The beauty of the Consolidate feature is that it
can easily sum, count, average, etc. The ranges you consolidate do not have to be of the same
size in each worksheet, the number of rows or columns might be different from sheet to
sheet. And yet, you can still consolidate the data into a summary sheet.

For example, you can use the Consolidate command to total all budget spreadsheets prepared
by each department in the company or to create summary totals for income statements for a
period of several years. When you consolidate data in one worksheet, you can easily update
and combine it. However, even when the data entries are laid out differently in each
spreadsheet, Excel can still consolidate them provided that you’ve used the same labels to
describe the data entries in their respective worksheets.

Consolidate data in multiple worksheets within the same workbook

In our example, we have data for 3 years expenditure on tea, coffee and milk. The data is
broken down into quarters and stored in one year per worksheet in one workbook. We can
create a ‘Consolidated Summary’ sheet which will show expenditure by year and quarter. It
does not matter if the data has the same arrangement of columns and rows or not. Excel
will sort that out for you.

Year 1 worksheet

34
 Unit - III

Year 2 worksheet

Year 3 worksheet

The following are the steps to Consolidate:

Step1: To start using the Data Consolidation tool, you need to select an empty sheet in the
workbook as your master worksheet or add a new one if necessary. In this example the
worksheet is renamed ‘Consolidated Summary’.

Step2: Select the upper-left cell of the area where you want the consolidated data to appear.

Step3: On the Ribbon, Choose Data > Consolidate to view the Consolidate dialog:

Step4: In the Function box, click the summary function that you want Excel to use to
consolidate the data. As you will see from the drop-down, there are 11 functions to choose
from. For our data we want to add up the values so we’ll set the Function to Sum.

35
 Unit - III

Step5: Click in the Reference area and select the first data range to consolidate – to do this
you will need to click the Sheet tab i.e. “Year 1” and then drag over the data (including row
and column headings) and then click the Add button to add this first set of data to the
consolidation dialog.

Step6: Continue in the same way by clicking on the next sheet, highlighting the data, and
clicking on the Add button until all your data and worksheets appear in the
References section of the dialog e.g. “Year 2” and “Year 3”.

Note: You can name your ranges before you start the Consolidation process. If you name each
range then, when you create the consolidation, place your cursor in the Reference field, press
F3 and then choose the range from the list in the Paste Name dialog.

Step7: To indicate where the labels are located in the source ranges, select the check boxes
under Use labels in: either the Top row, the Left column, or both. In this example, Top
row is the name of the quarters, i.e. Quarter 1, Quarter 2, etc. and the Left Column are the
list of items, i.e. Coffee, Tea and Milk.

Automatic vs. Manual updates: If you want Excel to update your consolidation table
automatically when the source data changes, select the Create links to source data check box.
If unchecked, you can still update the consolidation manually.

Step8: When you click OK, Excel summarizes all the data into your new sheet as your master
worksheet (Consolidated Summary).

36
 Unit - III

Consolidate data from multiple workbooks to one new workbook

Step1: Make sure all the individual workbooks you wish to consolidate are currently open.

Step2: Open a new, blank workbook as your master worksheet or add a new one if necessary.
The worksheet is renamed as “Consolidate Summary” and save this workbook with a name
e.g. Summary.xlsx.

Step3: Select the upper-left cell of the area where you want the consolidated data to appear.

Step4: On the Ribbon, Choose Data > Consolidate to view the Consolidate dialog Step5:

selecting data ranges from different workbooks instead of different worksheets.

Step6: When you click OK, Excel summarizes all the data into your new master
worksheet (Consolidated Summary).

37
Advance Excel Unit - IV

Unit - IV

Formulas and functions: creating a formula, formula auditing, meaning and advantages of
functions, insert function.
Mathematical functions: sum, autosum, sumif, subtotal product, power, sqrt, round.
Statistical functions: average, averageif and averageifs, count, counta, countif, countifs, max min,
median, mode.
Date & time functions: date, now, day, year, month, time, today, weekday, date-value.
Look up & reference: vlookup & hlookup
Financial functions: rate, type, pv, fv, nper, pmt, ipmt, cumipmt, npv, irr. Names
in excel: defining names, using and managing defined names.

Function:

Functions are nothing but in-built formulae. They are the simplified formulas already
inserted in the computer. In other words, the simplified form of formula is function.

A function is a calculation or operation that returns a result. The inputs in a function

are called “arguments.”

All functions begin with an equals sign [=]. That way Excel knows not to treat the
arguments as text. For example, =AVERAGE(2,4) is a function but AVERAGE(2,4) is
just a string of text. Without an equals sign, Excel will not calculate a result. The
arguments in this function are 2 and 4.

Note, Excel uses upper-case letters to list functions, but you can use lower or upper- case
letters when you write them.

In Excel, the “Function Library” can be found on the “Formulas” tab.

SJC 1
Advance Excel Unit - IV

There are 13 categories of functions, some of which are:

 Mathematical: AVERAGE() – calculates the average of a series of numbers.

 Date and time: DATEVALUE() – converts a string of text like “30 November
2013” to a number so that you can use this number in other date and time functions.
You cannot do math with dates unless you convert them to numbers first. We explain
this in detail in Lesson 4.
 Text: LEN()– returns the length of a string. Ex: =LEN(“Excel”) is 5.
 Logical: IF() – the IF() function is written like =IF(<test>, then A, else B). So, if
“test” is true, then the result is A; if “test” is not true, then B.
 Lookup and Reference: These are needed to lookup values elsewhere in the
spreadsheet. Ex: VLOOKUP looks in a table of values to find one cell.

What is a Formula?

A formula is combination of “operators”, “operands”, and “functions.”

For example, the function =SUM adds a list of numbers (it is so commonly used, that is
listed on the first menu in Excel, abbreviated by the Greek letter Sigma (Σ), which is
the notation that mathematicians use to sum a series).

You use a formula like doing a calculation by hand. For example, you could put your
family budget into a formula like this:

Remaining cash = (4 * weekly salary) – mortgage – food – utilities

The operators are multiply [*] and subtract [-]. The operands are the values “weekly
salary”, “mortgage”, “food”, and “utilities.” The result is “remaining cash.”

SJC 2
Advance Excel Unit - IV

Creating Formulas:

Excel formula begins with the equal sign (=). So, whatever formula you are going to write,
begin by typing = either in the destination cell or in the Excel formula bar.

 Creating formulas in Excel by using constants and operators

 Writing formulas in Excel using cell references

 Creating Excel formula by using defined names

 Making a formula in Excel by using functions

 Formula by using constants and operators:

In Microsoft Excel formulas, constants are numbers, dates or text values that you enter
directly in a formula. To create a simple Excel formula using constants, just do the
following:

Select a cell where you want to output the result.

Type the equal symbol (=), and then type the equation you want to calculate. Press
the Enter key to complete your formula.
Here is an example of a simple subtraction formula in Excel: =100-50

 Formulas in Excel using cell references:

Instead of entering values directly in your Excel formula, you can refer to the cells,
containing those values. Ex: if you want to subtract a value in cell B2 from the value in
cell A2, you write the following subtraction formula: =A2-B2

Note: By default, Excel adds relative cell references. To switch to another reference type,
press the F4 key.

SJC 3
 Advance Excel Unit - IV

 A big advantage of using cell references in Excel formulas is that whenever you change a
value in the referred cell, the formula recalculates automatically without you having to
manually update all the calculations and formulas on your spreadsheet.
 Formula by using defined names:

 To take a step further, you can create a name for a certain cell or a range of cells, and
then refer to that cell(s) in your Excel formulas by simply typing the name.

 The fastest way to create a name in Excel, is to select a cell(s) and type the name
directly in the Name Box. For example, this is how you create a name for cell A2:

 A professional-like way to define a name is via the Formulas tab > Defined
names group or Ctrl+F3 shortcut.
For Ex: revenue for cell A2, expenses for cell B2
 And now, to calculate the net income, you can type the following formula in any cell on
any sheet within the workbook in which those names were created:=revenue- expenses

 Formulas by using functions:

 Excel functions are nothing else than predefined formulas that perform the
required calculations behind the scene.

SJC 4
 Advance Excel Unit - IV

 Each formula begins with an equal sign (=), followed by the function name and the
function arguments entered within the parentheses. Each function has specific
arguments and syntax (particular order of arguments).
 In your Excel spreadsheets, you can create a function-based formula in 2 ways:
1. By using the function wizard
2. By writing a formula in a cell or formula bar

1. Create a formula in Excel by using the Function Wizard

a) Run the function wizard.

To run the wizard, click the Insert Function button on the Formulas tab > Function
Library group, or pick a function from one of the categories:

 Alternatively, you can click the Insert Function button to the left of the formula bar.

 Or, type the equal sign (=) in a cell and pick a function from the drop-down menu to the left
of the formula bar. By default, the drop-down menu displays 10 most recently used
functions, to get to the full list, click More Functions...

b) Find the function you want to use.

 When the Insert Function wizard appears, you do the following:

 If you know the function name, type it in the Search for a function field and click Go.

SJC 5
 Advance Excel Unit - IV

 If you are not sure exactly what function you need to use, type a very brief description of
the task you want to solve in the Search for a function field, and click Go. For example,
you can type something like this: "sum cells", or "count empty cells".
 If you know what category the function belongs to, click the small black arrow next to
Select a category and choose one of the 13 categories listed there. The functions belonging
to the selected category will appear in the Select a function
 You can read a short description of the selected function right under the Select a function
box. If you need further details regarding that function, click the Help on this function
link near the bottom of the dialog box.
 Once you've found the function you wish to use, select it and click OK.

c) Specify the function arguments.

In the second step of the Excel function wizard, you are to specify the function's arguments.
Good news is that no knowledge of the function's syntax is required. You just enter the cell
or range references in the arguments' boxes and the wizard will take care of the rest.
To enter an argument, you can either type a cell reference or range directly into the box.
Alternatively, click the range selection icon next to the argument (or simply put the
cursor into the argument's box), and then select a cell or a range of cells in the
worksheet using the mouse. While doing this, the function wizard will shrink to a
narrow range selection window. When you release the mouse button, the dialog box
will be restored to its full size.

SJC 6
 Advance Excel Unit - IV

A short explanation for the currently selected argument is displayed right under

the function's description.

Excel functions allow you to perform calculations with cell residing on the same worksheet,
different sheets and even different workbooks. In this example, we are calculating the
average of sales for 2014 and 2015 years located in two different spreadsheets, which in
why the range references in the above screenshot include the sheet names. Find more about
how to reference another sheet or workbook in Excel.
As soon as you've specified an argument, the value or array of values in the selected cell(s)
will be displayed right to the argument's box.

d) Complete the formula.

When you have specified all the arguments, click the OK button (or just press the Enter
key), and the completed formula is entered into the cell.

2. Write a formula directly in a cell or formula bar

As you've just seen, creating a formula in Excel by using the function wizard is easy, though it's quite a long
multi-step process. When you have some experience with Excel formulas, you might like a faster way - typing a
function directly into a cell or formula bar.

 As usual, you start by typing the equal sign (=) followed by the function name. As you
do this, Excel will perform some kind of incremental search and display a list of

SJC 7
 Advance Excel Unit - IV

functions that match the part of the function's name you've already typed:

 So, you can either finish typing the function name on your own or select from the
displayed list. Either way, as soon as you type an opening parenthesis, Excel will show
the function screen tip highlighting the argument you need to enter next. You can type
the argument in the formula manually, or click a cell (select a range) in the sheet and
have a corresponding cell or range reference added to the argument.

 After you've input the last argument, type the closing parenthesis and hit Enter to
complete the formula.

II. FORMULA AUDITING:

 Formula auditing in excel is a fundamental part of spreadsheets, and anyone who works
on spreadsheets regularly should make use of this tool.
 It helps to visualize what cells are being used. Formula auditing in Excel allows you
to display the relationship between formulas and cells.
 Excel Formula Auditing commands provide you an easy way to find
o Which cells are contributing in the calculation of a formula in the active cell.
o Which formulas are referring to the active cell.
 These findings are shown graphically by arrow lines that makes the visualization easy.
You can display all the formulas in the active worksheet with a single command. If
your formulas refer to cells in a different workbook, open that workbook also. Excel
cannot go to a cell in a workbook that is not open.

SJC 8
 Advance Excel Unit - IV

 This group has the collection of various such tools. Because these are necessary for
the checking purposes. The example below helps you master Formula Auditing
quickly and easily. If you click on Formulas – formula auditing ribbon you will find
the following options:

Formula Auditing options:

 Trace Precedents
 Trace Dependents
 Remove Arrows
o Remove-Precedent Arrows
o Remove-Dependent Arrows
 Show Formulas
 Error Checking
o Trace Error
o Circular References
 Evaluate Formula
 Watch Window

Trace Precedents - which cells using this formula?

Precedent cells are those cells that are referred to by a formula in the active cell. The Trace
Precedents tool shows the arrows indicating which cells affect the value of the selected cell.

Ex: In the following example, the active cell is C2. In C2, you have the formula
=B2*C4.
B2 and C4 are precedent cells for C2.

To trace the precedents of the cell C2,

SJC 9
Advance Excel Unit - IV

 Click in the cell C2.

 Click the Formulas tab.
 Click Trace Precedents in the Formula Auditing group.

Two arrows, one from B2 to C2 and another from C4 to C2 will be displayed, tracing the
precedents.

Note that for tracing precedents of a cell, the cell should have a formula with valid
references. Otherwise, you will get an error message.
Removing Arrows

To remove the arrows, execute the following steps. The already drawn arrows by the
Trace Precedents; and the Trace Dependents buttons is removed by the Remove Arrows
button. Also, this button has the Remove Precedent Arrows and the Remove Dependent
Arrows buttons.
 On the Formulas tab, in the Formula Auditing group, click Remove Arrows.
Result: All the arrows in the worksheet will disappear.

SJC 10
 Advance Excel Unit - IV

Tracing Dependents - which formulas use this cell?

Dependent cells contain formulas that refer to other cells. That means, if the active cell
contributes to a formula in another cell, the other cell is a dependent cell on the active cell.
The Trace Dependents button shows the arrows, indicating which cells have been
affected by the value of selected cell.
In the example below, C2 has the formula =B2*C4. Therefore, C2 is a dependent cell on the
cells B2 and C4

To trace the dependents of the cell B2,

 Click in the cell B2.

 Click the Formulas tab.
 Click Trace Dependents in the Formula Auditing group.

An arrow appears from B2 to C2, showing C2 is dependent on B2.

To trace the dependents of the cell C4 −

SJC 11
Advance Excel Unit - IV

 Click in the cell C4.

 Click the Formula tab > Trace Dependents in the Formula Auditing group.
Another arrow appears from C4 to C2, showing C2 is dependent on C4 also.

Click Remove Arrows in the Formula Auditing group. All the arrows in the
worksheet will disappear.
Note − For tracing dependents of a cell, the cell should be referenced by a formula in
another cell. Otherwise, you will get an error message.

Show Formulas:
By default, Excel shows the results of formulas. To show the formulas instead of their results,
execute the following steps. This button will show formulas in cells instead of results of
formulas. In this example it will looks like this:

Evaluating a Formula:
To find how a complex formula in a cell works step by step, you can use Evaluate Formula
command.
To debug a formula by evaluating each part of the formula individually, execute the
following steps.
1. Select cell C13.
2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

SJC 12
 Advance Excel Unit - IV

3. Click Evaluate four times.

In the Evaluate Formula dialog box, the formula is displayed in the box under Evaluation.
By clicking the Evaluate button several times, the formula gets evaluated step-wise.
Error Checking
It is a good practice to do an error check once your worksheet and/or workbook is ready with
calculations.
Consider the following simple calculations.

The calculation in the cell has resulted in the error #DIV/0!.

 Click in the cell C5.
 Click the FORMULAS tab on the Ribbon.
 Click the arrow next to Error Checking in the Formula Auditing group. In the
drop-down list, you will find that Circular References is deactivated, indicating
that your worksheet has no circular references.
 Select Trace Error from the drop-down list.

SJC 13
 Advance Excel Unit - IV

The cells needed to compute the active cell are indicated by blue arrows.

 Click Remove Arrows.

 Click the arrow next to Error Checking.
 Select Error Checking from the drop-down list.

The Error Checking dialog box appears.

Observe the following −
 If you click Help on this error, Excel help on the error will be displayed.
 If you click Show Calculation Steps, Evaluate Formula dialog box appears.
 If you click Ignore Error, the Error Checking dialog box closes and if you
click Error Checking command again, it ignores this error.

SJC 14
 Advance Excel Unit - IV

 If you click Edit in Formula Bar, you will be taken to the formula in the
formula bar, so that you can edit the formula in the cell.

III. FUNCTIONS: MEANING AND ADVANTAGES OF FUNCTIONS

Fu n c t i o n s

A function is a built in formula. There are over 400 built in functions in Excel
that can be accessed through the Insert Function button on the Formula Bar. The power
of Excel, however, is to do more complicated calculations, including calculations for the
cell itself, no matter what the content and more complex formulas (such as averages, sums,
etc., beyond basic math).

Understanding How Functions Work

Excel uses straightforward formulas to perform simple calculations, such as adding or
subtracting, on a number or series of numbers. For example, the formula
=SUM(B4:B8) inserts the sum of the numbers contained in the range B4 to B8 into the
cell containing the formula. These simple formulas are the foundation of many functions.
Other functions combine several formulas or procedures to achieve a desired result.
Functions should be entered in the following basic order:
1. Start a function with an equal sign (=).
2. Enter the function name.
3. Include information about a cell or range of cells to be analyzed.
4. Enter arguments about what to do with the selected range of cells.

Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name,
parenthesis, comma separators, and arguments.

Like all formulas, functions begin with the equal sign ( = ) followed by the function's name and
its arguments:

SJC 15
Advance Excel Unit - IV

 The function name tells Excel what calculations to perform;

 The arguments are contained inside parentheses or round brackets and tell the
function what data to use in those calculations.

For example, one of the most used functions in Excel and Google Sheets is the SUM
function:

= SUM ( D1 : D6 )

In this example,

 the name tells Excel to add together the data in selected cells;
 the argument (D1:D6) function adds the contents of cell range D1 to D6.

Advantages of Functions:
 Functions are tools you can use to analyze data and get information. In other words,
functions help answer your questions so that you can evaluate and examine your
business and make projections.
 Excel uses straightforward formulas to perform simple calculations.

 One of the key benefits of using Excel functions is that they help simplify your
formulas.
 Another key benefit of using functions is that they help you accomplish tasks that
would be impossible with standard formulas. For instance, imagine that you
wanted a couple of formulas that would automatically returns the largest and
smallest numbers in a range of cells. Sure, if your range was small enough, you
could eyeball the largest and smallest numbers. But that’s hardly automated.
 Functions can also help save time by helping you automate tasks that would take you
hours to accomplish manually. For example, say that you needed to extract the first
10 characters of a customer number.

MATHEMATICAL FUNCTIONS:

Sum( ): This function helps to calculate the sum of the given numbers or cell references.

SJC 16
 Advance Excel Unit - IV

Syntax: =sum(number1,number2…)

For Ex: =sum(A1:A23) will return the sum of the values in cells A1 to A23

Auto sum: Simplest way of adding, subtracting numbers is known auto sum. It will be
displayed on the home menu. Just click on the auto sum option and automatically it get
select, click on ok.

 Home – editing ribbon - autosum

Sumif( ): It will add the numbers for specified criteria The Excel SumIf function finds the
values in a supplied array, that satisfy a given criteria, and returns the sum of the
corresponding values in a second supplied array.

Syntax: =sumif(range, criteria,[sum range]) For Ex:

A B C D

1 Date State Name Amount

2 1/5/2009 MI John 2

3 1/6/2009 NY Peter 6

4 1/7/2009 PA John 8

5 1/5/2009 NH Peter 3

6 1/6/2009 MI John 4

=SUMIF(C2:C7,"John"
7 1/7/2009 FL,D2:D7)Peter 6
or
=SUMIF(C2:C7,C2 ,D2:D7) It will display as 14

=SUMIF(D2:D7,">5",D2:D7) It will display as 20

SJC 17
Advance Excel Unit - IV

Subtotal( ): Microsoft defines Excel SUBTOTAL as the function that returns a subtotal in a list or
database. In this context, "subtotal" is not just totaling numbers in a defined range of cells. Unlike other
Excel functions that are designed to do only one specific thing, SUBTOTAL is amazingly versatile - it
can perform different arithmetic and logical operations such as counting cells, calculating average,
finding the minimum or maximum value, and more. The Excel SUBTOTAL function returns an
aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and
others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.

Syntax: =SUBTOTAL (function number, ref1, [ref2], ...)

Where:

 Function number - a number that specifies which function to use for the subtotal.
 Ref1, Ref2, … - one or more cells or ranges to subtotal. The first ref argument is required,
others (up to 254) are optional.

The function number argument can belong to one of the following sets:

 1 - 11 ignore filtered-out cells, but include manually hidden rows.

 101 - 111 ignore all hidden cells - filtered out and hidden manually.

Function
Function Description
number

1 101 AVERAG Returns the average of numbers.

E

2 102 COUNT Counts cells that contain numeric values.

3 103 COUNTA Counts non-empty cells.

4 104 MAX Returns the largest value.

5 105 MIN Returns the smallest value.

6 106 PRODUC Calculates the product of cells.

T

SJC 18
Advance Excel Unit - IV

Function
Function Description
number

Returns the standard deviation of a population based on a sample of numbers.

7 107 STDEV

8 108 STDEVP Returns the standard deviation based on an entire population of numbers.

9 109 SUM Adds up the numbers.

10 110 VAR Estimates the variance of a population based on a sample of numbers.

Estimates the variance of a population based on an entire population of

11 111 VARP
numbers.

For example, this is how you can make a Subtotal 9 formula to sum up the values in cells C2 to C8:

To add a function number to the formula, double-click on it, then type a comma, specify a range, type
the closing parenthesis, and press Enter. The completed formula will look like this:

=SUBTOTAL(9,C2:C8)

SJC 19
Advance Excel Unit - IV

In a similar manner, you can write a Subtotal 1 formula to get an average, Subtotal 2 to count cells with
numbers, Subtotal 3 to count non-blanks, and so on. The following screenshot shows a few other
formulas in action:

Note. When you use a Subtotal formula with a summary function like SUM or AVERAGE, it
calculates only cells with numbers ignoring blanks and cells containing non-numeric values .

Product( ): PRODUCT function returns the product (multiplication) of a supplied set of

numerical values.

Syntax: =PRODUCT( number1, [number2], ... )

For ex: =PRODUCT(A1, A2)

Power( ): Power function calculates a given number, raised to a supplied power.

Syntax: =POWER( number, power )

where the number and power arguments are numeric values. Note that the Excel Power
function is the same as using the power operator ^.

For Ex: =POWER( 4, 2 ) is the same as 4^2. i.e 16

Sqrt( ):The Excel Sqrt function calculates the positive square root of a supplied
number.

Syntax: =sqrt( number )

SJC 20
Advance Excel Unit - IV

where the number argument is the numeric value that you want to find the square root
of.

For Ex: =sqrt(4) i.e 2

Round( ): The Microsoft Excel ROUND function returns a number rounded to a

specified number of digits.

The ROUND function is a built-in function in Excel that is categorized as Math/Trig

Function. It can be used as a worksheet function (WS) in Excel. As a worksheet
function, the ROUND function can be entered as part of a formula in a cell of a
worksheet.

Syntax: =ROUND( number, digits )

For Ex: =ROUND(-23.67, 1) Result: -23.7

Statistical functions
Statistical functions: average, averageif and averageifs, count, counta, countif,
countifs, max, min, median, mode.

AVERAGE:

The AVERAGE function will average cells that contain numbers.

Its syntax is: =AVERAGE(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the AVERAGE
formula.
The following AVERAGE function example uses only one argument - a reference to
cells A1:A5.

1. Enter the sample data on your worksheet

2. In cell A7, enter an AVERAGE formula, to average the numbers in column
A: =AVERAGE(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 21.83, the average of the cells that contain numbers.
Cell A1 isn't included in the average, because it contains text, and the empty cell
C3, is ignored.

SJC 21
Advance Excel Unit - IV

AVERAGEA:

The AVERAGEA function will average the cells that are not empty.
Its syntax is: =AVERAGEA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the formula. The
following example uses one argument -- a reference to cells A1:A5.

1. Enter the sample data on your worksheet

2. In cell A7, enter a AVERAGEA formula, to average the numbers in column
A: =AVERAGEA(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 16.38, the average of the cells that contain data. Cell A1
contains text, which is treated as a zero, and cell A3 is not included in the
average, because it is blank.

Note: AVERAGEA will include cells with formulas — including those that look
empty, because they evaluate to "", e.g. =IF(B2="","",B2).

AVERAGEIF: Match criterion exactly

In Excel, average cells that meet a specific criterion. In this example only the
quantities for the Pen orders will be averaged.

SJC 22
Advance Excel Unit - IV

1. Select the cell in which you want to see the average (cell A12 in this example)

2. The completed formula is: =AVERAGEIF(A1:A10,"Pen",B1:B10)

3. Press the Enter key to complete the entry
4. The result will be 9.25, the average quantity for rows that contain "Pen"

AVERAGEIFS: Match multiple criteria

In Excel 2007 and later versions, you can use the AVERAGEIFS function to average
rows that meet two or more criteria. In this example only the rows where the item is
"Pen" and the quantity is greater than or equal to ten will be averaged.
The criteria have been entered in cell D3 and E3. We'll refer to those cells in the
formula, instead of typing the values in the formula.
The completed formula is:
=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,">=" & E3)

Note: Instead of referring to cells for the criteria, you can type them into the formula.
Here is the same formula, with typed criteria:
=AVERAGEIFS(B2:B10,A2:A10,"Pen",B2:B10,">=10")
COUNT:
The COUNT function will count cells that contain numbers.
Its syntax is:
=COUNT(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the COUNT formula.
The following COUNT function example uses one argument -- a reference to cells
A1:A5.
 Enter the sample data on your worksheet

SJC 23
 Advance Excel Unit - IV

 In cell A7, enter an COUNT formula, to count the numbers in column

A: =COUNT(A1:A5)
 Press the Enter key, to complete the formula.
 The result will be 3, the number of cells that contain numbers.
Cell A1 isn't counted, because it contains text.

COUNTA:
The COUNTA function will count cells that are not empty. (See note on blank cells
below)
Its syntax is:
=COUNTA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the formula. The
following example uses one argument -- a reference to cells A1:A5.
1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTA formula, to count the numbers in column
A: =COUNTA(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 4, the number of cells that contain data.

COUNTBLANK:
The COUNTBLANK function will count cells that are empty. Its
syntax is: =COUNTBLANK(range).
The following example uses a reference to cells A1:A5.
1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTBLANK formula, to count the numbers in column
A: =COUNTBLANK(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 1, the number of empty cells.

COUNTIF:
In Excel, count cells that meet a specific criterion. In this example only the Pen orders
will be counted.
1. Select the cell in which you want to see the count (cell A12 in this example)

SJC 24
 Advance Excel Unit - IV

2. Select the cells that contain the values to check for the criterion. In this
example, cells A1:A10 will be checked. Type a comma, to separate the
arguments
3. Type the criterion. In this example, you're checking for text, so type the word in
double quotes: "Pen"
Note: upper and lower case are treated equally
4. The completed formula is: =COUNTIF(A1:A10,"Pen")
5. Press the Enter key to complete the entry
6. The result will be 4, the number of cells that contain "Pen"

Match criterion in a string

In Excel, count cells that contain a criterion as part of the cell's contents. In this example
all Pen, Gel Pen, and Pencil orders will be counted, because they contain the string
"pen"
The completed formula is: =COUNTIF(A1:A10,"*Pen*")
The result will be 6, the number of cells that contain the string, "Pen”

COUNTIFS - Count Cells that Match multiple criteria

In Excel you can use the COUNTIFS function to count rows that meet two or more criteria.
In this example only the rows where the item is "Pen" and the quantity is greater than or
equal to ten will be counted.
1. Select the cell in which you want to see the total
2. Select the cells that contain the values to check for the first criterion. In this
example, cells A2:A10will be checked. Use the following formula:

SJC 25
 Advance Excel Unit - IV

=COUNTIFS(A2:A10,D3,B2:B10,">=" & E3)

Or
Use typed criteria:
=COUNTIFS(A2:A10,"Pen",B2:B10,">=10")

MEDIAN: The Microsoft Excel MEDIAN function returns the median of the numbers
provided. Median is a function which is used to find the middle number in a given range of
numbers. When you are finding median manually, you need to sort the data in an ascending
order but in Excel, you can simply use the Median function and select the range and you
will find the median.

Syntax: MEDIAN( number1, [number2, ... number_n] )

=MEDIAN(A2, A3)
Result: 8.85
=MEDIAN(A2:A6)
Result: 8.1
=MEDIAN(1, 3, 13, 14, 15)
Result: 13

SJC 26
Advance Excel Unit - IV

MODE: The Microsoft Excel MODE function returns the most frequently occurring
number found in a set of numbers.
Syntax
The syntax for the MODE function in Microsoft Excel is:
MODE( number1, [number2, ... number_n] )
Parameters or Arguments: number1, number2, ... number_n
Each number can be a range, a cell or a numeric value. There can be up to 255
numbers.
Returns:The MODE function returns a numeric value.

=MODE(B2:H2)
Result: 85
=MODE(B2,C2,D2,E2,F2,G2,H2)
Result: 85
=MODE(B4:H4)
Result: #N/A 'No repeating number is found

MIN: To find the minimum value, use the MIN function.

To find the lowest value in a range of cells, use the MIN function. For example, this
formula will find the lowest value in cells H2:H17
=MIN(H2:H17)

SJC 27
Advance Excel Unit - IV

MAX: To find the maximum value, use the MAX function.

To find the highest value in a range of cells, use the MAX function. For example,
this formula will find the highest value in cells H2:H17
=MAX(H2:H17)

LARGE: The Microsoft Excel LARGE function returns the nth largest value from a
set of values.
Syntax: LARGE( array, nth_position )
Where:
array: A range or array from which you want to return the nth largest value.
nth_position: The position from the largest to return.
Returns: The LARGE function returns a numeric value.
If nth_position is larger than the number of values in array, the LARGE function
will return the #NUM! error.
If array is empty, the LARGE function will return the #NUM! error.

=LARGE(A1:A5, 1)
Result: 32
=LARGE(A1:A5, 2)
Result: 8
=LARGE({6, 23, 5, 2.3}, 2)
Result: 6

SMALL: he Microsoft Excel SMALL function returns the nth smallest value from a
set of values.
Syntax: SMALL( array, nth_position )
Where:
array: A range or array from which you want to return the nth smallest value.
nth_position: The position from the smallest to return.
Returns: The SMALL function returns a numeric value.
Ex: =SMALL(A1:A5, 1)

SJC 28
Advance Excel Unit - IV

Result: -2.3
=SMALL(A1:A5, 2)
Result: 4

Creating Dates & Times

DATE Returns a date, from a user-supplied year, month and day
Syntax:=date(year,month,day)
For ex: =date(2012,5,12)
TIME Returns a time, from a user-supplied hour, minute and second
=time(hour,minute,second)
DATEVALU Converts a text string showing a date, to an integer that
E represents the date in Excel's date-time code.
Syntax:=datevalue(date_text)

Current Date & Time

NOW Returns the current date & time. For
Ex: =now()
it display date and as well as time
TODAY Returns today's date. For
Ex: type =today() it gives current date

Extracting The Components of a Time

MINUTE Returns the minute part of a user-supplied time

Syntax:=minute(serial_number)
=MINUTE("12:34:45 PM"). It display 34
SECOND Returns the seconds part of a user-supplied time Syntax:
=second(serial_number)
For ex: =second(“10:33:35 PM”) it displays 35

Extracting The Components of a Date

DAY Returns the day (of the month) from a user-supplied date
Syntax:=day(serial_number)
For ex: =DAY("2/12/2017").

SJC 29
Advance Excel Unit - IV

It will display as 12
MONTH Returns the month from a user-supplied date
Syntax:=month(serial_number)
For ex: =month("2/12/2017"). It
will display 2
YEAR Returns the year from a user-supplied date
Syntax:=year(serial_number)
For ex: =year("2/12/2017"). It
will display 2017

WEEKDAY Returns an integer representing the day of the week for a

supplied date
Syntax: =day(serial_number)
For ex: =WEEKDAY("3/22/2017")

Lookup & Reference Functions:

These functions help you to work with arrays of data, and are particularly useful when you
need to cross reference between different data sets.

Vlookup:
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and
then returns a value in the same row from another column you specify. When you need to find
things in a table or a range by row. For example, look up a price of an automotive part by the
part number.

In its simplest form, the VLOOKUP function says:

=VLOOKUP(Value you want to look up, range where you want to lookup the value, the
column number in the range containing the return value, Exact Match or Approximate
Match – indicated as 0/FALSE or 1/TRUE).

The syntax of the function is:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup] )

where the arguments are as follows:

SJC 30
 Advance Excel Unit - IV

lookup_value - The value that you want to look up.

table_array - The data array or table, containing the search values in the left
hand columns and the return values in another column.

col_index_num - The column number, within the supplied table_array, that you want to
return a value from.

[range_lookup] - An optional logical argument, that describes what the function

should return in the event that it does not find an exact match to
the lookup_value.
The [range_lookup] can be set to TRUE or FALSE, meaning:

TRUE - If the function cannot find an exact match to

the supplied lookup_value, it should use the
closest match below the supplied value.
Note: if this option is used, the left-hand
column of the table_array must be in
ascending order.
FALSE - If the function cannot find an exact match
to the supplied lookup_value, it should
return an error.

Vlookup Examples: Example 1 - Vlookup Requiring an Exact Match

A B C D E

1 Item Description Cost ($) Current Item: Current Item Cost ($)

2 Tinned Tomatoes $0.90 Cornflakes =VLOOKUP( D2, A:B, 2, - returns the value $3.50
FALSE )
3 Tinned Tuna $1.50

4 Cornflakes $3.50

5 Shortcake Biscuits $1.00

SJC 31
 Advance Excel Unit - IV

6 Toothpaste $4.10

7 Tinned Baked Beans $0.99

8 White Sliced Bread $0.80

. .
. .
9 . .

Columns A and B of the spreadsheet below list an inventory of grocery items and their prices. In cell E2
of the spreadsheet, the Vlookup function is used to look up the price of an item from the inventory.

The above Vlookup function returns the price for "Cornflakes", which is $3.50.

In this example:
 The lookup_value is the text string "Cornflakes", which is located in cell D2;
 The table_array is defined as columns A-B of the spreadsheet;
 The col_index_num is set to 2, to denote that the value returned should be taken from column 2 of
the table_array;
 The [range_lookup] argument is set to FALSE, to indicate that we only want a result to be returned if an exact
match to the lookup_value is found.

Ex2:

SJC 32
Advance Excel Unit - IV

HLOOKUP function: (Horizontal Lookup)

Searches for a value in the top row of a table or an array of values, and then returns a value
in the same column from a row you specify in the table or array. Use HLOOKUP when your
comparison values are located in a row across the top of a table of data, and you want to look
down a specified number of rows. Use VLOOKUP when your comparison values are located
in a column to the left of the data you want to find.

The syntax of the function is:

HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Where the function arguments are as follows:

lookup_value - The value that you want to search for, in the first row of the supplied data
array.

table_array - The data array or table, containing the data to be searched in the top row,
and the return values in any other row.

row_index_num - The row number, within the supplied table_array, that you want the
corresponding value to be returned from.

[range_lookup] - An optional logical argument, which can be set to TRUE or FALSE,

meaning:

TRUE - if the function cannot find an exact match to

the supplied lookup_value, it should use the
closest match below the supplied value.
Note: If [range_lookup] is set to TRUE, the
top row of the table_array must be in
ascending order.
FALSE - if the function cannot find an exact match to
the supplied lookup_value, it should return
an error.

Ex:

Axles Bearings Bolts

4 4 9

SJC 33
Advance Excel Unit - IV

5 7 10
6 8 11

Formula Description Result

=HLOOKUP("Axles", A1:C4, 2, Looks up "Axles" in row 1, and returns the value from 4
TRUE) row 2 that's in the same column (column A).

=HLOOKUP("Bearings", A1:C4, Looks up "Bearings" in row 1, and returns the value 7

3, FALSE) from row 3 that's in the same column (column B).

=HLOOKUP("B", A1:C4, 3, Looks up "B" in row 1, and returns the value from 5
TRUE) row 3 that's in the same column. Because an exact
match for "B" is not found, the largest value in row 1
that is less than "B" is used: "Axles," in column A.

=HLOOKUP("Bolts", A1:C4, 4) Looks up "Bolts" in row 1, and returns the value from 11
row 4 that's in the same column (column C).

=HLOOKUP(3, Looks up the number 3 in the three-row array C

{1,2,3;"a","b","c";"d","e","f"}, constant, and returns the value from row 2 in the same
2, TRUE) (in this case, third) column. There are three rows of
values in the array constant, each row separated by a
semicolon (;). Because "c" is found in row 2 and in the
same column as 3, "c" is returned.

Copy the example data in the following table, and paste it in cell A1 of a new Excel
worksheet. For formulas to show results, select them, press F2, and then press Enter. If you
need to, you can adjust the column widths to see all the data.

Excel Financial Functions

Excel provides a number of functions to make calculating financial data very easy.
These functions perform many of the common financial calculations, such as the
calculation of yield, interest rates, duration, valuation and depreciation. Spreadsheets are
frequently used for financial information, because of their ability to re-calculate the
entire sheet automatically, after a change to a single cell is made. Microsoft Excel is the
most important tool of Investment Bankers and Financial

SJC 34
Advance Excel Unit - IV

Analysts. They spent more than 70% of the time on preparing Excel Models,
formulating Assumptions, Valuations, Calculations, Graphs etc. The following are
some of the financial functions with syntax and examples.

1. FV( ) - FUTURE VALUE (FV):

Calculates the future value of an investment with periodic constant payments and a
constant interest rate. If you want to find out the future value of a particular investment
which has a constant interest rate and periodic payment, use the following formula –
Syntax: FV (Rate, Nper, [Pmt], PV, [Type])
 Rate = It is the interest rate/period
 Nper = Number of periods
 [Pmt] = Payment/period
 PV = Present Value
 [Type] = When the payment is made (if nothing is mentioned, it’s assumed
that the payment has been made at the end of the period)

EX:A has invested US $100 in 2016. The payment has been made yearly. The interest
rate is 10% p.a. What would be the FV in 2019?
Solution: In excel, we will put the equation as follows –

= FV (10%, 3, 1, – 100)
= US $129.79

SJC 35
Advance Excel Unit - IV

2. PV( ) - PRESENT VALUE (PV) :

Calculates the present value of an investment (i.e. the total amount that a series of
future periodic constant payments is worth now). If you know how to calculate FV, it’s
easier for you to find out PV.
Syntax: PV = (Rate, Nper, [Pmt], FV, [Type])
 Rate = It is the interest rate/period
 Nper = Number of periods
 [Pmt] = Payment/period
 FV = Future Value
 [Type] = When the payment is made (if nothing is mentioned, it’s assumed
that the payment has been made at the end of the period)
EX: The future value of an investment is US $100 in 2019. The payment has been
made yearly. The interest rate is 10% p.a. What would be the PV as of now?
Solution: In excel, we will put the equation as follows –

= PV (10%, 3, 1, – 100)
= US $72.64

3. NPV - NET PRESENT VALUE (NPV):

Calculates the net present value of an investment, based on a supplied discount rate,
and a series of periodic cash flows. Net Present Value is the sum total of positive and
negative cash flows over the years. Here’s how we will represent it in excel –
Syntax: NPV = (Rate, Value 1, [Value 2], [Value 3]…)
 Rate = Discount rate for a period

SJC 36
 Advance Excel Unit - IV

 Value 1, [Value 2], [Value 3]… = Positive or negative cash flows

 Here, negative values would be considered as payments and positive values
would be treated as inflows.

EX: Here is a series of data from which we need to find NPV –

Details In US $

Rate of Discount 5%

Initial Investment -1000

Return from 1st year 300

Return from 2nd year 400

Return from 3rd year 400

Return from 4th year 300

Find out the NPV.
Solution: In Excel, we will do the following –

=NPV (5%, B4:B7) + B3

= US $240.87

4. PMT
In excel, PMT denotes the periodical payment required to pay off for a particular period of
time with a constant interest rate. Calculates the payments required to reduce a loan,
from a supplied present value to a specified future value. Let’s have a look at how to
calculate it in excel –

SJC 37
Advance Excel Unit - IV

Syntax: PMT = (Rate, Nper, PV, [FV], [Type])

 Rate = It is the interest rate/period
 Nper = Number of periods
 PV = Present Value
 [FV] = An optional argument which is about the future value of a loan (if
nothing is mentioned, FV is considered as “0”)
 [Type] = When the payment is made (if nothing is mentioned, it’s assumed
that the payment has been made at the end of the period)
EX: US $1000 need to be paid in full in 3 years. Interest rate is 10% p.a. and the
payment needs to be done yearly. Find out the PMT.
Solution: In excel, we will compute it in the following manner –

= PMT (10%, 3, 1000)

= – 402.11

5. IRR( ) - INTERNAL RATE OF RETURN:

Calculates the internal rate of return for a series of periodic cash flows. To
understand whether any new project or investment is profitable or not, firm uses IRR.
If IRR is more than the hurdle rate (acceptable rate/ average cost of capital), then it’s
profitable for the firm and vice-versa.
Syntax: IRR = (Values, [Guess])
 Values = Positive or negative cash flows (an array of values)
 [Guess] = An assumption of what you think IRR should be
EX: Here is a series of data from which we need to find IRR –
Details In US $

Initial Investment -1000

SJC 38
Advance Excel Unit - IV

Return from 1st year 300

Return from 2nd year 400

Return from 3rd year 400

Return from 4th year 300

Find out IRR.
Solution: Here’s how we will compute IRR in excel –

= IRR (A2:A6, 0.1)

= 15%

6. NPER( )- FINANCIAL FUNCTION IN EXCEL

It is simply the number of periods one requires to pay off the loan. Returns the number
of periods for an investment with periodic constant payments and a constant interest rate
Syntax: NPER = (Rate, PMT, PV, [FV], [Type])
 Rate = It is the interest rate/period
 PMT = Amount paid per period
 PV = Present Value
 [FV] = An optional argument which is about the future value of a loan (if
nothing is mentioned, FV is considered as “0”)
 [Type] = When the payment is made (if nothing is mentioned, it’s assumed
that the payment has been made at the end of the period)
EX: US $200 is paid per year for a loan of US $1000. Interest rate is 10% p.a. and the
payment needs to be done yearly. Find out the NPER.
Solution: We need to calculate NPER in the following manner –

SJC 39
Advance Excel Unit - IV

= NPER (10%, -200, 1000)

= 7.27 years

7. RATE( )
Calculates the interest rate required to pay off a specified amount of a loan, or reach a
target amount on an investment over a given period. Through RATE function, we can
calculate the interest rate needed to pay to pay off the loan in full for a given period of time.
Syntax: RATE = (NPER, PMT, PV, [FV], [Type], [Guess])
 Nper = Number of periods
 PMT = Amount paid per period
 PV = Present Value
 [FV] = An optional argument which is about the future value of a loan (if
nothing is mentioned, FV is considered as “0”)
 [Type] = When the payment is made (if nothing is mentioned, it’s assumed
that the payment has been made at the end of the period)
 [Guess] = An assumption of what you think RATE should be
EX: US $200 is paid per year for a loan of US $1000 for 6 years and the payment needs
to be done yearly. Find out the RATE.
Solution:

= RATE (6, -200, 1000, 0.1)

= 5%

SJC 40
 Advance Excel Unit - IV

8. TYPE( ) :
The Excel TYPE function returns a numeric code representing "type" in 5 categories:
number = 1, text = 2, logical = 4, error = 16, and array = 64. Use TYPE when the operation
of a formula depends on the type of value in a particular cell. Use TYPE to test the value in
a particular cell so that other functions that depend on the type can perform as expected.
You can use TYPE to find out the type of data that is returned by a function or formula.

Syntax : =TYPE (value)

 value - The value to check the type of.

Ex:

9. IPMT( ):
Get principal for given period by using IPMT function. The Excel IPMT function can
be used to calculate the interest portion of a given loan payment in a given payment
period. For example, you can use IPMT to get the principal amount of a payment for
the first period, the last period, or any period in between.
Syntax: =IPMT (rate, per, nper, pv, [fv], [type])

 rate - The interest rate per period.

 per - The payment period of interest.
 nper - The total number of payment periods.
 pv - The present value, or total value of all payments now.
 fv - [optional] The cash balance desired after last payment is made. Defaults to 0.

SJC 41
 Advance Excel Unit - IV

 type - [optional] When payments are due. 0 = end of period. 1 = beginning of period.
Default is 0.
Ex:

10. CUMIPMT( ):

Get cumulative interest paid on a loan by using CUMIPMT function. The Excel
CUMIPMT function is a financial function that returns the cumulative interest paid on
a loan between a start period and an end period. You can use CUMIPMT to calculate
and verify the total interest paid on a loan, or the interest paid between any two
payment periods.

Syntax: =CUMIPMT (rate, nper, pv, start_period, end_period, type)

Arguments

 rate - The interest rate per period.

 nper - The total number of payments for the loan.
 pv - The present value, or total value of all payments now.
 start_period - First payment in calculation.
 end_period - Last payment in calculation.
 type - When payments are due. 0 = end of period. 1 = beginning of period. Ex:

SJC 42
Advance Excel Unit - IV

SJC 43
Unit – V Advance Excel

What is a Pivot Table?

A pivot table is a special Excel tool that allows you to summarize and explore data
interactively.

Table - A collection of data. It was first coined in MS Access. However, it is commonly

used in Excel nowadays. A table in Excel has a header and there are no entirely blank
rows or columns. (Example: Home > Format as Table)

Pivot - The ability to alter the perspective of retrieved data.

Pivot Table - The ability to create a brand new table based on existing data for the purpose
of viewing, reporting and analyzing data.

A Pivot Table is a summary of a large dataset that usually includes the total figures,
average, minimum, maximum, etc. let's say you have a sales data for different regions,
with a pivot table, you can summarize the data by region and find the average sales per
region, the maximum and minimum sale per region, etc. Pivot tables allow us to
analyze, summarize and show only relevant data in our reports.

In other words, pivot tables extract meaning from that seemingly endless jumble of numbers
on your screen. And more specifically, it lets you group your data together in different
ways so you can draw helpful conclusions more easily.

Creating a pivot table:

Specifying PivotTable Data Before creating a PivotTable you must know what you want to
analyze. There are three questions you have to ask before proceeding: 
 What do you want your column headers to be?
 What do you want your row headers to be?
 What data do you want to analyze?
By understanding the layout, you will have a better perspective on how to create a
PivotTable.

Ex: Our data set consists of 213 records and 6 fields. Order ID, Product, Category,
Amount, Date and Country.

Dept of Comp.Sci, SJC. 1

Unit – V Advance Excel

Insert a Pivot Table

To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, in the Tables group, click PivotTable.
The following dialog box appears. Excel automatically selects the data for you. The
default location for a new pivot table is New Worksheet.
3. Click OK.

Drag fields
The PivotTable Fields pane appears. To get the total amount exported of each
product, drag the following fields to the different areas.
1. Product field to the Rows area.
2. Amount field to the Values area.
3. Country field to the Filters area.

Dept of Comp.Sci, SJC. 2

 Unit – V Advance Excel

As a result, Bananas are our main export product. That's how easy pivot tables can be!

Sort
To get Banana at the top of the list, sort the pivot table.
1. Click any cell inside the Sum of Amount column.
2. Right click and click on Sort, Sort Largest to Smallest.

Filter
Because we added the Country field to the Filters area, we can filter this pivot table by
Country. For example, which products do we export the most to France?
1. Click the filter drop-down and select France. Result.
Apples are our main export product to France.
Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts
of specific products.
Change Summary Calculation
By default, Excel summarizes your data by either summing or counting the items. To change
the type of calculation that you want to use, execute the following steps.

Dept of Comp.Sci, SJC. 3

Unit – V Advance Excel

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings.

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK.
Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table

It's perfectly ok to drag more than one field to an area in a pivot table. If you drag a field
to the Rows area and Columns area, you can create a two-dimensional pivot table. First,
insert a pivot table. Next, to get the total amount exported to each country, of each
product, drag the following fields to the different areas.
1. Country field to the Rows area.
2. Product field to the Columns area.
3. Amount field to the Values area.
4. Category field to the Filters area.

Dept of Comp.Sci, SJC. 4

 Unit – V Advance Excel

Grouping Data:
Grouping data in a PivotTable can help you show a subset of data to analyze. For example, you
may want to group an unwieldy list of dates or times (date and time fields in the
PivotTable) into quarters and months.
You can generally group Items in a Pivot Table in 2 different ways:
1. Automatically.
2. Manually.
The grouping option that's more suitable for a situation depends on the type of data you're
working with.
Group Products
The Product field contains 7 items. Apple, Banana, Beans, Broccoli, Carrots, Mango and
Orange.
To create two groups, execute the following steps.
1. In the pivot table, select Apple and Banana.
2. Right click and click on Group.
3. In the pivot table, select Beans, Broccoli, Carrots, Mango and Orange.
4. Right click and click on Group.

Dept of Comp.Sci, SJC. 5

 Unit – V Advance Excel

Note: to change the name of a group (Group1 or Group2), select the name, and edit the
name in the formula bar. To ungroup, select the group, right click and click on Ungroup.
5. To collapse the groups, click the minus signs.
Conclusion: Apple and Banana (Group1) have a higher total than all the other products
(Group2) together.
Group Dates
To create the pivot table below, instead of the Product field, add the Date field to the Rows area.
The Date field contains many items. 1/6/2016, 1/7/2016, 1/8/2016, 1/10/2016, 1/11/2016, etc.
To group these dates by quarters, execute the following steps.

Dept of Comp.Sci, SJC. 6

Unit – V Advance Excel

1. Click any cell inside the column with dates.

2. Right click and click on Group.
3. Select Quarters and click OK.
Note: also see the options to group by seconds, minutes, hours, etc.
Conclusion: Quarter 2 is the best quarter.

Update Pivot Table

Any changes you make to the data set are not automatically picked up by the
pivot table. Refresh the pivot table or change the data source to update the pivot
table with the applied changes.

Refresh
If you change any of the text or numbers in your data set, you need to refresh the
pivot table.

At any time, you can click Refresh to update the data for the PivotTables in your
workbook. You can refresh the data for PivotTables connected to external data, such
as a database (SQL Server, Oracle, Access, or other), You can also refresh data from
a source table in the same or a different workbook. And you

Dept of Comp.Sci, SJC. 7

 Unit – V Advance Excel

can set your workbook to refresh its PivotTable data automatically when you
open it.

By default, PivotTables are not refreshed automatically, but you can specify
that the PivotTable is automatically refreshed when you open the workbook that
contains the PivotTable.

Manually refresh

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

2. Click Analyze > Refresh, or press Alt+F5.

If refreshing takes longer than you expect, click Analyze > Refresh arrow
> Refresh Status to check the refresh status. To

stop refreshing, click Cancel Refresh.

Refresh data automatically when opening the workbook

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

2. Click Analyze > Options.

Dept of Comp.Sci, SJC. 8

 Unit – V Advance Excel

3. On the Data tab, check the Refresh data when opening the file box.

Formatting a Pivot Table

Excel 2016 makes formatting a new pivot table you’ve added to a worksheet as quick and
easy as formatting any other table of data or list of data. All you need to do is click a
cell of the pivot table to add the PivotTable Tools contextual tab to the Ribbon and then
click its Design tab to display its command buttons.

The Design tab is divided into three groups:

 Layout group that enables you to add subtotals and grand totals to the pivot table and
modify its basic layout

 PivotTable Style Options group that enables you to refine the pivot table style you
select for the table using the PivotTable Styles gallery to the immediate right

 PivotTable Styles group that contains the gallery of styles you can apply to the active
pivot table by clicking the desired style thumbnail

To change the layout:

On the Design tab, in the Layout group, click Report Layout, and then do one of the
following:

 To keep related data from spreading horizontally off of the screen and to help
minimize scrolling, click Show in Compact Form.

In compact form, fields are contained in one column and indented to show the nested
column relationship.

Dept of Comp.Sci, SJC. 9

 Unit – V Advance Excel

 To outline the data in the classic PivotTable style, click Show in Outline Form.
 To see all data in a traditional table format and to easily copy cells to another
worksheet, click Show in Tabular Form.

Change the Default PivotTable Style

When you create a pivot table, a default PivotTable Style is automatically applied. You
can change the default PivotTable Style in a workbook, so a different style is
automatically applied.
Follow these steps to change the default PivotTable Style for a workbook:

1. Select a cell in any pivot table.

2. On the Ribbon, under the PivotTable Tools tab, click the Design tab.
3. In the PivotTable Style gallery, right-click on the style that you want to set as the
default.
4. In the context menu, click on Set As Default

Modify a PivotTable Style

You can't change any of the built-in PivotTable styles, but you can create a Custom
PivotTable style in your workbook, with the formatting that you need.
1. First, choose one of these ways to get started::

 copy an existing style, to start with its formatting

 create a new style, to start from scratch

2. Next, follow the instructions here, to modify the Custom Style.

3. Finally, follow the steps to apply the new custom style to a pivot table.

Slicers:

Slicers in Excel are graphic filters for tables, pivot tables and pivot charts. Due to their
visual qualities, slicers fit especially well with dashboards and summary reports, but you
can use them anywhere to make filtering data faster and easier.

Slicers provide buttons that you can click to filter table data, or PivotTable data. In
addition to quick filtering, slicers also indicate the current filtering state, which makes it
easy to understand what exactly is shown in a filtered PivotTable.

Basically, slicers and pivot table filters do the same thing - show some data and hide other.
And each method has its strengths and weaknesses:

Dept of Comp.Sci, SJC. 10

Unit – V Advance Excel

 Pivot table filters a bit clumsy. With slicers, filtering a pivot table is as simple as
clicking a button.
 Filters are tied to one pivot table, slicers can be connected to multiple pivot tables
and pivot charts.
 Filters are locked to columns and rows. Slicers are floating objects and can be moved
anywhere. For example, you can put a slicer next to your pivot chart or even within
the chart area and have the chart contents updated in real time on a button click.

Ex: Consider the above table used for pivot tables. To

insert a slicer, execute the following steps.

1. Click any cell inside the pivot table.
2. On the Analyze tab, in the Filter group, click Insert Slicer.
3. Check Category and click OK.
4. For example, click Fruit to only show the fruit exported to each country.
Note: the report filter (cell B1) changes to Fruit. Hold down CTRL to include fruit and
vegetables.

How to insert a slicer for pivot chart

To be able to filter a pivot chart with a slicer, you can actually make a slicer for your
pivot table like explained above, and it will control both the pivot table and the pivot
chart.

To integrate a slicer with your pivot chart more closely like shown in the screenshot above,
carry out these steps:

1. Click anywhere in your pivot chart.

2. On the Analyze tab, in the Filter group, click Insert Slicer.
3. Select the checkboxes for the slicer(s) you want to create, and click OK. This
will insert the already familiar slicer box in your worksheet:

Once you have a slicer, you can use it to filter the pivot chart data straight away. Or, you
may want to make a few improvements, for example, hide the filter buttons on the chart,
which have become redundant since you are going to use the slicer for filtering.

Dept of Comp.Sci, SJC. 11

 Unit – V Advance Excel

How to select multiple items in a slicer

There are 3 ways to select multiple items in an Excel slicer:

 Click the slicer buttons while holding the Ctrl key.

 Click the Multi-Select button (please see the screenshot below), and then click on the items
one by one.

Pivot charts:
After creating a pivot table in Excel 2016, you can create a pivot chart to display its
summary values graphically.
Sometimes it's hard to see the big picture when your raw data hasn’t been summarized.
Your first instinct may be to create a PivotTable, but not everyone can look at numbers
in a table and quickly see what's going on. PivotCharts are a great way to add data
visualizations to your data.

Create a PivotChart

1. Select a cell in your table.

2. Select Insert > PivotChart .
3. Select OK.

Create a chart from a PivotTable

1. Select a cell in your table.

2. Select PivotTable Tools > Analyze > PivotChart .
3. Select a chart.
4. Select OK.

Filter Pivot Chart

To filter this pivot chart, execute the following steps.
1. Use the standard filters (triangles next to Product and Country). For example, use the
Country filter to only show the total amount of each product exported to the United States.

Dept of Comp.Sci, SJC. 12

 Unit – V Advance Excel

2. Remove the Country filter.

3. Because we added the Category field to the Filters area, we can filter this pivot chart
(and pivot table) by Category. For example, use the Category filter to only show the
vegetables exported to each country.
Change Pivot Chart Type
You can change to a different type of pivot chart at any time.
1. Select the chart.
2. On the Design tab, in the Type group, click Change Chart Type.

3. Choose Pie.
4. Click OK.

What if analysis:
Excel includes many powerful tools to perform complex mathematical calculations,
including what-if analysis. This feature can help you experiment and answer
questions with your data, even when the data is incomplete.
By using What-If Analysis tools in Excel, you can use several different sets of values in one
or more formulas to explore all the various results.

What-If Analysis is the process of changing the values in cells to see how those changes will
affect the outcome of formulas on the worksheet.

Three kinds of What-If Analysis tools come with Excel:

1. Scenarios
2. Goal Seek
3. Data Tables.

Scenarios and Data tables take sets of input values and determine possible results. A Data
Table works with only one or two variables, but it can accept many different values for
those variables. A Scenario can have multiple variables, but it can only accommodate
up to 32 values. Goal Seek works differently from Scenarios and Data

Dept of Comp.Sci, SJC. 13

Unit – V Advance Excel

Tables in that it takes a result and determines possible input values that produce that
result.

A Scenario is a set of values that Excel saves and can substitute automatically in cells
on a worksheet. You can create and save different groups of values on a worksheet and
then switch to any of these new scenarios to view different results.

The following example helps you master what-if analysis quickly and easily.
Assume you own a book store and have 100 books in storage. You sell a certain % for the
highest price of $50 and a certain % for the lower price of $20.

If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40
* $20 = $3800.
Create Different Scenarios
But what if you sell 70% for the highest price? And what if you sell 80% for the highest
price? Or 90%, or even 100%? Each different percentage is a different scenario. You can
use the Scenario Manager to create these scenarios.
Note: You can simply type in a different percentage into cell C4 to see the corresponding
result of a scenario in cell D10. However, what-if analysis enables you to easily compare
the results of different scenarios. Read on.
1. On the Data tab, in the Forecast group, click What-If Analysis.
2. Click Scenario Manager.

The Scenario Manager dialog box appears.

Dept of Comp.Sci, SJC. 14

Unit – V Advance Excel

1. Add a scenario by clicking on Add.

2. Type a name (60% highest), select cell C4 (% sold for the highest price) for
the Changing cells and click on OK.
3. Enter the corresponding value 0.6 and click on OK again.
4. 6. Next, add 4 other scenarios (70%, 80%, 90% and 100%).
5. Finally, your Scenario Manager should be consistent with the picture
below:

Scenario Summary
To easily compare the results of these scenarios, execute the following steps.
1. Click the Summary button in the Scenario Manager.
2. Next, select cell D10 (total profit) for the result cell and click on OK.

Conclusion: if you sell 70% for the highest price, you obtain a total profit of $4100, if you
sell 80% for the highest price, you obtain a total profit of $4400, etc. That's how easy what-
if analysis in Excel can be.

Dept of Comp.Sci, SJC. 15

Unit – V Advance Excel

Goal Seek
What if you want to know how many books you need to sell for the highest price, to obtain
a total profit of exactly $4700? You can use Excel's Goal Seek feature to find the
answer.
1. On the Data tab, in the Forecast group, click What-If Analysis.
2. Click Goal Seek.

The Goal Seek dialog box appears.

3. Select cell D10.
4. Click in the 'To value' box and type 4700.
5. Click in the 'By changing cell' box and select cell C4.
6. Click OK.
Result. You need to sell 90% of the books for the highest price to obtain a total
profit of exactly $4700.

Data Tables:

Instead of creating different scenarios, you can create a data table to quickly try out
different values for formulas. You can create a one variable data table or a two variable
data table.
Assume you own a book store and have 100 books in storage. You sell a certain % for the
highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the
highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 =
$3800.
One Variable Data Table
To create a one variable data table, execute the following steps.
1. Select cell B12 and type =D10 (refer to the total profit cell).
2. Type the different percentages in column A.
3. Select the range A12:B17.

Dept of Comp.Sci, SJC. 16

 Unit – V Advance Excel

We are going to calculate the total profit if you sell 60% for the highest price, 70% for the
highest price, etc.
4. On the Data tab, in the Forecast group, click What-If Analysis.

5. Click Data Table.

6. Click in the 'Column input cell' box (the percentages are in a column) and select cell
C4.
We select cell C4 because the percentages refer to cell C4 (% sold for the highest price).
Together with the formula in cell B12, Excel now knows that it should replace cell C4 with
60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.

Note: this is a one variable data table so we leave the Row input cell blank.
7. Click
OK.
Result.
Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if
you sell 70% for the highest price, you obtain a total profit of $4100, etc.

Dept of Comp.Sci, SJC. 17

Unit – V Advance Excel

Two Variable Data Table

To create a two variable data table, execute the following steps.
1. Select cell A12 and type =D10 (refer to the total profit cell).
2. Type the different unit profits (highest price) in row 12.
3. Type the different percentages in column A.
4. Select the range A12:D17.
We are going to calculate the total profit for the different combinations of 'unit profit
(highest price)' and '% sold for the highest price'.
5. On the Data tab, in the Forecast group, click What-If Analysis.

6. Click Data Table.

7. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.
8. Click in the 'Column input cell' box (the percentages are in a column) and select cell
C4.
We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the
percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it
should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace
cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.
9. Click OK.
Conclusion: if you sell 60% for the highest price, at a unit profit of $50, you obtain a total
profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a
total profit of $5200, etc.

Dept of Comp.Sci, SJC. 18

Unit – V Advance Excel

Macros

What is a macro?

A macro is a piece of programming code that runs in Excel environment and helps
automate routine tasks. In a layman's language, a macro is a recording of your
routine steps in Excel that you can replay using a single button.

In other words, it is an action or a set of actions that you can use to automate tasks.
Macros are recorded in the Visual Basic for Applications programming language.
You can always run a macro by clicking the Macros command on the Developer tab
on the ribbon.

The importance of macros in Excel

Let's say you work as a cashier for a water utility company. Some of the customers
pay through the bank and at the end of the day, you are required to download the data
from the bank and format it in a format that meets your business requirements.

Developer Tab
To turn on the Developter tab, execute the following steps.
1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs
(if necessary).
3. Check the Developer check box.
4. Click OK.
5. You can find the Developer tab next to the View tab.

Command Button
To place a command button on your worksheet, execute the following steps.
1. On the Developer tab, click Insert.
2. In the ActiveX Controls group, click Command Button.
3. Drag a command button on your worksheet.

Dept of Comp.Sci, SJC. 19

 Unit – V Advance Excel

Assign a Macro
To assign a macro (one or more code lines) to the command button, execute the
following steps.
1. Right click CommandButton1 (make sure Design Mode is selected).
2. Click View Code.
The Visual Basic Editor appears.
3. Place your cursor between Private Sub CommandButton1_Click() and End
Sub.
4. Add the code line shown below.
Note: the window on the left with the names Sheet1, Sheet2 and Sheet3 is called the
Project Explorer. If the Project Explorer is not visible, click View, Project
Explorer. To add the Code window for the first sheet, click Sheet1 (Sheet1).
5. Close the Visual Basic Editor.
6. Click the command button on the sheet (make sure Design Mode is
deselected).
Result:
Congratulations. You've just created a macro in Excel!

Visual Basic Editor

To open the Visual Basic Editor, on the Developer tab, click Visual Basic.

Macro Recorder
The Macro Recorder, a very useful tool included in Excel VBA, records every task you
perform with Excel. All you have to do is record a specific task once. Next, you can
execute the task over and over with the click of a button. The Macro Recorder is also
a great help when you don't know how to program a specific task in Excel VBA.
Simply open the Visual Basic Editor after recording the task to see how it can be
programmed.

Dept of Comp.Sci, SJC. 20

Unit – V Advance Excel

Take a closer look at the macro

You can learn a little about the Visual Basic programming language by editing
a macro.

To edit a macro, in the Code group on the Developer tab, click Macros, select the
name of the macro, and click Edit. This starts the Visual Basic Editor.

See how the actions that you recorded appear as code. Some of the code will
probably be clear to you, and some of it may be a little mysterious.

Experiment with the code, close the Visual Basic Editor, and run your macro
again. This time, see if anything different happens!

Record a Macro
1. On the Developer tab, click Record Macro.
2. Enter a name.
3. Select This Workbook from the drop-down list. As a result, the macro will
only be available in the current workbook.
Note: if you store your macro in Personal Macro Workbook, the macro will be
available to all your workbooks (Excel files). This is possible because Excel
stores your macro in a hidden workbook that opens automatically when Excel
starts. If you store your macro in New Workbook, the macro will only be
available in an automatically new opened workbook.
4. Click OK.
5. Right mouse click on the active cell (selected cell). Be sure not to select any other
cell! Next, click Format Cells.
6. Select Percentage.

Dept of Comp.Sci, SJC. 21

 Unit – V Advance Excel

7. Click OK.
8. Finally, click Stop Recording.

Run a Recorded Macro

Now we'll test the macro to see if it can change the number format to
Percentage.
1. Enter some numbers between 0 and 1.
2. Select the numbers.

Dept of Comp.Sci, SJC. 22

 Unit – V Advance Excel

3. On the Developer tab, click Macros.

4. Click Run.

Absolute or Relative
During macro recording cell references can be made either relative to the start
position or with an absolute address.
By default, recorded macros use absolute cell referencing.
This means the exact cell references are recorded into the macros.
You can switch back and forth between these two macro recording settings as many
time as you want.
When you record a macro it will be recorded with "absolute recording" by default.

Relative reference macros record an offset from the active cell. Such macros will be
useful if you have to repeat the steps at various places in the worksheet.

Use Relative References

By default, Excel records macros in absolute mode. However, sometimes it is
useful to record macros in relative mode.
Recording in Absolute Mode
To record a macro in absolute mode, execute the following steps.
1. First, click Record Macro.
2. Next, select cell B3. Type Sales and press enter.

Dept of Comp.Sci, SJC. 23

 Unit – V Advance Excel

3. Type Production and press enter.

4. Type Logistics and press enter.
Result:

5. Click Stop Recording.

6. Empty Range("B3:B5").
7. Select any cell on the sheet and run the recorded macro.
Result:
A macro recorded in absolute mode always produces the same result.

Recording in Relative Mode

Wouldn't it be nice to place these words anywhere on the sheet automatically? Not just
Range("B3:B5"). This would make the macro much more flexible. Solution: record
the macro in relative mode.
1. Select "Use Relative References".
2. First, select any single cell (for example, cell B8).
3. Next, click Record Macro.
4. Type Sales and press enter.
5. Type Production and press enter.
6. Type Logistics and press enter.
Result:

Dept of Comp.Sci, SJC. 24

 Unit – V Advance Excel

7. Click Stop Recording.

8. Select any other cell (for example, cell D4) and run the recorded macro.
Result:
Excel places the words relative to the initial selected cell. That's why it's called
recording in relative mode.

Dept of Comp.Sci, SJC. 25