Skip to Content

MICROSOFT EXCEL

Unlock the Power of Your Data: Mastering Excel
24 June 2024 by
Gautam
| No comments yet

MS Excel is a commonly used Microsoft Office application. It is a spreadsheet program which is used to save and analyse numerical data.

In this article, we bring to you the important features of MS Excel, along with an overview of how to use the program, its benefits and other important elements. A few sample MS Excel question and answers are also given further below in this article for the reference of Government exam aspirants. 

Basics of MS Excel

What is MS Excel?

MS Excel is a spreadsheet program where one can record data in the form of tables. It is easy to analyse data in an Excel spreadsheet. 

How to open MS Excel?

To open MS Excel on your computer, follow the steps given below:

  • Click on Start
  • Then All Programs
  • Next step is to click on MS Office
  • Then finally, choose the MS-Excel option

Alternatively, you can also click on the Start button and type MS Excel in the search option available.

What is a cell?

A spreadsheet is in the form of a table comprising rows and columns. The rectangular box at the intersection point between rows and columns forms a cell.

What is Cell Address?

The cell address is the name by which is cell can be addressed. For example, if row 7 is interested in column G, then the cell address is G7. 

Features of MS Excel

Various editing and formatting can be done on an Excel spreadsheet. Discussed below are the various features of MS Excel. 

  • Home
    • Comprises options like font size, font styles, font colour, background colour, alignment, formatting options and styles, insertion and deletion of cells and editing options
  • Insert
    • Comprises options like table format and style, inserting images and figures, adding graphs, charts and sparklines, header and footer option, equation and symbols
  • Page Layout
    • Themes, orientation and page setup options are available under the page layout option
  • Formulas
    • Since tables with a large amount of data can be created in MS excel, under this feature, you can add formulas to your table and get quicker solutions 
  • Data
    • Adding external data (from the web), filtering options and data tools are available under this category
  • Review
    • Proofreading can be done for an excel sheet (like spell check) in the review category and a reader can add comments in this part 
  • View
    • Different views in which we want the spreadsheet to be displayed can be edited here. Options to zoom in and out and pane arrangement are available under this category

Benefits of Using MS Excel

MS Excel is widely used for various purposes because the data is easy to save, and information can be added and removed without any discomfort and less hard work.

Given below are a few important benefits of using MS Excel:

  • Easy To Store Data: Since there is no limit to the amount of information that can be saved in a spreadsheet, MS Excel is widely used to save data or to analyse data. Filtering information in Excel is easy and convenient.
  • Easy To Recover Data: If the information is written on a piece of paper, finding it may take longer, however, this is not the case with excel spreadsheets. Finding and recovering data is easy.
  • Application of Mathematical Formulas: Doing calculations has become easier and less time-taking with the formulas option in MS excel
  • More Secure: These spreadsheets can be password secured in a laptop or personal computer and the probability of losing them is way lesser in comparison to data written in registers or piece of paper.
  • Data at One Place: Earlier, data was to be kept in different files and registers when the paperwork was done. Now, this has become convenient as more than one worksheet can be added in a single MS Excel file.
  • Neater and Clearer Visibility of Information: When the data is saved in the form of a table, analysing it becomes easier. Thus, information is a spreadsheet that is more readable and understandable.

MS Excel – Points To Remember

There are certain things which one must know with respect to MS Excel, its applications and usage:

  • An MS Excel file is saved with an extension of .xls
  • Companies with large staff and workers use MS Excel as saving employee information becomes easier
  • Excel spreadsheets are also used in hospitals where the information of patients can be saved more easily and can be removed conveniently once their medical history is cleared
  • The sheet on which you work is called a Worksheet
  • Multiple worksheets can be added in a single Excel file
  • This is a data processing application

What are the main functions of Excel?

MS Excel has a wide range of functions with which it is possible to interact with the data in the rows and columns of the program's sheets. These are the main Excel analysis functions:

  1. Basic formulas- They allow you to perform basic arithmetic calculations. (+, -, *, /)
  2. Mathematical functions- Excel provides both basic and complex trigonometric functions (SUM, AVERAGE, MAX, MIN, SIN, COS, and TAN)
  3. Conditional functions- Thanks to them, it is possible to perform evaluations based on conditions (IF, AND, OR, NOT)
  4. Lookup functions- They allow you to find values in the table (VLOOKUP, HLOOKUP, INDEX, MATCH)
  5. Text functions- They provide the ability to interact, manipulate, and format text strings (CONCATENATE, LEFT, RIGHT, UPPER)
  6. Statistical functions- Perform statistical analysis (AVERAGE, MEDIAN, CORREL, STDEV)
  7. Date and time functions- They allow you to perform calculations based on time (TODAY, NOW, DAY, MONTH, YEAR)
  8. Financial functions- They are useful for performing calculations based on finance (RATE, FV, PV, PMT).

Excel Formulas Cheat Sheet

Basic Formulas

Formula

Structure

Explanation

AVERAGE


=AVERAGE(A2:A10)


Returns a mathematical average of a given cell range

COUNT


=COUNT(A2:A10)


Returns the count of the numbers in given cell range

MAX


=MAX(A2:A10)


Finds the largest value in a given cell range

MEDIAN


=MEDIAN(A2:A10)


Returns the median value, or middle value, in a given cell range

MIN


=MIN(A2:A10)


Finds the smallest value in a given cell range

SUM


=SUM(A2:A10)


Totals numbers in a given cell range

Cell range A2:A10 is used above to indicate that each formula uses a cell range as it arguments.

Time Formulas

 Formula

Structure
Explanation

TODAY


=TODAY()

Volatile – takes no arguments – returns today’s date

NOW


=NOW()

Volatile – takes no arguments – returns today’s date and time

DATEDIF


=DATEDIF(Start Date, End Date, Unit)

Returns the number of years, months or days between two dates

Start Date – date furthest in the past

 Unit could be “Y” for years, “M” for months or “D” for days

 Units must be in double quotes

 This formula is NOT in the function library

YEAR


=YEAR(Date)

 Returns the year portion of date

 Example =YEAR(7/16/2005) would return 2005

MONTH


=MONTH(Date)

 Returns the month portion of date

 Example =MONTH(7/16/2005) would return 7

DAY


=DAY(Date)

 Returns the day portion of date

 Example =DAY(7/16/2005) would return 16

 Use a time formula and get an answer you didn’t expect? If you got a date and were expecting a number, remember to change the formatting from date to number. If you got a number and were expecting a date, change the formatting to date.

Logical Formulas

Formula

Structure
Explanation

IF


=IF(Logical Test, TRUE, FALSE)

  1.  Evaluates the statement in the logical test to determine if it is TRUE or FALSE
  2.  A Logical test compares the value of one cell to another, or a cell value to a constant value, using a comparison operator such as:
  3. Equal à =
  4. Less than à <
  5. Greater than à >
  6. Less than or equal to à <=
  7. Greater than or equal to à >=
  8. Not equal to à <>
  9.  TRUE – this part of the IF function will only execute when the logical test is TRUE
  10.  FALSE – this part of the IF function will only execute when the logical test is FALSE
  11.  Both TRUE and FALSE can be a word, a formula or a constant value
  12.  To return a blank cell use two double quotes

OR


=OR(Logical Test 1, Logical Test 2, ...)

  1.  This formula can only return TRUE or FALSE
  2.  Only one test in the group must return TRUE for the formula to return TRUE

AND


=AND(Logical Test 1, Logical Test 2, ...)

  1.  This formula can only return TRUE or FALSE
  2.  Only one test in the group must return FALSE for the formula to return FALSE

Lookup Formulas

Formula

Structure
Explanation

VLOOKUP

=VLOOKUP (Lookup Value, Table Array, Col Index, Range Lookup)

  1.  Lookup Value - What the function is looking for in the table array
  2.  Table Array - The table defined as a cell range
  3.  Col Index - The column in the table that forms the return
  4.  Range lookup - False for exact match, True or blank for near match
  5. A vlookup can only search vertically through the left most column of a table array for near or exact matches
  6. In most cases you will want to use absolute cell referencing when indicating a table array
  7. If you omit the Range Lookup, Excel will assume “True” and look for a near match

HLOOKUP


=HLOOKUP (Lookup Value, Table Array, Col Index, Range Lookup)

  1. Lookup Value - What the function is looking for in the table array
  2.  Table Array - The table defined as a cell range
  3.  Col Index - The column in the table that forms the return
  4.  Range lookup - False for exact match, True or blank for near match
  5. A hlookup can only search horizontally through the left most column of a table array for near or exact matches
  6. In most cases you will want to use absolute cell referencing when indicating a table array
  7. If you omit the Range Lookup, Excel will assume “True” and look for a near match


Financial Formulas

Formula

Structure
Explanation

PMT


= PMT(rate, nper, pv, [fv], [type])

  1. rate – Annual Percentage Rate
  2. Divide yearly rate by 12 months
  3. REQUIRED
  4. nper – number of periods
  5. How many months are in the loan? (12 months/year) * length of loan in years = length of loan in months
  6. REQUIRED
  7. pv – present value
  8. amount of loan – this is the amount that was borrowed
  9. REQUIRED
  10.  [fv] and [type] are in square brackets because they're optional arguments.
  11. fv – future value - $0 if the loan is paid if full
  12.  Excel assumes zero if omitted
  13. (zero) means the payments are due at the end of each period,
  14.  1 means they're due at the beginning
  15.  Excel assumes your payments are due at the end of the period if omitted

 

Statistical Formulas

Formula


Structure
Explanation

SUMIF


= SUMIF(range, criteria, [sum_range])

 SUMIF will return at total based on one criteria

  1.  Range - Required. The range of cells that you want evaluated by criteria.
  2.  Criteria - Required.
  3.  The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.
  4.  For example, criteria can be expressed as 32, ">32", B5, "32", "apples", or TODAY().
  5.  Important: Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.
  6.  Sum_Range Optional.
  7. The actual cells to add, if you want to add cells other than those specified in the range argument.
  8. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

SUMIFS


=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  1.  SUMIFS will return at total based on more than one criteria
  2.  Sum_Range - The range of cells to sum
  3.  Criteria_range1 – Required
  4. The range that is tested using Criteria1
  5.  Criteria1 – Required
  6. The first value tested for – this must be a match to be included in the total
  7.  Criteria_range2 – Optional
  8. The range that is tested using Criteria2
  9.  Criteria2 – Optional
  10. o The second value tested for – this must be a match to be included in the total
  11.  And so on…

 

COUNTIF


=COUNTIF(range, criteria)

  1.   Answers the question “How many of something (criteria) exist within specific set of cells (range)?
  2.  =COUNTIF(Where do you want to look?, What do you want to look for?)
  3.  Range – Required – Sets cells to be included in the count
  4.  Criteria – Required – Tells formula what to look for

COUNTIFS


=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

  1.  criteria_range1 – Required.
  2. o The first range in which to evaluate the associated criteria.
  3.  criteria1 – Required.
  4. o The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as:
  5.  32,
  6.  ">32",
  7.  B4,
  8.  "apples", or
  9.  "32"
  10.  criteria_range2, criteria2, ...
  11. o Optional.
  12. o Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
  13.  Important:
  14. o Each additional range must have the same number of rows and columns as the criteria_range1 argument.
  15. o The ranges do not have to be adjacent to each other

AVERAGEIF


=AVERAGEIF(range, criteria, [average_range])

  1.  Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
  2.  Range – Required.
  3. o One or more cells to average, including numbers or names, arrays, or references that contain numbers.
  4.  Criteria – Required.
  5. o The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as:
  6.  32,
  7.  ">32",
  8.  B4,
  9.  "apples", or
  10.  "32"
  11.  Average_range – Optional.
  12. o The actual set of cells to average.
  13. o If omitted, range is used

AVERAGEIFS


= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  1.  Returns the average (arithmetic mean) of all cells that meet multiple criteria
  2.  Average_range – Required
  3. o One or more cells to average, including numbers or names, arrays, or references that contain numbers.
  4.  Criteria_range1 – Required, subsequent criteria_ranges are optional (up to 127 ranges)
  5.  criteria_range2, criteria_range3,… – Optional
  6.  Criteria1 – Required, subsequent criteria are optional
  7. o Criteria can be in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as:
  8.  32,
  9.  ">32",
  10.  B4,
  11.  "apples", or
  12.  "32"
  13.  criteria2, criteria3,...
  14.  If you have a Criteria_Range, you must have a corresponding Criteria

Our Top 10 Most Popular Use Functions

Function
Description

SUM function

Use this function to add the values in cells.

IF function

Use this function to return one value if a condition is true and another value if it's false. Here's a video about using the IF function.

LOOKUP function

Use this function when you need to look in a single row or column and find a value from the same position in a second row or column.

VLOOKUP function

Use this function when you need to find things in a table or a range by row. For example, look up an employee's last name by her employee number, or find her phone number by looking up her last name (just like a telephone book). Check out this video about using VLOOKUP.

MATCH function

