Essential Excel Formulas for Data Analytics with Examples

1. SUM()

• Purpose: Adds up a range of cells.
• Example: Suppose you have the following data in cells A1 through A5 and you want to sum them:
  

  A
  10
  20
  30
  40
  50

  • Formula: =SUM(A1:A5)
  • Result: 150

2. AVERAGE()

• Purpose: Calculates the average of a range of cells.
• Example: Using the same data set as above, to find the average:
  

  A
  10
  20
  30
  40
  50

  • Formula: =AVERAGE(A1:A5)
  • Result: 30

3. COUNT()

• Purpose: Counts the number of cells in a range that contain
• • Example:
    • Data Setup: Assume cells A1 to A3 contain the numbers 5, 10, and 15.
    • Formula: =COUNT(A1:A3)
    • Result: 3

  4. IF()

  • Purpose: Checks whether a condition is met, returns one value for TRUE, and another for FALSE.
  • Example:
    • Data Setup: Assume cell A1 contains the number 5.
    • Formula: =IF(A1 > 10, "Above 10", "10 or Below")
    • Result: “10 or Below”

4)Highlight Duplicate Values

• • Function: Use Conditional Formatting to highlight duplicate values in a dataset.
  • Example:
    

    A
    Cat
    Dog
    Cat
    Bird

    • Use Conditional Formatting from the Home tab to highlight “Cat” as it appears more than once.

• Remove Duplicates

  • Function: Remove duplicate rows from a dataset to ensure unique data.
  • Example:
    

    A	B
    Cat	1
    Dog	2
    Cat	1

    • After using Remove Duplicates from the Data tab, the table will only retain one “Cat” row.

• Concatenate

  • Function: Combines text from multiple cells into one cell.
  • Example:
    

    A	B	C
    John	Smith	=CONCATENATE(A1, ” “, B1)

    • Result in C1: “John Smith”

• Round

  • Function: Rounds a number to a specified number of decimal places.
  • Example:
    

    A	B
    3.14159	=ROUND(A1, 2)

    • Result in B1: 3.14

• Roundup

  • Function: Rounds a number up to a specified number of decimal places.
  • Example:
    

    A	B
    3.14159	=ROUNDUP(A1, 2)

    • Result in B1: 3.15

• Rounddown

  • Function: Rounds a number down to a specified number of decimal places.
  • Example:
    

    A	B
    3.14159	=ROUNDDOWN(A1, 2)

    • Result in B1: 3.14

• Insert Shapes

  • Function: Inserts customizable shapes into an Excel sheet.
  • Example: Add a rectangle shape and modify the border and fill color via the Format Shape options.

• Insert Image

  • Function: Adds an image from the computer to an Excel sheet.
  • Example: Insert a logo image into the sheet using the Insert tab.

• Insert Link

  • Function: Creates a hyperlink in a cell that can link to a website or document.
  • Example:
    

    A
    =HYPERLINK(“http://go1digital.com“, “Go1Digital”)

    • Clicking on this cell takes you to the Go1Digital website.

• Link Within Document

  • Function: Creates a hyperlink to another sheet within the same workbook.
  • Example:
    

    A
    =HYPERLINK(“#’Sheet2′!A1”, “Go to Sheet2”)

    • Clicking on this cell navigates to cell A1 of Sheet2.

• Edit or Remove Link

  • Function: Modify or remove an existing hyperlink.
  • Example: Right-click on a linked cell, choose “Edit Hyperlink” or “Remove Hyperlink” to modify or delete it, respectively.

• Create Dropdown List

  • Function: Creates a dropdown list for a cell from specified values.
  • Example:
    

    A	B	C
    Cat
    Dog
    Bird		=DATA VALIDATION, List, =$A$1:$A$3

    • Cell C1 will have a dropdown list containing “Cat”, “Dog”, “Bird”.

• Insert Comment

  • Function: Adds a comment to a cell for notes or reminders.
  • Example:
    • Right-click on cell A1 and select “Insert Comment”, type “This is a sample comment”.

Text to Columns

• Purpose: Splits the contents of one Excel column into multiple columns based on a delimiter or fixed width.
• Example:
  

  A	B
  John	Smith

  • Usage: If A1 contains “John Smith”, using Text to Columns on A1 with space as a delimiter will split “John” into A1 and “Smith” into B1.

2. Goal Seek

• Purpose: Adjusts a data value to achieve a specific goal.
• Example:
  

  B10	B20
  ?	5000

  • Usage: To determine what sales figure in cell B10 is needed to reach a total of $5000 in cell B20, you could set B20 as the target cell, 5000 as the value, and B10 as the changing cell in Goal Seek.

3. Scenario Manager

• Purpose: Allows you to create and save different input values that produce different results.
• Example:
  

  Scenario	Interest Rate
  Best Case	5%
  Worst Case	7%
  Expected Case	6%

  • Usage: Create scenarios such as “Best Case,” “Worst Case,” and “Expected Case” with different interest rates to see how each affects profitability.

4. Filter

• Purpose: Filters data in Excel to display only the records that meet specified criteria.
• Example:
  

  Sales
  $500
  $1500
  $950

  • Usage: Apply a filter to the sales column to show only values where sales are greater than $1,000.

5. CONCATENATE() / CONCAT()

• Purpose: Combines two or more text strings into one string.
• Example:
  

  A	B	Result
  Blue	Sky	Blue Sky

  • Usage: =CONCATENATE(A1, " ", B1) or =CONCAT(A1, " ", B1) combines “Blue” and “Sky” into “Blue Sky”.

6. COUNTIF()

• Purpose: Counts the number of cells that meet a criterion; for example, the number of entries greater than a certain value.
• Example:
  

  Values
  10
  20
  30

  • Usage: =COUNTIF(A1:A3, ">20") counts how many cells in range A1:A3 have values greater than 20.

7. Conditional Formatting

• Purpose: Changes the appearance of cells based on specific conditions.
• Example:
  

  Values
  $200
  $500
  $800

  • Usage: Apply a rule to the range to highlight cells in red where values are greater than $500.

8. TEXT()

• Purpose: Converts a value to text in a specified number format.
• Example:
  

  Value	Formatted Text
  1234.567	$1,234.57

  • Usage: =TEXT(A1, "$#,##0.00") converts 1234.567 to a text string formatted as currency, resulting in “$1,234.57”.