INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

UNIT III

Electronic Spread Sheet (Open Office Calc/MS-Excel) - Introduction to Spread Sheet, Creating & Editing
Worksheet, Formatting and Essential Operations, Formulas and Functions, Charts, Advanced features –
Pivot table & Pivot Chart, Linking and Consolidation.

Electronic Spread Sheet (Open Office Calc/MS-Excel)

analyzing data.

They provide a structured grid where you can neatly organize and work with your data.

They help you do math easily with formulas and functions and turn numbers into

understandable pictures with charts.

Learning how to use electronic spreadsheets is a valuable skill for everyone, as it helps in

making decisions based on data.

Introduction to Spreadsheets

A spreadsheet is a digital tool that simplifies the management and organization of data, making

it an integral part of various professional and personal tasks. Spreadsheets use a grid system

consisting of rows and columns, creating a structured space where information can be

efficiently entered and manipulated.

Key points about the introduction to spreadsheets:

1. Data Organization: Spreadsheets allow data to be neatly arranged, providing an easy-to-

follow structure. This structure makes it simpler to understand and work with data.

pg. 1
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

2. Versatility: Spreadsheets find applications in a wide range of fields, from finance and

accounting to project management and scientific research. They are essential tools for

businesses, students, and individuals alike.

3. Mathematical Functions: Spreadsheets offer built-in mathematical functions and

formulas to perform calculations and automate tasks. This capability is particularly

useful for managing budgets, creating charts, and conducting data analysis.

4. Data Analysis: Spreadsheets can be used to analyze data, identify trends, and draw

conclusions. They make it easier to spot patterns, irregularities, and insights within

datasets.

5. Ease of Use: Spreadsheets are designed with user-friendliness in mind. Most people can

quickly grasp the basics and start working with data without extensive training.

6. Data Visualization: They enable the creation of charts and graphs, making it simple to

transform raw data into visual representations that are easier to understand and

present to others.

Creating & Editing Document

Creating and editing Excel worksheets is a fundamental skill that can be applied in various

personal, academic, and professional contexts.

I. CREATING A NEW EXCEL WORKSHEET

Creating an Excel worksheet is a straightforward process. Here are the basic steps to create an
Excel worksheet:
pg. 2
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

Open Microsoft Excel: First, you need to open Microsoft Excel on your computer. You can
usually find it in your list of installed programs or applications.

Create a New Workbook:

When Excel opens, you'll see a blank workbook by default. This workbook consists of three
blank worksheets (Sheet1, Sheet2, and Sheet3).

You can also create a new workbook by going to "File" > "New" and selecting a blank workbook.

Navigate to a Worksheet:

In a workbook, you can switch between different worksheets by clicking on the sheet tabs at
the bottom. The default sheet is usually named "Sheet1."

Rename Worksheets (Optional):

If you want to give your worksheets more descriptive names, you can right-click on the sheet
tab and choose "Rename." Then, type the new name for the worksheet.

Begin Data Entry:

Click on a cell in the worksheet to start entering data. A cell is where you can type numbers,
text, or formulas. You can also use the formula bar at the top to edit the contents of a cell.

Navigate Between Cells:

You can move between cells using your keyboard arrow keys, the "Tab" key to move to the
right, or the "Enter" key to move down to the next cell. You can also click on any cell to select it.

Format Your Data (Optional):

To format your data, select the cell or range of cells you want to format. You can change the
format of cells for numbers, dates, or text. You can also adjust font size, style, and color to
make your data more visually appealing.

Save Your Workbook:

pg. 3
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

It's essential to save your workbook to retain your data. Go to "File" > "Save" or use the
shortcut "Ctrl + S" (or "Command + S" on Mac) to save your workbook. Choose a location on
your computer and provide a name for the file.

Save As (Optional):

If you want to save a copy of your workbook with a different name or in a different format, use
the "Save As" option. This allows you to create duplicates or save the file in a different file type
like .csv or .pdf.

