MS Excel 2010

Microsoft Excel 2010

Introduction

Microsoft excel 2010 is an excellent program for organizing, formatting, and calculating numeric data. Excel displays data in a row and column format, with gridlines between the rows and column, similar to accounting ledger books or graph paper. Its file extension is .xlsx

Worksheets

Microsoft excel consists of worksheets. Each worksheet contains columns and rows. The columns are lettered A to Z and then continuing with AA, AB, AC and so on to XFD (16384) the rows are numbered 1 to 1048576.

The Interface

Cell: The intersection of a column and a row. Information is stored in cells.

Cell Reference: Address, consisting of a column and a row IDs of a specific cell.

Column: A vertical group of cells. Total no of columns are 16384.

Formula: A self-made method of calculation is called formula.

Function: A pre-defined method of calculation is called function.

Range: A group of cell.

Row:  A horizontal group of cells is called row. Total no of rows are 1048576.

Value: A number that can be used in an excel calculation is called value.

Worksheet: Combination of columns and rows within a sheet is called worksheet.

Workbook: A collection of worksheets contained within a single file.

Entering Text

• Select the cell where you want to enter text
• Type text into the cell
• To accept the text, press Enter button

Entering Number

• Select the cell where you want to enter number
• Type the number into the cell
• To accept the text, press Enter button

Formatting Text

• Select the text that you want to format
• On the Home tab, in the Font group, From the option available option
• Select the desired formatting.

Applying Cell Style

• Select the Cell
• From the Home tab, in the Style group, Click Cell Style, A pull down list appears.
• Select the desire cell style

Changing Borders

• Select the cell
• From the Home tab, in the Font group, click next border option
• Select the desire border. The border is applied.

Changing Border Color

• From the Home tab, in the Font group
• Click the next border option
• Select the Line Color, than select the desire color.

Creating a Cell Merge

• Select the cells
• From the home tab, in the alignment group
• Click Merge & Center option
• Selected cell is merged and text align to the center.

Removing Cell Merge

• Select the cell you want to unmerge
• From the home tab, in the alignment group
• Click Merge & Center option
• Selected cell is unmerged

Wrapping Text

• Select the desire cell
• From the home tab, in the alignment group
• Click Wrap Text option

Clear Cell Formatting

• Select the cell containing the formatting to be cleared
• From the home command tab, in the Editing group
• Click on the Clear option
• In the pull-down list, Select the Clear Format
• The cell formatting is removed

Filling cells: Ribbon option

1. Types the (cell contents or formula )in the first cell of the group
2. Starting with cell containing the contents to be copied. Selected the group of cell to be filled .
3. From the Ribbon , select the Home  command tab
4. In the Editing group , click FILL then  selected the appropriate option:

Filling cells: Mouse option

1. Types the information (cell contents or formula ) in the first cell of the group
2. In this cell, move your pointer over the fill corner so your pointer changes into crosshairs +
3. Click and hold the crosshairs
4. Drag the mouse in the direction you want the information to be copied
5. Release the mouse button. The fill is applied.

Adjusting Row Height:

1. From the Ribbon select the Home command tab
2. In the cells group click FORMAT
3. In the cell size section select Height…

Adjusting column width: Ribbon option

1. In the cells group click FORMAT
2. In the cell size section. Select width…, the column, width dialog box appears. In the column width text box. Type the desired width

Using AutoFit to Adjust Row Height:

1. To select a row to be adjusted. Click the Row ID the entire row is selected.
2. From the Ribbon. Select the Home command tab
3. In the cells group click format. In the cell size section. Select AutoFit the row height adjusts so the tallest item in the row is displayed in full.

Using AutoFit to Adjust Column Width: Ribbon option

1. To select a column to be adjusted click the COLUMN ID
2. From the Ribbon select the Home command tab
3. In the cells group click FORMAT
4. In the cells size section, select AutoFit Selection

