IT Fundamental

Unit - 3

[Link] Spreadsheet Using MS-Excel

Microsoft Excel is one of the most widely used spreadsheet programs across the world, offering
a powerful tool for organizing, analyzing, and visualizing data. In this section, we will explore the
fundamentals of using MS Excel, including creating and editing worksheets, applying essential
formatting and operations, utilizing formulas and functions, creating charts, and understanding
advanced features like PivotTables and PivotCharts. We will also look into how data can be
linked and consolidated from multiple sources.

[Link] to MS-Excel

Microsoft Excel is a spreadsheet application that allows users to organize data in rows and
columns. It enables users to perform calculations, create charts, and develop complex data
models. Excel is primarily used in business, finance, and data analysis environments for tasks
like budgeting, accounting, and statistical analysis.

• Key Components of Excel:

• Workbook: An Excel file is called a workbook, which can contain multiple

worksheets.

• Worksheet: A worksheet is a grid of cells where data is entered. It is made up of

rows (labeled with numbers) and columns (labeled with letters).

• Cells: Each cell is identified by a unique combination of a column letter and row
number (e.g., A1, B2, C3).

• Ribbon: The Ribbon at the top of the screen contains tabs with various
commands and tools that help you manage your workbook.

3. Creating & Editing Worksheets

Creating a New Worksheet:

• When you open Excel, a new workbook is usually created automatically. You can
also create a new workbook by going to File > New > Blank Workbook or by using a template.
 • A worksheet consists of a grid of rows and columns, and you can enter data in
any of the cells. Excel provides several predefined templates for finance, calendars, inventory
management, etc., which can help you quickly create specialized worksheets.

Editing a Worksheet:

• Entering Data: Click on a cell and start typing. You can enter numbers, text, or
formulas. You can move to adjacent cells using the arrow keys or by clicking on the desired cell.

• Copying and Pasting Data: Use standard Ctrl+C to copy and Ctrl+V to paste.
This can be done within the same worksheet or across different worksheets.

• Inserting and Deleting Rows/Columns: Right-click on the row or column header

and choose Insert to add a new row/column or Delete to remove them.

• Cell References: Cells in Excel can reference other cells for calculations. For
example, typing =A1+B1 in cell C1 will display the sum of the values in cells A1 and B1.

[Link] and Essential Operations

Formatting Cells:

• Formatting allows you to change how the data appears in your worksheet. You
can change fonts, sizes, colors, and borders to improve readability and make your data more
visually appealing.

• Text Formatting: You can bold, italicize, underline, change the font, and adjust the
text alignment. Excel allows for automatic text wrapping when the content exceeds the cell’s
width.

• Number Formatting: Excel offers a range of number formats like general,

currency, date, time, percentages, and custom formats to suit different types of data.

• Conditional Formatting: This powerful tool helps highlight data based on certain
conditions. For example, you can apply a color scale to represent values visually, such as using
a green-to-red scale to represent high-to-low values.

Essential Operations:

• Sorting: You can sort data alphabetically or numerically to make it easier to

analyze. Data can be sorted in ascending or descending order.

• Filtering: Filtering allows you to display only the rows that meet certain criteria,
such as all records greater than a specific number or all values from a particular category.

• Find and Replace: Use Ctrl+F to search for specific data in a worksheet, or
Ctrl+H to replace certain values with new ones.
[Link] and Functions

Formulas are mathematical expressions used to calculate values in Excel. Functions are
predefined formulas that simplify complex calculations. Both are fundamental to Excel’s power
in data analysis.

• Basic Formulas: All formulas in Excel start with an equal sign (=). For example:

• =A1 + B1 adds the values of cells A1 and B1.

• =A1 * B1 multiplies the values.

• =A1 / B1 divides one cell by another.

• =A1 - B1 subtracts one cell’s value from another.

• Common Functions:

• SUM(): Adds all the numbers in a specified range. Example: =SUM(A1:A5)

• AVERAGE(): Calculates the average of numbers in a range. Example:

=AVERAGE(A1:A5)

• COUNT(): Counts the number of cells containing numbers in a range. Example:

=COUNT(A1:A5)

