Class Notes – 04/10/24

1. Understanding Electronic Worksheet or Spreadsheet

A spreadsheet is a digital tool used for organizing, calculating, and analyzing data. The
most common spreadsheets are Microsoft Excel, Google Sheets, and LibreOffice
Calc.

Key Features:

• Data Entry: Spreadsheets allow users to enter text, numbers, dates, and
formulas into cells.

• Automatic Calculations: By using formulas, you can automatically calculate

results. For example, you can sum a range of numbers, calculate averages, or
even apply complex financial models.

• Data Analysis: Built-in tools for sorting, filtering, and analyzing large datasets
are useful for professionals in fields like finance, business, and science.

Use Cases:

• Budgeting: Keep track of income and expenses.

• Data Analysis: Analyze and visualize trends using graphs and charts.

• Project Management: Create timelines, task lists, and Gantt charts.

2. Components of Worksheet Screen and Organization of Worksheet Area

Main Components of a Worksheet:

1. Cells:

o The most basic unit in a spreadsheet.

o Every cell has a unique reference based on its row and column (e.g., A1,
B2).

o Cells can contain different types of data such as text, numbers, dates,
and formulas.

2. Rows and Columns:

o Rows: Horizontal lines of cells, identified by numbers (1, 2, 3...).

o Columns: Vertical lines of cells, identified by letters (A, B, C...).

 3. Formula Bar:

o Displays the content of the active cell, and it’s where you can enter or edit
text and formulas.

4. Worksheet Tabs:

o Located at the bottom of the screen, these tabs allow users to switch
between different worksheets within a workbook.

5. Ribbon/Toolbar:

o The Ribbon in Excel and similar tools offers commands for formatting,
inserting charts, using functions, and customizing data views.

6. Gridlines:

o The lines that define the cells in a worksheet. These are usually visible to
help with organizing data but don’t appear when printing.

3. Opening a Worksheet

1. Starting a New Worksheet:

o Open your spreadsheet software and choose File > New.

o This opens a blank worksheet where you can start entering data.

2. Opening an Existing Worksheet:

o Go to File > Open, and browse to locate the spreadsheet file you want to
open.

4. Entering Numbers, Text, etc.

• Entering Data:

o Click on any cell and type your data. Cells can contain text, numbers, or
formulas.

• Text:

o Used for labels, headings, or descriptive information (e.g., "Total Sales").

• Numbers:

o Any numeric value can be entered (e.g., 1234, 45.67).

• Dates and Times:

 o Entered in a specific format (e.g., "12/31/2023" or "10:30 AM").

• Formulas:

o Begin with an = sign and perform calculations based on cell data.

5. Navigating the Worksheet

• Using the Keyboard:

o Arrow Keys: Move one cell at a time in any direction.

o Tab: Move one cell to the right.

o Enter: Move one cell down.

o Ctrl + Arrow Keys: Jump to the end of a continuous data range in any
direction.

• Using the Mouse:

o Click on any cell to make it active, then type to enter data.

• Using the Name Box:

o In Excel, you can type a specific cell reference in the Name Box (next to
the formula bar) to quickly jump to that cell.

6. Editing a Worksheet

Editing Cells:

1. Direct Edit:

o Double-click on a cell to edit its contents directly within the cell.

2. Formula Bar Edit:

o Click on the cell, then make edits in the formula bar.

3. Undo and Redo:

o Use Ctrl + Z to undo and Ctrl + Y to redo your changes.

Deleting Content:

• Delete Key: Pressing the Delete key removes the content of the selected cell,
but keeps the formatting.

Inserting New Data:

 • Click on any empty cell and enter new data or modify existing entries.

7. Adding and Deleting Cells, Rows, and Columns

Adding Rows/Columns:

1. Rows: Right-click on the row number where you want to add a row and select
Insert.

2. Columns: Right-click on the column letter where you want to add a column and
choose Insert.

Deleting Rows/Columns:

• Right-click the row number or column letter, and choose Delete.

Adding Cells:

• Right-click on any cell and select Insert. You can shift other cells left or down
depending on where the new cell is inserted.

8. Setting Column Width

• Automatic Width Adjustment:

o Double-click the boundary between two column letters, and the width
will auto-adjust to fit the longest content.

• Manual Width Setting:

o Right-click on the column header, choose Column Width, and enter a

specific number.

9. Selecting Ranges

• Selecting a Range:

o Click on the first cell of the range, hold the mouse button, and drag across
the cells you want to include.

• Selecting Multiple, Non-Adjacent Ranges:

o Hold Ctrl while clicking on different ranges.

10. Copying and Moving Data

Copying Data:

• Ctrl + C: Copies the selected cells.

• Ctrl + V: Pastes the copied cells into a new location.

Moving Data:

• Ctrl + X: Cuts the selected data.

• Ctrl + V: Pastes it into a new location.

11. Using Formulas

Formulas in spreadsheets allow you to perform calculations using values stored in

cells.

Basic Formula Example:

• Addition:

Copy code

=A1 + B1

Adds the values in cells A1 and B1.

Common Operators:

• + (Addition), - (Subtraction), * (Multiplication), / (Division).

Using Cell References:

• You can reference other cells in your formulas so that when the cell values
change, the formula recalculates automatically.

12. Naming Cells and Ranges

Naming a cell or a range of cells allows for easier reference in formulas.

Example:

• Instead of writing =SUM(A1:A5), you could name the range "SalesData" and use
=SUM(SalesData) in your formula.

• How to Name: Select the range, click on the Name Box (next to the formula bar),
and enter a name.
13. Using Functions
Functions are pre-built formulas in spreadsheets. Common examples include:

• SUM:

=SUM(A1:A10)

Adds all values between A1 and A10.

• AVERAGE:

=AVERAGE(B1:B10)

Calculates the average of the values between B1 and B10.

• IF:

arduino

=IF(A1 > 50, "Pass", "Fail")

Returns "Pass" if the value in A1 is greater than 50; otherwise, it returns "Fail".

1. SUM Function

• Purpose: Adds a range of numbers.

• Real-Life Use: Calculating total expenses, income, or any data accumulation.

Formula:

=SUM(A1:A10)

Adds all the values in cells from A1 to A10.

2. AVERAGE Function

• Purpose: Calculates the mean of a range of numbers.

• Real-Life Use: Finding average grades, average monthly expenditure, or average

scores.

Formula:

=AVERAGE(B1:B10)

3. IF Function
 • Purpose: Performs logical tests and returns different values based on the
condition.

• Real-Life Use: Making decisions like checking if you’re within a budget, whether
a task is complete or not, or eligibility criteria.

Formula:

=IF(A1 > 1000, "Above Budget", "Within Budget")

Returns "Above Budget" if the value in A1 is greater than 1000; otherwise, returns
"Within Budget".

4. COUNT Function

• Purpose: Counts the number of numerical values in a range.

• Real-Life Use: Counting how many products are sold, number of days with
expenses, or the number of tests passed.

Formula:

=COUNT(A1:A10)

5. COUNTIF Function

• Purpose: Counts the number of cells that meet a specific condition.

• Real-Life Use: Tracking how many times a specific event happens (e.g., how
many days the sales were above a certain threshold).

Formula:

=COUNTIF(B1:B10, ">100")

6. MAX and MIN Functions

• Purpose: Find the highest (MAX) or lowest (MIN) number in a range.

• Real-Life Use: Determining the highest or lowest expense in a month, the

highest score in a test, etc.

Formula (MAX):

=MAX(A1:A10)

Formula (MIN):
=MIN(B1:B10)

7. VLOOKUP or XLOOKUP (in Excel 365)

• Purpose: Searches for a value in the leftmost column and returns a value in the
same row from another column.

• Real-Life Use: Searching for a product price, looking up employee information,

retrieving data from a list.

Formula:

=VLOOKUP("Product A", A1:C10, 2, FALSE)

8. CONCATENATE (or CONCAT) Function

• Purpose: Joins two or more text strings into one.

• Real-Life Use: Combining first and last names, creating addresses, or building
text-based information.

Formula:

=CONCATENATE(A1, " ", B1)

9. TODAY and NOW Functions

• Purpose: Returns the current date (TODAY) or the current date and time (NOW).

• Real-Life Use: Date-stamping documents, keeping track of deadlines, setting

reminders.

Formula (TODAY):

=TODAY()

Formula (NOW):

=NOW()

10. PMT Function (Loan Payment Calculation)

• Purpose: Calculates the payment for a loan based on constant payments and a
constant interest rate.
 • Real-Life Use: Calculating monthly loan payments for a car, house, or other
loan.

Formula:

=PMT(interest_rate/12, number_of_months, loan_amount)

Example:

=PMT(5%/12, 60, 20000)

This calculates the monthly payment for a $20,000 loan with 5% annual interest over 60
months.

11. LEN Function

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

• Real-Life Use: Useful for data validation (e.g., checking if a code or ID has the
correct length).

Formula:

=LEN(A1)

12. ROUND Function

• Purpose: Rounds a number to a specified number of digits.

• Real-Life Use: Rounding off totals in invoices, rounding grades, or dealing with
decimals in financial reports.

Formula:

=ROUND(A1, 2)

Rounds the value in A1 to two decimal places.

13. TRIM Function

• Purpose: Removes any unnecessary spaces from text except for single spaces
between words.

• Real-Life Use: Cleaning up imported data or names to remove extra spaces.

Formula:

=TRIM(A1)

14. PROPER, UPPER, LOWER Functions

• Purpose: Change the case of text strings.

o PROPER: Capitalizes the first letter of each word.

o UPPER: Converts all text to uppercase.

o LOWER: Converts all text to lowercase.

• Real-Life Use: Standardizing the format of names, titles, addresses, etc.

Formula (PROPER):

=PROPER(A1)

Formula (UPPER):

=UPPER(A1)

Formula (LOWER):

=LOWER(A1)

15. TEXT Function

• Purpose: Format a number as text, using specific formatting codes.

• Real-Life Use: Displaying numbers as percentages, dates, or with specific

formatting.

Formula:

=TEXT(A1, "0.00%")

14. Inserting a Chart

Charts help in visualizing data.

Steps:

1. Select the range of data.

 2. Go to Insert > Chart.

3. Choose the type of chart (e.g., Line, Bar, Pie).

4. Customize the chart by adding titles, labels, and legends.

15. Editing and Formatting a Chart

Editing:

• You can adjust the chart's data range, titles, or axis labels by right-clicking the
chart and selecting Edit Data.

Formatting:

• To change the chart's colors, fonts, and styles, select the chart and use the
options under the Chart Tools tab.

16. Sorting and Filtering

Sorting:

• Organizes data in ascending or descending order.

o Go to Data > Sort and select the column by which you want to sort the
data.

Filtering:

• Filters allow you to display only the data that meets certain criteria.

o Select the range and click Data > Filter. Small dropdown arrows will
appear, allowing you to filter by specific values.

17. Using Macros

Macros automate repetitive tasks.

Steps to Record a Macro:

1. Go to View > Macros > Record Macro.

2. Perform the actions you want the macro to record.

3. Click Stop Recording when finished.

Running a Macro:
• Use Alt + F8 to view and run saved macros.