MICROSOFT EXCEL 2007

Introduction
Microsoft released Excel 2007 in November 2006 with new user interface
known as fluent interface. We have already studied about Ms- Excel 2003. So, we
can easily learn Ms- Excel 2007 because most of the commands and tools are
similar to previous version. In Excel 2007 we can get totally new interface than
previous versions and some new features.
For example, File menu is replaced by office buttons. Toolbar replaced by
Quick access toolbar. Menu and its commands list are replaced by Ribbon. Zoom
control is given with slider etc.

Starting Excel 2007

To start Excel 2007, follow these steps:
Step 1: Click on the Start button and select All Programs.
Step 2: Choose Microsoft Office and click on Microsoft Office Excel 2007.

Opening from Run command

Step 1: Click on Start button and click on Run option.
Or,

1
 Press [ win key + r] keys than Run
dialog box will be displayed.
Step:2 Type excel in run dialog box.

Parts of Excel 2007 Window

The main parts of Excel 2007 window are
shown below.
Quick Access Toolbar Title Bar
Help Button

Office
Button

Ribbon

Cell
Column
Row

Sheet tabs
Status bar Worksheet view Zoom control
buttons

The user interface of Excel 2007 is similar to word 2007 window. In the
above figure, you have seen office button at the upper left corner of the window.
Near the office button, there is Quick Access Toolbar. It contains the most
commonly used commands. We can also add or remove commands from there.
Then, you can see title bar to the right of Quick Access Toolbar. The title bar
displays name of the currently opened document. Just below the title bar, you can
see a panel with fixed arrangement of command buttons and Tabs that is called
Ribbon. At the upper right corner of Ribbon Panel, there is a help button that
provides various information and tutorial about Excel.
The large area at the middle of the window is working area where we can
perform our calculations.

2
Creating New Workbook
In Excel 2007, we can create a new workbook using a Blank Template,
Installed templates or online available templates.

Using a Blank template

When we start Excel program, a blank workbook is automatically created. It
is the default template and based on blank template.
To create a new workbook using blank template,
follow these steps:
Step 1: Click on the Office Button.
Step 2: Click on New option.
Then New Document Dialog Box appears.
Step 3: Select Blank Workbook from the Blank and Recent option.
Step 4: Click on the Create button.

3
 Then, a New Blank workbook will appear.

Using the Installed Template

Excel 2007 provides various type
of templates which are installed while
installing Ms-Office 2007.
To create a new workbook using
installed template, follow these steps:
Step 1: Click on the Office Button.
Step 2: Click on New option.
Then, New Workbook Dialog
Box appears.
Step 3: Click on the Installed Templates located at the left side of the New
Workbook dialog box.
Step 4: Select desired template from the Installed Templates list.
Step 5: Click on Create Button.
Then, new workbook based on selected template will be created.

Using online available Template

There are lots of templates available in Microsoft's websites. To use these
templates, we have to connect out computer in the Internet.
To create a new workbook using online available template, follow the given
steps.
Step 1: Click on the Office Button.
Step 2: Click on New option.
Then, New Workbook
Dialog Box appears.
Step 3: Click on the desired template
category under the Microsoft
Office Online list.
Step 4: Select desired template from
the available Template list.
Step 5: Click on Download Button.
Then, Microsoft Office

4
 Genuine Advantage message box will appear.
Step 6: Click on Continue button.
Then, it starts to download the selected template from Microsoft Office
Website.

Saving Workbook
By default, Excel 2007 saves workbook with .xls extension. It always
provides various file formats for saving workbook. To save Excel workbook,
follow these steps:
Step 1: Click on Office Button.
Step 2: Click on Save option.
Then, Save As dialog
box will appear.
Step 3: Select the location
where you want to save
the document from the
Save In drop down list.
Step 4: Type the Name for this
document in the
Filename Text Box.
Step 5: Click on Save Button.

Opening an Existing Workbook

To open an existing workbook, follow these steps:
Step 1: Click on the Office Button.
Step 2: Click on the Open Option.
Then, a Open dialog box will appear.
Step 3: Select the required Workbook, that you want to open.
Step 4: Click on the Open Option.

