learn excel 2010

“Virtual World,Free Education”

EXCEL 2010Eng Soraya Elsayed

Microsoft certified Trainer

Module One.

Module One: Excel Environment

Excel • Microsoft Excel is a spreadsheet application developed

by Microsoft for Microsoft Windows and Mac OS. • It allows one to enter numerical values or data into the

rows or columns of a spreadsheet, and to use these numerical entries for such things as calculations, graphs, and statistical analysis.

http://en.wikipedia.org/wiki/Spreadsheet

http://en.wikipedia.org/wiki/Application_software

http://en.wikipedia.org/wiki/Microsoft

http://en.wikipedia.org/wiki/Microsoft_Windows

http://en.wikipedia.org/wiki/Mac_OS


Our Course• Module One Enviroment• Module TwoFormulas and Functions• Module Three Analysis and statistics

Module One: Excel Environment• Chapter 1: First Steps With spreadsheets

• Introduction to Interface of program• Open one, several spreadsheets• Switch between open spreadsheets• Create new spreadsheet• Save spreadsheets• Switch between open spreadsheets• Help

• Chapter 2:Adjust Settings• Use magnification / zoom tool• Freeze, unfreeze columns and rows• splitting a worksheet• Preferences

• Chapter3: Cells• Insert Data, Edit data• Select Cells• Copy, cut, paste & Delete• Fill handle

• Chapter 4: Rows and columns • Insert • Delete• Resize• Hide and Unhide

• Chapter 9: Worksheet setup• Margins• Orientation and paper size• Header and footer• Document properties

• Chapter 10:Printing Preparation• Finalize a workbook by spell checking

• Using find commond• Using Replace command

• Display guidelines• Display rows and columns headings• Apply automatic title rows

• Chapter11: Printing• Preview a worksheet• Page break preview• Print cell range• Print copies

Chapter 5 :Worksheets• Insert• Rename• Delete• Duplicate, Move• rearrange worksheets

Chapter 6:Formatting cell content• Font size, type, style• Font color and cell background• Copy formatting• Alignment, text wrap• Borders and shading• Format Painter

Chapter 7:Formatting Numbers and dates• Decimal places, comma• Date styles• Currency symbols• Percentage

Chapter 8:• Formulas and Functions• Copy and paste functions• Relative vs absolute reference • Charting


• Introduction to Interface of program• Open one, several spreadsheets• Switch between open spreadsheets• Navigate Inside Worksheet• Selecting Data• Create new Workbook• Save spreadsheets• Help• Close

Chapter 1: First Steps With spreadsheets


Introduction to Interface of programTitle bar

Ribbon

Tabs bar

Formula bar

Status bar

Row bar

Column bar

Sheet area

sheet Tab


Title bar

Quick Access ToolbarMinimize, Maximize, close

Application buttonsWorkbook Title

• by default it contains icons for New, Save, Undo, and Redo.

• You can add & remove commands to it

• by default ,Excel will name new workbook with book1, book2,…etc, until you save it.


Tile bar Tips1. Maxmize button is replaced with Restore button if window is already

maxmized.

2. Double click on tiltle bar will restore the window.

3. Click on application icon , open shortcut menu That have Restore, minimize and close.

4. Drag the title bar to any edge of any of the screens to make a window in that portion of the screen.

5. Show Quick access toolbar below or above ribbon


Tabs Bar

File tab To access the Backstage view from within the application, click the File

Click another tab, to close backstage view

Minimize & maximize Ribbon

Help

Minimize, Maximize, close current book

buttons

Active tab looks different

2 types of Tabs

Main Tabs

• By defaults, it always appears

• File, Home, Insert, page layout, formulas, Review and view tabs

Contextual Tabs

• It appears only when u click on picture, chart, table, shape, textbox, Header &footer


Ribbon

• Tools are organized in logical groups that are collected together under specific tabs.

Groups

Ribbon helps you quickly find the tools

Some groups have dialog box launcher


Ribbon tips

