3.10 Spread sheet

 Spreadsheets: -

• A spreadsheet is an application program which is used to organize, calculate, and analyze data in rows and columns.

• Each intersection of a row and column is called a cell, which can hold text, numbers, or formulas.

• Spreadsheets are commonly used for mathematical calculations, budgeting, data analysis, charts, and financial reports. They allow users to perform automatic calculations using formulas and functions, which save time and reduce errors.

• Popular spreadsheet programs include Microsoft Excel, Google Sheets, and LibreOffice Calc. Spreadsheets also support features like sorting, filtering, chart creation, and data visualization, making them powerful tools for both personal and professional data management.

• The components make spreadsheets powerful tools for data entry, calculation, and analysis.

• The main components of a spreadsheet are:

1. Cell: – The smallest unit in a spreadsheet where data (text, numbers, or formulas) is entered.
2. Row: – A horizontal line of cells, identified by numbers (1, 2, 3, …).
3. Column: – A vertical line of cells, identified by letters (A, B, C, …).
4. Cell Address / Reference: – The unique location of a cell, formed by combining its column letter and row number (e.g., A1, B5).
5. Worksheet: – A single page or sheet within a spreadsheet file where data is entered and analyzed.
6. Workbook: – A complete spreadsheet file that can contain one or more worksheets.
7. Formula: – An equation used to perform calculations on data (e.g., =A1+B1).
8. Function: – A predefined formula for specific tasks (e.g., =SUM(A1:A5) adds up values).
9. Range: – A group of selected cells (e.g., A1:A10).
10. Chart/Graph: – A visual representation of data for easier understanding.
11. Menu Bar / Ribbon: – Contains commands and tools for formatting, inserting charts, using formulas, etc.
12. Status Bar: – Displays information like average, sum, or count of selected cells.

Formulas: -

• A formula in a spreadsheet is an instruction that performs calculations or operations on the data in your cells.

• Formulas help you automatically compute values instead of calculating them manually.

 Key Points about Formulas:

1) Start with an equal sign (=):

• Every formula begins with = so the spreadsheet knows it’s a calculation.

• Example: =A1 + A2 adds the values in cells A1 and A2.

 2) Use cell references:

• Instead of typing numbers directly, you can use cell references to make formulas dynamic.

• Example: =B1 * C1 multiplies the values in B1 and C1.

 

3) Perform different operations:

•  Arithmetic: +, -, *, /
• Example: =A1 - A2
• Functions: Predefined formulas like SUM, AVERAGE, IF, VLOOKUP.

 4) Automatic updating:

• If the value in a referenced cell change, the formula updates automatically.

• Example: If A1 = 10 and A2 = 20, then =A1 + A2 gives 30. If A1 changes to 15, the formula result becomes 35.

 

Some of the frequently used formulas in spread sheet are:

1. SUM
2. AVERAGE
3. IF
4. VLOOKUP

 

1) SUM Formula: -

• Adds up a range of numbers in a spreadsheet

            Syntax: -

                                = SUM(number1, number2, ...)

                                = SUM(C2:E2)  // adds values from cell C2 to E2

            Example: -

2) AVERAGE Formula: -

• Calculates the mean (average) of a range of numbers.

            Syntax: -

                                = AVERAGE(number1, number2, ...)

                                = AVERAGE(C2:E2)  // calculates the average of values from C2 to E2

            Example: -

3) IF Formula: -

• Performs a logical test and returns one value if TRUE and another if FALSE.

            Syntax: -

                                =IF (condition, value_if_true, value_if_false)

            Example: -

4) VLOOKUP Formula: -

• Searches for a value in the first column of a range and returns a value in the same row from another column.

            Syntax: -

                        = VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

            Example: -

            Explanation:

                                        102 → value we want to find.

                                        A2 : A4 → table range.

                                        2 → column number from which to return the value (Name column).

                                        FALSE → exact match.

 

Data charts: -

• To create a data chart in Excel, first select your data, then go to the Insert tab and choose a chart type from the Charts group or click Recommended Charts for suggestions.

