UNIT – 4
SPREADSHEET
Autosum - Autosum is used
for adding the values given in cells automatically without writing the
formula to perform
the operation.
Steps to perform
Autosum:
1. Select the cell in
which you want sum.
2. Click on Home
Tab, then click the Autosum option in the Editing
group.
3. The values in the
cell will be calculated automatically.
Note: Autosum
automatically selects the values around the cells either horizontally or vertically.
Conditional
Formatting - Conditional formatting allows you to change the formatting (font
color, border, shading) of the cells based on the values in it. So, you specify
a condition for the values in the cell, and if the condition is satisfied then
automatically the formatting of the cell changes.
You can control the
following formats:
Number format
Font, font style, and
font colour (but not font size)
Fill colour and fill
pattern
Border colour and
border style (but not border thickness)
Steps to perform
Conditional Formatting:
1. Select all the cells
you want to format conditionally.
2. Click on Conditional
Formatting option available under Styles group
in the Home tab. A drop down list appears.
3. Select the desired
formatting option, then specify the required values.
4. Click OK
Freeze Rows and
Columns - When you are dealing with a huge amount of data that spans
several rows or columns, you may want the headers to remain constant (frozen)
while you scroll through the data so that you can see the identifying names of
the rows/columns is called Freezing of rows and columns.
Steps for Freezing
Rows and Columns
1. Click on Freeze
Panes option available under Window group
in View tab.
2. A drop down list
appears as shown below.
3. Click on Freeze
Panes.
Note: The panes are formed
where your cursor is placed. You can also freeze the entire row or column by
selecting the entire row or column and click “Freeze Panes” to freeze the
entire row or column.
Hide / Unhide Rows
and Columns - Sometimes you have data in rows and columns which are needed for
formulas or charts, but you do not want the data to be visible. So, you can
Hide / Unhide that rows and columns. The data in hidden row/column is available
for calculations though not visible.
Steps to Hide a row /
column:-
1. Either select the
particular row/column or just one cell in the corresponding row/column.
2. Click on Format option
available under Cells group in the Home tab.
3. A dropdown list
appears. Click on Hide & Unhide > Hide
Rows.
OR
Select the
row/column, right click and select Hide.
Steps to Unhide a row
/ column:-
1. Select any cells
before and after the cell that is hidden.
2. Click Unhide
Rows in the Format option available under Home tab.
PAGE BREAKS
To print a worksheet
with the exact number of pages that you want, you can adjust the page breaks in
the worksheet before printing it. This feature is very useful especially when
printing huge sheets.
Steps To Set Page
Break
1. On the View tab, in
the Workbook Views group, click Page Break Preview.
2. To insert a vertical
page break, select the row below where you want to insert the page break. To
insert a horizontal page break, select the column to the right of where you want
to insert the page break.
3. Click on Breaks down
arrow under Page Setup group in the Page Layout tab.
4. Click on Insert Page
Break option.
5. To move a page break,
simply drag the page break to the desired location.
Steps to Remove Page
Break
Click the Remove
Page Break option in Breaks drop down list.
Note: To return to
Normal view after you finish working with the page breaks, click Normal
in the Workbook Views
group under the View tab.
PAGE LAYOUT
Spread sheet software
provides various page layout options for organizing pages using the Page Layout
option.
Steps to set the page
layout options
Click on Page
Layout option under Workbook Views group on View
tab. We can set
◦ Margins
◦ Orientation
◦ Page headers
and footers
◦ Hide or display
grid lines
◦ Size of the
page
◦ Define the
print area
◦ Specify the
background
MARGINS
Click on Margins
option under Page Setup group, a dropdown list appears.
You can either select
anyone of the predefined margin options available or else you can define your
own margin settings by clicking Custom Margins option and set the margin values
manually from the options available under the dialog box.
ORIENTATION
You can set the
orientation of the page to either Portrait or Landscape by clicking the
Orientation option under Page Setup group in the Page Layout tab.
PAGE HEADERS AND
FOOTERS
HIDE OR DISPLAY GRID
LINES
SIZE
You can set the size
of the page by clicking the Size option under Page Setup group in the Page
Layout tab.
DEFINE THE PRINT AREA
Click on Set Print
Area to set the selected to get printed. You can also clear the print area by
clicking the Clear Print Area option.
SPECIFY THE
BACKGROUND
You can specify a
background for your sheet using the Background option under Page Setup in the
Page Layout tab.
MANAGE WORKBOOK VIEWS
Workbook views are
used for the purpose of viewing the outcome of the worksheet while printing.
There are five types of views in the spreadsheet:-
1. Normal-This is the default
view of the spreadsheet application. It is a collection of cells arranged in
the work area.
2. Page Layout- With page layout
view, you can quickly fine tune a worksheet and achieve professional looking
results.
3. Page Break Preview- This option is
similar to Page Layout option except you can set the area that is to be set as
a page after inserting page break.
4. Custom View- With custom
view, you can view selected areas of a document.
5. Full Screen- Selecting this
option the workbook cover the entire screen. All tabs are hidden from view. To
get back the tabs, click on File>Restore.
Multiple windows
You can open multiple
windows that display the current spreadsheet and then arrange those windows in
a variety of ways.
Steps to open a new
window of a workbook
1. Click New Window
button on the View tab, in the Window group.
Steps to arrange
multiple windows
1. Click Arrange All
button in the Window group. An Arrange Windows dialog
box appears.
2. Click either Tiled,
Horizontal, Vertical, or Cascade options.
3. Click OK.
APPLY CELL AND RANGE NAMES
We can assign names
to cells in a worksheet and use it for quickly locating specific cells by
entering the names. Range is a collection of cells.
Steps to apply range
names:-
1. Keep the Ctrl key
pressed and click on the cells that you want to give a name OR select the range
of cells.
2. Right click and
select Name a Range…. OR select Define name under Defines
Names group in the Formulas tab.
3. A New
Name dialog box appears. Enter the name in the Name field.
4. Click OK.
CREATE MODIFY AND
FORMAT CHARTS
A chart is graphical
representation of data in which data is represented by symbols such as bars in
a bar chart, lines in a line chart or slices in a pie chart. Spreadsheet helps
to create, modify and format charts based on the data given in the spreadsheet.
Steps to create a
chart:-
1. Prepare data in the
spreadsheet on which you want to create the chart. Select the data.
2. Click on the chart
type that you want from Charts group under the Insert tab.
3. A chart will be
displayed in the spreadsheet.
4. You can modify the
chart values by making necessary modifications in the data table.
Chart has many
elements, however, only some of the elements are displayed by default. Following
are the elements of a chart:-
1. Chart Area
2. Plot Area
3. Data Points
4. Horizontal and
Vertical Axis
5. Legend
6. Chart and Axis Title
7. Data Label
Chart Types
Different charts
display data in very different ways. Using the best chart type and format helps
you to display data visually in the most meaningful way. Following are the
different types of chart:-
1. Bar charts: A bar chart
(horizontal bars) emphasizes the comparison between items at a fixed period of
time. This chart type also includes cylinder, cone and pyramid subtypes.
2. Column
Charts: A column chart emphasizes variation over a period of time. This
chart type also includes cylinder, cone and pyramid subtypes.
3. Line Charts: A line chart
shows the relation of the changes in the data over a period of time.
4. Pie Charts: A Pie chart
shows the relationship of the parts to the whole.
5. Area Charts: An area chart
shows the relative importance of values over time.
6. XY (Scatter) Charts: Scatter charts
are useful for showing a correlation among the data points that may not be easy
to see from data alone.
SORT & FILTER
DATA
Sort:- It is a feature that
helps you arrange the selected data either in an ascending or descending order.
Steps to Sort Data
1. Select the data to be
sorted and then click on Sort option under Sort & Filter group in the Data
tab.
2. Specify on what basis
you need to sort the data in the Sort by field and select the order that is
ascending and descending and then click OK.
Filter:- It is a feature used
for extracting particular data using some conditions.
Steps to Filter Data
1. Click the Filter
option available under Sort & Filter group in the Data tab. Before applying
the filter, select the data along with the header.
2. Once you click on
filter, the header will be displayed with a down arrow.
CALCULATE DATA ACROSS
WORKSHEETS
To summarize and
report results from separate worksheets, you can consolidate data from each
into a master worksheet. The worksheets can be in the same workbook as the
master worksheet or in the other workbooks. The assembling of data is required
so that updates and calculations can be performed easily.
Steps:-
1. Create a sheet 1 name
it as year1.
2. Create a sheet 2 name
it as year2.
3. For using sheet 1 or
sheet 2 data in any of the sheets use year1! Or year2! Respectively.
For example:- if you
want to add B1:B3 range of sheet 1 in sheet 2 use =SUM(year1!B1:B3).
Note:- using this feature,
we can use a value of a cell located at one sheet in the another sheet.
USING MULTIPLE
WORKBOOKS & LINKING CELLS
Spreadsheet also
allows to link the cells from various worksheets and from various spreadsheets
to summarize data from several sources. In this way, we can create formulas
that span different sources and make calculations using a combination of local
and linked information. We can link the data from other spreadsheets and keep
the information up to date without editing multiple locations every time, the
data changes.
Steps :-
1. Open a new
spreadsheet workbook.
2. For example, we are using
sum formula, type =sum(, while the parenthesis is opened, click on Switch
Windows option under Window group in the View tab. A drop down list appears
containing all the active workbooks names.
3. Click on the other
workbook name eg. Book1 and select the desired cells eg. A2:A5
4. Press Enter. After
pressing enter, we see that we are back on the previous workbook with the
desired result.
SHARING WORKSHEET
DATA
Using this feature of
spreadsheet, multiple users can access a single sheet simultaneously in a
network location.
Steps:-
1. Click on Share
workbook option under Changes group in the Review tab.
2. A dialog box appears
as shown below.
3. Check the
option Allow changes by more than one user at the same time. This also
allows workbook merging.
4. Click OK.
============================CHAPTER
ENDS============================
No comments:
Post a Comment