1. Double click any tab will minimize ribbon

2. You can Use the keyboard to work with the ribbon:• If you Press and release the ALT key.You see the little boxes called

KeyTips over each command available in the current view.

3. You can create custom ribbon tabs and groups

4. You can rearrange tabs and groups

5. You can hide built in tabs but you are not allowed remove them


Commands types

• Each group contains commands, related to a central task• Commands shape describe themselves:

• with a arrow commands

• ON/off commands

• Half on/off and half with arrow


Formula bar

Name box Expandformula bar

Formula Box

The Name Box in Excel can be used to quickly navigate to different cells in a spreadsheet.

Tip: Hide formula bar

The Formula Box in Excel can be used to show cell’s content

http://spreadsheets.about.com/od/glossary/g/name_box_def.htm

http://spreadsheets.about.com/od/c/g/cell_definition.htm

http://spreadsheets.about.com/od/s/g/spreadsheet_def.htm

http://spreadsheets.about.com/od/glossary/g/name_box_def.htm


Sheet Area• Columns run vertically in a worksheet.• Each column is identified by a letter in the column header starting with

Column A to XFD

• Rows run horizontally in an Excel worksheet.• Each row is identified by a number in the row header

starting from 1 to 1048576

The intersection point between a column and a row is a cell.

column header

row

hea

der.

Columns:

Rows:

Worksheet size

1,048,576 rows by 16,384 columns

http://spreadsheets.about.com/od/uvw/g/worksheet_def.htm

http://spreadsheets.about.com/od/c/g/column_header.htm


http://spreadsheets.about.com/od/uvw/g/worksheet_def.htm

http://spreadsheets.about.com/od/r/g/row_head_def.htm


http://spreadsheets.about.com/od/glossary/g/row_definition.htm

http://spreadsheets.about.com/od/c/g/cell_definition.htm




sheet tabs

Navigation buttonsHorizontal scroll bar

sheets

• By default, Each new workbook you open in Excel three blank worksheets, although you can add more.

Add new sheet button

• TIP: You also can use the Ctrl+Page Down and Ctrl+Page Up keyboard shortcuts to display the next and previous sheet, respectively, in your workbook.

• TIP: Navigation buttons cannot work without this separator hides sheet.

http://www.dummies.com/how-to/content/how-to-navigate-excel-2010s-worksheet-tabs.html#glossary-workbook

http://www.dummies.com/how-to/content/how-to-navigate-excel-2010s-worksheet-tabs.html#glossary-worksheet


Status barThe status bar at the bottom Excel window displays status on options that are selected.

If you want to customize the status bar, right-click it, and then click the options that you want.

Views shortcuts

Zoom slider

Cell Mode Indicator

•Ready•-Enter• Edit• Point

Auto Calculate Indicator


Open file• File tabopen• Ctrl +O

• Each book is opened in several window

• File tabRecent

Open several files

Open Recent files


Switch between several Window • View tab Window Group Switch Windows


Navigate Inside Worksheets• Move one cell up, down, left, or right in a worksheet. Arrow keys• Move one screen down / one screen up in a worksheet.page up/down• Move one screen to the right / to the left in a worksheet. Alt+page up/down• Move to the beginning of a row in a worksheet.-->Home• Move to the beginning of a worksheet.ctrl+Home• Move to the last cell on a worksheet.ctrl+arrows


Selecting Data• Selecting Cells

• To Select Single cell• To Select Range of cells• To Select Non-Adjacent cells: hold ctrl• To Select All cells: Ctrl +A

TIP To cancel a selection of cells, click any cell on the worksheet.


Selecting Data• Selecting Columns, Rows in Worksheets and Workbooks

• To Select Row or columnclick the row/column header (Ctrl+space)(Shift+space)• To Select Adjacent rows / columnsdrag on the row/column header• To Select Nonadjacent rows / columnshold ctrl


Extending SelectionsTo extend the selection to the last nonblank cell in the same column or row as the active cell CTRL+SHIFT+arrow key