• IF(): This function allows for conditional logic. Example: =IF(A1>10, "Yes", "No")
checks if the value in A1 is greater than 10 and returns “Yes” or “No” accordingly.

• VLOOKUP(): This function searches for a value in the first column of a range and
returns a value in the same row from another column. Example: =VLOOKUP(A1, B1:D10, 2,
FALSE).

6. Charts

Charts in Excel help visualize data, making it easier to identify trends and patterns.

• Creating Charts: To create a chart, select the data range you want to plot, then
click on the “Insert” tab and choose the chart type (e.g., Bar, Line, Pie, Column).

• Chart Types: Different types of charts represent data in different ways. Common
chart types include:

• Column and Bar Charts: Compare data across categories.

• Line Charts: Show trends over time.

• Pie Charts: Show proportions of a whole.

 • Scatter Plots: Show relationships between two variables.

• Customizing Charts: You can customize charts by adding titles, changing colors,
adjusting the axis, or adding data labels for clarity.

7. Advanced Features of MS-Excel

• PivotTables: PivotTables are an advanced tool for summarizing large data sets.
They allow you to group, filter, and aggregate data dynamically. You can pivot rows and columns
to view the data from different perspectives.

• For example, if you have sales data for different regions and months, a
PivotTable can summarize total sales by region and month.

• PivotCharts: PivotCharts are charts that are directly connected to a PivotTable,

making it easier to visualize the summarized data in a dynamic format.

• Data Linking and Consolidation:

• Linking Data: You can link data across different sheets or workbooks. If data in
one sheet changes, the linked cells will automatically update.

• Consolidation: Excel allows you to consolidate data from multiple worksheets into
one. This is particularly useful when combining financial statements or data from different
sources.

[Link] Using MS-PowerPoint

Microsoft PowerPoint is a presentation software used to create slide-based presentations. It

provides various tools for creating, editing, and enhancing slides with text, images, and
multimedia. In this section, we will explore how to create and manipulate presentations, use
organizational charts, incorporate Excel charts, and apply animations and sounds.

1. Presentations Overview

A PowerPoint presentation consists of slides, each serving as a visual aid to the speaker. The
software allows for adding text, images, charts, videos, and animations to enhance
presentations.

2. Creating, Manipulating, and Enhancing Slides

• Creating a New Presentation: You can start a new presentation by selecting File
> New > Blank Presentation. Choose a template for a structured design or create your own from
scratch.
 • Adding and Rearranging Slides: Insert new slides by going to Home > New Slide.
You can choose from various layouts, such as Title Slide, Content Slide, and Blank Slide.
Rearrange slides by dragging them in the slide sorter view.

• Enhancing Slides: Customize your slides by adding background colors,

gradients, or themes to make them visually appealing. PowerPoint also allows for adding
borders, logos, and other design elements to create a professional-looking presentation.

. Organizational Charts

• Creating Organizational Charts: Use PowerPoint’s SmartArt tool to create

organizational charts that visually represent hierarchical data. For example, you can create
charts for displaying team structures or company hierarchies.

[Link] Features of MS-Excel

MS-Excel offers a variety of advanced features that help users efficiently manage, analyze, and
present data. Among these advanced features, PivotTables and PivotCharts are powerful tools
for summarizing large datasets, and Linking and Consolidation are essential for working with
data from multiple sources. Below, we’ll explore each of these advanced features in greater
detail.

1. PivotTables and PivotCharts

PivotTables

What is a PivotTable?

A PivotTable is a tool in Excel that allows you to quickly summarize and analyze large amounts
of data. It enables you to group data, calculate summaries, and dynamically explore different
views of your data.

• Creating a PivotTable:

1. Select Data: First, select the range of data you want to analyze. This could be a
dataset containing sales data, financial reports, or any other structured data.

2. Insert PivotTable: Go to the “Insert” tab on the Ribbon, and select “PivotTable.”
Excel will automatically suggest the data range and location for the PivotTable. You can choose
to place it in a new worksheet or an existing one.

3. Arranging Fields: After creating the PivotTable, you will be prompted to drag
fields into the following areas:

• Rows: The data you want to organize into rows.

