MsExcel-2007

Advanced Excel 2007

Study Material

Developed By
Sanjay Jamalpur

Page 1 of 38

MsExcel-2007
Table of Contents
What is a SpreadSheet?.................................................................................................................. 4
Fluent ............................................................................................................................................. 4
Ribbon ............................................................................................................................................ 4
Mini Toolbar................................................................................................................................... 5
Formula AutoComplete .................................................................................................................. 5
Zoom and Page View controls ........................................................................................................ 5
Cell Addresses................................................................................................................................. 5
Moving in the worksheet................................................................................................................ 6
Creating a File ................................................................................................................................ 6
Saving the Worksheet .................................................................................................................... 6
Editing a cell-data .......................................................................................................................... 6
Range Names ................................................................................................................................ 7
Formatting ..................................................................................................................................... 8
Conditional Formatting . ................................................................................................................ 8
Changing Column-widths and row-height...................................................................................... 8
Inserting rows, columns and cells . ................................................................................................. 9
Comments ...................................................................................................................................... 9
Relative Reference........................................................................................................................ 10
Absolute Reference ...................................................................................................................... 10
Formula Auditing.......................................................................................................................... 10
Page Setup ................................................................................................................................... 11
Printing a Worksheet.................................................................................................................... 11
Operators in Ms-Excel ................................................................................................................... 12
Two way Decision Making............................................................................................................ 14
Decision Making using AND ......................................................................................................... 14
Decision Making using OR............................................................................................................ 14
Using LookUp and Reference Functions........................................................................................ 15
VLOOKUP ..................................................................................................................................... 15
HLOOKUP ..................................................................................................................................... 15
Mathematical and Trig Functions................................................................................................. 16
Statistical Functions ..................................................................................................................... 17
Financial Functions ....................................................................................................................... 17
Date and Time Functions.............................................................................................................. 18
Page 2 of 38

MsExcel-2007
Text Functions .............................................................................................................................. 19
Sorting and Filtering..................................................................................................................... 20
Filtering ....................................................................................................................................... 20
Advanced Filter ............................................................................................................................ 20
Subtotals ...................................................................................................................................... 21
PivotTable .................................................................................................................................... 22
Updating the Pivot Table with changes in existing data .............................................................. 22
Updating the Pivot Table with new data added in data range .................................................... 22
Charts ........................................................................................................................................... 23
Creating Charts............................................................................................................................. 23
Changing the Chart type............................................................................................................... 23
Adding Data labels and Axis Title................................................................................................. 23
Data Validation ............................................................................................................................ 24
Goal Seek and Scenarios............................................................................................................... 25
Solver............................................................................................................................................ 26
Adding the solver tool .................................................................................................................. 26
Table............................................................................................................................................. 27
Creating a one-variable data table................................................................................................ 27
Linking WorkSheets...................................................................................................................... 28
Hyperlink ...................................................................................................................................... 28
Protection..................................................................................................................................... 29
Protecting the Worksheet ............................................................................................................ 29
Protecting the Workbook ............................................................................................................. 30
Consolidation................................................................................................................................ 31
Consolidate data by position ......................................................................................................... 32
Sharing Workbook........................................................................................................................ 32
Steps for Setting up a shared workbook ........................................................................................ 32
Sharing Data between Word and Excel ........................................................................................ 33
Importing the data into Ms-Excel ................................................................................................. 34
Importing a Delimited text file ..................................................................................................... 34
Macro ........................................................................................................................................... 37
Record a macro ............................................................................................................................. 37
Running a Recorded Macro .......................................................................................................... 38
Deleting a Macro.......................................................................................................................... 38

Page 3 of 38

MsExcel-2007
What is a SpreadSheet?
A spreadsheet is a graphical representation and processing of data. In an Electronic Spreadsheet
data is represented in a matrix or tabular form, consisting of rows and columns. The first Electronic
Spreadsheet was developed in 1976 by Dan Bricklin and the program was called VisiCalc.

What can a spreadsheet do?

A spreadsheet is useful to work with numbers and mostly used for different business applications.
Activities that can be covered with spreadsheet are Forecasting, Budgeting, Financial Analysis,
Accounting, Planning, Marketing & Sales, Data Analysis, etc.

Microsoft Excel and the Excel 2007 screen

Microsoft Excel works under the Windows environment and uses the feature of WYSIWYG. Excel
2007 screen consists of the following.
Fluent
The Office 2007 version has all new look as compared to the earlier versions. Fluent is the
common UI present in Word, Excel, PowerPoint and Access. The fluent has many components but
the most important one is the Ribbon. The MsOffice button on the top left corner is the
replacement for the file menu of previous versions. It contains the options found in the File menu
like New, Open, Save, Print etc.

Ribbon
The Ribbon is the replacement for all the other menus and toolbars. It is the banner that occupies
the top of the window. Its designed in tab based, and the corresponding icons are displayed
whenever a tab is selected by default the Ribbon has seven tabs namely Home, Insert, Page
Layout, Formulas, Data, Review and View. The icons are grouped logically under each tab.

The Ribbon

Page 4 of 38

MsExcel-2007
Mini Toolbar
The mini toolbar pops up on right clicking the mouse on top of the context menu. The mini toolbar
contains frequently used formatting options, and significantly decreases the mouse navigation
involved.

Formula AutoComplete
Whenever user types a formula in the cell a window pops up next to the cell which contains list of
functions matching the letters typed by the user with a brief description about the function. It also
displays the syntax of the function therefore reducing the chances of making an error.
Formula AutoComplete

Zoom and Page View controls

The zoom control is much easier this is now present as a horizontally sliding control at the bottom
left of the window. Next to the zoom control is the Page View control, which can be used to check
the page break preview and print page layout with one click.

Cell Addresses
The label for a cell is called a cell address. The first cell is A1 and the last cell is XFD1048576.

Page 5 of 38

MsExcel-2007
Moving in the worksheet
An Excel worksheet consists of 1048576 Rows and 16384 Columns. To move around the
worksheet you can use arrow keys page up and page down can be used to move a screen full of
cells. Pressing function F5 displays the GOTO dialog box. By which we can navigate in any desired
cell.

Entering Data
The Data is entered in the intersection of Row & Column called as Cell a Cell can contain six
types of entries. i.e.
Values Dates Times Formulae Functions - Labels / Text A Cell can contain number,
underscore, character & space.

Creating a File
To create a new file, click on the MsOffice button located on the top left corner.

Entering Formulae: A formula should always start with = sign.

There are two different methods of entering formulae:


Typing in physical cell addresses


Using the Pointing method
Always remember the golden rule of working with a spreadsheet.
As far as possible, do not type in physical numbers, but use cell addresses.
The AutoSum Tool - The sigma tool is used to sum the values that are typed besides the
current cell.

Saving the Worksheet
Click on the MsOffice button located on the top left corner & choose
Save. File in excel 2007 is saved as a workbook with extension .xlsx this particular format does
not support macros, an excel 2007 macro file is saved with extension .xlsm and an excel 2007
template file is saved with extension .xlst.
To Quit Excel
Click on the MsOffice button located on the top left corner and choose Exit Excel.

Editing a cell-data
Press F2 function key on the cell or DBLClick in the Cell then Use arrow keys
and make corrections
Using Undo and Redo
Click on Undo button on the quick access toolbar. To reverse the Undo,
Click on the Redo button on the same toolbar.

Erasing a cell-value
place the cursor in the cell and press delete/backspace button on the
keyboard

Page 6 of 38

MsExcel-2007
Selecting Values- Contiguous and non Contiguous
Contiguous - Click and drag across to select all the cells for Non contiguous cells, click & drag to
select the first block of cells hold the Ctrl key on keyboard and click & drag to select the next block
of cells
Selecting rows and columns: To select a column click on the column alphabet, to select a row click
on the row number.

Range Names
Used for giving common name to multiple cells selected.
Defining a range name
Select the range of cells and click on the Formulas tab
Choose Define
Name under Name manager category
Specify the required name (it will allow characters,
numbers and underscores only).
Choose the scope required
lastly click on OK button
Range Name dialog box

Using AutoFill
Select the cell and move the mouse cursor to the bottom right corner of the cell
click and drag.
Using Cut/Copy/Paste
Copy - Creates a duplicate copy of the data. Select a block of cells. You want to copy and then click
on Home tab
click on the Copy button.
Move - To move data, select a block of cells and then click on Home tab
choose the Cut
(Scissor) button.

Paste for pasting the copied or cut data position the cursor in desired position and click on Home
tab
choose Paste button and specify the way in which you want to paste the data.

Page 7 of 38

MsExcel-2007
Formatting
To beautify text or apply sizes to text excel allows formatting.
Text Wrap
Select the Home tab
click on the option Wrap Text.
Fonts, Attributes, Sizes and Colors
select the cells
click on the Home tab
choose the
required font, size, color, bold, italic etc.

Formatting Value
Select the cells
Click on the Home tab
choose Format option
select
format cells
this will pop up the format cells dialog box
choose the required formatting in
the dialog box.
Conditional Formatting
Is used to monitor your data and alert you when the values enter a
certain range or move outside a set criteria.
For applying conditional formatting Select the Home tab
click on Conditional Formatting
button
this will display drop down menu choose the required option from it.

Copying Styles
To copy formats of styles from one cell to another, click on Format Painter on
the Home tab and paste on the other cells by selecting them.

Changing Column-widths and row-height

Select the Home tab
Format button
select column width / row height, or click & drag the
column / row arrows besides the alphabets.

Page 8 of 38

MsExcel-2007
Inserting rows, columns and cells
Select the Home tab
click on Insert button
choose the
required option under it.

Deleting rows and columns
Select the Home tab
Delete button
choose the required
option under it.

Comments
To explain a certain formula or section of cells, attach comments.

For inserting a comment select the Review tab
New Comment button under comments
category.

Page 9 of 38

MsExcel-2007
Spell Check
Place the cursor at the beginning of worksheet, select the Review tab
Spelling
button.

Find and Replace
To search for any entry use Find and to change them use Replace. Press the
shortcut key CTRL + H this will pop up the Find and Replace dialog box.

Using Relative and Absolute Reference for writing Formulaes

A Formula or Function in Excel should begin with an = Sign
Relative Reference
the formula when copied in other Cells goes on changing the Cell Reference.
Absolute Reference
the formula when copied in other Cells does not change the Cell Reference.
While using Absolute Reference you have to Prefix the Cell reference with $ Sign.

Formula Auditing
To display all the locations involved in a formula or to come to know what are the precedents and
dependents in the formula, click on Formulas tab
choose the required option under the Formula
Auditing category.

Creating Custom Lists
To customize the Autofill click on the

office button
Excel
options
Popular
Edit custom list button
Highlight New List and add up the list entries.
And lastly click on Ok.

Page 10 of 38

MsExcel-2007
Page Setup
Using Page Setup
Click on the Page Layout tab
next click on Size button and select the Paper Size
click on
Orientation button to set the desired Orientation i.e. Portrait to print vertically. Landscape to
print horizontally
click on the Margins button to specify the Margins
to print the Data with
Grid lines click on Print Titles button
select the Sheet Tab and select the check box print
gridlines

Printing a Worksheet

Office button on top left corner and choose Print option this will pop up the
Click on the
print dialog box specify the printer and required settings for printing, you can click on the Preview
button to view how the page looks after printing. And finally click on OK button to print the data.

Page 11 of 38

MsExcel-2007
Operators in Ms-Excel
Arithmetic Operators To perform basic mathematical operations such as addition, subtraction, or
multiplication; combine numbers; and produce numeric results, use the following arithmetic
operators.
Arithmetic
operator

Meaning

Example

+ (plus sign)

Addition

3+3

(minus sign)

Subtraction
Negation

31
1

* (asterisk)

Multiplication

3*3

/ (forward slash)

Division

3/3

% (percent sign)

Percent

20%

^ (caret)

Exponentiation

3^2 (the same as

3*3)

Comparison Operators You can compare two values with the following operators. When two
values are compared by using these operators, the result is a logical value, either TRUE or FALSE.
Comparison
operator

Meaning

Example

= (equal sign)

Equal to

A1=B1

> (greater than sign)

Greater than

A1>B1

< (less than sign)

Less than

A1<B1

>= (greater than or equal to sign)

Greater than or equal to

