​

It's important to understand a few key concepts:

• Cells: The fundamental building blocks of a worksheet. Each cell is identified by a column letter and a row number (e.g., A1, B5, Z100).

• Values: The data you enter into cells. This can be numbers, text, dates, or other types of data.

• Formulas: Instructions that perform calculations or operations on values. Formulas always begin with an equals sign (=).

• Functions: Pre-built formulas that perform specific tasks (e.g., SUM, AVERAGE, IF).

• Operators: Symbols that specify the type of calculation to be performed (+, -, *, /, ^, etc.).

• Cell References: The address of a cell used in a formula (e.g., A1, B2:B10).

• ​

Basic Operators

• + (Addition): Adds two or more values. =5+3 or =A1+B1

• - (Subtraction): Subtracts one value from another. =10-4 or =C5-A2

• * (Multiplication): Multiplies two or more values. =6*7 or =D3*E3

• / (Division): Divides one value by another. =20/5 or =F4/G4

• ^ (Exponentiation): Raises a number to a power. =2^3 (2 to the power of 3, which equals 8) or =H1^2

 

Common Basic Functions

                                                         These functions are fundamental for working with data in Excel:

1. SUM:

   • Function: Adds up all the numbers in a range of cells.

   • Formula: =SUM(number1, [number2], ...) or =SUM(range)

   • Example:

     • =SUM(A1,A2,A3) Adds the values in cells A1, A2, and A3.

     • =SUM(B1:B10) Adds the values in cells B1 through B10.

   • Description: This is probably the most frequently used function. It makes it easy to calculate totals.

2. AVERAGE:

   • Function: Calculates the average (arithmetic mean) of numbers in a range of cells.

   • Formula: =AVERAGE(number1, [number2], ...) or =AVERAGE(range)

   • Example:

     • =AVERAGE(C1,C2,C3,C4) Averages the values in cells C1 to C4.

     • =AVERAGE(D1:D5) Averages the values in cells D1 through D5.

   • Description: Useful for finding the central tendency of a dataset.

3. COUNT:

   • Function: Counts the number of cells in a range that contain numbers.

   • Formula: =COUNT(value1, [value2], ...) or =COUNT(range)

   • Example:

     • =COUNT(E1,E2,E3,E4) Counts the number of cells with numbers from E1 to E4.

     • =COUNT(F1:F10) Counts the number of cells with numbers in cells F1 through F10.

   • Description: Helpful when you need to see how many numerical entries exist in a range.

4. COUNTA:

   • Function: Counts the number of cells in a range that are not empty (containing any data – numbers, text, etc.).

   • Formula: =COUNTA(value1, [value2], ...) or =COUNTA(range)

   • Example:

     • =COUNTA(G1,G2,G3,G4) Counts the number of non-empty cells from G1 to G4.

     • =COUNTA(H1:H20) Counts the number of non-empty cells in H1 through H20.

   • Description: More inclusive than COUNT, useful for finding the number of data entries regardless of type.

5. MAX:

   • Function: Returns the largest number in a range of cells.

   • Formula: =MAX(number1, [number2], ...) or =MAX(range)

   • Example:

     • =MAX(I1,I2,I3,I4) Finds the maximum value from I1 to I4.

     • =MAX(J1:J10) Finds the maximum value in the cells J1 through J10.

   • Description: Great for identifying the highest value in a dataset.

6. MIN:

   • Function: Returns the smallest number in a range of cells.

   • Formula: =MIN(number1, [number2], ...) or =MIN(range)

   • Example:

     • =MIN(K1,K2,K3,K4) Finds the minimum value from K1 to K4.

     • =MIN(L1:L10) Finds the minimum value in the cells L1 through L10.

   • Description: Great for identifying the lowest value in a dataset.

 

Basic Text Functions

• LEN:

  • Function: Returns the number of characters in a text string.

  • Formula: =LEN(text)

  • Example: =LEN("Hello") will return 5.

  • Description: Useful for checking the length of text entries.

• TRIM:

  • Function: Removes extra spaces from the beginning or end of a text string and reduces multiple spaces in between to single spaces.

  • Formula: =TRIM(text)

  • Example: =TRIM(" Hello World ") will return "Hello World".

  • Description: Helpful for cleaning up data imported from other sources.

• LEFT:

  • Function: Extracts a specified number of characters from the beginning (left) of a text string.

  • Formula: =LEFT(text, [num_chars])

  • Example: =LEFT("Excel", 2) will return "Ex".

  • Description: Useful for extracting prefixes from text strings.

• RIGHT:

  • Function: Extracts a specified number of characters from the end (right) of a text string.

  • Formula: =RIGHT(text, [num_chars])

  • Example: =RIGHT("Excel", 2) will return "el".

  • Description: Useful for extracting suffixes from text strings.

• CONCATENATE (or &):

  • Function: Joins two or more text strings into a single string.

  • Formula: =CONCATENATE(text1, [text2], ...) or text1 & text2 & ...

  • Example:

    • =CONCATENATE("Hello", " ", "World") will return "Hello World"

    • ="Hello"&" "&"World" will also return "Hello World"

  • Description: Useful for creating combined text entries.

 

Key Takeaways

• Formulas are the core of Excel calculations.

• Begin each formula with an equals sign (=).

• Understand the different operators (+, -, *, /, ^) and what they do.

• Familiarize yourself with basic functions like SUM, AVERAGE, COUNT, MAX, MIN.

• Use cell references (e.g., A1, B2:B10) to make formulas dynamic.

• Text functions can be helpful to manage, manipulate, and extract data from your data sets.

​