Adding Rows

1. Select a cell below where you want  to add a new row
2. From the cells group. Select Home command tab
3. In the cells group. Click the arrow on the INSERT button   select
4. In the cells group click the arrow is added above the selected cell.

Adding columns

1. Select a cell to  the right of where you want to add a new column
2. For the Ribbon select the Home command tab
3. In the cells group , click the arrow  on the INSERT  button select Insert sheet columns a new column is added left of the selected cell.

Hiding Columns

1. Select a cell within the column (S) to be hidden
2. On the Home command tab, in the cells group click FORMAT 
3. From the format menu, in the Visibility section. Select Hide& Unhide Hide column , The column is hidden.

Hiding column :Quick Menu option

Right click the column ID select Hide

Redisplaying Column 

1. Select at least one cell form both of the column around the hidden.
2. One the Home command tab, in the cells group click FORMAT
3. From the Format menu, is the visibility section. Hide &Unhide 

Find & Replace

Hiding Rows

1. Select a cell within the row (s) to be hidden
2. On the Home command tab, in the Cells group, click FORMAT
3. Form the format menu, in the Visibility section, select  Hide& Unhide then click Hide Rows

Printing the Active worksheet

1. To activate the worksheet  you want printed, click the tab of that worksheet
2. In the top left corner of the Excel window, click the File Ta Select print OR press [Ctrl] + [p]
3. In the print what section, select Active sheet (s)
4. Click print, the active worksheets are printed.

Printing the Entire workbook

1. In the top left comer of the Excel window, click the File Tabs select print or Press [Ctrl] + (P)
2. In the print what section, select Entire workbook
3. Click print, the entire workbook is printed.

Defining the print Area: Dialog Box Option

1. Select the page layout command tab
2. In the page setup group, click page setup
3. Select the sheet tab
4. In the print area text box, type the range of cells you want to print  or To select the area

a.       Click collapse  dialog

b.      Select the desired range of cells

c.       Click Restore Dialog Click ok, the print area kiss defined.

Selecting a print Range manually

1. Select the range of cells to be printed OR from the Name Box pull-down list, select the range name to be print
2. In the top left corner of the excel window, click the File Tab select  print or press [Ctrl]+[p], the print dialog box appears.
3. In print what section, select selection
4. Click Ok, the specified range of cells is printed.

Selecting Multiple Ranges

1. Select the first range to be printed
2. Press [Ctrl] + select the second range to be printed
3. For each additional range to be printed, repeat step 2
4. In the top left corner of the Excel window, click the File tab, select print or press [Ctrl] + [p]. the print dialog box appears.
5. In click print. The specified range of cells is printed.
6. Click print the specified range of cells is printed.

Adjusting the Header and Footer Margins

1. From the ribbon, select the page layout command tab
2. In the page setup group, click MARGINS select Custom Margins.
3. In the Header and Footer text boxes,type the appropriate margins OR With the nudge buttons, select the appropriate value
4. Click OK

Adding Headers and Footers

1. From the Ribbon, select the Page Layout command tab
2. In the page setup group, click PAGE SETUP
3. Select the Header /Footer tab
4. From the Header or Footer pull-down list, select a preset header or footer
5. Click OK, The header or footer is applied to he document.

Adding a Custom Header or Footer: Ribbon Option

• From the Ribbon, select the Insert command tab
• In the Text group, click HEADER & Footer A header appears, divided into three sections, and the Design command tab is visible on the Ribbon.

OPTIONAL: To work with a footer, in the Navigation section, click Go to the Footer

• Click the section of the  header or footer where you want to add content
• Type or use the Header & Footer Elements to add all desired information

Setting a Custom Page Number

1. Add your header or footer 
2. From the Ribbon, Select the page layout command tab
3. In the page Setup group, click PAGE SETUP
4. Select the page tab
5. In the First page number text box, type the number that will begin the page numbering
6. Click OK, the page   Setup dialog box closes

