MS Excel

Spreadsheet

COntent

1 Objectives
2 Elements of Electronic Spread Sheet
2.1 Opening of Spread Sheet
2.2 Addressing of Cells
2.3 Printing of Spread Sheet
2.4 Saving Workbooks
3 Manipulation of Cells
3.1 Entering Text, Numbers and Dates
3.2 Creating Text, Number and Date Series
3.3 Editing Worksheet Data
3.4 Inserting and Deleting Rows, Column
3.5 Changing Cell Height and Width
4 Function and Charts  

Fundamental 53%
Word 47%
Excel 64%
PPT 50%
Internet & Communication 85%






Spreadsheet

  • Microsoft Excel is the most widely used spreadsheet package and lets user to organize their data into lists and then summarize, compare and present the data graphically.
  • A spreadsheet is a generic term for the software application package that simulates a paper worksheet often used by people in management.
  • Microsoft-Excel is an electronic spreadsheet.
  • MS-Excel can be used for a variety of tasks which include automating of financial statements.
  • The spreadsheet is an interactive computer application program for organizing and analyzing data in tabular form. 
  • The spreadsheet program operates on data represented as cells of an array, organized in rows and columns.
  • Each cell of the array is an element that contains numeric, text data or the results of formulas that automatically calculate and display a value based on the contents of other cells.
  • Data stored in database formats can be accessed through MS-Excel.
  • Excel emerges as a powerful and flexible graphical presentation tool.
  • Graphs or charts can be created based on data, for quick assessment of a situation.



Name Box

  • The Name box is a quick and easy way to move around and select ranges in a large spreadsheet.

Office Button

  • In the upper left corner of the Excel 2007 window is the Microsoft

Office button 

  • Clicking on the Office Button displays a drop down menu containing a number of options.

Quick Access Toolbar

  • Next to the Microsoft Office button is the Quick Access Toolbar.
  • The Quick Access Toolbar is grouped with Save, Undo, Redo and Print options.

Title bar 

  • Next to the Quick Access Toolbar is the Title bar.
  • On the Title bar, Microsoft Excel displays the name of the current workbook.

 Ribbon 

  • In Microsoft Excel 2007, the Ribbon is located on the top of the Excel window and below the Quick Access Toolbar.
  • Ribbon has several tabs, clicking a tab displays several related command groups, within each group are related command buttons. 

Tab list 

  • Similar to a menu, it display a different ribbon.

Column Letters 

  • Columns are vertical lines of Cells. They are named from A to Z and then continuing from AA to AZ, BA to BZ and so on. 

Row Numbers

  • Rows are horizontal lines of Cells.
  • A number identifies each row.
  • The rows are numbered 1 to 1,048,576.

Formula bar 

  • It is a toolbar at the top of the Microsoft Excel spreadsheet window.
  • The formula bar can be used to enter or copy an existing formula into the
  • cells or charts.
  • The formula bar is a section in Microsoft Excel that shows the contents of the current cell and allows the user to create and view formulas.
  • It is labeled with function symbol (fx).
  • The Formula Bar will become activated when the user type an equal (=) symbol in a cell, or if he clicks on it.

Status bar 

  • This bar displays various messages like status of the Num Lock, Caps Lock, and Scroll Lock keys on your keyboard.

Active Cell 

  • The dark outline indicates the currently active cell.

Sheet Tab Scroll Button 

  • On the bottom left of the worksheet user will find the Sheet Tab Scroll Buttons to move to the First sheet, Previous sheet, Next sheet, and Last sheet.
  • Page View Buttons Change the way the worksheet is displayed by clicking one of these buttons. 

Page Zoom Control 

The worksheet has, in and out zoom controller

Creating a new workbook 

                  Creating a Blank Workbook 

  • User can create new excel file, Click File tab then click New button and then double click Blank Workbook or click Create button.



Creating a Sample Template 

  • If user wants to open a template, click File tab then click New button and then double click one of the template or click Create button.



Saving a Workbook 

  • For saving a workbook, click File Tab then click Save or Save As or press Ctrl + S in Keyboard.

Select required drive then type the file name and then check the file type to save the workbook by clicking Save button