5
Exiting from Excel
After completing your work on Excel, save all the work done and close
Excel program. To close Excel program, follow these steps:
Step 1: Click on Office Button.
Step 2: Click on Exit Excel Button located at the bottom of the pane.

Typing Data in to cell

We can type data into the worksheet cell in the similar way as previous
version of excel (excel 2003).
Place the cursor in the cell where you want to type data, then type required
data. To go to the next cell, use Enter key or Tab key.

6
Typing Data in the formula bar
Instead of typing data directly into the cell, we can also type data in the
formula bar. First of all, we have to click on the cell where we have to type data
and then type data in the formula bar. That means data typed on formula bar
appears on active cell.
To type data in the formula bar, follow these steps:
Step 1: Click on the Cell where you want to type data.
Step 2: Click on the Formula bar and type required data.

Adding Data using Auto fill

We can also quickly add data into worksheet cell using auto fill feature. By
using this feature, we can fill series of text as well as series of numbers.

Auto fill a Text series

Excel provides some built-in text series such as days of week, months of
year etc. To Autofill text services, we need to type only the first entry of the
services in the cell then other text services will automatically added to the
adjacent cells. To autofill text services, follow these steps:
Step 1: Click on the Cell where you want to begin text series.
Step 2: Type the first entry of the text series into the selected cell.
Step 3: Click on the fill handle of the cell and drag it across or down.
Step 4: Then release the mouse button.

7
Autofill a Number series
Excel also provides some built-in number series. To autofill number
series, we need to add the first two numbers of the services in the adjacent cells
then the other numbers in this service will
automatically added. To Autofill number
series, follow these steps:
Step 1: Click on the cell where you want to
begin number series.
Step 2: Type the first entry of the numberseries
into the selected cell.
Step 3: Type the second entry of the number
series into the adjacent cell.
Step 3: Select both the cell.
Step 4: Select the fill handle at the bottom
right corner of the second cell. Then, drag it across or down.
Step 4: Then, release the mouse button.

Inserting Row and Column

We can insert required number of rows and columns in our worksheet.

To insert row, follow these steps:

Step 1: Select the row above which you want to add new row.

8
Step 2: Click on the Home tab.
Step 3: Click on the drop-down arrow of Insert button.
Step 4: Click on the Insert Sheet Row Option.
Then, a new row will be added.

To insert column, follow these steps

Step 1: Select the column to the left of which you want to add new column.
Step 2: Click on the Home tab.
Step 3: Click on the drop-down arrow of Insert button.
Step 4: Click on the Insert Sheet Column Option.
Then, a new column will be added.

9
 We can also insert new row by right clicking on the row above where you
want to insert new row. Then select insert option.

Deleting Row and Column

Excel 2007 also allows us to delete unnecessary rows and columns from the
worksheet. To delete row, follow these steps:
Step 1: Select the rows which you want to delete.
Step 2: Click on the Delete button from the cells group in the Home tab.

To delete column
To delete column, follow these steps:
Step 1: Select the columns which you want to delete.
Step 2: Click on the Delete button from the cells group in the Home tab.

We can also delete row or column by right clicking on selected row or

column and click on delete option.

Hiding/ Unhiding Rows and Columns

We can also hide or unhide desired rows or columns in the worksheet.

To hide Rows/ Column

To hide rows/column follow these steps:
Step 1: Select the Rows/column which you want to hide.

10
Step 2: Click on the Format button in the Home tab and go to Hide & Unhide
option and click on Hide Rows or Hide Column option.

To Unhide Rows/Column
To unhide rows/columns follow these steps:
Step 1: Select the Rows/columns which you want to unhide.
Step 2: Click on the Format button in the Home tab and go to Hide & Unhide
option and click on Unhide Rows or Unhide Column option.

Deleting Worksheet
Excel allows us to delete any worksheet from the
workbook if that is not needed. But, the worksheet
should be empty which we want to delete. To delete
worksheet, follow these steps:
To delete column, follow these steps:
Step 1: Select the worksheet tab which you want to

11
 delete.
Step 2: Click on the Delete button from the cells group in the Home tab.
Step 3: Click on Delete sheet button from the drop-down list.

We can also delete sheet by right clicking on selected sheet and click on
delete option.

