(PDF) Microsoft Excel 2010 Full Notes in English with Explanation :- Infinity4Education
MICROSOFT EXCEL 2010 NOTES
1. Window manipulation buttons - minimize,
maximize and close window 2. Title bar - contains document's name,
extension (.xlsx) and the program name
3.
Tabs:
•
File Page
layout Review
•
Home Formulas
View
•
Insert Data
4.
Toolbar
•
some tools have hidden tools. If they do, they
have an arrow in their lower right corner which, when clicked, displays
additional options
•
tools that cannot be applied to a selected
object are greyed out:
5.
Selected cells – before we can enter a text, number,
“Sparkline” (chart contained within one cell) or other data, we must first
select a cell or cells
6.
Column titles (letters)
7.
Row titles (numbers)
8.
Sheets – can be renamed
9.
Scroll bars for horizontal and vertical navigation
(sliders)
10.Status bar - displays information about
some special functions of Microsoft Excel
11.Formula Bar – for cell contents
including formulas or text
Spreadsheet or
Workbook: a Microsoft Excel file which contains any number of worksheets (3
by default)
Worksheet: consists
of a large number of cells arranged in columns and rows that form a table
Cell: basic
element in Excel for data entry (text, number, formula)
•
cell address:
column letter and row number on a worksheet, e.g. A1, C7, F25
Selecting cells - press the left mouse button on a cell in
order to select it.
Enter data – data can be added cells and existing content
can also be edited.
•
Add content to cell – single-click with the
left mouse button on a cell and enter any extra data.
•
Change the existing content – single click on the
selected cell and then click on the formula bar to make changes to the data.
Alternatively, press F2 after selecting the cell to move the cursor into the
formula bar
By default numbers align to the right side and the text aligns
to the left side of the cell. However this can be customised using the Alignment tools in the toolbar.
If entered content exceeds cell width, it will be
displayed over adjacent cells, provided that the adjacent cells have no data
entered. However this can also be customised using the Wrap Text option in the Alignment
toolbar (see picture above).
To move to another cell: you can use the TAB key to move to
the right, the ENTER key to move down, the keys with arrow on the keyboard, or
the left mouse button.
To select a range of cells:
•
select the first cell in a range, press and hold
the left mouse button, move the mouse to the last cell and release the left
button, or
•
select the first cell in a range, press and hold
the Shift key, select the last cell in the range and release the Shift key
Select a row or column: press the mouse button on the row
number or column letter.
Select several adjacent rows: press the left mouse button
on the row number, press and hold the left mouse button, move the mouse to the
last row and release the left button (or use the Shift key, while it is
pressed, select the first then the last row and then release the Shift key).
Select several non-adjacent cells, rows and columns: press
the left mouse button on the row number in order to select it, press and hold
the Ctrl key, select other rows and then release the Ctrl key.
Rename worksheet:
•
while we are positioned in the worksheet, we
want to rename: press the Format button on the Home ribbon, choose the Rename
Sheet option, enter a new name and press the Enter key, or
•
press the right mouse button on the tab of the
worksheet and from the quick menu choose the Rename option, or
•
double click on the tab of the worksheet and
type the new worksheet name.
Copying and moving worksheets within or between
spreadsheets (spreadsheets must be open):
1. press
the right mouse button on the worksheets sheet tab and choose the Move or Copy
sheet option
2. within
the dialog box that appears, choose the workbook you want to move the sheet to,
and the sheet before which it will be placed
If you want to copy the sheet, mark the checkbox next to
the Create a Copy option, otherwise the worksheet will be moved.
Deleting cell content – select one or more cells (with the
Shift key if they are adjacent, or with the Ctrl key if they are not), and
press the Delete key or Delete button on the Home ribbon.
Always select the cells first and then the
desired tool. Arrow in the lower right corner will provide you with additional
options
Undo change and
redo change
Undo Redo
Pressing the arrow next to the tool opens a drop-down list
of all the changes that we have made.
File menu
Tool
Save (keyboard shortcut Ctrl + S)
•
Save in - choose the location where the file
will saved
•
File name - type in the file name
•
Save as type – choose the file type - format in
which file will be saved
Creating a new workbook: press the Office button, choose
the New option, then Blank workbook, and then press the Create button (or use
the keyboard shortcut Ctrl + N)
Creating a new file can be facilitated by using a template
– extension .xltx
Creating a new spreadsheet using template: after choosing
the New option, select the Sample Templates option
Open an existing
file - to open several files at
once, use the Ctrl key or the Shift key to select them
Print:
•
Printer – choose printer
•
Copies – number of copies to be printed
•
Range o Print active sheets o Print entire workbook o Print selection
•
Orientation o Portrait - vertical paper o Landscape-
horizontal paper
•
Margins: - Normal - Narrow -
Wide
•
Scaling:
o
No scaling
o Fit
all columns to one page o Fit sheet to one page o Fit
all rows to one page
•
Collated – will print the entire worksheet from
start to finish, then will make copies according to the selected number of copies;
Uncollated - will print the first page according to the
selected number of copies, then the second page, etc.
•
Page setup
o Page
– on the drop-down menu, choose paper size o Margins –manually set the margins
o
Header / Footer – enter header / footer
information within the fields
o
Sheets – select a cell range that will
automatically print and rows that will repeat on each page of the worksheet
Share – there are new features that make it easy to share
documents:
•
Change File Type – save the file in another file
type - format
•
Create PDF/XPS Document – save the file in a
fixed format, cannot be easily changed
Options
•
on the General tab, you can enter a username
•
on the Save tab > there are fields to enter
“AutoRecover file location” and “Default file location”
Home tab
Font: (drop-down
menu) Times New Roman, Verdana, Arial, Calibri etc.
Font
Size: (drop-down menu)
Text formatting
Bold Italic Underline
- Ctrl
+ B -
Ctrl + I - Ctrl + U Ã keyboard shortcuts
Font Color 
- choose Font color (drop-down menu)
- choose Font color (drop-down menu)
Text alignment: - horizontal alignment:
- top, middle or the bottom of
the cell
- top, middle or the bottom of
the cell
- vertical
alignment -Align Left, Center, Align Right, Justify
Merge and center – tool to merge
the selected cell range and center the content
Orientation – tool for setting the direction
of the content in a cell
Wrap Text – wraps text within cells width in
order to make it visible contain the text within the cell and ensure visibility
when next cell (horizontally) is not empty.
Number Format – set the number format in which
numbers will be displayed
Increase / decrease number of
decimal places displayed
Conditional Formatting – tool
for automatically setting the format of a cell based on the inputted
conditions. The formatting and conditions can be customized.
Insert – tool for inserting
cells, columns, rows, worksheets
Delete – tool for deleting cell
content and/or cell formatting; deleting columns, rows, worksheets
•
Format – tool for formatting selected cells,
columns, rows, worksheets
•
Protect sheet – enter a password and protect the
entire or a part of a sheet (cell range)
•
Rename Sheet - enter a name and press the Enter
key
•
Lock cell –
functional only if we protect the worksheet first Enter function:
1.
select a cell range
2.
enter a function via menu shown on the right
Or: 1. select the cell in which you want to
enter function value
2. enter
the symbol „=“
3. enter
the function manually (e.g. „sum“), and the cell range to which the function
will apply, respecting syntax
|
Most often used functions:
|
|
|
=SUM(cell range)
|
à adding the numbers in
selected cells
|
|
=AVERAGE(cell range)
|
Ã
finds the average value
|
|
=MIN(cell range)
|
Ã
finds the smallest value
|
|
=MAX(cell range)
|
Ã
finds the biggest value
|
Deleting options:
•
Clear All
•
Clear Contents
•
Clear Formats
Sort A to Z - sort
data within a table from minimum to maximum value, or
Sort Z to A – sort data within a table from maximum to
minimum values
Find: enter a word or phrase and
press the Find button 
Replace: -
Find What – field to enter the word we are searching for;
- Replace With - field to enter the word that we want to
use as a replacement
Format Painter - copy formatting from one part of the text to
another
Help in MS Excel , or F1 on the keyboard
Insert tab
automatically defines its size and position.
Arrange
– sets elements in front of (option Bring forward) or behind (option Send to
back) other elements. - opens color palette
- opens color palette and
options for choosing type and thickness of lines - effects: 3D, shadow, reflection,
rotation etc.
Pictures
•
to select a picture: press the left mouse button
on it, the picture will get squares on the border called control points which
allows the size of the image to be manipulated.
•
set object size:
o manually: select an object, move some of the control points
using the drag-and-drop method o enter values: press the right mouse
button on the object and choose the Format Picture option; on the Size tab,
enter values for height and width
While the object is selected, we can open the Format tab,
that contains some new formatting options
- Corrections
- tool for contrast, brightness, sharpness correction
- Color
- change color of the images in order to better match the rest of the content
- Artistic
effects - tools that can make an image
appear as if it was a drawing or painting
- tool for inserting
a text box; after selecting this tool, we can set the position and the size of
the text box in the worksheet via the drag-and-drop method
Header and Footer - edit: press the left mouse button within the header or footer field, or:
press the right mouse button and from the quick menu, choose the Edit Text
option
- insert date and
time
- insert
page number
Insert symbol 
€
£ © ∞ J ≠ " H Y ^ › ¯ ü etc.
€
£ © ∞ J ≠ " H Y ^ › ¯ ü etc.
Chart
Charts are graphical representations of table data in
various forms, like column graph. Tabular data and graph are connected -
therefore changes in the table are reflected in the chart
•
when we insert a chart, a table for data entry
opens in Microsoft Excel and we can enter data. Also, if we select a cell range
filled with data, the chart will be automatically filled with the selected
data.
•
to change chart size: select a chart and move (drag-and-drop)
the control points to the desired direction
•
changes in the chart can be done via the Chart
Tools toolbar, that appears when the chart is selected; via the quick menu or
by double-click with the left mouse button on the chart o Format
Chart Area – effects like color fill, 3D, rotation, shadow etc. are accessible o Change
Chart Type – choose chart type
•
move the chart: within the Chart Tools toolbar,
select the Design and choose tool Move chart:
> New sheet (and there is a field to enter the
worksheet's name), or
> Object in > on the drop-down
menu, choose the worksheet in which you want to place the chart
„SmartArt“
•
fields can be moved in order to edit its
structure: press the left mouse button on the fields border, via the
drag-and-drop method, move it to the desired position
•
add new field: press the right mouse button over
the field, upon which you want to add a new field and choose the Add Shape
option on the quick menu, then choose where to add it “below” or “above”
(hierarchal)
•
deleting: select a field and press the Delete
button
Sparklines
•
chart within one
cell;
•
needs to have a defined position (cell address
that contains it) and data cell range (data to display)
Tab name: Page Layout
The Page Layout
settings change the look of the Excel file when it is printed.
Margins: -
Normal - Narrow - Wide
Orientation: Portrait (vertical) and Landscape
(horizontal)
Scale: tool for fitting data for print to a paper
Breaks – it is recommended that user sets the breaks
manually:
•
Insert Page Break
•
Remove Page Break
Tools for organizing content: - Bring Forward
- Send
Backward
Review tab
- opens
a dialog box with options: Ignore once, Ignore all, Change, Change all
•
the word that is not in the dictionary can be
added: press the right mouse button over it and choose the Add to dictionary
option
View tab
Normal - for creating a worksheet
Page Layout – for print preview of the data set for
printing, header and footer
Zoom
– choose offered or enter value
Freeze and unfreeze rows and columns in order to always be
visible. This is useful when the data in the worksheet does not fit on the
computer screen but the user needs to see the row and column labels:
•
freezing columns and rows: select a cell within
the sheet and choose the Freeze Panes button.
Columns to the left and rows above will be “frozen” meaning
that as the user scrolls left and right in the worksheet, the frozen cells
always remain visible.
•
freezing top row: choose the Freeze Panes button
and choose freeze top row
•
freezing first column: choose the Freeze Panes
button and choose freeze first column
FORMULAS
Formula uses numerical data found in the chosen cell range
(the value entered in a cell range)
Enter the formula:
1. select
the cell where you want to enter a formula
2. enter
the sign „=“
3. enter
numeric values or cell addresses and
arithmetic operator
4. press
the Enter key to complete
|
Basic arithmetic operators:
|
|
|
addition
|
= A1 + A2
|
|
multiplication
|
= A3 * C5
|
|
division
|
= C5/C3
|
|
exponentiation
|
= F15^ A2
|
Microsoft Excel follows the mathematical order of
calculation operations.
Formula can be seen in the formula bar when the
cell, that contains it, is selected or if we position the cursor with a double
click in the cell, that contains the formula (that way it will be visible in
the cell and edited too). The formula result will be displayed in the cell that
contains the formula and where the cursor is not positioned.
Relative cell referencing
(e.g. C5)
when the formula is copied with AutoFill and it has relative cell references,
cell references are going to adapt, for example:
If we use Auto fill to copy the following formula: =C5+B5,
it will change to: =C6+B6, =C7+B7 etc.
Absolute cell referencing
(e.g. $C$5)
If the cell is referenced absolutely in a formula,
then applying the Auto fill tool will result in: =$C$5+B5, =$C$5+B6, =$C$5+B7
etc.
You can change the selected cell reference from relative to
absolute and vice versa by using the F4 key
Logical function if
= logical function that compares cell
values with some expression or value. We define the appropriate action
depending on the result
Syntax:
•
IF(logical
_condition;value_if_true;value_if_false)
•
logical function checks if the condition is met,
and returns true or false
•
logical operators:
greater than > less
than <
equal =
greater than or equal >= less
than or equal <=
Comments
Post a Comment