A1>=B1

<= (less than or equal to sign)

Less than or equal to

A1<=B1

<> (not equal to sign)

Not equal to

A1<>B1

Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text
strings to produce a single piece of text.
Text
operator
& (Ampersand)

Meaning

Example

Connects, or concatenates, two values to "North" & "wind" produce

produce one continuous text value
"Northwind"

Page 12 of 38

MsExcel-2007
Reference operators Combine ranges of cells for calculations with the following operators.
Reference
operator

Meaning

Example

: (Colon)

Range operator, which produces one reference to

all the cells between two references, including the
two references

B5:B15

, (Comma)

Union operator, which combines multiple

references into one reference

SUM (B5:B15,D5:D15)

Logical Operators used for logical calculations.

Logical
operator
IF

Meaning

Example

Used for Decision Making.

=IF (B2>=18, Eligible, Not Eligible)

AND

Used for testing if two or more than

conditions are satisfied

=IF (AND (B2>=18, C2=INDIAN),

ELIGIBLE, NOT ELIGIBLE)

OR

Used for testing if either one of the

condition is satisfied

=IF (OR (B2>=18, C2=INDIAN),

ELIGIBLE, NOT ELIGIBLE)

NOT

Used for testing if the condition is not

satisfied.

=IF (NOT (C2=INDIAN), NOT ELIGIBLE,

ELIGIBLE)

Page 13 of 38

MsExcel-2007
Decision making using IF
IF ()
Tests the contents of a cell and returns a logical value of true or false.
Different types of Decision Making.
Two way Decision Making
=IF (CONDITION, TRUE VALUE, FALSE VALUE)
e.g. To check if A5 is below 1000 give commission of 2% otherwise 5%.
=IF (A5<1000,A5*5%,A5*2%)
Decision Making using AND
AND
The AND operator returns true if all its arguments are True.
=IF (AND (A5>1000,A5<5000),A5*2%,A5*5%)

Decision Making using OR

OR
The OR operator returns true if any of its arguments are True.
=IF (OR (A5>1000,B5<5000),A5*2%,A5*5%)
Decision Making using NOT
NOT
The NOT operator returns true if the condition specified is not true.
=IF (NOT (A5>1000),A5*5%,A5*2%)

Page 14 of 38

MsExcel-2007
Using LookUp and Reference Functions
VLOOKUP ()
This function looks up for the first column of the list and moves across the row to
return the value of a cell. Used for vertically oriented lists.
Formulas tab
Lookup & Reference
Vlookup
Syntax
= VLOOKUP (Lookup value, Table array, Column index, Range lookup)
The list must be sorted in ascending order of the value to be searched. If the last argument value is
specified as true or omitted, it returns an approximate match. If specified as false, it returns an
exact match otherwise it displays #N/A
(Not Available) as error.

HLOOKUP ()
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,
Formulas tab
Lookup & Reference
Vlookup
Syntax
= HLOOKUP (lookup_value,table_array,row_index_num,range_lookup)
The Lookup Value, table array & Range will remain same as defined in Vlookup except the
row_index, i.e. in Vlookup we give Col_Index & in Hlookup we give Row_Index.

Page 15 of 38

MsExcel-2007
Mathematical and Trig Functions
INT ( )
returns an integer value.
e.g. =INT (2.5) returns 2, =INT (-2.25) returns -3

MOD ( )
returns remainder from a division

e.g. =MOD (24, 7) returns 3, where 24 is the number & 7 is the Divisor

POWER ( )
returns a number raised to a power.

e.g. =POWER (2, 3) returns 8, where 2 is the number & 3 is the Power.

ROUND ( )
rounds a number to a specified number of decimal places.

e.g. =ROUND (2.557,2) returns 2.56, =ROUND(2.553,2) returns 2.55

SQRT ( )
returns a square root of a positive integer.

e.g. =SQRT (16) returns 4

TRUNC ( )
truncates a number to a integer.

e.g.=TRUNC(-2.25) returns -2

SUM ( )
adds all the arguments.

e.g. =SUM (A5:A10) returns the total of all values from A5 to A10

FACT ( )
Returns the Factorial of the number

e.g. =FACT (5) returns 120