• Columns: The data you want to organize into columns.

 • Values: The data you want to summarize, such as sums, averages, counts, etc.

• Filters: Allows you to filter data based on certain criteria (e.g., filtering by a
specific region or time period).

• Benefits of PivotTables:

• Summarization: PivotTables automatically group and summarize data, making it

easier to spot trends or outliers.

• Dynamic Analysis: You can drag and drop fields to change the layout and see
different views of your data.

• Calculations: You can perform aggregations such as sum, average, count, or

custom calculations within the PivotTable.

PivotCharts

What is a PivotChart?

A PivotChart is a graphical representation of the data in a PivotTable. It allows you to visually

analyze and interpret the summarized data, providing insights that may not be immediately
obvious in tabular form.

• Creating a PivotChart:

1. Select the PivotTable you’ve created.

2. Go to the Insert tab and choose the type of chart you want to create (e.g.,
Column, Line, Pie, etc.).

3. The chart will automatically update as you modify the data in the PivotTable.
PivotCharts are interactive, allowing you to drill down into the data and adjust the view.

• Advantages of PivotCharts:

• They provide a more intuitive, visual way to explore data.

• PivotCharts are linked to the PivotTable, so any changes in the PivotTable (such
as filtering or reorganizing) will automatically update the chart.

10. Linking and Consolidation

Linking Data

What is Linking?
Linking data involves creating a connection between cells, ranges, or even entire workbooks so
that changes made in one location automatically reflect in the linked cells.

• How to Link Data:

1. In one cell, type = and then navigate to the cell in another worksheet or workbook
that you want to link to.

2. Press Enter and the cell will now display the value from the linked cell. If the
linked cell changes, the linked cell in the current worksheet will update automatically.

• Benefits of Linking:

• Real-Time Updates: Linked data stays synchronized, ensuring consistency

across multiple worksheets or workbooks.

• Centralized Data: It allows for a single source of truth, reducing the need to
manually update data in multiple places.

Data Consolidation

What is Data Consolidation?

Data consolidation is the process of combining data from multiple ranges or workbooks into one
central location. This is particularly useful when you need to aggregate data from different
departments or sources.

• How to Consolidate Data:

1. Go to the Data tab and click on the Consolidate button.

2. Choose the consolidation method (e.g., Sum, Average) and select the ranges of
data from the different worksheets or workbooks you want to consolidate.

3. Click OK to combine the data into a single summary.

• Benefits of Consolidation:

• Efficient Aggregation: Quickly summarize data from different sheets into a single
report.

• Cross-Workbook Consolidation: Excel allows you to consolidate data from

multiple workbooks, which is ideal for large, multi-source projects.

[Link] Using MS-PowerPoint

PowerPoint is an essential tool for creating professional presentations. It allows users to create,
manipulate, and enhance slides with a variety of content, including text, images, charts, and
multimedia. Here we’ll cover how to use PowerPoint for creating presentations, incorporating
advanced features like WordArt, animations, and sound, and enhancing slide content for
maximum impact.

12.. Presentations: Creating, Manipulating, and Enhancing Slides

Creating a Presentation

• Starting a New Presentation: When you open PowerPoint, you can either start a
blank presentation or choose from a variety of templates that suit your needs (e.g., for business,
education, etc.).

• Adding and Managing Slides: You can add new slides by clicking the “New Slide”
button. Each slide can have a different layout, such as Title, Content, Two Content, or Blank.
Use the “Slide Sorter” view to rearrange the order of your slides.

Enhancing Slides

• Design Themes: Use PowerPoint’s built-in design themes to give your

presentation a consistent and professional look. Themes control the font styles, colors, and
backgrounds of your slides.

• Backgrounds and Color Schemes: Customize the background of your slides

using solid colors, gradients, or even images. This makes the slides more visually engaging.

• Text and Image Formatting: Format text by changing fonts, sizes, colors, and
applying special effects like shadows. You can also insert and resize images, shapes, and other
graphical elements to support your content.

13. Organizational Charts

• Creating Organizational Charts:

• PowerPoint includes SmartArt, which allows users to create visually appealing

organizational charts. These charts are ideal for depicting hierarchies or relationships between
different entities within an organization.