Close Excel:

When you are done creating your worksheet and want to exit Excel, go to "File" > "Close" or
simply close the Excel application. Make sure to save your work before closing if you've made
changes.

By following these steps, you can create a basic Excel worksheet to store, manage, and analyze
data for various purposes, whether it's for personal use, work, or school.

II. ENTERING AND FORMATTING DATA

1. Data Entry: To enter data into your Excel worksheet, simply click on the cell where you

want to input information and start typing. You can also use the formula bar at the top

to edit the contents of a cell.

2. Formatting: Excel provides a wide range of formatting options to make your worksheet

more visually appealing and easier to understand. You can format cells for number,

date, currency, and more. Additionally, you can change fonts, styles, and colors to

enhance the readability of your data.

pg. 4
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

3. Cell Styles: Excel includes predefined cell styles that you can apply to your data to

quickly format it, making it look professional and consistent.

Creating and editing Excel documents involves a series of steps that allow you to set up a new

worksheet, enter data, format it, and perform various operations such as calculations and data

manipulation. Below are the essential steps to create and edit Excel documents:

Creating a New Excel Document:

1. Open Microsoft Excel: Launch the Microsoft Excel application on your computer. You

can typically find it in the Microsoft Office suite.

2. Select a Workbook: Excel opens with a blank workbook by default. A workbook is the

primary file in which you create and organize your worksheets.

3. Add Worksheets (Optional): By default, a new workbook includes three blank

worksheets named "Sheet1," "Sheet2," and "Sheet3." You can add or delete worksheets

as needed. To add a new worksheet, right-click on an existing sheet tab and choose

"Insert" or click the "+" button at the bottom of the worksheet tabs.

4. Rename Worksheets (Optional): You can rename worksheets to give them more

descriptive names by double-clicking on the sheet tab and typing the new name.

pg. 5
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

Entering and Editing Data:

1. Select a Cell: Click on the cell where you want to enter data. You can use the keyboard

or mouse to navigate to the desired cell.

2. Data Entry: Start typing to enter your data. You can also edit the contents of a cell by

clicking in the formula bar at the top of the Excel window.

3. Navigate Between Cells: Use the arrow keys, the "Tab" key to move to the right, or the

"Enter" key to move down to the next cell. You can also click on any cell to select it.

Formatting Data:

1. Format Cells: To format cells, select the cell(s) or range of cells you want to format.

Right-click and choose "Format Cells" or use the "Format Cells" option from the Home

tab.

2. Number Formats: You can choose from various number formats (e.g., currency, date,

percentage) to format the data in your cells.

3. Font and Style: You can change the font, size, style, and color of your text using options

in the Home tab.

4. Cell Styles: Excel provides predefined cell styles that you can apply to format your data

consistently.

EDITING AND FORMATTING IN EXCEL:

Editing and formatting in Excel are essential tasks for refining your data and making it

more presentable. Here are the steps to edit and format data in Excel:
pg. 6
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

EDITING IN EXCEL:

1. Open Your Excel Workbook: Start by opening the Excel workbook that contains the data

you want to edit.

2. Select the Cell/Range: Click on the cell containing the data you want to edit. To select a

range of cells, click and drag your cursor over the cells or use the keyboard arrow keys

to navigate to the desired cell.

3. Edit the Data:

• To edit text or numbers, click on the cell and start typing. You can also edit the

data in the formula bar at the top of the Excel window.

• To edit a formula, select the cell containing the formula, and you can change the

formula in the formula bar.

4. Undo and Redo (Optional): If you make a mistake while editing, you can use the "Undo"

(Ctrl+Z) and "Redo" (Ctrl+Y) shortcuts to revert or reapply your changes.

FORMATTING IN EXCEL:

1. Select the Cell/Range: Begin by selecting the cell or range of cells that you want to

format.

2. Font and Alignment:

• To change the font, size, style, and color of the text, use the options in the "Font"

group on the "Home" tab.

