× Back

MS EXCEL

Common terms

Workbooks

  • Excel files are known as workbooks.

Cell

  • Intersection of row and a column.

Range

  • Group of cells.

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

Sum of data

  • =SUM(C2:E2)

Finding max and min

  • =MAX(C2:E2)
  • =MIN(C2:E2)

Counting cells

  • =COUNT(E2:F3)

Finding average

  • =AVERAGE(E2:H2)

Finding percentage

  • =total_sum/total_value*100

Conditional formatting

Insert/Delete = Row/Column

Merge and center

Tables

Filter and sort

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

Freeze column

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)

Reference