RADIANS ( )
accepts the angle in degrees and returns the relative value in radians
e.g. = RADIANS (90) returns 1.57

SIN ( )
accepts the angle in Radians and Returns the SIN value.
e.g. =SIN (1.57) returns 1

Page 16 of 38

MsExcel-2007
Statistical Functions
AVERAGE ( )
returns the average of its arguments.
e.g. =AVERAGE (A5:A10)
COUNT ( )
counts number of cells containing numbers in a list.
COUNTA ( )
counts number cells that are not empty in the list
COUNTIF ( )
counts number cells in the list with given condition.
COUNTBLANK ( )
counts number of empty cells in the list
MAX ( )
returns the maximum value in a list of arguments.
MIN ( )
returns the minimum value in a list of arguments.

Financial Functions
PMT ()
returns the periodic payment of an annuity based on a constant payments and a
constant interest rate. In simple terms, it will tell you what your payments will be for a given loan
amount, number of installments, at a fixed rate of interest.
= PMT (rate of interest, total number of installments, present value of loan, desired cash balance
(optional), 0-indicating payment at end of period or 1 indicating payment at beginning of period
(optional))
E.g. =PMT(12%/12,60,450000,0,1) returns the installment for a car to be purchased, the loan
amount being Rs. 4,50,000 at 12% p.a for 5 years.
You can use PMT to determine payments to annuities other than loans.

PPMT ()
returns the Principal value out of the Monthly payment made.
=PPMT (rate, per, nper, pv, fv, type)

IPMT ()
returns the Interest out of the Monthly payment made.

=IPMT (rate, per, nper, pv, fv, type)

SLN ()
returns the straight line depreciation of an Asset for one period.
=SLN (cost, salvage, life)

Page 17 of 38

MsExcel-2007
Date and Time Functions
Excel stores Date internally in the form of number starting from 1st Jan 1900 till 31st Dec 9999
DATE ()
returns the serial number of a date. =DATE (2005,1,19)
NOW ()
returns the current system date and time in one cell =NOW ()
TODAY ()
returns the current system date = TODAY ()
DAY ()
returns the day of the month for a date =DAY (A5)
MONTH ()
returns the month of the year of a date =MONTH (A5)
YEAR ()
returns the year of a date =YEAR (A5)
TIME ()
returns the number representing the time
HOUR ()
returns an hour, from (12:00 A.M.) to (11:00 P.M.)
MINUTE ()
returns Minute from 0 to 59
SECOND ()
returns Second form 0 to 59
NETWORKDAYS ()
returns the difference between two dates excluding holiday

Page 18 of 38

MsExcel-2007
Text Functions
EXACT ( )
To check if two text values are identical and returns True or False
e.g. =EXACT (excel,EXCEL) returns FALSE
LEFT ( )
returns the leftmost characters from a text value
e.g. =LEFT (Computer,3) returns Com
RIGHT ( )
returns the rightmost characters from a text value
e.g. =RIGHT (Computer,3) returns ter
MID ( )
returns a specific number of characters from a text string, starting at the position you
specify, based on the number of characters you specify.
e.g. =MID (Sachin, 2, 3) return ach
LEN ( )
returns the length of a text
e.g. =LEN (EXCEL) returns 5
CONCATENATE ()
Joins two or more strings into one.
e.g. =CONCATENATE (Word, Excel) returns WordExcel
UPPER ()
converts the Text string to Upper case
e.g. =UPPER (Word) returns WORD
LOWER ()
converts the Text string to Lower case
e.g. =LOWER (WORD) returns word
CHAR ()
returns the character at specified ASCII Code.
e.g. =CHAR (65) returns A
CODE ()
returns the ASCII Code of the specified character.
e.g. =CHAR (97) returns a

SEARCH ()
returns the position of the character to be searched in the text.

e.g. = Search (h, Sachin, 1) returns 4

Page 19 of 38

MsExcel-2007
Sorting and Filtering
Sorting
is used for arranging the Data either in Ascending or Descending order, alphabetically
or numerically.
Place the cursor in the Data area then select Data tab
click on Sort button under Sort and Filter
category.

