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.
No comments:
Post a Comment