Create New Workbook

To create a new workbook,:• you can open a blank workbook. Ctrl+N• You can also base a new workbook on an existing workbook,• You can also base a new workbook on an default workbook

template, or any other template.


Save Workbook• To avoid losing your data

• Save• Save as• Save as type• Default save Location• Save Autorecovry

Save vs Save as

Save

• No dialog box is opened except in the first time

• To save an existing workbook in its current location, without changing its name or type

Save as

• Save as Dialog box always opened

• To create a copy of your workbook in the same location with different name

• Or to copy it to another location


Save as type

•abc.xlsx -------- Excel 2007 and 2010

•abc.xls -------- Excel 97-2003


Autorecovery• The AutoRecover feature saves copies of all open Excel

files at a user-definable fixed interval. The files can be recovered if Excel closes unexpectedly, for example, during a power failure.

Versions• Excel saves all previous autosaved files under Versions.


Autorecover• Save my work every 10 min or as u like• Save when i close workbook without saving• Save when excel crashes


Help• File Help• Click the Help button . Located above the ribbon• Use the Keyboard shortcut press F1.

Version of the Microsoft Office Help topics

• Topics those installed on your computer as part of Microsoft Office (offline)

• Topics available on Microsoft Office.com.


close• File Exit


Thank you for watching Edupedia World Videos

See You at the next video


Chapter 2:Adjust Settings• Use magnification / zoom tool• Freeze, unfreeze columns and rows• Preferences


Use magnification / zoom tool• Excel provides two methods to zoom in on your data.

• Zoom slider • zoom dialog box


Freeze, unfreeze columns and rows

• lock one row only, click Freeze Top Row.• lock one column only, click Freeze First Column.• To lock more than one row or column,

• To lock multiple rows, select the row below the row or rows that you want to keep visible when you scroll.

• To lock multiple columns, select the column to the right of the column or columns that you want to keep visible when you scroll.

• To lock both rows and columns, click the cell below and to the right of the rows and columns that you want to keep visible when you scroll.

Note

You cannot freeze rows and columns in the middle of the worksheet.


Can you freeze rows and columns in the middle of the worksheet?

•Answer:•Of course not.


Comparing Data in excel workbook

Comparing Data in excel workbook

compare two different worksheets from the same workbook.

• By opening multiple windows for a single workbook at the same time.

compare different sections of the same worksheet

• Using Split command that allows you to divide the worksheet into multiple panes that scroll separately.

Vertical split Horizontal split

Both vertical & Horizontal split


Any changes you make to one pane are immediately reflected in the other ones.

• True or false


Preferences• File location• Number of sheets• Font and size• File format


Chapter3: Cells• Insert Data, Edit data and Select Cells’ content• Duplicate, Move, Delete

Click the cell

• To Activate it• Ready Mode

Start Typing

•Enter Mode

•Replce

D.C it’s content•To Insert insertion point

•Edit Mode

F2D.C it’s content

•To Insert insertion point•Edit Mode

D.C it’s content again•Highlight cell content•Edit mode•Replace all content

One Click on the cell

If the cell is empty

• Cell is in Ready mode• Enter mode activated by typing.

If the cell contains data

• Data completely replaced by new entry

New data is inserted

Double Click on the cell

If the cell is empty

• insertion curser appears

If the cell contains data

Edit mode activated to add data to it or remove from it

Enter mode activated to insert data

Double Click + Double Click on the cell

• insertion curser appears• Enter mode activated

Edit mode activated


• Click• double click = F2• double click then double click again

1 click = 4 click


True or false:• If cell contain data surfing

1 click = 4 click

Cut and Copy Data in an Excel Worksheet

Cut

• When you move data to another location, the data is removed from its original location and placed at the location you specify.

Copy

• You may want to have the same information in several places within the worksheet.


There are several methods to Cut and Copy Data :

1. Copy & Paste, Cut & Paste from ribbon

2. Copy & Paste, Cut & Paste from r.c shortcut menu

