No one likes to work more and humans have always tried to find more and more convenient methods to perform every task. I will not be wrong if I say that usually most of the people do not like to perform calculations, especially if it has to be done for a lengthy record and even, keeping record, storing data is also not that easy, and if you have to alter it at some point of time, you need to look at whole data again and then manipulate which is very time-consuming. so, keeping the same thing in mind a software has been designed called Excel in which you can perform all these above-mentioned tasks, various other tasks very easily.
- Traditional ways of storing data:
- Why Excel? Why not primitive methods?
- What are the capabilities of Excel?
- Basics of excel:
- Entering data: (Formatting and Editing)
- Some other Commands:
- Fundamentals of Calculations in excel:
- Addition:
- Subtraction:
- Multiplication:
- Division:
- Most common Functions:
- Visualizing Data:
Excel is basically, used to store data. Commonly used by businessmen, accountants, professionals, companies to manage there work and keep a record of everything in a systematic order. You can make Trial Balance, Ledger, Double Entry, invoices with the help of excel sheet.
Traditional ways of storing data:
Traditionally, to store data a booklet was used which contained sheets of paper called Spreadsheets, having rows and columns for the systematic storage of data.
Why Excel? Why not primitive methods?
- It is very easy to perform calculations using excel than doing manually.
- The chances of mistakes are reduced.
- Even if there is something incorrect, then also data can be easily manipulated without any complications and disturbing the order of data, which is very difficult if it has to be done on a sheet of paper.
- Moreover, sharing of the data becomes very easy and if you are using it online then more than one individual can have access to the record, simultaneously.
What are the capabilities of Excel?
- Entering and storing data
- Manipulating data
- Performing calculations
- Displaying data visually
- Sharing of data
Basics of excel:
- When you open excel first thing that you will notice is the vast white-colored area with wire mesh-like structure, it is called a spreadsheet or worksheet. You can create as many sheets you want and how to create a new sheet we will be discussing later ahead. The name of the current worksheet is displayed in the sheet tab which is located at the bottom of the excel window.
- Horizontally running spaces in a spreadsheet is called Rows. You will notice a series of numbers towards the extreme left in front of each row, these numbers represent the rows.
- Vertically running spaces are called Columns. Alphabets on the top of a spreadsheet are column labels, representing each column.
- The rectangular box formed at each intersection of a row and column is called Cell. Data is entered into these cells. The cell with the black thickened border is called Active cell, information is entered in the active cell.
- At the top of your screen, you can see different options such as home, view, insert, etc, all these are called Ribbons. It enables you to access all the various commands in the excel.
- Just above the labels (alphabets) of the columns is present Formula bar. Whatever is written in the active cell is displayed in the formula bar. (manipulation of the information can also be done from here)
- To the left of the formula bar, there is Name box, name of the active cell appears here, and the name is assigned as, for example: A2 : A here represents the column and 2 represents the row number.
- The group of selected cells is called Range. (Ahead range word may be used instead of selected cells)
Entering data: (Formatting and Editing)
- By clicking on the cell, it becomes active and information can be stored in it.
- Text allignment: When we enter text by default it is aligned on the left and information in numeric form is aligned in right, however, this alignment can be easily changed from the Home ribbon.
- Entered information can be easily formatted using a Home ribbon.
- There are also some shortcuts for formatting the text like(ctrl+ B) for bold text, (ctrl+ I)for Italic and (ctrl+ U) for underlining.
- Copying information: select one cell and drag the mouse to select other cells, another way can be to enter cell reference in the next cell and then copy that formula to other cells with the previous method.
- Inserting and deleting rows/columns: right click on the active cell then select insert row/column option or select delete row/column.
- Inserting a new sheet: Go to sheet tab, right-click and various options will appear in front of you, select insert new sheet and a new sheet will be added.
- Rename the sheet: By default, the name of the sheets is as Sheet1, Sheet2 but you can also change the name of the sheet by simply double-clicking on it or right-click on the sheet and various options appear select rename.
Some other Commands:
- Freeze panes: It is used when you want the headings of row and column to remain visible even if you scroll and pass by in a spreadsheet. Go to view a ribbon and select freeze panes. (The row or column in front of/at top of the active cell will be visible to you even if you scroll the spreadsheet.
- Sort= Data can be sorted out or arranged in a particular order like ascending or descending. Select the data, go to Home ribbon select sort
- Wrap the text: It is used when you do not want to increase the width of the column but the information entered in the cell is too long that it is not fully displayed (by default in such cases half of the text hides). In such situations wrap the text command can be very useful go to the home ribbon and select wrap the text, the height of the column will be automatically adjusted (increases) and the whole information is visible to you.
- Remove Duplicate: It is used to eliminate the same information which is being repeatedly entered, for example:- you have the list of students who made power point presentation on various topics but the topics selected by some the learners were similar but you want the unique ones, go to data ribbon and select remove duplicate and the similar ones will be removed.
- Conditional Formatting: It enables you to format a range or a cell based on the value entered in the cell using different color scales or icons.
- Filter: You can filter the data as you wish. Go to home ribbon and select filter and little arrows will pop down across the headers and click on the arrow and various option will appear select one and you can filter the data as you want, (only the data you want to see will be visible that is the filtered one) rest of the data is not being deleted rather it is just hidden, anytime can remove the filters (go to home ribbon select remove filter) and whole data will be visible.
- Format Painter: It is used to copy the formatting of the cells. Go to home ribbon and select format painter then select the range you want to be formatted.
Fundamentals of Calculations in excel:
There are different formulas are used for performing calculations and equal (=) sign is a must for any formula.
The basic structure of any formula looks like:
=FUNCTION(——–,———-,———), After an equal to sign, we write Function like SUM OR IF and the information/values mentioned inside the brackets are called Arguments.
-
Addition:
There are various methods of adding the data you can use any one as per your convenience, for example:- =SUM(3+6) then press enter and you will get 9, but this formula can not be copied to other cells, so what can be done that there is no need to rewrite the formula every single time?
Solution of this is that use cell reference instead of writing 3+6 (cell reference is the name of the cell in which the data is present, for example:- =SUM(A3+A4) or directly write =A3+A4 then press enter, now this formula can be copied to other cells and the cell reference will change accordingly.
Taking another example: =SUM(A3:A9) then press enter and you will get the total sum of the data in the given range
(A3:A9) here this represents the range that is from the cell A3 to cell A9.
-
Subtraction:
Similarly, subtraction can also be done:
for example: =MINUS(A3:A6) then press enter
-
Multiplication:
Coming to Multiplication, =PRODUCT(A3,A5) or simply write =(A3*A5) then press enter.
-
Division:
The division is also very simple, you can do it as: =DIVIDE(A3,A5) or=(A3/A5) then press enter.
Tip: You need not rewrite the formulas just copy them to the cells but make sure that cell references are entered in the formula. Also, these calculations can be performed by a command in the home ribbon called auto sum.
Most common Functions:
There are various other functions in excel such as:
- IF: This logical function is used when we have to impose a specific condition, this function reinstates one value if the given condition is satisfied/true and another value if the condition is not satisfied/false.
=IF([logical_test,[value_if_true],[value_if_false])
| Arguments: | logical_test: | Condition that has to be met |
| value_if-true | Enter the value that should come if the condition is met. | |
| value_if_false | Enter the value that should come if the condition is not met. |
for example: =IF(A3<30,”FAIL”,”PASS”) here excel will show FAIL if the value in cell A3 is less than 30, otherwise PASS.
- COUNTIF:Used to count the number of cells that emulates the provided criteria.
=COUNTIF(range,criteria)
| Arguments: | range | range in which excel should count the cells as per the given norm. |
| criteria | Based on what criteria excel should count the cells. |
for example: =COUNTIF(B2:ND2,0) here excel will count the number of cells that contain zero in the given range.
- REPLACE: It is used when you have to replace some text value with the other.
=REPLACE(old_text,start_num,num_of_chars,new_text)
| Arguments: | old_text | The text which has to be replace. |
| start_num | From which word should excel start replacing. | |
| num_of_characters | Up to how many characters (alphabets) should excel replace | |
| new_text | New text that should appear. |
for example: =REPLACE(D4,1,8,”DEAR”) here excel will replace the text value entered in the cell D4, starting from the first word up to 8 characters (till 8 alphabets) with DEAR.
- VLOOKUP: It is used to track down and recover some data matching the desired value from another table or worksheet from a particular column.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
| Arguments: | lookup_value | Value for which excel should look for. |
| table_array | Table(range) in which excel should look for the given value. | |
| col_index | Column number from where data has to be recovered. | |
| range-lookup | (optional) If you want the exact match of the desired value then write FALSE, If approximate match then write TRUE(default). |
for example: =VLOOKUP(A3,products’!A2:H16,2,FALSE) here excel will lookup for the value entered in the cell A3, in the given table and will restore data from second column.
- HLOOKUP: It is very similar to the VLOOKUP function, only the difference is that this function is used to lookup and retrieve data from a specific row in a table.
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
| Arguments: | lookup_value | Value for which excel should look for. |
| table_array | Table(range) in which excel should look for the given value. | |
| row_index | Row number from where data has to be recovered. | |
| range-lookup | (optional) If you want the exact match of the desired value then write FALSE, If approximate match then write TRUE(default). |
for example: =HLOOKUP(A3,products’!A2:H16,2,FALSE) here excel will lookup for the value entered in the cell A3, in the given table in the second row.
Visualizing Data:
Data can also be presented graphically, pictures graphs can be easily inserted.
Go to insert ribbon select table/picture and it will be inserted.
Excel has immense possibilities just play with its functions and explore, and excel in excel.