Working   with  Conditional Formatting

1. Select  the range of cells be formatted
2. From the Home command tab, in the Styles group, click  CONDITIONAL FORMATTING, A pull-down list appears.
3. Select Highlight Cells Rules select the desired criterion,
4. In the dialog box, specify your criteria
5. Click OK, The rule is applied to cells which satisfy the criteria.

Applying Top, Bottom, and Average Rules

1. Select   the range of cells to be formatted
2. From the Home command tab, in the Styles group, click Conditional Formatting, A pull-down list appears.
3. Select     Top/Bottom Rules select the desired criterion
4. In the dialog box specify your criteria
5. Click OK, The rule is applied to cells which satisfy the criteria.

Applying Data Bars, Color Scales, and Icon Sets

1. Select   the range of cells to be formatted
2. From the Home command tab in the Styles group, click Conditional Formatting, A pull-down list appears.
3. Select Data Bars, Color Scales, or Icon Sets select your desired style.

Creating a Customized Rule

1. Select the range of cells to be formatted
2. From the Home command tab, in the styles group , click Conditional Formatting  select NEW RULE
3. From the select a Rule Type section, select the condition that will trigger formatting, The Edit the Rule Description section will refresh to display  new option
4. In the Edit the Rule Description section, select your criteria .The criteria will vary based on the selection made in step3.
5. To specify the formatting

a)      Click Format …, The Format Cells dialog box appears

b)      Select the desired formatting option

c)      Click ok, the Format Cells dialog box closes.

When finished, click ok, the new rule is applied and saved.

Formatting Building

A formula always begin with an equal sign (=), and include cells reference, and some type of mathematical operator. For example, the formula =C4+C5 takes the value in C4 and adds computer to C5.

Excel uses arithmetic operation (+,-,*,/,.%,^)and logical operation ,(>,<=>,=,<=,<>)which return values of TRUE or FALSE in formula to compare two values.

Precedence of Operations

General Errors of Formula or Functions

How to copy formula

select the cell in which a formula has applied

Press Ctrl + C

Select the range

Press Ctrl  +  V to the  formula .

By Dragging

To copy the  formula to next cell select the cell and drag the mouse placing the cursor in the lower right corner of the of the active cell. The desired formula will be copied

Relative and Absolute Cell Reference

Excel use two type of cell reference to create formula Which has own purpose Read on to determine which type of cell reference to use for your formula .

Relative Cell Reference

This is the most widely used type of cell references in formula Relative cell references are basic cell reference  that adjust and change when copied or when using Auto Fill

Example 

 = SUM (B2: B8) change to = SUM(C5:C8) when copied across to the next cell

Absolute Cell Reference

Situations arise in which the cell reference must remain the same when copied or when using Auto fill. Dollar signs are used to freeze a column and or row reference.

Functions:                                     

A pre-made of calculation is called “Function”. There are some standard functions or built – in for calculating and manipulating data. Functions have two basic parts which you should be aware of:

• An equation, which is provided by Excel when you select the desired function.
• Values or cell references to be used in the equation, which you will provide.

Inserting Functions: Dialog Box Option

1. Select the cell where the function should be added.
2. From the Ribbon, select the Formulas command tab.
3. In the Function Library group, click FUNCTION WIZARD The Insert Function dialog box appears.
4. From the Or select a category pull-down list, select the appropriate function category OR Select All.
5. From the Select a function scroll box, select the desired  functions.
6. Click  OK, the Function Arguments dialog box appears. A function’s arguments are the value (s) the function is being performed upon.
7. In the text boxes, type the data to be used in the function OR To select cell ranges.

         a.         Click COLLAPSE DIALOG

         b.         Click and drag the mouse to select the desired cells

         c.          Click RESTORE DIALOG

Click OK, the results of the function appear in the selected cell.

Insert SUM function in a worksheet in a worksheets

