MS EXCEL
Common terms
Workbooks
- Excel files are known as workbooks.
Cell
- Intersection of row and a column.
Data Entry
Filling number
- Ctrl + click on fill handle and drag → if only one digit is entered.
- click on fill handle drag to fill data.
Adjusting multiple column width
- Select all columns and double click on any mid section.
- Ctrl + A then Alt + h + o + i
Finding percentage
- =total_sum/total_value*100
Conditional formatting
- The range that we have selected on that we are going to do formatting according to the conditions
- Like we have to highlight all the number that are less than 35 then in 'conditional formatting'
option select 'less than'.
- There are more options like greater than, equal to, between and so on.
Insert/Delete = Row/Column
- Insert → Click on row or column and press Ctrl + +
- Delete → Ctrl + -
Merge and center
- Selecting single range click 'merge and center' on home ribbon under alignment group.
Tables
- Convert your data into table and then formating it becomes easier.
- If you want to remove the table so inside design tab there is an option 'convert to range'.
Filter and sort
- Filter → In data tab there is a 'filter' option, using this we can filter data according to the
option.
- Sort → arranging data in ascending or descending order.
If formula
If
- =IF(I12>=35,"PASS","FAIL") → if in cell I12 the value is greater or equal to 35, it will
show "PASS" else "FAIL".
- This works when there is only single column or row.
IF...AND → works with multiple rows and columns.
- =IF(AND(C3>=35,D3>=35,E>=35),"PASS","FAIL")
- Here everything option should be true.
IF...OR
- =IF(OR(C3<35,D3<35,E<35),"PASS","FAIL")
Nesting IF
- =IF(L3="FAIL","FAIL",IF(K3>=75,"A+",IF(K3>=60,"B+",IF(K3>=55,"B-",IF(K3>=35,"C")))))
Wrap text
- To fit the text inside small width.
Freeze column
- When we scroll we permanently want to see some particular column.
- Select a cell below the row you want to freeze and press 'freeze panes' under view tab.
Mean, Median and mode
Mean
- Basically it is an average.
- Formula → =AVERAGE(cell_a:cell_z)
- Or you can select the cells.
Median
- this means the middle value in list of arranged numbers
- Formula → =MEDIAN(cell_a:cell_b)
Mode
- Most common value or repeated value in a given data.
- Formula → =MODE.SNGL(cell_a:cell_z)
Some questions
What is MS Excel?
- Microsoft Excel is an electronic spreadsheet application that enables users to store, organize,
calculate and manipulate the data with formulas using spreadsheet system broken up by rows and
columns.
- It also provides flexibility to use an externel database to do analysis, make reports, etc. thus
saving a lots of time.
What is ribbon?
- Ribbon refers to the topmost area of the application that contains menu items and toolbars
available in MS-Excel.
How many data formats are available in Excel?
- Eleven data formats are available in Microsoft Excel for data storage. Example:
- Number - Stores data as a number
- Currency → Stores data in the form of currency
- Date - Data is stored as dates
- Percentage - Stores numbers as a percentage
- Text Formats - Stores data as string of texts
Explain Macro in MS-Excel
- Macros are used for iterating over a group of tasks.
- Users can create macros for their customized repetitive functions and instructions.
- Macros can be either written or recorded depending on the user.
What are charts in MS-Excel
- To enable graphical representation of the data in Excel, charts are provided.
- A user can use any chart type, including column, bar, line, pie, etc. by selecting an option
from insert tab's chart group.
What are freeze panes in MS-Excel
- To locak any row or column, freeze panes is used.
- The locked row or column will be visible on the screen even after we scroll the sheet vertically
or horizontally.
What is IF function in Excel
- To perform the logic text IF function is performed.
- It checks whether certain conditions is true or false.
- If the condition is true, then it will give result accordingly if the condition is false then
the result or out-put will be different.
- =IF(Logical test, value if true, value if false)