Editing a Workbook

  • If user wants to edit the saved workbook, open the existing file.
  • Make changes in the file then save.




  • To move or copy entire data, select the data, point to the top or bottom or left or right border around the data, now the mouse appears as an arrow, and then drag and drop.

Inserting and Deleting Worksheets 

  • Excel gives three Worksheets by default.
  • If user wants to add a worksheet
  • First, right click the tab of the sheet that the user wants to add the new one. 
  • To insert a sheet between 2 and 3 for instance, right click the sheet 3 tab.
  • Then, choose Insert then double click Worksheet.

If user wants to delete a worksheet 

  • Right click the tab of the sheet which the user wants to delete.
  • Then click delete.

Entering Data into Spreadsheet

  • Enter data into a spreadsheet by typing in the active cell.
  • After typing, press ENTER key, the next cell becomes active.
  • User can also use arrow keys to move from one cell to another.(Show keyboard arrow keys)
  • User can enter the student details table shown below, by doing the following steps.
  • In the blank workbook, move cell pointer to the cell A1. Type S.NO.
  • Press the right arrow key to move to cell B1. Type STUDENT NO.
  • Press the right arrow key to move to cell C1. Type STUDENT NAME.
  • Press the right arrow key to move to cell D1. Type MARK1.
  • Press the right arrow key to move to cell E1. Type MARK2.
  • Press the right arrow key to move to cell F1. Type MARK3.
  • Press the right arrow key to move to cell G1. Type TOTAL.
  • Now move the cursor to cell A2 and press Enter.
  • Repeat the steps to enter the data for S.NO, STUDENT NO, STUDENT NAME, MARK1, MARK2, MARK3.
  • Now, move the cell pointer below the TOTAL field (G2).
  • Enter the formula in the formula bar by =SUM(D2+E2+F2) for calculating the TOTAL and then press ENTER.
  • The TOTAL is displayed. Then, drag the active cell as need, the total for all consequent cells are calculated.
  •  
  • Enter the formula in the formula bar by =SUM(D2+E2+F2) for calculating the TOTAL and then press ENTER.
  • The TOTAL is displayed. Then, drag the active cell at G2 to G6, the total for all consequent cells are calculated.

Handling Operators 

  • Excel allows the user to use formulae to calculate and analyze data in their worksheet.
  • A formula uses the values in cells to perform operations such as addition, subtraction, multiplication and division.
  • Formulas need mathematical operators such as (+, – , *, /) and can use the cell reference or cell names for referring to the contents of a cell.
  • A formula always begins with an equal (=) sign.
  • Excel allows the user to use formulae to calculate and analyze data in their worksheet.
  • There are four types of operators.
  • Arithmetic operators
  • Comparison operators
  • Text operator
  • Reference operators

Arithmetic operator 

  • These are used to perform basic mathematical operations, and to combine numeric values to produce numeric result.

Comparison operators

  • These are used to compare two values and produce a logical result either True or False, 0 (Zero) or 1 (one). The following are the Logical operators.

Text operator

  • This operator joins two or more text values to produce a single combined text value.
  • Text formulas use the ampersand (&) operator to work with text cells, text strings enclosed in quotation marks, and text function results

Reference operators 

The reference operators combine two cell references or ranges to create a single joint reference

Functions

  • A function is a predefined formula in Excel.
  • Like formulas, functions begin with the equal sign ( = ) followed by the function’s name and its arguments.
  • The function name tells Excel what calculation to perform.
  • The arguments are contained inside round brackets.
  • There are several types of functions in spreadsheet some of them are
  • Math Functions
  • Logical Functions
  • Statistical Functions
  • Text Functions
  • Date and Time Functions

Math Functions

  • The Excel Math Functions are provided by Excel, to carry out many of the common mathematical calculations, including basic arithmetic, conditional sums & products, and the trigonometric ratios.
  • g. ABS (), CEILING (), COS (), SIN (), LOG () etc.

Logical Functions 

  • Logical functions are provided by Excel, to help the user to work with logical values, TRUE and FALSE.
  • They include boolean operators, conditional tests and functions to return the constant logical values.
  • g. IF (), NOT (), TRUE (), FALSE (), AND () etc.