1. Click = sign on the formula bar.
2. Select the SUM function from functions drop down list.
3. The SUM function dialogue box will appear.
4. Give the cell range in Number 1 box,
5. Click Ok.

Commonly used Functions

If Function

The syntax for the IF function is:

If (condition, value –if – value – if – false)

1. Condition is the value to be tested.
2. Value it true is the value that is returned if condition evaluates to TRUE
3. Value if false is the value that is returned if condition evaluates to FALSE

• Insert marks of the students in A1. 
• Types if (A1>40,”pass,”fail”)in B1
• Press ENTER
• If the value of A1 is more than 40,”pass”will be displayed in B1. Otherwise “fail “will be displayed 

Define a Range Name

Highlight the cells B2 to B4, then click the Formula menu

Locate the Named Cells panel

Click Name a Range

From the Name a Range menu, click Name a Range “New name “dialogue box will appear

Click OK on the New Name dialogue box. Notice that the name is our heading of Monthly Totals (if present).

Apply a Name

To apply your new Name, click into cell B5 where your formula is and do this:

On the Named Cells panel, Click Name a Range

From the menu, select apply Names

Apply Name dialogue box, select the Name you want and click OK.

When you click OK, Excel should remove all those cell references between the round brackets, and replace them the Name you defined, cell B5 now says:

=SUM (monthly totals)

Sorting Data   : Sort Dialog Box

1. Select a cell in the column you want to you to sort

2. From the Data command tab, in the sort &Filter group, click

3. To sort by ascending or descending values (i.e., alphabetically, time , or date )

a) In sort by pull down list, select the column you want to sort

b) In the sort on pull –down list , select Values

c) In the order –pull –down list, select A to Z or Z to A

4. To  start according to a custom list

   a)      In the sort by pull –down list, select the column you want to sort Example. Select a column containing days of the week

   b)      In the order pull –down list, select Values

   c)      In the Order pull –down list, select custom list

  d)     Select the custom list by which you want to sort Example : select Sunday ,Monday ,Tuesday , Wednesday, Thursday , Friday , Saturday,

5. To sort according a formatting criterion

   a)      In the sort by pull –down list , select the column you want to use to sort

   b)      In the sort on pull list, select , the appropriate option

   c)      In the order pull –down list, select , a Color and appropriate sort order Example select Blue and on Top

6. Optional : To sort by more than one column

    a)      Click ADD LEVEL

   b)      In Then by row of sort dialog box , select addition sort options

7. OPTIONAL to control how to sort list of days or months, case-sensitive data and orientation.

8. Click Option make necessary changes, Click OK. The data is sorted.

Locking Cells

1. Select the cell (s) to be looked
2. From the ribbon , select the home command tab
3. In the Cells group , click format
4. In the protection section, select lock                                                                                     
5. note : If the icon is highlighted , the cells are locked.
6. Protect the worksheet.

Unlocked Cells

1. In order to unlock cells sheet protection must first be turned off.
2. Unprotect the worksheet
3. Select the cells you cells you want to unlocked
4. From the ribbon, Select the home command tab
5. In the cells group, click format
6. In the protection section, deselect, lock         The cells are unlocked.

Note: If the icon is not highlighted, the cells are unlocked.        

Protecting the workbook

1. From the ribbon, select  the review command tab
2. In the changes group, click protect workbook
3. Select the appropriate options(S).
4. Click Ok The workbook is protected.

Protecting the Worksheet       

1. From the ribbon, select the home command tab
2. In the cells group click format
3. In the protection section select protect sheet
4. In the Protect sheet dialog box, select the appropriate options:
5. Protect worksheet and contents of locked cells
6. Prevents change to locked cells.
7. Password to unprotect sheet                                                                                               
8. Allows only those who know the know the assigned password to unprotect the worksheet.
9. Allow all users to of this worksheet to
10. Checked boxes are aspects that any user can access.
11. Click ok, the worksheet is protected.

Protecting  the File

1. From the File menu, select Info tab.
2. Click on protect Document a pull down list will appear…
3.  Click on Encrypt with password option,
4. A password box will appear
5. Then Enter your password
6. Click OK. Your file is saved with password protection.

Filtering Your Table

1. When Table filtering is enabled, some Excels commands will produce different results. These can include:
2. Cell formatting effects only visible Table cells
3. When printing the Table, only visible cells will be printed
4. The sort command will affect visible cells
5.  When deleting data from the Table, entire rows must be deleted

Activating Table Filters

1. Select a cell within the Table
2. From the Home command tab, in the Editing group, click SORT group & Filterer arrow select Filter OR From the Data command tab, in the sort &Filter group, click FILTER
3. AutoFilter buttons appear at the top of each Column of the selected Table.

Running Table Filters

1. Activate Table Filtering

2. In the column you want to filter, click the

3. The Table filter pull-down list appears, including a submenu of

Column-specific records you can use to filter your table.

NOTE: By default, all records are selected (i.e., set to display).

To filter the selected column, deselect the records you do not want displayed (i.e., be sure that only the records you want displayed are selected)

4. Click OK; All rows fitting the criteria of the selected column are displayed.

NOTES: When you use AutoFilter within a Table, the row numbers of the displayed records turn blue, and the filter results appear in the Status bar (e.g., 1of `1 records found). The button at the top of the column changes to filter shape

5. To remove the filter from your Table, in the filtered column, click the select Clear Filter from…

Using Custom Auto Filter       

Custom Auto Filter allows you to filter a range of information and /or set multiple criteria.

1. Activate Table Filtering
2. In the column you want to filter, click the arrow select Text Filter or Number Filter than click on Custom Filter
3. IN the Comparison Operator pull- down list, select a type of comparison
4. IN the Comparison Operator pull- down list, select a type a criteria value. EXAMPLE:300
5. OPTIONAL: If you want multiple criteria, select either And or and repeat steps 3 and 4
6. Click OK, your table is filtered to display rows in the selected column containing values between 300 and 500
7. To remove the filter from your Table, in the filtered column, click the select Clear Filter From…

Turning Off  the Auto Filter

Select a cell within the table, From the Home command tab, in the Data command tab, in the short & filter group, click Filter Auto Filter is disabled. The Auto Filter buttons are removed are removed from the Table.

About charting

Charts are graphical representations of data in your worksheet.

Creating a Chart

1. Create the data to be charted
2. Select the data to be charted
3. From the Inset command tab, in the Charts group, click the type of chart you prefer A pull- down menu papers. EXAMPLE: Click BAR
4. Click the specific chart you want, the chart appears on the spreadsheet.
5. The Design, Layout, and Format command tabs appear on the Ribbon.

Changing the Chart Type

1. Right click your chart then select Change Chart Type…..
2. From the categories pane, select the type of chat you  prefer
3. On the  right , in the chart type  section, select the specific chart you want
4. Click OK, the chart is changed.

Adding Data Series

1. You can add an additional data series to an existing chart.
2. Right click the chart then click Edit Data Source… OR From the Design command tab, in the Data group, click EDIT DATA SOURCE, The Edit Data source dialog box appears.
3. Click ADD, The Edit Data Source dialog box closes and the Edit series dialog box appears.
4. In the Series name text box, type, the name of the series.
5. In the Series values text box , type the appropriate data range to be added

Chart Tools

Within the Design tab you can control the chart type, layout styles, and location.

Within the Layout tab you can control inserting pictures, shapes and text boxes, labels, axes, background, and analysis.

Within the Format tab you can modify shape styles, word styles and size of the chart.

Copy a Chart to word

• Select the chart
• Click Copy on the Home tab
• Go to the Word document where you want the chart located
• Click Paste on the Home tab

------------------------ End ------------------------