Posted on Leave a comment

Data Entry & Management in Excel

One of the main uses of Microsoft Excel is for data entry, maintenance and monitoring. Data in Excel can be organized in columns, as a table, or in a pie chart or graph.

Right now, we’ll be focusing on the basics of data entry and management in a worksheet. Data here is entered in a table that consists of rows and columns.

The top rows of tables have headings that go down to the left and are used to identify the data being entered in the tables. This lets Excel do any necessary calculations through the use of formulas. The program definitely makes it easier to manage and process data.

To make it even easier, Excel also provides a number of ways to formatting the data. If you want to locate and review some information, you’ll appreciate these data formatting features. They can be used for data formatting in individual cells, different columns and rows, and for formatting entire tables.

In previous versions of Excel each worksheet contained a billion cells, while the most recent updates released by Microsoft have almost seventeen billion cells.

To make it easier to access and reference these multitudinous cells, each of them has been given an address. This is called a cell reference.

Posted on 1 Comment

MS Excel Index Function

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Syntax 

=INDEX (array, row_num, [col_num], [area_num])

=INDEX (reference, row_num, [col_num], [area_num])

Arguments 

  • array – A range of cells, or an array constant.
  • row_num – The row position in the reference or array.
  • col_num – [optional] The column position in the reference or array.
  • area_num – [optional] The range in reference that should be used.

Explanation:

1.We want to know how much student 5 got In physics. So we first type index and select array (all the numbers).

2.Forrow_num we can give 5 or use match function (it is easier to use match function when the list is long).

3. For column_num we can give 4 or use match function (it is easier to use match function when there are many column).

Usage notes

Use the INDEX function to get a value from a list or table based on its location. For example, the formula =INDEX(A1:B5,2,2) will return the value at the address B2.

The INDEX function has two forms: array and reference.