Statistical Functions

  • Excel provides a large selection of Statistical Functions, that perform most of the common statistical calculations, from basic mean, median & mode calculations to the more complex statistical distribution and probability tests.
  • g. AVERAGE (), COUNT (), CORREL (), FDIST (), FINV () etc.

Text Functions 

  • Text functions are provided by Excel, to help the user to work with text
  • They include functions to return information about a text string, to apply formatting to a text string, to convert between text and other data types, and to cut up and join together text strings.
  • g. FIND (), CONCATENATE (), LOWER (), UPPER (), MID () etc.

Date and Time Functions 

  • Data and Time functions are used to calculate Dates, Times, and Days. eg. DATE (), DAY (), MONTH (), HOUR (), MINUTE () etc.

Function wizard 

  • Function Wizard prompts user for the information their need to enter for each function.
  • Function Wizard can be useful if users are not sure of the correct format to use to enter the formula.
  • The Function Wizard also gives a description of the function at the bottom, as well as presenting the formula result.

Formatting a Worksheet

  • In Excel, every cell can be formatted differently.
  • There are many options available to customize the Excel Workbook, which can make the worksheet easier to read.
  • Excel also provides many number formats, allowing user to standardize how numbers will appear in the document. Formatting Cells includes the following
  • Changing data alignment
  • Date Fields
  • Currency or Account Fields
  • Changing Font

Changing Data Alignment 

  • Formatting is the process of changing the appearance of the data in a Worksheet.
  • The default alignment of text data, such as labels and column titles is on the left side of a cell.
  • Numbers, formulas, and dates, which are referred to as values, are right aligned by default.

These default alignments are not always the best choice for user’s data, so Excel makes it easy to improve the layout and appearance of a worksheet by using the cell alignment icons on the Home tab of the ribbon

Vertical Alignment 

  • In the Vertical Alignment three options are there to do Top Align, Middle Align, Bottom Align.

To change the vertical alignment of the alignment group

  • Select a cell or range of cells.  Click the Top Align, Center, or Bottom Align command.

Horizontal Alignment 

  • Horizontal Alignment used to align the text Left, Center and Right.

To align text or numbers in a cell 

  • Select a cell or range of cells.

Click on either the Align Left, Center, or Right commands on the Home tab

Orientation 

  • User can rotate data clockwise, counterclockwise, vertically, rotate text up or down or click Format Cell Alignment to set a more precise orientation by specifying the number of degrees to rotate the text. Select the cell. 
  • On the Home tab of the ribbon, in the Alignment group, locate the button containing “ab” (angled with an arrow underneath).
  • Click the button to display the Orientation menu.
  • There the user will find a button with an „a‟ and a „b‟ and an arrow all on an angle.

Indentation

  • Select Increase Indent to increase the indentation within the cell by one character space.
  • Select Decrease Indent to remove indentation by one character space.

 Indenting cell data 

  • Select the cells containing text, which the user want to indent.
  • Click the Increase Indent button in the Alignment group on the Home tab.
  • Each time the user click the Increase Indent button, Excel adds a small amount of space between the cell border and the data itself.

 Removing cell indentation 

  • Select the cells containing the indented text.
  • Click the Decrease Indent button in the Alignment group on the Home tab.

Each time user click the Decrease Indent button, Excel removes a small amount of space between the cell border and the data itself

Date Fields 

  • Highlight all the date fields.
  • Select the Home Tab.
  • In the Cell group, click Format button.
  • From the drop-down list, select Format Cells.



Currency or Accounting Fields

  • Currency format
  • Displays the currency symbol immediately to the left of the number and displays a minus sign in front of negative values.
  • Accounting format
  • Displays the currency symbol at the left edge of the cell, lines up the decimal points in a column, and encloses negative values within parentheses.
  • Also indents numbers slightly from the right edge of the cell to accommodate the right parenthesis in negative values.
  • Follow these steps to format numbers in Excel 2007 as currency
  • Select the cells containing the numbers, that the user wants to format.
  • From the Home tab, click the Number dialog box launcher in the bottom-rightØ corner of the Number group.
  • The Format Cells dialog box appears, with the Number tab on top.
  • In the Category list, select Currency.
  • Change any other options as desired, such as the number of decimal places or the symbol to use for currency.
  • The Accounting format also enables you to choose different ways to display negative values.
  • Click OK.

 