pg. 7
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

• To change the alignment (left, center, right, or justify), use the "Alignment"

group on the "Home" tab.

3. Number Formatting:

• To format numbers, such as currency, percentages, or date formats, select the

cell or range, right-click, and choose "Format Cells" from the context menu. In

the "Format Cells" dialog box, go to the "Number" tab and choose the desired

format.

4. Cell Styles (Optional):

• Excel provides predefined cell styles for a consistent look. You can apply these

styles from the "Cell Styles" group on the "Home" tab.

5. Borders and Shading:

• To add borders around cells or apply shading (fill color), use the "Borders" and

"Fill Color" options in the "Font" group on the "Home" tab.

6. Conditional Formatting (Optional):

• If you want to apply formatting based on specific conditions or rules, you can use

conditional formatting. This feature is found in the "Styles" group on the "Home"

tab.

7. Clear Formatting (Optional):

• If you want to remove formatting from a cell or range, select the cell or range,

right-click, and choose "Clear Formats."

pg. 8
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

8. Save Your Work:

• After editing and formatting your data, be sure to save your workbook to keep

your changes.

9. Close Excel (Optional):

• When you're done with your editing and formatting tasks, close Excel. Make sure

to save your work before closing if you've made changes.

By following these steps, you can efficiently edit and format your data in Excel, making it

more visually appealing and easier to understand, whether you're working on

spreadsheets for personal, academic, or professional use.

BASIC OPERATIONS IN EXCEL:

1. Entering Data: To enter data, click on a cell and start typing. Press Enter to move to the

next cell or use the arrow keys.

2. Basic Calculations: You can perform calculations using operators such as + (addition), -

(subtraction), * (multiplication), and / (division). For example, typing "=A1+B1" in a cell

will add the values in cells A1 and B1.

3. Functions: Excel has a wide range of built-in functions for various purposes, such as

SUM, AVERAGE, MAX, MIN, and more. To use a function, start with an equal sign,

followed by the function name and arguments, enclosed in parentheses. For example,

"=SUM(A1:A5)" calculates the sum of values in cells A1 to A5.

pg. 9
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

4. AutoFill: Excel can automatically fill a series or pattern of data. For instance, if you enter

a date (e.g., 01/01/2023) and drag the fill handle (a small square at the bottom-right

corner of the selected cell), Excel can autofill the following dates in a sequence.

5. Sorting and Filtering: You can sort data in ascending or descending order and apply

filters to display specific subsets of data.

• Select a Range: Click on a cell within the range of data you want to sort.

• Go to the "Data" Tab: Navigate to the "Data" tab in the Excel ribbon.

• Click "Sort A to Z" or "Sort Z to A": To sort data in ascending order, click

"Sort A to Z." For descending order, click "Sort Z to A."

• Confirm the Sorting: Excel will ask you to confirm the range you want to

sort. Click "OK" to apply the sorting.

6. Copying and Pasting: Cutting, copying, and pasting data in Excel are fundamental

operations that allow you to move and duplicate data within your worksheets. Here are

the steps for cutting, copying, and pasting in Excel:

Cutting Data:

Right-click on the selected cells and choose "Cut" from the context menu.

Use the keyboard shortcut "Ctrl + X" (or "Command + X" on Mac).

Copying Data:

Right-click on the selected cells and choose "Copy" from the context menu.

pg. 10
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

Use the keyboard shortcut "Ctrl + C" (or "Command + C" on Mac).

Pasting Data:

To paste the copied data, Right-click on the destination cell and choose "Paste" from the

context menu.

Use the keyboard shortcut "Ctrl + V" .

7. Charts and Graphs: Excel allows you to create various types of charts and graphs to

visualize data.

( To create a chart in Excel, select the data you want to visualize, then go to the "Insert"

tab, click on the chart type you prefer (e.g., column, line, pie), and Excel will generate a

chart based on your selected data.)

8. Data Validation: You can set rules to control what can be entered in a cell, which helps