Renaming Worksheet
Excel also allows us to change name of the worksheet. We can give the
name of worksheet so that it represents the data contains in this worksheet. To
rename the worksheet, follow these steps:
Step 1: Select the Sheet which you want to rename.
Step 2: Click on the Format button in the Home tab and click on Rename Sheet
option.
Then, the selected sheet name will be highlighted.
Step 3: Type the new name for the sheet.

We can also rename the work sheet by double clicking on the sheet.

Changing Number Formats

Various number formats are available in Excel 2007. We can apply these
number formats for different types of data. To change Number format, follow
these steps:
Step 1: Select the Cell range containing the data whose number format you want
to change.
Step 2: Click on the drop- d o w n list of Number format combo box from the
Number group of Home tab.
Step 3: Select the required number format that you want to apply.

12
Applying Border to cells
We can highlight the important data in the cell by adding border to cells. To
apply border to cell, follow these steps:
Step 1: Select the cell range in which you want to apply border.
Step 2: Click on the drop- d o w n arrow of the border button in the font group of
Home tab.
Step 3: Select the required Border style from the drop-down menu.

13
Shorting Data
Excel provides facility to short data in ascending
or descending order. To short follow these steps:
Step 1: Select the data that you want to short.
Step 2: Click on the Short & Filter button from the
Home tab in Editing group.
Step 3: Click on required short option like, Short A to
Z or short Z to A.

Creating Table
Sometimes, we have to present data in the table. Excel provides facility to
present data in the form of table. We can create table either
from blank cell range or from existing Data range. To
create table from blank cell range, follow these steps:
Step 1: Select the cell range that you want to convert into
table.
Step 2: Click on Insert tab.
Step 3: Click on the Table button in the Table group.
Then, create table dialog box will appear.
Step 4: Click on the OK button.

Formatting a Table
Excel 2007 provides various predefined table formatting styles. We can
choose required table style to format table. To format table, follow these steps:
Step 1: Select the table that you want to format.
Step 2: Click on the Design tab.
Step 3: Click on the more button in the table Style group.
Then, drop down list of table style appears.
Step 4: Select the required style.

14
Formula
We need to perform various mathematical calculations in excel data. We
need to use different formulas to perform calculations. Formula used in excel are
like mathematical formula that help us to perform simple to very complex
calculations. The excel formula are created by using functions, operations and
cell references.

Using Formula
Excel formula always begins with an = (equal) sign. For example, suppose
wehave to find out the total amount by adding the values stored in D2, D3, D4 and
D5then we have to use the formula
= D2 + D3 + D4 + D5
Let's perform some calculations using formula.
Example 1:
In the following sales table, calculate Amount, Discount (10%), Total amount
and Grand total.

15
 =E7*F7
=SUM(G7:G11)
=G12*10%
=G12-G13

Step 1: Type required data in the worksheet.

Step 2: Click on the cell where you want to create formula.
Step 3: Type the equal to sign (=) in the selected cell.
Step 4: Click the first cell that you want to reference in the formula.
Step 5: Type the required operator to create formula.
Step 6: Click the next cell you want to reference in the formula.
Step 7: Then press Enter key.

Functions
Functions are predefined formula that we can use anywhere in the formula.
Functions make very easy to perform calculations. Suppose, we have to add
numbers stored in B1, B2, B3, B4, B5, B6. We can use function and B1:B6 is
cell reference. Some commonly used functions in excel are: SUM, PRODUCT,
AVERAGE, SQRT, MIN, MAX, COUNT, LOWER, UPPER, etc.

Using Functions
Like formula, functions with an equal sign (=). We can apply function in
the cell the result have to display. To apply formula in the cell, follow these steps:
Step 1: Click on the cell in which you want to apply function.
Step 2: Click on the Formulas tab.
Step 3: Click on the Insert Function button from the function library group.

16
 Then, Insert Function dialog
box will appear.
Step 4: Click the down arrow of
Select category.
Step 5: Select the Category option.
Step 6: Select the Function category
from the drop-down menu.
Step 7: Select the required function.
Step 8: Click on the OK button.
Step 9: Type the cell range to the
selected function in the
Number one option.
Step 10: Click on OK button.

Perform the following calculations in Excel by creating formulas