Filtering
is used to view only those records that meet a given criteria
Place the cursor in the Data area select Data tab
click on Filter button
Customizing Filter- To give conditions within the filter choose Text Filter under filter
choose
Custom Filter option.

Advanced Filter
used for filtering the data based on more than one criterion

Page 20 of 38

MsExcel-2007
Subtotals
Subtotals
used for mathematically summarising data by a particular field but the data should
be in sorted order.
For creating Subtotals Place the cursor in the Data area then select Data tab
and click on
Subtotals button.

Note: For creating subtotal make sure that the data is in sorted order or else the output of
subtotal will be in haphazard order which becomes very difficult to refer and its always good
practice to make copy of data on which you want to run subtotals.

Page 21 of 38

MsExcel-2007
PivotTable
Pivot Table
To summarize data in a list by totaling sets of entries. We can define the Fields we
want and the Data related to the Fields.

Creating a Pivot Table

Place the cursor on the data. Click on Insert tab
PivotTable button
select Pivot Table or Pivot
Chart
select the Range
specify the placement of the pivot table i.e. in new worksheet or
existing worksheet
click on OK button
Define the Fields in Rows & Columns and also the Data
Related to the Fields in the Data Area

Updating the Pivot Table with changes in existing data
Right click on the Pivot Table Field

Refresh

Updating the Pivot Table with new data added in data range
click on PivotTable Tools tab

select Change data source
specify the new range

Page 22 of 38

MsExcel-2007
Charts
Charts
Presenting Data in the form of a chart can makes a point much more effective.
Chart Terms and Basics
Every chart consists of Axis, Titles, Legend, data Series & Gridlines

Creating Charts
Select the data for the chart & click on Insert tab
Next click on the required chart type
select
the subtype in it for generating the chart.

Updating Chart
If you make any changes in the data Excel automatically updates those changes
in the Chart.

Changing Chart Type
Right-click on the chart & choose Chart Type or click on Chart Tools

next click on the button Change Chart Type.
Changing the Chart type

Adding Data labels and Axis Title

Select the chart and click on the Layout Tab


Specify the Chart Title, Axis, Data labels etc.

Page 23 of 38

MsExcel-2007
Data Validation
Excel allows you to validate the entry of Data, by which we can restrict, the entry of data in the
cells based on a given criteria.

Select the Cells to be Validated
Click on Data tab
Data Validation button
this will pop up
the Data Validation dialog box as shown below
In The Settings Tab specify the condition i.e. on
what basis the Data should be validated
In The Input Message Tab give appropriate Message

in the Error Alert Tab specify the Error Alert if user enters a invalid value.

Page 24 of 38

MsExcel-2007
Goal Seek and Scenarios
Goal Seek is used when you know the Output and want to find the Input based on the output

Click on Data tab
What if Analysis button
Goal Seek
Specify the Set Cell
Enter the
Value for it
Specify the changing Cell.

Scenarios are used to get the Output at different Input Values.

Click on Data tab
What if Analysis button
Scenario
Add
Give Scenario Name

Specify the changing cell
enter the value
Repeat this process for required number of
Scenarios
for generating a report of Scenarios click on
summary Button.

Page 25 of 38

MsExcel-2007
Solver
Solver is part of a suite of commands sometimes called what-if analysis tools. With Solver, you can
find an optimal value for a formula in one cell called the target cell on a worksheet. Solver
works with a group of cells that are related, either directly or indirectly, to the formula in the
target cell. Solver adjusts the values in the changing cells you specify called the adjustable
cells to produce the result you specify from the target cell formula. You can apply constraints to
restrict the values Solver can use in the model, and the constraints can refer to other cells that
affect the target cell formula.
Use Solver to determine the maximum or minimum value of one cell by changing other cells for
example, you can change the amount of your projected advertising budget and see the affect on
your projected profit amount.
Solver is an Add-Ins function which has to be added when we want to use.
Adding the solver tool

Click on
office button
Excel options
Add-Ins
Manage
Excel Add-Ins
Solver
(Adding Solver from Add-Ins) once it is added it will be available under data tab

Above figure: Shows Solver Parameter dialog box

Page 26 of 38