maintain data accuracy.

9. Page Layout and Printing: Excel offers options for adjusting page orientation, margins,

headers, footers, and print settings for professional reports and documents.

Page orientation: Select the "Page Layout" tab, change the page orientation to portrait

or landscape in the "Orientation" group.

Margins: To adjust margins in Excel, use the "Margins" option within the "Page Layout"

tab to set specific margin sizes for your printed worksheet.

pg. 11
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

Header and Footer: To add headers and footers in Excel, go to the "Insert" tab, then click

"Header & Footer" to enter text or insert elements like page numbers and dates at the

top (header) or bottom (footer) of each printed page.

Print: To print in Excel, click on the "File" tab, select "Print," configure your print settings,

and click "Print" to print the active worksheet or workbook.

FORMULAS AND FUNCTIONS IN EXCEL:

Formulas and functions are essential components of Microsoft Excel that allow you to

perform calculations, analyze data, and automate tasks. They help you manipulate data

and make your spreadsheets more powerful and dynamic. Here's an explanation of

formulas and functions in Excel:

Formulas:

• What are Formulas?

A formula in Excel is a combination of numbers, operators, cell references, and functions

that perform a specific calculation or operation. Formulas always start with an equal

sign (=).

• Operators: You can use mathematical operators such as + (addition), - (subtraction), *

(multiplication), / (division), and ^ (exponentiation) in formulas to perform calculations.

For example, "=A1+B1" calculates the sum of values in cells A1 and B1.

pg. 12
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

• Cell References: Instead of entering constant values in formulas, you can reference cell

values. For instance, "=A1*B1" multiplies the values in cells A1 and B1. If you change the

values in those cells, the formula automatically updates the result.

• Arithmetic Functions: Excel provides basic arithmetic functions like SUM, AVERAGE,

MAX, MIN, and COUNT, which you can use to perform common calculations on a range

of cells. For example, "=SUM(A1:A5)" calculates the sum of values in cells A1 to A5.

• Parentheses: You can use parentheses to control the order of operations in complex

formulas. For instance, "=(A1+B1)*C1" ensures that the addition operation is performed

before the multiplication.

Functions:

• What are Functions?

Functions are pre-built formulas that simplify specific tasks or calculations. Excel offers a

vast library of functions, categorized into different groups like mathematical, statistical,

date and time, text, and more.

• Syntax: Functions have a predefined syntax, which includes the function name,

arguments, and, in some cases, additional parameters. Functions are written with the

function name followed by open and close parentheses, and any required arguments or

parameters are placed within the parentheses.

• Examples: Some common Excel functions include:

1. SUM: Calculates the sum of a range of numbers, e.g., "=SUM(A1:A5)".

pg. 13
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

2. AVERAGE: Calculates the average of a range of numbers, e.g.,

"=AVERAGE(A1:A5)".

3. IF: Performs conditional logic, e.g., "=IF(A1>10, "Yes", "No")" returns "Yes" if A1

is greater than 10, otherwise "No."

• AutoComplete and Help: Excel provides auto-complete suggestions and built-in help for

functions. When you start typing a function name, Excel will suggest matching functions,

and you can press the Tab key to complete it. You can also access the Excel Help feature

to learn more about a function's usage.

Math and Trigonometry Functions:

1. SUM: Adds up a range of numbers. Example: =SUM(A1:A5).

2. AVERAGE: Calculates the average of a range of numbers. Example: =AVERAGE(A1:A5).

3. MAX: Returns the highest value in a range. Example: =MAX(A1:A5).

4. MIN: Returns the lowest value in a range. Example: =MIN(A1:A5).

5. ROUND: Rounds a number to a specified number of decimal places. Example:

=ROUND(A1, 2) rounds the value in cell A1 to two decimal places.

6. SQRT: Calculates the square root of a number. Example: =SQRT(A1).

7. RAND: Generates a random number between 0 and 1. Example: =RAND().

Text Functions:

