Office Clipboard: this is
a panel which Ms Excel (Microsoft Excel)
It is an electronic spread sheet
program of office package use to tabulation work or calculation based work.
Like product analysis, billing, mark sheet creation accounting.
Ms excel - workbook
File extension - .xls
File contains - work sheets
Used to store numeric data
File extension - .xls
File contains - work sheets
Used to store numeric data
Starting Microsoft Excel:
Ø
Start → All Programs
→ Ms
office → Microsoft Excel
Ø
Start → run → type “excel” → Ok.
v
Workbook: It is collection of many
worksheets. Excel has three default worksheet.
v
Worksheets is made of many rows and column and also called “spreadsheet.”
v
There are 256 columns and 65536 rows in a work sheet
v
On the worksheet intersection of a row and a columns is called is cell.
v
Each cell has s specific address, which is combination of number and
letter For Example; A1, E10.
v
On the work sheet a thick Rectangle box represents currently selected or
“Active cell”.
v
Name Box indicates, what cell you are in.
(Active Cell)
v
Formula bar indicate the contents of the cell
selected, if you have created a formula, the formula will appear in this space.
Row: A row is a series of data banks laid out in
a horizontal fashion
in a table or spreadsheet.
Columns : Vertical parts of the spared sheet
or worksheet.
Cell : Intersection point of row and
column is called cell.
Value : Numeric entry of a cell is called Value.
Label : Set of character is called Label.
Address Bar : Address bar is the left part of the formula bar, also called name box.
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.
Scroll Bar:
1)
Horizontal
Scroll Bar (Left to Right)
2)
Vertical
Scroll Bar (Up to Down)
File Menu:
New : Open New workbook (File).
Open : By default opens "My Documents" folder to
open an existing workbook. You can browse to other folder or drive to open an
existing file.
Close : Closes the active document but does not quit the application.
Save : saves the active document with its current file name,
location and format.
Save as : Saves the active file with a different file name,
location or file format.
Save as Web Page : Enables to save the
currently open worksheet as a web page.
Search: Enables to search for a file or document on
the user's computer or on the network.
Page Setup: Enable setting up width, height, margins,
orientation, header, footer, and orientation etc. of worksheets in the
workbook.
Print Area: Enables setting up or clearing the print area of
the worksheet.
Print Preview: Displays print preview of the worksheet.
Print : Prints the active file, also gives
the opportunity to change print options.
Properties: Displays the
property sheet for the active file.
Exit : Closes Microsoft Excel
Edit Menu:
Undo (Ctrl + Z): this is to undo or cancel or
delete or discard the last action you have done within our document.
Redo (Ctrl + Y): this is to redo the action that you have undo in the document.
Cut (Ctrl + X): to move selected content from one location to another using cut and paste
Redo (Ctrl + Y): this is to redo the action that you have undo in the document.
Cut (Ctrl + X): to move selected content from one location to another using cut and paste
Options.
Copy (Ctrl + C).:
this is to make duplicate copy of the selected content in your document using
copy and paste options.
contains
the slots of contents that have been cut or copied. It can contain 24 multiple
different contents.
Paste (Ctrl + V):
this is to paste the selected content that has been selected using cut or copy
option.
Paste Special: Ten
choices are available when making this selection; examples include pasting
formulas, values, and comments.
1)
All
: Pastes
the cell’s contents, formats, and data validation from the Windows Clipboard.
2)
Formulas
: Pastes
formulas, but not formatting.
3)
Values
: Paste
only values not the formulas.
4)
Formats
: Pastes
only the formatting of the source range.
5)
Comments
: Pastes
the comments with the respective cells.
6)
Validation
: Paste
validation applied in the cells.
7)
All
except borders : Pastes everything except borders that
appear in the source range.
8)
Column
Width : Pastes formulas, and also duplicates the column width of the
copied cells.
9)
Formulas
& Number Formats : Pastes formulas and number formatting
only.
10)Values & Number Formats : Pastes the results of formulas, plus
the number
Fill: Fill
contents of a selected cell Up, Down, Left or Right. It is also use to make a
series of any number.
Clear: Deletes the selected object or text, but does not
place it on the clipboard. Four choices are available; All, Format, Contents or
Comments.
Delete: This menu
entry can be used to delete entire rows or columns.
Delete Sheet: This
option use to delete sheet. You cannot undo this command.
Move or copy Sheet: Moves or copies the
selected sheets to another workbook or to different location with in the same
workbook.
Find: Search selected cells or sheets for the characters
you specify and selects the first cell that contains those characters.
Replace: Search for and replace the specified text and
formatting.
Go to: Scrolls through
the worksheet and select the cell, range or cell with special characteristics
you specify.
View Menu:
Normal: This Option shows the actual view of Ms Excel.
Page Break
Preview: Switches the active
worksheet to page break preview, which is an editing view that displays your
worksheet as it will print. In page break preview, you can move page breaks by
dragging them left, right, up or down. Ms excel automatically scales the
worksheet to fit the columns and rows to the page.
Task
pane: Displays the task pane, an
area where you can create new files, search for information, views the contents
of the clipboard, and perform other tasks.
Toolbars: Hide or show the different toolbars.
Formula
Bar: Displays or hides the formula
bars.
Status
Bar: Show or hides the status
bars.
Header
and Footer : Adds or changes the
text that appears at the top and bottom of every page or slide.
Comments
: Turns display of comments on the
worksheet on or off.
Custom
Views : Create different views of
a worksheet. A view provides an easy way to see you data with different
options. You can display, print and store different views without saving them
as separate sheets.
Full
Screen : hides most screen
elements so that you can view more of your document. To witch back to your previous view, click Full Screen or press ESC.
Zoom : Controls how large or small the current file
appears on the screen.
Insert Menu :
Cells : Use this command to
insert a cell. A pop-up window allows you to move existing data down or to the
right. You can also insert rows or columns with this window.
Rows : Inserts a new row in
the spreadsheet, above the row that contains the active cell.
Columns : Inserts a new column in the spreadsheet, to the
left of the column that contains the active cell.
Worksheet : By default, an Excel workbook is made up
of three worksheets. You may insert as many additional sheets as you require.
Sheets are inserted in front of the current worksheet.
Chart : This adds a chart of the selected data, or of the
entire worksheet if you have no data selected.
Page Break : Inserts page breaks above and to the left
of the active cell. To avoid adding a page break to the left, make sure a cell
in Column A is selected before inserting the break.
Function : Opens the Paste Function window allowing the
selection of a specific equation to go in the active cell.
1 Count and Sum: The most used functions in Excel are the functions
that count and sum. You can count and sum based on one criteria or multiple
criteria.
2 Logical: Learn how to use Excel's logical functions such as
the IF, AND and OR function.
3 Cell
References: Cell
references in Excel are very important. Understand the difference between
relative, absolute and mixed reference, and you are on your way to success.
4 Date &
Time: To enter a date
in Excel, use the "/" or "-" characters. To enter a time,
use the ":" (colon). You can also enter a date and a time in one
cell.
5 Text: Excel has many functions to offer when it comes to
manipulating text strings.
6 Lookup &
Reference: Learn all
about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP,
MATCH, and INDEX and CHOOSE function.
7 Financial: This chapter illustrates Excel's most popular
financial functions.
8 Statistical: An overview of some very useful statistical
functions in Excel.
9 Round: This chapter illustrates three functions to round
numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.
10 Formula
Errors: This chapter
teaches you how to deal with some common formula errors in Excel.
11 Array
Formulas: This chapter
helps you understand array formulas in Excel. Single cell array formulas
perform multiple calculations in one cell.
Count
To count the number of
cells that contain numbers, use the COUNT function.
Countif
To count cells based on
one criteria (for example, higher than 9), use the following COUNTIF function.
Sum
To sum a range of cells,
use the SUM function.
Sumif
To sum cells based on one
criteria (for example, higher than 9), use the following SUMIF function (two
arguments).
2 Logical
Function :
If Function
The IF
function checks whether a condition is met, and returns one value if TRUE
and another value if FALSE.
1. Select cell C2 and
enter the following function.
The IF function returns
Correct because the value in cell A1 is higher than 10.
The AND
Function returns TRUE if all conditions are true and returns FALSE if any
of the conditions are false.
1. Select cell D2 and
enter the following formula.
The AND function returns
FALSE because the value in cell B2 is not higher than 5. As a result the IF
function returns Incorrect.
The OR
function returns TRUE if any of the conditions are TRUE and returns FALSE
if all conditions are false.
1. Select cell E2 and
enter the following formula.
The OR function returns
TRUE because the value in cell A1 is higher than 10. As a result the IF
function returns Correct.
3. Cell references
Cell references
in Excel are very important. Understand the difference between
relative, absolute and mixed reference, and you are on your way to success.
By default, Excel
uses relative reference. See the formula in cell D2 below. Cell D2
references (points to) cell B2 and cell C2. Both references are relative.
1. Select cell D2, click
on the lower right corner of cell D2 and drag it
down to cell D5.
Cell D3 references cell B3
and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5
and cell C5. In other words: each cell references its two neighbors on the
left.
Absolute Reference
See the formula in cell E3
below.
1. To create
an absolute reference to cell H3, place a $ symbol in front of the
column letter and row number of cell H3 ($H$3) in the formula of cell E3.
2. Now we can quickly drag
this formula to the other cells.
The reference to cell H3
is fixed (when we drag the formula down and across). As a result, the correct
lengths and widths in inches are calculated.
Mixed Reference
Sometimes we need a
combination of relative and absolute reference (mixed
reference).
1. See the formula in cell
F2 below.
2. We want to copy this
formula to the other cells quickly. Drag cell F2 across one cell, and look at
the formula in cell G2.
Do you see what happens? The reference to the price
should be a fixed reference to column B. Solution: place a $
symbol in front of the column letter of cell B2 ($B2) in the formula of cell
F2. In a similar way, when we drag cell F2 down, the reference to the reduction
should be a fixed reference to row 6. Solution: place a $ symbol
in front of the row number of cell B6 (B$6) in the formula of cell F2.
Result:
Note: we don't place a $ symbol in front of the row
number of B2 (this way we allow the reference to change from B2 (Jeans) to B3
(Shirts) when we drag the formula down). In a similar way, we don't place a $
symbol in front of the column letter of B6 (this way we allow the reference to
change from B6 (Jan) to C6 (Feb) and D6 (Mar) when we drag the formula across).
3. Now we can quickly drag this formula to the
other cells.
The references to column B
and row 6 are fixed.
4. Date and Time Function :
To enter
a date in Excel, use the "/" or "-"
characters. To enter a time, use the ":" (colon). You can also
enter a date and a time in one cell.
Note: Dates are in US
Format. Months first, Days second. This type of format depends on your windows
regional settings. Learn more about Date and Time
formats.
Year, Month, Day
To get the year of a date,
use the YEAR function.
Note: use the MONTH and
DAY function to get the month and day of a date.
1. To add a number of days
to a date, use the following simple formula.
2. To add a number of
years, months and/or days, use the DATE function.
Note: use the MINUTE and
SECOND function to return the minute and second.
To add a number of hours,
minutes and/or seconds, use the TIME function.
Note: Excel adds 2 hours,
10 + 1 = 11 minutes and 70 - 60 = 10 seconds.
Statistical Functions
This chapter gives an
overview of some very useful statistical functions in Excel.
Comment: Have something to say about the contents of a
cell? Add a small note with the appearance of a Post-It note.
Picture : Insert
pictures from clip art or a file. You can also insert auto shapes, word art, or
a chart.
Diagram : Creates an organization chart or a cycle, radial,
pyramid, or target diagram in your
document.
Object : Insert
an object such as clip art, word art, an equation or much more.
Hyperlink : An
interesting use of hyperlinks is to place a link to any document stored on your
computer. You can later open that document by clicking on the link.
Format
Menu :
Cells : Format the way a number is displayed; alignment of
data in the cell(s), font (size, color, style, etc.), borders and colors for
the selected cells, and you may also lock the contents of a cell here.
Row : Specify a row height, choose auto-fit, and hide or
un - hide the selected row.
selected column, or
choose the standard width for a column.
Sheet : Here you can rename the sheet if sheet 1 is not
descriptive enough (and it's not), you can hide or un - hide a sheet, or you
can tile an image in the background of the entire sheet.
Auto Format : There are sixteen pre-designed formats to
change the look of your spreadsheet. You may apply the format to the entire
sheet or only to selected cells.
Conditional Formatting : This option is used to apply
formatting in selected area according to given condition.
Style : Opens a Style window which will lead to the Format
Cells window if you wish to Modify the format.
Tools Menu :
Spelling : Checks spelling in the active document, file
workbook or item.
Error Checking : Checks the active Worksheets for errors.
Track Change :
Ø
Highlight Changes : Highlights changes to cell contents in a shared
workbook, including moved and pasted contents and inserted and delete rows and
columns.
Ø
Track changes accept or reject changes: Finds and selects each tracked in a documents so
that you can review, accept or reject the change.
Compare and Merge Workbook: Combines changes from multiple copies of a shared
workbook into one workbook.
Goal Seek : This option is used to get a target value without
changing the formula.
Scenarios : This option
is used to fix value in cell according to different Scenarios.
Formula Auditing : This Option mathematical relation between cells.
Data Menu :
Sort : Arranges the information in selected rows or lists
alphabetically, numbering or by date.
Filter : This options used to filter data according to the
choice.
Form :
Subtotal : Calculates subtotal and Grand total value for the
labeled columns you select. Ms Excel automatically inserts and labels the total
rows and outlines the list.
Validation : This option is used to set a validation in selected
area.
Table : create a data table based on input values and
formulas you define. Data tables can be used to show the results of changing
value in your formulas.
Convert Text to table: Converts the selected text to a table.
Consolidate : This option is use to applied a formula in
different sheet value and show the result the different sheet.
Group an
Outline: Group and Outline enables to group or un-group rows
containing details data from rows containing totals for such rows. The Outline
enables to outline the range containing details data and summary data. The
symbols '1','2','3','+','-' enable to show / hide details rows and / or the
range.
Pivot table and pivot chart report: Starts the pivot table and pivot chart wizard,
which guides you through creating or modifying a pivot table or pivot chart
report.
Window Menu :
New Window : Opens a new window with the same contents as the
active window so you can view different parts of a file at the same time.
Compare side by side:
Arrange : Displays all open files in separate windows on the
screen. The arrange command makes it easier to drag between files.
Hide : Hides the
active workbook window. A hidden window remains open.
Unhide: Displays hidden workbook window.
Freeze Panes : This option freeze the area of sheet from the
selected point.
Microsoft Excel
Reviewed by Up bord 2020
on
19:33:00
Rating:
Reviewed by Up bord 2020
on
19:33:00
Rating:
No comments: