DEV Community

Mark Glemba
Mark Glemba

Posted on

HOW EXCEL IS USED IN REAL-WORLD DATA ANALYSIS

#datascience #beginners

INTRODUCTION
Excel is a tool one can use to organize data for work or any other specific software functions. It allows one to develop graphs for comparison of data and create charts to organize and visualize data.

HOW EXCEL IS USED IN REAL WORLD SCENARIOS

  1. DATA CLEANING
    Excel is in various ways for data cleaning by performing functions such as; removing duplicates, trimming spaces, fixing formatting, finding errors, standardizing data, splitting data and filling blanks.

  2. DATA SUMMARIZATION
    Excel summarizes large data sets to offer quick insights by use of; pivot tables, formulas and functions, instead of users manually going through large data sets.

  3. VISUALIZING TRENDS
    Done by the use of charts; pie, line and bar charts.

FORMULAS USED IN EXCEL
Basic formulas in excel include:

  • Add: Used to add values of two or more cells. The (+) is used.

  • Subtract: Used to subtract values of two or more cells. The (-) is used.

  • Multiply: Used to multiply values of two or more cells. The (*) is used.

  • Divide: Used to divide values of two or more cells. The (/) is used.

BASIC FUNCTIONS USED IN EXCEL
Functions are used to automate tasks normally performed by formulas in excel. Some popular and basic functions include:

  1. AVERAGE: It sums up and calculate the mean of values within a range given. For example,=AVERAGE(A1:A15).
  2. SUM: It adds up a range of cells. For example,=SUM(A1:A15).
  3. IF: Used to arrange values based on a logical test. The syntax for IF ; =IF(logical_test, value_if_true, [value_if_false]). For example, =IF(N2>30, "young", "old").
  4. COUNTIF: Used to return the number of cells that meeet a certain criteria. The syntax is; =COUNTIF(range, criteria). For example, =COUNTIF(A1:A27, "Nairobi"). NOTE: COUNTIFS function counts the number of cells that meet multiple criteria.
  5. VLOOKUP: Allows one to search for a value on a spreadsheet. Its syntax ; =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP(300,A1:A20, 3, "False").

CONCLUSION
In conclusion Excel's versatility makes it an efficient tool for quick analysis and reporting. Its availability allows users to efficiently handle large data sets through processes such as performing calculations and creating meaningful visuals.

Top comments (0)