B10- INTRODUCTION TO EXCEL DATA ANALYSIS.pptx

B10- INTRODUCTION TO EXCEL DATA ANALYSIS.pptx

• 1.
  Introduction to Microsoft Excel
• 2.
  Key Features ofExcel 1 Spreadsheet Layout Grid of cells in rows and columns 2 Formulas and Functions Built-in and custom calculations 3 Data Visualization Various chart types to identify trends 4 Data Management Tools Sorting, filtering, and conditional formatting
• 3.
  Excel for DataAnalysis 1 Organize Data Use sorting and filtering 2 Perform Calculations Apply functions for analysis 3 Visualize Results Create charts to present findings
• 4.
  Basic Excel Functions SUMAdds numbers in a range AVERAGE Calculates arithmetic mean MAX Returns the highest value in a range MIN Returns the lowest value in a range
• 5.
  =CONCAT Links togetherthe content of multiple cells =COUNT Counts cells with numbers in a range =COUNTA Counts all cells in a range that has values, both numbers and letters =COUNTBLANK Counts blank cells in a range =COUNTIF Counts cells as specified =COUNTIFS Counts cells in a range based on one or more TRUE or FALSE condition =IF Returns values based on a TRUE or FALSE condition =LEFT Returns values from the left side of a cell =LOWER Reformats content to lowercase =MEDIAN Returns the middle value in the data =SUMIF Calculates the sum of values in a range based on a TRUE or FALSE condition
• 6.
  IF Function Condition Check ifa condition is met True Value Return this if condition is true False Value Return this if condition is false IFERROR function returns a specified value if a formula evaluates to an error; otherwise, it returns the result of the formula. Example: =IFERROR(A1/B1, "Error")
• 7.
  Date Functions DATE Creates datefrom year, month, day TODAY Returns current date NOW Returns current date and time MONTH Extracts month from date
• 8.
  Advanced Date Functions DATEDIF Calculatesdifference between two dates EOMONTH Returns last day of month EDATE Returns date shifted by months
• 9.
  Cell Referencing inExcel Relative references Absolute references
• 10.
  Sorting Data inExcel 1 Select Data Click and drag to select range 2 Data Tab Go to Data tab in ribbon 3 Sort Option Choose single or multi-column sort 4 Specify Criteria Select column and sort order
• 11.
  Filtering Data inExcel Enable Filters Click Filter button in Data tab Apply Criteria Use dropdown arrows to select filter options View Results Only matching rows displayed
• 12.
  Advanced Functions VLOOKUP andINDEX/MATCH Functions The VLOOKUP function searches for a value in the first column of a table and returns a value in the same row from a specified column. Example: =VLOOKUP(A2, B2:D10, 3, FALSE)
• 13.
  Introduction to VLOOKUP Definition VLOOKUPis a powerful function in Excel that searches for a value in the first column of a range (table or array) and returns a corresponding value in the same row from a specified column. Syntax =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Purpose VLOOKUP is used to find and retrieve specific data from large datasets, making it an essential tool for data analysis and reporting in Excel.
• 14.
  VLOOKUP Parameters 1 lookup_value Thevalue you want to look up in the first column of the table array. 2 table_array The range of cells that contains the data you want to search through. 3 col_index_num The column number in the table array from which the matching value should be returned. 4 range_lookup Optional. TRUE for approximate match (default), FALSE for exact match.
• 15.
  Introduction to INDEX Function Definition TheINDEX function can return an item from a specific position in a list or table. Syntax =INDEX(B4:C12,5,2) Purpose INDEX is used to retrieve values from a specific row and column intersection within a given range.
• 16.
  INDEX Function Parameters 1array The range of cells you want to return a value from. 2 row_num The row number in the array from which to return a value. 3 column_num The column number in the array from which to return a value.
• 17.
  Introduction to MATCH Function Definition TheMATCH function can return the position of a value in a list. Syntax =MATCH(B19,B4:B12,0) Purpose MATCH is used to find the relative position of an item in an array that matches a specified value.
• 18.
  MATCH Function Parameters 1lookup_value The value you want to find in the array. 2 lookup_array The range of cells being searched. 3 match_type Specifies how Excel matches the lookup_value with values in lookup_array.
• 19.
  Combining INDEX andMATCH 1 Purpose The INDEX / MATCH functions can be used together for the purpose of extracting data from a table. 2 Syntax =INDEX(C$4:C$12,MATCH($I4,$B$4:$B$12,0)) 3 Advantage This combination offers an interesting alternative to VLOOKUP, providing more flexibility and potentially better performance.
• 20.
  VLOOKUP vs INDEX/MATCH VLOOKUP -Simpler syntax - Searches only in leftmost column - Can be slower with large datasets INDEX/MATCH - More flexible - Can search in any column - Often faster with large datasets
• 21.
  VLOOKUP A powerful and straightforwardfunction for vertical data lookup in Excel. INDEX and MATCH Versatile functions that can be combined for more flexible and efficient data retrieval. Choose Wisely Select the appropriate function based on your specific data structure and lookup needs.