MsExcel-2007
Table
Data tables are part of a suite of commands sometimes called what-if analysis tools. A data table is
a range of cells that shows how changing certain values in your formulas affect the results of the
formulas. Data tables provide a shortcut for calculating multiple versions in one operation and a
way to view and compare the results of all of the different variations together on your worksheet.
We can create two types of data table a) One variable data table b) Two variable data table.
Creating a one-variable data table
You must design one-variable data tables so that input values are listed either down a column
(column-oriented) or across a row (row-oriented). Formulas used in a one-variable data table must
refer to an input cell.
1. Type the list of values you want to substitute in the input cell either down one column or
across one row.
2. Do one of the following:
o If the data table is column-oriented, type the formula in the row above the first
value and one cell to the right of the column of values. Type any additional formulas
to the right of the first formula.
o If the data table is row-oriented, type the formula in the column to the left of the
first value and one cell below the row of values. Type any additional formulas below
the first formula.
3. Select the range of cells that contains the formulas and values you want to substitute.
4. On the Data tab
What if Analysis
Under it select Data table

5. Do one of the following:

o If the data table is column-oriented, type the cell reference for the input cell in the
Column input cell box.
o If the data table is row-oriented, type the cell reference for the input cell in the
Row input cell box.

Page 27 of 38

MsExcel-2007

Above figure: Shows Data Table dialog box

Linking WorkSheets
Linking helps in updating any changes made in related worksheets
For linking do the following steps
Select the Data
copy
click in the New Sheet
Right click mouse
Paste Special
Paste
Link.

Hyperlink
Hyperlinks are references in a document to the location (address) of other information.
Creating a Hyperlink
Select the cell to be linked
click on Insert tab
next click on Hyperlink button
Choose the
file/web page /Bookmark to link
lastly click on Ok.

Page 28 of 38

MsExcel-2007
Protection
You can secure your Data by protecting the Workbook & Worksheets inside the Workbook. For
protecting both the workbook and worksheet select the Review tab
click on protect sheet and
protect workbook buttons respectively under changes category.

Protecting the Worksheet

Click on Review tab
Protect Sheet
this will pop up the dialog box as shown below
check
the required boxes and enter the password in the text box.

Page 29 of 38

MsExcel-2007
Protecting the Workbook
Click on Review tab
Protect Workbook
this will pop up the dialog box as shown below

check the required boxes and enter the password in the text box.

Note: The password you mention is case sensitive and you should remember very well the
password you assigned to the worksheet / workbook, because if you forget the password then you
will not be able to unprotect.

Page 30 of 38

MsExcel-2007
Consolidation
Data from different positions can brought in one place in a consolidated form. Consolidation can
be done by Position and by using Pivot Table.
Click on Data tab
Consolidate tool as shown below.
Consolidation Tool

Page 31 of 38

MsExcel-2007
Consolidate data by position
1. Click the upper-left cell of the destination area for the consolidated data.
2. On the Data tab, click Consolidate.
3. In the Function box, click the summary function you want Microsoft Excel to use to
consolidate the data.
4. In the Reference box, enter a source area you want to consolidate.
5. Click Add.
6. Repeat steps 4 and 5 for each source area to consolidate.
7. To update the consolidation table automatically when the source data changes, select the
Create links to source data check box.
To create links, the source and destination areas must be on different worksheets. Once
you create links, you cannot add new source areas or change the source areas that are
included in the consolidation.
Note When you consolidate by position, Microsoft Excel does not copy the category labels in the
source areas to the destination area. If you want labels for the destination worksheet, copy them
or enter them manually.

Sharing Workbook
Sharing allows distributing the same workbook on different computers and multiple persons can
work on it at the same time.
Steps for Setting up a shared workbook
1. On the Review tab, click Share Workbook, and then click the Editing tab.
2. Select the Allow changes by more than one user at the same time check box, and then
click OK.
3. When prompted, save the workbook.
4. On the Office button, click Save As, and then save the shared workbook on a network
location where other users can gain access to it.

Page 32 of 38

