NIOS Data Entry Ch-6 Basics of Spreadsheet Part 1

Download PDF of This Page (Size: 202K)

Basics of Spreadsheet

Introduction

Spreadsheet is quite useful in entering, editing, analyzing and storing data. Arithmetic operations with numerical data such as addition, subtraction, multiplication and division can be done using Excel. You can sort numbers/ characters according to some given criteria (like ascending, descending etc.) and use simple financial, mathematical and statistical formulas.

Features of Spreadsheets

1. AutoSum - helps you to add the contents of a cluster of adjacent cells.

2. List AutoFill - automatically extends cell formatting when a new item is added to the end of a list.

3. AutoFill - allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text. AutoFill can also be used to copy functions. You can also alter text and numbers with this feature.

4. AutoShapes toolbar will allow you to draw a number of geometrical shapes, arrows, flowchart elements, stars and more. With these shapes, you can draw your own graphs.

5. Wizard - guides you to work effectively while you work by displaying various helpful tips and techniques based on what you are doing.

6. Drag and Drop - it will help you to reposition the data and text by simply dragging the data with the help of mouse.

7. Charts - it will help you in presenting a graphical representation of your data in the form of Pie, Bar, Line charts and more.

8. PivotTable - it flips and sums data in seconds and allows you to perform data analysis and generating reports like periodic financial statements, statistical reports, etc. You can also analyze complex data relationships graphically.

9. Shortcut Menus - the commands that are appropriate to the task that you are doing will appear by clicking the right mouse button.

Features of MS Excel 2007

  • Results-oriented user interface

  • More rows and columns, and other new limits

  • Office themes and Excel styles

    • Applying a theme

    • Using styles

  • Rich conditional formatting

  • Easy formula writing

    • Resizable formula bar

    • Function AutoComplete

    • Structured references

    • Easy access to named ranges

  • Improved sorting and filtering

Starting Excel

  • Click on (with the help of mouse) the Start button on the Taskbar at the bottom left corner of the Screen.

  • Highlight the All Programs item. The program menu will open.

  • Select Microsoft Office from the list of programs.

  • Click on Microsoft Excel.

  • Select Start→All Programs→Microsoft Office→Microsoft Excel 2007 commands from your menu bar.

Image of MS

Image of MS

Image of MS

Excel Worksheet

The worksheet is a grid of columns (designated by letters) and rows (designated by numbers). The letters and numbers of the columns and rows (called labels) are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. Cells can contain text, numbers, or mathematical formulas.

Selecting, Adding and Renaming Worksheets

  • Insert a new worksheet

  • To insert a new worksheet before an existing worksheet

  • Alternative Method to insert a new worksheet

  • To rename a worksheet

Selecting Cells and Ranges

  • Way to select particular range of cells is:

  • Go to Name Box

  • Select range by typing (A1:C5)

  • Press Enter

  • All the cells between range A1 to C5 will be selected.

  • Move or scroll through a worksheet

  • We can scroll through worksheet by different ways. One can use mouse, scroll bar or arrow keys to move between cells and to different areas of the worksheet. To move between cells on a worksheet, click any cell or use the arrow keys. When you move to a cell, it becomes the active cell.

  • Scroll and zoom by using the mouse

  • Some mouse devices and other pointing devices, such as the Microsoft IntelliMouse pointing device, have built-in scrolling and zooming capabilities that you can use to move around and zoom in or out on your worksheet or chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.). You can also use the mouse to scroll in dialog boxes that have drop-down lists with scroll bars.

Data Entry

  • Numbers

  • Text

  • Date and Time

  • Data in Series

Editing Data

  • Select the cell containing data to be edited. Press F2. Use Backspace key and erase the wrong entry. Retype the correct entry.

  • Select the cell and simply retype the correct entry.

  • If you want only to clear the contents of the cell, select the cell and press Delete key.

  • To bring back the previous entry, either click on Undo button on standard Toolbar or select Edit→Undo command or use keyboard shortcuts CTRL+Z.

Cell References

Each worksheet contains a number of columns and rows. Each cell of the worksheet has a unique reference. For example, A8, refers to the cell containing column number A and row number 8.

Find and Replace Data in a Worksheet

You may want to locate a number or text that is already typed in the worksheet. This is done through Home Tab→Find. You can also locate your data and replace with new data with Home Tab→Find→Replace.

Image of Replace Data in a Worksheet

Image of Replace Data in a Worksheet

Image of Replace Data in a Worksheet