3. Drag and Drop

4. Use keyboard shortcut


Fill Handle• The fill handle is a small black box at the bottom right

corner, as shown in the image below.

Copy DataExtend aseries Data

Uses:


Thank you for watching Edupedia World Videos

See You at the next video


Chapter 4: Rows and columns • Insert • Delete• Move• Resize• Hide and unhide


Insert Rows and columns• Insert a single row/column• Insert multiple rows/columns• Insert nonadjacent rows/columns


Delete key from keyboard• Delete key = clear command


3 ways u can use to insert rows or columns

• Home > Insert > Insert Sheet Rows/ Insert Sheet columns• Using right click to open context menu• Keyboard shortcut press and hold (Ctrl and Shift keys )

and press (+) key


Delete Rows and columns• Delete a single row/column• Delete multiple rows/columns• Delete nonadjacent rows/columns


3 ways u can use to delete rows or columns

• Home > Delete > Delete Sheet Rows/ Insert Sheet columns

• Using right click to open context menu• Keyboard shortcut press and hold (Ctrl key ) and press (-)

key


Insert and delete cells• You can insert blank cells above or to the left of the selected cells on

a worksheet.• By choosing option Shift cells down

or Shift cells right

• When deleting cells• Shift cells left :a cell to the right of the deleted one

will be moved to the left; • Shift cells up :a cell below the deleted one

will be moved up;