Use this function to search for an item in a range of cells, and then return the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 7, and 38, then the formula =MATCH(7,A1:A3,0) returns the number 2, because 7 is the second item in the range.

CHOOSE function

Use this function to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

DATE function

Use this function to return the sequential serial number that represents a particular date. This function is most useful in situations where the year, month, and day are supplied by formulas or cell references. For example, you might have a worksheet that contains dates in a format that Excel does not recognize, such as YYYYMMDD.

Use the DATEDIF function to calculate the number of days, months, or years between two dates.

DAYS function

Use this function to return the number of days between two dates.

FIND, FINDB functions

FIND and FINDB locate one text string within a second text string. They return the number of the starting position of the first text string from the first character of the second text string.

INDEX function

Use this function to return a value or the reference to a value from within a table or range.

Compatibility functions

In Excel 2010 or later, these functions were replaced with new functions that provide improved accuracy and have names that better reflect their usage. You can still use them for compatibility with earlier versions of Excel, but if backward compatibility isn't required, you should start using the new functions instead. For more information about the new functions, see Statistical functions (reference) and Math and trigonometry functions (reference) .

If you're using Excel 2007, you'll find these functions in the Statistical or Math & Trig categories on the Formulas tab.

Function
Description

BETADIST function

Returns the beta cumulative distribution function

BETAINV function

Returns the inverse of the cumulative distribution function for a specified beta distribution

BINOMDIST function

Returns the individual term binomial distribution probability

CHIDIST function

Returns the one-tailed probability of the chi-squared distribution

CHIINV function

Returns the inverse of the one-tailed probability of the chi-squared distribution

CHITEST function

Returns the test for independence

CONCATENATE function

Joins two or more text strings into one string

CONFIDENCE function

Returns the confidence interval for a population mean

COVAR function

Returns covariance, the average of the products of paired deviations

CRITBINOM function

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

EXPONDIST function

Returns the exponential distribution

FDIST function

Returns the F probability distribution

FINV function

Returns the inverse of the F probability distribution

FLOOR function

Rounds a number down, toward zero

FORECAST function

Calculates, or predicts, a future value by using existing values.

FTEST function

Returns the result of an F-test

GAMMADIST function

Returns the gamma distribution

GAMMAINV function

Returns the inverse of the gamma cumulative distribution

HYPGEOMDIST function

Returns the hypergeometric distribution

LOGINV function

Returns the inverse of the lognormal cumulative distribution function

LOGNORMDIST function

Returns the cumulative lognormal distribution

MODE function

Returns the most common value in a data set

NEGBINOMDIST function

Returns the negative binomial distribution

NORMDIST function

Returns the normal cumulative distribution

NORMINV function

Returns the inverse of the normal cumulative distribution

NORMSDIST function

Returns the standard normal cumulative distribution

NORMSINV function

Returns the inverse of the standard normal cumulative distribution

PERCENTILE function

Returns the k-th percentile of values in a range

PERCENTRANK function

Returns the percentage rank of a value in a data set

POISSON function

Returns the Poisson distribution

QUARTILE function

Returns the quartile of a data set

RANK function

Returns the rank of a number in a list of numbers

STDEV function

Estimates standard deviation based on a sample

STDEVP function

Calculates standard deviation based on the entire population

TDIST function

Returns the Student's t-distribution

TINV function

Returns the inverse of the Student's t-distribution

TTEST function

Returns the probability associated with a Student's t-test

VAR function

Estimates variance based on a sample

VARP function

Calculates variance based on the entire population

WEIBULL function

Returns the Weibull distribution

ZTEST function

Returns the one-tailed probability-value of a z-test

Cube functions

Function
Description

CUBEKPIMEMBER function

Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.

CUBEMEMBER function

Returns a member or tuple from the cube. Use to validate that the member or tuple exists in the cube.

CUBEMEMBERPROPERTY function

Returns the value of a member property from the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.

CUBERANKEDMEMBER function

Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.

CUBESET function

Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Excel.

CUBESETCOUNT function

Returns the number of items in a set.

CUBEVALUE function

Returns an aggregated value from the cube.



Gautam 24 June 2024
Share this post
Tags
Microsoft excel advanced excel data entry data entry jobs data entry operator excel excel sheet exel google excel google sheet google spreadsheet google worksheet googlesheets gsheet in excel ms excel sheet spreadsheets
Our blogs
Archive
Sign in to leave a comment
Read Next
MICROSOFT WORD
Master Your Docs With MS. Word