Example 2:

Criteria
1. Amount.
2. Discount.
3. Net amount.
4. Grand amount.
5. Average rate of item.
6. Minimum rate of item.
7. Maximum quantity of item.
8. Grand Total
9. Difference of rate between Easy learning and English grammar.

17
Example 3:
Criteria:
1. Calculate the Discount Amount and Total Amount of Vegetable.
2. If anyone buys cabbage more than 5kg, then discount 3%, and
If anyone buys Tomatoes more than 10kg, then discount 5%.

18
 Example 4:
Calculate Bonus, Tax, HA and Total

1. Bonus will give 15% of salary if he/she is from Birgunj.

2. Bonus will give 5% of salary if he/she is from outside of Bigunj and
Janakpur.
3. Bonus will give 10% of salary if he/she is from Janakpur.
Tax
1. Tax will pay 10% of salary and bonus, if his/her post is engineer.
2. Tax will pay 5% of salary and bonus, if his/her post is manager.
HA
1. HA will give 12% of salary, if he/her salary is less than equal to 5000.

Example 5:

Criteria:
1. Bonus will give 15% of salary, if his/her salary is greater than 4000 and less than
equal to 10000.
2. Tax will pay 12% of salary and bonus if his/her post is not engineer.
3. HA will give 10% of salary if he/she is not from Birgunj.

19
Example 6:
Perform the following calculation to find salary.
Calculate Bonus, HA and Tax on basis of following criteria:
Criteria
10. Staff having IA qualification will get 10% bonus of basic salary.
11. Staff having BA qualification will get 13% bonus of basic salary.
12. Staff having MA qualification will get 20% bonus of basic salary.
13. Staff having PHD qualification will get 30% bonus of basic salary.
14. Staff from Palapa will get 15% HA.
15. Staff from Butwal will get 13% HA.
16. Staff from Pokhara will get 12% HA.
17. Staff from Biratnagar will get 18% HA.
18. Staff from Kathmandu will not get HA.

Type the above formulas in the respective cells and press Enter key to
conform entry.

Example 7:
Calculate Total marks, Result, Percentage, Division, Remarks and Rank to
find out the SLC result of the student on the basis of given criteria.
Criteria:
1. A student who got 32 or more than 32 marks in each subject will be

20
 passed otherwise fail.
2. A student who got 80% or more than 80% will get distinction, more
than 60% and less than 80% first division, more than 45% and less
than 60% second division, more than 32% and less than 45% third
division and below 32% will be failed.
3. If division is "Distinction" remarks is "Excellent", IF division is "first"
remarks is "very good", IF division is "second" remarks is "good" and
IF division is "third" remarks is "satisfactory" If division is “Failed”
remarks is "try again"

Type the above formulas in the respective cells and press Enter key toconform entry.

Creating Chart
Chart is a graphical representation of data. With the help of chart, we can
easily compare data values present in the worksheet. In excel, we can easily
create various type of charts according to our requirement. Excel 2007 provides
different varieties of charts such as: line, column, Bar, Pie, Area, etc. Some chart
types are further divided into other categories. For example, column chart has

21
five sub types. Such as: 2-d column, 3-d column, cylinder, cone and pyramid. To
create a chart from the worksheet data, follow these steps:
Step 1: Type some data in the worksheet.
Step 2: Select the range of data that you want to convert into a chart.
Step 3: Click on the Insert tab.
Step 4: Click on the required chart type from the chart group.
Then, displays all available charts in the selected chart type.
Step 5: Select the required chart from the available list.

Adding Chart Title

After creating chart, we can add title for this chart that makes easy to
understand. The title indicates that what data is represented by this chart. To add
chart title follow these steps:
Step 1: Select the chart in which you want to add title. We can select the chart by
clicking on the blank area of the chart.
Step 2: Click on the Layout tab.
Step 3: Click on the Chart Title button from the Labels group.
Step 4: Select the options from the drop-down menu.

22
 Then a text placeholder will appear on the chart.
Step 5: Type the required title for the chart.

Adding Axis Title