Changing Font

  • The character the Excel displays on the screen are a specific font types style, size and color.
  • Excel allows the user to change the font characteristics in a single cell, a range of cells, the entire worksheet or the entire workbook.
  • From Font Face drop-down list choose one of the font as user need.
  • Font size is used to change the size of the font.
  • Increase and Decrease font size is used to increase and decrease the size of font
  • Bold, Italic, Underline and Double Underline are used to make changes in the selected text.
  • Borders are used to do bordering around the selected cells.
  • Fill and Font colors are change the background and text color.

Printing a Worksheet

  • Step 1 :
  • From File tab, in the Backstage view click Print option or use keyboard shortcut with the combination of Ctrl + P
  • Step 2 :
  • Now user can see the print preview.
  • Step 3:
  • To preview the other pages that will be printed, click ‘Next Page’ or ‘Previous Page’ at the bottom of the window.
  • Step 4 :
  • Under Print, select the number of copies to be print.

 Step 5:

  • From Printer, choose the name of the printer.
  • Step 6:
  • In the Settings, the first drop down list has three options.
  • Step 7 :
  • Print Active Sheets – For only active sheets printing.
  • Step 8 :
  • Print Entire Workbook – For entire workbook printing.
  • Step 9 :
  • Print Selection – For selected sheet printing.
  • Step 10 :
  • Pages can be specified by entering the page number in the text box
  • Step11:
  • If one copy contains multiple pages, you can switch between Collated and Uncollated.
  • Step 12:
  • User can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows).
  • Step 13:
  • Select paper size as user need.
  • Step 14:
  • Select one of the predefined margins (Normal, Wide or Narrow) from the Margins drop-down list.
  • Step 15:
  • Select ‘Fit Sheet on One Page’ from the Scaling drop-down list.
  • Step 16 :
  • After all changes made, finally click Print button to get print out

Working with Charts

Creating Charts

  • To select the data for creating a graph click on the first cell of data and then drag the cursor over the remaining data to be included as the part of chart.

Go to Insert tab and click the line chart under the chart group

  • Click the chart and go to Design tab and click the chart layouts for creating chart title.
  • User can change the title name and axis name.
  • If user needs to edit the names of their series they can do so by first clicking on the graph to activate the Chart Tools Menu.
  • Under Chart Tools choose the Design tab, under the data group, click Select Data.

Modifying Charts

  • Click the chart and drag it another location on the same worksheet or click Move Chart button on the design tab.
  • Click the object in radio button to choose where the object is placed.
  • Then click OK.
  • To change the color of chart, click Design and click any one in Chart Styles
  • To reverse which data are displayed in rows or columns, click Chart
  • And then click Switch Row / Column button on the Design tab.
  •  
  1. Each excel file is called a workbook because.

a) It contains text and data Restarting

b) It can be modified

c) It can contain many sheets including worksheets and chart sheets

d) It can be used for calculation

2.What is the shortcut key to insert a new sheet in current workbook?

a) F11

b) Alt+F11

c) Shift+F11

d) Ctrl+F11

3.In Excel, by default Numeric Values appears in

a) Left aligned

b) Right aligned

c) Center aligned

d) Justify aligned

4.How are data organized in a spreadsheet?

a) Lines and spaces

b) Layers and planes

c) Rows and columns

d) Height and width

5 On an excel sheet the active cell in indicated by ?

a) A dotted border

b) A dark wide border

c) A blinking border

d) By italic text

Answers:

1.c

2.d

3.b

4.c

5.b

  1. Excel allows the user to use formulae to calculate and analyze data in their worksheet.

a) True

b) False

  1. Formatting is the process of changing the appearance of the data in a Worksheet.

a) True

b) False

  1. The dark outline indicates the currently active formula bar in the worksheet.

a) True

b) False

Answers:

1.a

2.a

3.b

Translate »
error: not allow