What do that means?• To see pound signs (#######) in a cell


Row height and column width

3 ways to Modify Row height and column width :• By dragging the line separating columns or rows• By D.c the line separating columns or rows = Autofit• Specific measurement


Reset column width• you can specify a column width of 0 (zero) to 255.• The default column width is 8.43 characters. • If a column has a width of 0 (zero), the column is hidden.


Reset row height• You can specify a row height of 0 (zero) to 409.• The default row height is 12.75 points • If a row has a height of 0 (zero), the row is hidden.


Hide and unhide rows and columns• Using ribbon• Using context menu• Using keyboard shortcut

Hidden columns and rows don’t print


Hide and unhide using keyboard shortcuts• The key combination for hiding rows is:

Ctrl + 9 • The key combination for hiding columns is:

Ctrl + 0• The key combination for unhiding rowsis:

Ctrl + Shift + 9

• The key combination for unhiding columns is:

Ctrl + Shift + 0


unhide rows and columns• To display hidden rows, select the row above and below

the rows that you want to unhide.

• To display hidden columns, select the columns adjacent to either side of the columns that you want to unhide.

•


Unhide Column A• Use Name box


Chapter 5 :Worksheets• Insert Worksheets• Rename Worksheets• Changing worksheet Tab Color• rearrange worksheets• Delete Worksheets• Duplicate, Move Worksheets• Hide and unhide Worksheets


Insert worksheet• Click new sheet icon• Shift +F11• Ribbon • R.c on sheet tab


Rename worksheet• R.c on sheet tab• D.c sheet name• Ribbon


Hide and unhide worksheets


Hide and unhide worksheets

Note:• Although you can hide several sheets at once, if u select

all of them first before hiding. But You can only unhide one worksheet at a time.

• Undo command cannot unhide• hide is not equal to delete


Copy & move worksheets• You can copy and move the worksheet :

• into the same workbook • or into a new workbook• To different workbook


3 ways to move and copy sheets

23

By dragging 1

Formatted sheet Non formatted sheet

Chapter 6:Formatting cell content


Formatting for text

• To change Font type, size. • Font color , style• Cell borders and fill colors

Formatting for Numbers

• Text Alignment, direction.• Indentation , orientation• Text wrap, Merge cells


Formatting for text



Font group on home tab

Alignment group on home tab


Number group on home tab


Formatting for text





Chapter 7:Formatting Numbers• Decimal places, comma and thousand separator• Date styles• Currency symbols• Percentage• Conditional formatting


If you applied a number formatting to cells,How can You remove that formatting?• Simply By applying general formatting, any applied

number formatting will be removed


Format painter

Only 1 click on format painter Double click on format painter

To Copy formatting for several cellsTo Copy formatting for only one cell

Formatted cell

Unformatted cell

Unformatted cell

Unformatted cell

1 2 3

double click

3

4

Formatted cell Unformatted cell

1 2 3

Formulas and Functions

Formulas

• A formula is statement written by the user to be calculated

• Ex:• =20+4/8*2• =A1+A2*(B2+B3)

Functions

• Functions are predefined formulas and are already available in Excel.

• Ex:• =SUM()• =Average()

an expression which calculates the value of a cell.


Formulas and Functions Rules

• always starts with an equal sign (=).

• Refer to data by including their cell references in the formula arguments.

• To complete a formula that uses a combination of numbers, cell references, and operators, press ENTER.


Order of operations• In mathematics and computer programming, the order of

operations (sometimes called operator precedence) is a rule used to clarify which procedures should be performed first in a given mathematical expression.

Exponents ^

Multiplication / Division * /

Addition / Subtraction + -

Brackets ()

http://en.wikipedia.org/wiki/Mathematics

http://en.wikipedia.org/wiki/Computer_programming

http://en.wikipedia.org/wiki/Expression_(mathematics)


Order of operations• In mathematics and computer programming, the order of

operations (sometimes called operator precedence) is a rule used to clarify which procedures should be performed first in a given mathematical expression.

http://en.wikipedia.org/wiki/Mathematics

http://en.wikipedia.org/wiki/Computer_programming

http://en.wikipedia.org/wiki/Expression_(mathematics)

Order of operation

Without order of operation

• =6+4/2• =10/2• =5

Corrected• =(6+4)/2

With order of operation

•=6+4/2•=6+2•=8


Order of operation

•=6+4-2•=10-2•=8


Copying formulas & functions• Copying a Formula into Multiple adjacent Cells• Copying using relative and absolute references• Copying a Formula Exactly

Relative and Absolute Cell References

Relative references

• change when a formula is copied to another cell. on the other hand,

Absolute references

• remain constant, no matter where they are copied.

http://www.gcflearnfree.org/excel2013/15.2

http://www.gcflearnfree.org/excel2013/15.2


Paste options• Paste group• Paste values• Other paste options

=A7+B7 =B7+C7

=B7+C7

=B10+C10

=B10+C10=A10+B10

Charts• Excel support different types of charts

Charts

• Create charts• Format Charts• Move Charts


The elements of a chart• 1. The chart area • 2. The plot area • 3. The data points of the data series • 4. The horizontal (category) and vertical (value) axis along

which the data is plotted in the chart.• 5. A chart title • 6. A data label• 7. The Legend


Manually Add/remove Chart Elements

• Add/remove Chart Title• Add/remove axis Title• Add/remove Chart Legend• Add/remove Data Label• Add/remove Data Table


Manually format Chart Elements

• Fill Color • Font type, size• Border style, color and width• 3d rotation


Apply chart Layout


Format chart


Format a chart element


Select a chart element


Print Preparation• Set page margins• Change page orientation and paper size• Create headers and footers• Create sheet settings


Print Area• What is Print area?• How to set Print area?• How to add to Print area?• How to clear Print area?


Chapter 8: Search and replace• Using find commond• Using Replace command• More search options

Defining Page Setup Options

• Margins Orientation paper size


Chapter 10:Printing Preparation• Display guidelines• Display rows and columns headings• Apply automatic title rows


Headers & Footers• What are Excel Headers and Footers?• Where are headers and footers located?

• Headers and footers are text that print at • the top (header)• bottom (footer)

• They contain information such as titles, dates, and/or page numbers.

of each page in each sheet


Chapter11: Printing• Preview a worksheet• Page break preview• Print cell range• Print copies

learn excel 2010

Education

close chapter

open spreadsheets

tiltle bar

worksheets chapter

spreadsheets switch

spreadsheets introduction

unhide chapter

handle chapter