8. CONCATENATE: Combines two or more text strings into one. Example:

=CONCATENATE("Hello", " ", "World") creates "Hello World".

9. LEFT: Returns a specified number of characters from the beginning of a text string.

Example: =LEFT(A1, 5) extracts the first 5 characters from cell A1.

10. RIGHT: Returns a specified number of characters from the end of a text string. Example:

=RIGHT(A1, 3) extracts the last 3 characters from cell A1.

11. LEN: Calculates the length (number of characters) in a text string. Example: =LEN(A1).

12. UPPER: Converts text to uppercase. Example: =UPPER(A1).

Logical Functions:

13. IF: Performs conditional logic. Example: =IF(A1 > 10, "Yes", "No") returns "Yes" if the

value in cell A1 is greater than 10; otherwise, it returns "No".

14. AND: Returns TRUE if all conditions are TRUE. Example: =AND(A1 > 10, B1 < 20) returns

TRUE if both conditions are met.

15. OR: Returns TRUE if at least one condition is TRUE. Example: =OR(A1 > 10, B1 < 20)

returns TRUE if either condition is met.

Date and Time Functions:

16. TODAY: Returns the current date. Example: =TODAY().

17. NOW: Returns the current date and time. Example: =NOW().

18. DATE: Creates a date from year, month, and day values. Example: =DATE(2023, 11, 6)

for November 6, 2023.

19. TIME: Creates a time from hour, minute, and second values. Example: =TIME(14, 30, 0)

for 2:30 PM.

CHARTS IN EXCEL:

Charts in Excel are graphical representations of data that help users visualize, analyze,

and understand numerical information more effectively. Excel provides a wide range of

chart types .

Creating a Chart:

1. Select Data: To create a chart, first, select the data you want to include in the chart. This

data can be in the form of a table with headers for clarity. You can select a range of cells

or an existing table in your spreadsheet.

2. Insert a Chart: After selecting the data, go to the "Insert" tab in the Excel ribbon. Here,

you'll find various chart types such as bar charts, line charts, pie charts, and more.

Choose the chart type that best suits your data.

3. Design Your Chart: Excel will create a default chart based on your data. You can resize

and move the chart within your worksheet. Excel also provides chart styles and

templates to customize the look and feel of your chart.

4. Chart Elements: Excel allows you to add and remove various chart elements, such as

titles, labels, data markers, and legends. You can also format elements to suit your

preferences.

Different types of charts

different types of charts in Excel:

1. Column Chart:

• Vertical bars represent data categories or values.

• Great for comparing data across different categories.

2. Bar Chart:

• Similar to column charts, but bars are horizontal.

• Useful for comparing data in a horizontal layout.

3. Line Chart:

• Connects data points with lines to show trends over time.

• Ideal for displaying continuous data, such as stock prices or

temperature fluctuations.

4. Pie Chart:

• Divides a circle into slices to represent parts of a whole.

• Useful for showing the composition of a total, like a budget

breakdown.

5. Doughnut Chart:

• Similar to a pie chart but with a hole in the center.

pg. 17
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

• Useful for comparing multiple data series within a total.

PIVOT TABLE

A pivot table is a powerful feature in Microsoft Excel that allows you to summarize and analyze

large datasets quickly and efficiently. It helps you extract meaningful insights from your data by

providing a structured and interactive way to view, filter, and analyze information.

Steps to Create a Pivot Table:

1. Data Preparation: Start with a well-structured dataset that includes headers for

columns and relevant data. Clean and organize your data to ensure it's ready for

analysis.

2. Select Your Data: Highlight the data range you want to analyze, including the headers.

This range will serve as the source data for your pivot table.

3. Create the Pivot Table: Go to the "Insert" tab, and click on "PivotTable." Excel will open

a dialog box where you can specify the data source range. Click "OK" to create a new

worksheet for your pivot table.

4. Choose Pivot Table Fields: You will see a PivotTable Fields pane on the right. Drag and