• To create an organizational chart, go to the Insert tab, click SmartArt, and select
Hierarchy. You can then add text to each shape and customize the layout.

• Editing Organizational Charts:

• You can expand or collapse levels, change the color scheme, and modify the
design of the chart to suit your presentation style.
14. Excel Charts in PowerPoint

• Inserting Excel Charts:

• If you’ve created charts in Excel, you can easily insert them into your PowerPoint
presentation. Simply copy the chart from Excel, and then paste it into PowerPoint. Alternatively,
you can go to Insert > Chart to create a chart directly in PowerPoint using data from Excel.

• Embedding and Linking: You can embed the Excel chart, which makes it part of
the PowerPoint file, or link it, so the chart will update automatically if the source data changes in
Excel.

15. WordArt

• Using WordArt:

• WordArt allows you to add stylized text to your slides. This feature is ideal for
creating titles, headings, and emphasizing key points in your presentation. To insert WordArt, go
to the Insert tab and click WordArt. You can choose from various styles and customize the
appearance by adjusting the font, size, and color.

16. Layering Art Objects

• Layering Objects:

• In PowerPoint, you can layer different elements such as text, shapes, images,
and charts. Use the Bring to Front and Send to Back options to control the stacking order of the
elements on your slides. This is useful when you want to ensure that certain elements are in
front of others, like placing a logo on top of an image.

17. Animations and Sounds

Animations

• Animating Objects:

• PowerPoint provides a variety of animations that can be applied to objects (text,

images, shapes, charts, etc.). You can animate objects to appear, disappear, fade, or move
across the slide. To apply animations, select the object, go to the Animations tab, and choose
from the options provided (e.g., Fade, Fly In, Wipe).

• Animation Pane: The Animation Pane allows you to control the order, timing, and
duration of animations, making it easier to create a smooth flow for your presentation.

Sounds

• Adding Sound Effects:

 • You can enhance your presentation by adding sound effects to animations or
transitions. Sounds can be applied to objects when they appear or disappear. PowerPoint
provides several built-in sound effects, such as Applause, Chimes, or Bells, which can be found
under the Transitions or Animations tabs.

• Inserting Recorded Sound: You can also record your own voice or insert other
audio files by going to Insert > Audio > Record Audio. Once recorded, the sound can be

Inserting Animated Pictures or Accessing Through Object, Inserting Recorded Sound Effect or
In-Built Sound Effect in MS PowerPoint

PowerPoint allows users to enhance presentations with animated pictures and sound effects.
Here’s how to add these multimedia elements to make your slides more engaging.

18. Inserting Animated Pictures or Accessing Through Object

Inserting Animated GIFs (Animated Pictures)

• Steps:

1. Go to the Insert tab.

2. Select Pictures > This Device or Online Pictures.

3. Choose an animated GIF and insert it.

4. The GIF will automatically play during the slide show.

• Note: The GIF won’t play in the editing mode but will animate during the
presentation.

Inserting Objects

• Steps:

1. Go to the Insert tab and click Object.

2. Choose Create from File or select an external object like videos or Flash
animations.

3. This method is useful for embedding media from other applications into your
slides.

19. Inserting Recorded Sound Effects

Recording and Inserting Sound

• Steps:
 1. Go to the Insert tab and select Audio > Record Audio.

2. Record your sound and click OK to insert it into the slide.

3. A speaker icon will appear, and you can adjust playback settings (e.g., start
automatically, loop, or hide icon during the show).

Inserting Audio Files

• Steps:

1. Click Audio > Audio on my PC.

2. Select the file (MP3, WAV) and insert it.

3. Customize playback settings as needed.

20. Inserting Built-In Sound Effects

PowerPoint includes a selection of built-in sound effects for transitions and animations.

• For Transitions: Go to the Transitions tab, choose a transition, and select a sound
from the Sound dropdown (e.g., Applause, Chimes).

• For Animations: In the Animations tab, apply an animation and add a sound
effect in the Effect Options.

By adding animated pictures and sound effects, you can create dynamic, engaging PowerPoint
presentations that keep your audience’s attention.

जय हिन्द