Computer Basics -8 || Let’s learn the usage of MS-Excel.

Excel is mainly used to do accounts related work and to maintain huge record like we do in databases.

When we open the Excel application and create a new file it looks like the Math Copy, we have used in our school during the study. These files contain grids to store data and records.

MS Office Suite

Excel files are mainly saved in .xls and .xlsx format. Few of the features and functionalities are same as in Word we have read in our previous chapter.

Before reading this chapter please visit the previous chapter, the link is given below for the reference.

Excel Features

As we discussed, excel is used to store records and for all-purpose of accounting work. Below are the unique features of Excel which make it an outstanding product.

  • We can store data in Tabular Format.
  • The Formula can be applied to the cell values for minor to complex calculations.
  • Various categories of Formula can be used like Math, Trignomatric, Logical, Text, Date-Time, Financials, Engineering and many more.
  • Formatting like font color, font style, background color, bold, italic, underline, etc. can be applied to the content.
  • Multiple Reports can be generated using a single data table.
  • Multiple Charts and Pivot Tables can be generated using a single data table.
  • Conditional Formatting: We can apply formatting on the content based on a condition also. For example, highlighting all the cells having text “Fail” with red color.
  • Sort: Sorting means displaying data in alphabetical or numerical sequence. We can apply sorting on the data very easily and quickly.
  • Filter: Some times we need some specific records out of the long list of table. In that case, we can apply Filter based on certain conditions to get relevant information.
  • Adding Hyperlinks, Images, Special Characters, Mathematical equations are similar to Word.
  • Text to Column: We can convert the normal text into the table based on any common symbol or text.
  • Remove Duplicate: Compares two or more columns and remove duplicates content.
  • Data Validation: We can set values in a cell while creating a document so that only relevant data can be filled by the user. Suppose, we have a column named “Gender”. Ideally, this field should have only three values like “Male”, “Female” and “Other”. But the user can enter anything in the cells. So using data validation we could restrict the user to select a value from the required list only.
  • Group/UnGroup: We can group the similar type of content (row or column) as per the user requirement. This feature can be useful when we have a large number of row or column in an individual sheet.
  • Spelling and Grammer Checker Feature.
  • Password protect the sheet or workbook. Even we can protect as a set of range in the sheet.

Launch Application

  1. Press Windows key from the keyboard.
  2. Type Excel in the search box.
  3. Look for the Microsoft Excel and Click.
  4. By default blank document would open with name “Book1

Application Introduction

Various sections of Excel Application
Various sections of Excel Application
  • Title Bar would remain the same as Word Application.
  • Few of the features from Menu & Tool Bar would be same as Word Application.
  • We can create any number of files, these files are called Workbook.
  • Each Workbook can contain n number of Worksheets. Like, Sheet1, Sheet2 and so on, as mentioned in the Navigation Bar.
  • Each worksheet contains 1,048,576 rows and 16,384 columns in the Content Editor area.
  • Rows are present horizontally and named as 1, 2, 3 and so on.
  • Columns are present vertically and named as A, B, C, D and so on.
  • We store data in a Cell. A cell is the intersection of Row and Column.
  • We can identify a cell from the Cell Reference box. A cell address is a combination of Row and Column name. For example, the first cell would be referred to as A1.
  • Any formula written in any cell can be seen in the Formula Box.

Creating Table

Any standard table should contain a Table Headings, Rows, and Columns.

  • Table Headings: To specify row or column heading for the table.
  • Row: Each Row in the table is called a record.
  • Column: Each Column should be similar value with respect to the heading.
  • Cell Value: Relevant content for each cell.

For example, we have to create a table for a Class where we need to capture marks of each subject scored by individual Student. As mentioned below:

Sample table without any Formatting.
Sample table without any Formatting

In the above table, we observed that:

  • Headings: Student Name, Math, History, Science, GK, Hindi
  • Row: All the horizontal values for “Student_1” is called a row or a record.
  • Column: All the vertical values for any subject like “GK” is called a column.
  • Cell Value: All the text either heading name, student name, marks are known as cell values.
  • Table Range: Range is the starting and ending cell reference of a table is called Table Range. In this example, Table Range would be A1 to F6.

Apply Formatting to the Table

Center Text under Home Tab.
  • Align all the text center in each cell. To implement this follow steps:
    • Select the entire table from range A1 to F6.
    • Navigate to Home Menu
    • Locate the Alignment section
    • Click on the Center icon.
Locate All Borders option under Font section.
  • Apply border around entire Table.
    • Select the entire table from range A1 to F6.
    • Navigate to Home Menu
    • Locate the Font section
    • Click on the Borders drop-down
    • Now Click on All Borders option
Apply special formatting to the Headings.
  • Apply special formatting to the Headings.
    • Select the first-row from range A1 to F1.
    • Navigate to Home Menu
    • Locate the Font section
    • Click on the B icon to change the text to Bold
    • Now Click on Fill Color drop-down
    • Select any color as per requirement.

Now the entire table will look like,

Data table with Formatting.
Data table with Formatting

Apply Formula to the Table

Now, let’s suppose we have to add a new column with the name “Total Marks“. The value in this column should be the sum of marks obtained in each subject by the individual Student.

  • Type “Total Marks” in the cell G1
  • Now navigate to just below in cell G2
  • Type formula like =sum(B2: F2) and press Enter, here formula shows that we have to sum all the values present in the range from B2 to F2.
  • Now, we have to apply this formula to the remaining cells.
  • For this, again click on cell G2
  • Press Ctrl+C to copy the cell value.
  • Now, paste the same formula in each cell G3, G4, G5, G6
  • All the remaining formatting can be applied as it is.
  • The updated table will look like as present below:
Table after inserting new Column and Formula

Related Links:

Excel:

TestNG:

Java Basics:

Java File Handling:

OOPs Concept:

Java Question And Answer:

Java Programs:


806total visits,2visits today

Leave a Reply

Your email address will not be published. Required fields are marked *