www.bankersguru.
org
www.bankersguru.org
www.bankersguru.org
Microsoft Excel 2007
Microsoft Excel is a spreadsheet program included in the Microsoft Office
suite of applications. Spreadsheets present tables of values arranged in
rows and columns that can be manipulated mathematically using both
basic and complex arithmetic operations and functions
Microsoft Excel 2007 has a completely redesigned user interface. The
standard menus along the top have been removed and replaced with a
series of toolbars Microsoft calls “The Ribbon.”
The Office Logo
Clicking the Office logo at the top left of the screen will
provide most of the items formerly found under the
file menu. Beside the logo you also will find a disk icon
to save your document as well as the undo and redo
buttons. More buttons can be added to this “Quick
Access Toolbar”
As you can see here the Office logo
opens up listing your options for new,
open etc. and also contains a list of
your recent documents for quick
opening. Any of the items listed with
an arrow beside them will replace the
recent documents on the right with
the options associated with the menu
item.
www.bankersguru.org
www.bankersguru.org
Save as
The save as option will provide you with the most common file formats to
save your document in. The common ones are Excel Workbook, Excel
Macro-enabled Workbook, Excel Binary Workbook, and Excel 97-2003
Workbook.
Print
Here you can choose from Print, Quick Print and Print Preview. Print brings
up the standard print dialog box, quick print will print one copy without
any dialog box coming up and print preview will bring up the print preview
screen.
The Home Tab
The home tab the basic formatting tools found in Excel 2007 and onwards.
You will find seven sections, Clipboard, Font, Alignment, Number, Styles,
Cells and Editing. Clicking the down arrow beside any of the icons here
will drop down more options for that tool. Each section also contains an
arrow in the bottom right corner which will open a window containing the
options found in that section.
Clipboard
The Clipboard allows you to cut, copy, paste and copy
formatting from one place to another.
www.bankersguru.org
www.bankersguru.org
Font
The font section of the ribbon provides a
section to handle the basic text
formatting. Items such as bold,
underline, strikethrough, highlight and font type can be changed here.
Alignment
The alignment section provides icons to justify,
Vertical alignment, indents, text wrap, merge cells
and centre text and text orientation.
Number
The number section provides the options for formatting
numbers. You can choose the type of number, like date,
currency, percentage, fraction or general. You can also increase or
decrease the number of decimal places shown.
Styles
The styles section allows you to quickly change the
formatting of a section of cells by choosing one of the
predefined styles. You can choose different types of conditional
formatting, table formatting or cell styles. These are used to change the
visual appearance of a section to quickly show what is being displayed in
an area.
www.bankersguru.org
www.bankersguru.org
Cells
This section allows you to insert of delete cells, rows, columns,
or sheets. You can also format the height or width of columns
and rows, hide or unhide elements, organize or protect cells and sheets.
There are a lot of features in this section under the format option.
Editing
The editing section gives you options for inserting functions,
filling formulas across cells, clearing formatting and formulas,
sorting and finding.
Insert Tab
The insert tab has five sections for inserting most types of objects. The
sections are tables, illustrations, charts, links, text and symbols.
Tables
The tables section has an option for pivot tables and charts
and tables. You can select an area and turn it into a
formatted table or pivot table with these options. Once you
have created your table there is another tab which becomes available to
work with the table design. This tab is shown below.
www.bankersguru.org
www.bankersguru.org
Table Design
Illustrations
The Illustrations section allows you to insert pictures, clipart, shapes, and
SmartArt. After inserting or selecting a picture you are provided with a
new toolbar along the top shown here.
This toolbar gives you the ability to change the brightness, contrast, shape,
position, text wrapping and other options for the picture. Clicking off the
picture or on one of the other tabs will take you back to the standard
toolbars.
The Shapes option of the Illustrations section allows you to insert lines,
arrows, boxes, basic flowchart shapes and a number of others. The
SmartArt option provides features like org charts, flow charts, illustrated
lists and processes.
Charts
The charts section provides drop down menus
to insert different types of column, line, pie, bar, area, scatter and other
types of charts. Once you create a chart you will get three additional tabs
to work with your chart. They are shown below.
www.bankersguru.org
www.bankersguru.org
Chart Design Tab
Chart Layout Tab
Chart Format Tab
Links
The links section provides options for inserting hyperlinks to
your spreadsheet.
Text
Options in the text section include text box,
header and footer, WordArt and a number of
predefined text blocks like a signature line and
symbols.
www.bankersguru.org
www.bankersguru.org
Page Layout Tab
The page layout tab has five sections, Themes, Page Setup, Scale to Fit,
Sheet Options, and Arrange.
Themes
The themes section provides a quick way to format your
document. By choosing a theme you will have a set colour
scheme, font combinations, and effects.
Page Setup
Page setup provides you with the
tools to change margins, size,
orientation, columns, breaks,
backgrounds and add print titles to the document.
Scale To Fit
This section allows you to scale to fit your document onto
a certain number of pages or to scale it to a certain
percentage of it’s current size.
Sheet Options
This section gives you checkboxes to view or print headings
and gridlines.
www.bankersguru.org
www.bankersguru.org
Arrange
The arrange section is also found in the image toolbar
when an image is selected. Here you can change an
images position, the alignment, grouping and rotation or
the image.
Formulas Tab
The formulas tab contains four sections, function library, defined names,
formula auditing and calculation.
Function Library
This section gives you access to the large
number of predefined Excel functions. There are Auto Sum functions,
financial, logical, text, date and time, lookup and reference, math and trig,
and additional functions.
Some examples are :
Character Function -
UPPER CASE: It converts all text into upper case
Syntax: =UPPER("xyz")
Result: XYZ
www.bankersguru.org
www.bankersguru.org
LOWER CASE: It converts all text into lower case
Syntax: =lower(“XYZ")
Result: xyz
Arithmetic Function
SUM: It adds cell data and shows result in one cell.
Syntax: = SUM(A1:A10)
AVG: It finds average of cell data and shows result in one cell.
Syntax: = AVERAGE(A1:A10)
ROUND : It finds round figure of the digits
Syntax: =ROUND(Number, Digits)
Logical Function
If Function
Syntax: =IF(logical test ,value if true ,value if false)
Date And Time Function
DATE Function
Syntax: =DATE(year , month, date)
Time Function
Syntax: =TIME(hour , minute , second)
Now Function
Returns current date and time both. Syntax: =NOW()
www.bankersguru.org
www.bankersguru.org
Data Tab
The data tab contains five sections, get external data, connections, sort and
filter, data tools, and outline.
Get External Data
This is a single drop down item which allows you to refresh data
from an external source. You can pull in data from Access, the
Internet, text files or from other external sources.
Connections
Here is where you manage any connections to external
data.
Sort & Filter
Here you can sort or filter your data based on
criteria you specify. You can use simple
alphabetical sorting or create more complex filters to manipulate your
data.
Data Tools
With the data tools section you can take text
and turn it into columns, remove duplicates,
check your data to make sure it is valid based on different criteria.
Outline
The final section in the data tab provides tools to group or
ungroup rows or columns and obtain subtotals for grouped
items.
www.bankersguru.org
www.bankersguru.org
Review Tab
The review tab offers three sections which include proofing, comments,
and changes.
Proofing
The proofing section provides the standard spelling
and grammar check, a thesaurus, and research tools.
You will also find translation tools to help with single words or the whole
document. The translation of the entire document is done by an online
service called Wordlingo.
Comments
The comments section allows you to add
comments to a document for easier
collaboration.
Changes
The changes section allows you to protect
a sheet or the entire workbook.
You can also share a workbook. Some of the sharing features are only
available to users on a Windows domain. This authenticates the users
against the domain for access so this will not work for many people.
www.bankersguru.org
www.bankersguru.org
View Tab
The view tab offers five sections which include workbook views,
show/hide, zoom, window and macros.
Workbook Views
The workbook views section switches your display
between normal view, page layout, full screen
and page break preview.
Show/Hide
The show/hide section will toggle certain tools on or
off the screen including rulers, gridlines, message
bar, formula bar and headings.
Zoom
The zoom section provides tools to zoom into or out of the
document. You can choose your own zoom factor or use
the predefined zoom factor of 100% or you can select a zoom up to 400%.
Window
The window section allows you to create a
new window, arrange your windows one on
top of the other or split your window so the same document is viewed in
two screens one on top of the other.
Here, Freeze panes keeps the headings at the top and down the side of the
sheet from disappearing while scrolling.
www.bankersguru.org
www.bankersguru.org
Macros
Macros are sets of instructions in Microsoft Excel for tasks to
perform on a given worksheet or data. By recording or
programming certain actions, Excel users utilize macros to automate
repetitive tasks, format data sheets, or perform complex mathematical
operations.
Shortcut Keys Used In Excel
Shortcut Description
F2 Edit the selected cell.
F4 Repeat last action..
F5 Go to a specific cell.
F11 Create chart from selected data.
Ctrl + Shift + ; Enter the current time.
Ctrl + ; Enter the current date.
Shift + F11 Insert New Worksheet.
Alt+ Enter Allows for multiple lines of text in one cell.
Shift + F3 Open the Excel formula window.
www.bankersguru.org
www.bankersguru.org
Ctrl + 2 Bold highlighted selection.
Ctrl + 3 Italic highlighted selection.
Ctrl + K Insert link.
Ctrl + 4 Underline highlighted selection.
Ctrl + 1 Change the format of selected cells.
Ctrl + 5 Strikethrough highlighted selection.
Ctrl + Z Undo last action.
Ctrl + Space Select entire column.
Shift + Space Select entire row.
Ctrl + - Delete the selected column or row.
Ctrl + Shift + = Insert a new column or row.
Ctrl + Home Move to cell A1.
www.bankersguru.org