drop fields from your data into the Rows, Columns, Values, and Filters areas to define

the structure of your pivot table.

5. Customize the Pivot Table: You can customize your pivot table by adding subtotals,

grand totals, changing the layout, and applying styles and themes.

pg. 18
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

6. Analyze Data: Interact with the pivot table by expanding/collapsing rows and columns,

filtering data, and drilling down to see more details.

Advantages of Pivot Tables:

1. Data Summarization: Pivot tables efficiently summarize large datasets

2. Dynamic Updates: When the source data changes, the pivot table can be refreshed to

reflect the changes, making it easy to keep your analysis up to date.

3. Aggregation and Calculations: You can easily perform calculations within the pivot

table, such as sums, averages, counts, and more.

4. No Formulas Required: You can analyze data without complex formulas or coding,

making it accessible to users with varying levels of Excel expertise.

PIVOT CHART:

A pivot chart is a graphical representation of data from a pivot table in Excel. It allows you to

visualize and analyze data from various perspectives, making it easier to identify trends,

patterns, and insights. Pivot charts are dynamic and linked to the underlying pivot table, so any

changes you make in the pivot table will be reflected in the pivot chart.

Steps to Create a Pivot Chart:

1. Prepare Your Data: Ensure your data is organized in columns with clear headers. It's

best to have numerical or categorical data that you want to analyze.

pg. 19
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

2. Create a Pivot Table: Select your data, then go to the "Insert" tab and click on

"PivotTable." Choose the data range and location for your pivot table.

3. Add Fields: In the pivot table, add the fields you want to analyze to the rows, columns,

and values areas. This will create a summarized table of your data.

4. Create a Pivot Chart: With the pivot table selected, go to the "Insert" tab and choose

the type of chart you want to create.

5. Configure the Chart: Excel will generate the pivot chart, and you can further customize

it by adding titles, labels, and changing the chart type or style.

Advantages of Pivot Charts:

1. Visual Representation: Pivot charts provide a visual representation of data, making it

easier to identify patterns and trends in large datasets.

2. Dynamic Updates: Pivot charts are linked to the underlying pivot table. When you

update or filter the pivot table, the chart automatically reflects these changes, saving

time and effort.

LINKING AND CONSOLIDATION IN EXCEL

Linking and consolidation are techniques used in Excel to combine data from multiple

worksheets or workbooks. These processes are commonly used in business and finance

for reporting and analysis. The purpose of linking is to establish a connection between

data in different sheets or workbooks, while consolidation is used to summarize and

analyze data from multiple sources in one place.

pg. 20
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

Linking:

Linking in Excel is used to connect data from one worksheet to another or from one

workbook to another. It helps maintain data consistency and accuracy across multiple

sheets, allowing changes in one location to automatically update linked data in other

locations.

Steps:

1. Open the destination worksheet where you want to display the linked data.

2. Click on the cell where you want the linked data to appear.

3. Type an equal sign (=) in the cell.

4. Navigate to the source worksheet or workbook.

5. Click on the cell you want to link to in the source worksheet.

6. Press Enter. The linked data will now be displayed in the destination cell.

Consolidation:

Consolidation in Excel is used to combine data from multiple worksheets or workbooks

into one summary worksheet or workbook. It's helpful for creating reports or

performing analysis when you have data scattered across different sources.

Steps:

1. Open the destination worksheet or workbook where you want to consolidate the data.

2. Select the cell where you want the consolidated data to start.

pg. 21
 INTRODUCTION TO COMPUTERS AND OFFICE AUTOMATION MODULE III

3. Go to the "Data" tab in Excel.

4. Click on "Consolidate" in the "Data Tools" group.

5. In the "Consolidate" dialog box, select the function you want to use (e.g., Sum, Average,

Count).

6. Click the "Add" button and navigate to the source worksheets or workbooks.

7. Select the range of data in each source.

8. Click "OK" in the "Consolidate" dialog box.

pg. 22