• After selecting the chart, you can customize its appearance, add elements like titles and legends, and change its data by using the options available on the Chart Design and Format tabs.

 

Step by step Guide to Creating an Excel Chart:

1. Select Your Data: -

• Open your Excel workbook and select the range of cells containing the data you want to visualize, including any relevant column headers.

 

2. Insert the Chart: -

• Go to the Insert tab on the Excel ribbon.
• In the Charts group, you can either:

1. Click Recommended Charts to let Excel suggest chart types based on your data.
2. Click a specific chart icon (like Column or Bar Chart, Line or Area Chart, etc.) and select the chart style you want from the options that appear

 

3. Choose a Chart Type: -

• Excel will display previews of how your data would look in different chart types.
• Select the chart that best represents your data and click OK or the desired option to insert it.

 

4. Customize Your Chart: -

• With the chart selected, the Chart Design and Format tabs will appear on the ribbon.
• Use these tabs to:

1. Add Chart Elements: Include or modify titles, axis labels, and legends.
2. Change Layout and Style: Apply different chart styles and quick layouts from the Chart Design tab.
3. Switch Data: Change how the data is displayed by switching rows and columns.
4. Format Components: Right click on specific parts of the chart (like an axis or a data series) and select Format to adjust colors, borders, and other visual aspects.

Validation: -

• Excel data validation is a tool to control what data can be entered into a cell, ensuring data consistency and accuracy by setting specific rules for data types (like numbers or dates), ranges, lengths, or lists.

• To add validation, select the cells, go to the Data tab, click Data Validation, and then define the criteria in the Settings tab.

• We can also customize prompts with an Input Message and error messages with the Error Alert tab.

 Steps to Add Data Validation: -

1) Select Cells: -

• Highlight the cell(s) where you want to apply data validation.

 

2) Open Data Validation: -

• Go to the Data tab in the Excel ribbon, then click on the Data Validation button in the Data Tools group.

 

3) Set Criteria (Settings Tab): -

• In the dialog box, select the Settings tab.

• Use the Allow dropdown menu to choose the type of data you want to permit. Options include:

1. Whole number or Decimal: To restrict entries to specific numerical values or a range.
2. List: To create a dropdown list of allowed options.
3. Date or Time: To ensure entries fall within a specific timeframe.
4. Text length: To limit the number of characters allowed.
5. For more complex rules, select Custom and enter a specific formula.

 

4) Add Input Message (Optional): -

• Go to the Input Message tab to create a message that appears when a user selects the cell.

• This helps guide users on the type of data to enter.

5) Add Error Alert (Optional): -

• Navigate to the Error Alert tab.
• Customize error messages and choose an alert style to prevent or stop invalid data entries.

 

6) Confirm: Click OK to apply the validation rules.

 

Benefits of Data Validation: -

• Ensures Data Integrity: Prevents incorrect data from being entered into your worksheet.

• Improves Consistency: Helps maintain uniformity across your data tables.

• Reduces Errors: Minimizes mistakes in calculations, reports, and other processes.

• Guides Users: Provides clear instructions and feedback through input and error messages.

Pivot tables: -

• Excel pivot tables are dynamic tools for summarizing and analyzing large datasets by allowing you to group, filter, and reorganize data to reveal insights and answer specific questions.

• To create one, select your data, go to the Insert tab, click PivotTable, choose a destination, and then use the PivotTable Fields pane to drag your data fields into the Rows, Columns, Filters, and Values areas to build your interactive report.

 Creating a Pivot Table: -

1. Select your data: Click on any cell within the table or data range you want to analyze.
2. Insert the PivotTable: Go to the Insert tab and click PivotTable.
3. Choose a destination: Select whether to place the PivotTable on a new worksheet (recommended) or an existing one, then click OK.
4. Build the report: In the PivotTable Fields pane on the right, drag and drop your data fields into the appropriate areas (Rows, Columns, Filters, Values) to build your desired report.