Horizontal axis is known as x-axis and the vertical axis is known as y-axis.
We can add title to both the axis of a chart. To add axis title follow these steps:
Step 1: Select the chart in which you want to add title.
Step 2: Click on the Layout tab.
Step 3: Click on the Axis Title button from the Labels group.
Step 4: Go to the Primary Horizontal Axis Title option in the drop-down menu.
Step 5: Click on the Title Below Axis option.
Then, a text placeholder will appear on the chart.
Step 5: Type the required name for the horizontal axis of the chart.

23
 Similarly, you can give Primary Vertical Access title for the chart.

Changing chart style

We can apply different styles in chart elements. Excel 2007 provides
various predefined chart styles. To change the chart style, follow these steps.
Step 1: Select the chart whose style you want to change.
Step 2: Click on the Design tab.
Step 3: Select New Chart Style from the available chart styles.

Changing chart type

To change the chart type, follow these steps:
Step 1: Select the chart whose chart type you want to change.
Step 2: Click on the Design tab.
Step 3: Click on the Chart Type button from the Type group.
Step 4: Select the New chart type from the left side pane in the change chart type
dialog box.

24
Step 5: From the available chart list, click on the desired one.
Step 6: Click on the OK button.

Printing a worksheet or a workbook

In Excel, we can print only selected (active) worksheet or entire workbook.
We can also print only the selected portion of a worksheet.

Previewing worksheet
Before printing, we have to preview
it because it displays how data in a
worksheet appears when it is sent for
printing. To preview worksheet, follow
these steps:
Step 1: Open the worksheet which you
want to print.
Step 2: Click on Office button and go to
print option.
Step 3: Click on Print Preview option
available at the right side.
Step 4: Click on Close Print Preview option to close preview window.

Printing
To print worksheet or workbook follow these steps:
Step 1: Open the worksheet which you want to print.

25
Step 2: Click on Office button and click on the print option.
Then, a print dialog box will appear.
Step 3: Set required options in the print dialog box.
Step 4: Click on the OK button.

To print all the worksheet in the workbook select the radio button of the
Entire Workbook option in the Print dialog box.

Microsoft Excel 2007 Shortcut keys

CTRL+SHIFT+( Unhides any hidden rows within the selection.
CTRL+SHIFT+) Unhides any hidden columns within the selection.
CTRL+SHIFT+& Applies the outline border to the selected cells.
CTRL+SHIFT_ Removes the outline border from the selected cells.
CTRL+SHIFT+~ Applies the General number format.
CTRL+SHIFT+$ Applies the Currency format with two decimal places (negative
numbers in parentheses).
CTRL+SHIFT+% Applies the Percentage format with no decimal places.
CTRL+SHIFT+^ Applies the Exponential number format with two decimal
places.
CTRL+SHIFT+# Applies the Date format with the day, month, and year.
CTRL+SHIFT+@ Applies the Time format with the hour and minute, and AM or
PM.
CTRL+SHIFT+! Applies the Number format with two decimal places, thousands
separator, and minus sign (-) for negative values.

26
CTRL+SHIFT+: Enters the current time.
CTRL+SHIFT+Plus(+) Displays the Insert dialog box to insert blank cells.
CTRL+Minus (-) Displays the Delete dialog box to delete the selected cells.
CTRL+; Enters the current date.
CTRL+' Copies a formula from the cell above the active cell into the cell or
the Formula Bar.
CTRL+9 Hides the selected rows.
CTRL+0 Hides the selected columns.
CTRL+B Applies or removes bold formatting.
CTRL+C Copies the selected cells.
CTRL+C followed by another CTRL+C displays the Clipboard.
CTRL+G Displays the Go To dialog box.
F5 also displays this dialog box.
CTRL+I Applies or removes italic formatting.
CTRL+N Creates a new, blank workbook.
CTRL+O Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O selects all cells that contain comments.
CTRL+P Displays the Print dialog box.
CTRL+SHIFT+P opens the Format Cells dialog box with the
Font tab selected.
CTRL+S Saves the active file with its current file name, location, and file
format.
CTRL+T Displays the Create Table dialog box.
CTRL+U Applies or removes underlining.
CTRL+V To past cut or copied objects or text.
CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL+Y Repeats the last command or action, if possible.
CTRL+Z To reverse the last command or to delete the last entry that you
typed.

27