MsExcel-2007
Sharing Data between Word and Excel
Word with Excel
As Text
Copy data from Excel In Word right click mouse Paste Special As: Unformatted
Text.
As a Table
Copy data from Excel In Word right click mouse Paste.
As a Picture
Select the Data in Excel Hold Shift Key & right click mouse Copy Picture In
Word select Paste.
As a Spreadsheet
Copy data from Excel In Word right click mouse Paste Special As:
Microsoft Excel Worksheet Object.
Creating a worksheet in Word
In Word Click on the Insert Microsoft Excel Worksheet button
on the Standard Toolbar.

Excel and Word

Into a cell- Copy data from Word In Excel click on Home tab - Paste
Into a Text Box Copy data from Word In Excel, Click on Text Box on Drawing Toolbar & Paste.
Table Into Multiple Cells In Word Select Table & copy it and in Excel Paste the table.

Page 33 of 38

MsExcel-2007
Importing the data into Ms-Excel
We can import a Text file, MsAccess database table or even an Excel sheet in excel by using the
import external data wizard.
Importing a Text file into MsExcel: You can import a Delimited or Fixed width text file into excel by
using the import wizard. In case of importing a delimited file you have to specify the delimiter in
the wizard and in case of fixed width file you have to manually specify the width for each column.
Importing a Delimited text file
1. Insert a new worksheet for importing the data (you can import in any existing worksheet
also but make sure the imported data does not overwrite your existing data)
2. Click on Data tab
Click on the button from Text
3. This will pop-up a dialog box for selecting the file, browse to the folder where the file is
located and under file type select Text files.
4. Select the text file which you want to import and click on Import button this will pop-up
the dialog box as shown below

5. Select the option Delimited under choose file type and click on Next button
6. This will pop-up the dialog box for choosing the delimiter as shown below.

Page 34 of 38

MsExcel-2007

7. Select the appropriate delimiter and click on Next button

8. This will pop-up the dialog box for selecting the data format as shown below

9. Choose General from the Column data format options (Choosing General converts numeric
values to numbers, date values to dates and all remaining values to text format) and then
click on Finish button.
10. This will pop-up the Import data dialog box as shown below prompting you to specify the
location for placing the imported data. (if you want to place it in the existing worksheets
then choose existing worksheet and specify the location or click on option New worksheet)

Page 35 of 38

MsExcel-2007

11. For setting the properties of the data imported place the cursor in the data area, right click
the
mouse
and
choose
the
option
Data
Range
Properties.
12. This will pop-up the External Data Range Properties dialog box as shown below.

13. For updating the data imported in excel check the required boxes under Refresh Control
and click on OK button

Page 36 of 38

MsExcel-2007
Macro
A macro is a set of instructions, which can be called number of times to ease a repetitive task.
The instructions called statements are written on a macro sheet in the Visual Basic language.
View tab
Macro

Record a macro
1. On the View tab, point to Macro, and then click Record New Macro.
2. In the Macro name box, enter a name for the macro.
Note: The first character of the macro name must be a letter. Other characters can be
letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an
underscore character works well as a word separator.
3. To run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key
box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase
letters), where letter is any letter key on the keyboard. The shortcut key letter you use
cannot be a number or special character such as @ or #. The shortcut key will override any
default Microsoft Excel shortcut keys while the workbook that contains the macro is open.

Page 37 of 38

MsExcel-2007
4. In the Store macro in box, click the location where you want to store the macro.
If you want a macro to be available whenever you use Excel, store the macro in the
Personal Macro Workbook in the Excel Startup folder.
To include a description of the macro, type the description in the Description box.
5. Click OK.
If you select cells while running a macro, the macro will select the same cells regardless of
which cell is first selected because it records absolute cell references. If you want a macro
to select cells regardless of the position of the active cell when you run the macro, set the
macro recorder to record relative cell references. On the Stop Recording toolbar, click
Relative Reference
. Excel will continue to record macros with relative references until
you quit excel or until you click Relative Reference again.
6. Carry out the actions you want to record.
7. On the Macro button, click Stop Recording
Running a Recorded Macro
Select the View tab
click on the Macro button
View Macro
this will pop-up the dialog box
as shown below
select the macro to run
click on button Run
Or
Press the shortcut key you assigned while recording the Macro.

Deleting a Macro
Select the View tab
click on the Macro button
choose View Macro
this will pop up the
above dialog box
select the macro you want to delete and click on Delete button.

Page 38 of 38