Excel-Practical Record 36-40

36.
Create the following table:

ROLL NO SUBJECT MARKS
1 ECONOMICS 90
1 ECONOMICS 90
3 ACCOUNTS 90
2 ACCOUNTS 80
2 ACCOUNTS 80
4 ECONOMICS 50
I) Remove duplicate rows
II) Prepare Subject-wise Sub-Totals
Step1 : Start Ms-Excel in the following way:

Start → All Programs → MS-Office → MS-Excel 2013
MS-Excel application will be opened.
Step 2: In sheet1, enter the data in the worksheet as given in the above question
Step3: Select the range of cells, or ensure that the active cell is in a table. Click on the Data tab,
click Remove Duplicates (in the Data Tools group).
Step4 : Click on ok.You can see that 2 duplicate values are found and removed and 4 unique
values remian.
Step5: To Prepare Subject-wise Sub-Totals. Select all the data,Go to data tab, Click on outline
group, click on subtotal, Click on subjects and click ok.
37. Create the following table with own data:

ROLLNO NAME
i. Open a New Window containing current document

ii. View Side-by-Side
iii. Freeze top row
Step3: To open a New Window containing current document. Go to view tab, Window group and
click on new window.
Step4: A new Window appears.
Step5: To view Side-by-Side. Go to the View tab, in the Window group, click View Side by Side
icon.
Step6: Now you can see that both the windows are side by side.
Step7: To Freeze top row. Go to the View tab, in the Window group, click Free panes and click
on Freeze top row option.
38. Create the following table with your own data:
ROLLNO S1 S2 TOTAL
i) Total by using a Function

ii) Using Paste Special perform the following:
a) copy formula and paste in another cell
b) copy only values from formula and paste in another cell
c) Perform Add, Subtract operations

Step3: To get the total click on D2 cell and type =SUM(B2+C2).
Step4:Click Enter. You can see that Total amount will come. After that Click and drag the end
of the D2 cell and drag it to D4.
Step5: Finally, You will get the answer.

Step6: For using Paste special perform the following:
a. To copy formula and paste in another cell:
 Select the cell containing the formula that you want to copy. Right Click on it and
click on copy.
To paste the formula in any other cell. Right click on the cell, click Paste Special and
click paste.
b. To copy only values from formula and paste in another cell:

 Select the cell containing the formula that you want to copy. Right Click on it and
click on copy.
To paste the Formula in any other cell. Right click on the cell, click Paste Special
and click paste Values.
c.Perform Add, Subtract operations:
i. To add any 2 values, Type =SUM(1+2).
Click on enter. You will see that the answer will be 3.
ii) To Subtract two Values, type =SUM(2-1).

You click on Enter and you will see that the answer will be 1.
39. Show the following concepts by using appropriate examples:

i) Merge and Center
ii)Format Painter iii)Wrap

text
Iv) Shrink to fit long data in a cell

v)Fill colour in a cell
vi)increase column/row height/width
i) Merge and Center:

 Select the Merge & Center button .
This button is located in the 'Alignment' group, on the Home tab of the Excel ribbon:
As the name suggests, if you click on the Merge & Center button, the currently
selected cells will be merged, and their contents will be centered horizontally and
vertically.
Example of Merge & Center:
a. Open your Excel document. Double-click an Excel document to open it in Excel.
● If you haven't yet created the document, open the Excel program and click Blank
Workbook.
b. Select the cells you want to merge. Click one cell, then drag your mouse to select the
other cell(s) you want to merge.
● For example, if you want to merge cells A1 through C1, you would click and drag from
A1 right to C1.
● The cells you merge must be touching each other; for example, you can merge A1 with
B1, but not with C1 without merging B1 as well.
c. Click the Home tab. It's in the top-left side of the Excel window. This will bring up the
Home toolbar below the green ribbon at the top of the Excel window.
d. Click Merge & Center. This box is in the "Alignment" section of options in the Home
toolbar. Doing so will automatically merge your selected cells and center their content.
● If you don't want to center the cells' content, click instead the ▼ icon to the right of
Merge & Center and then click Merge Cells.
ii) Format Painter with Example:
The Format Painter is one of the most underused features of Excel. The Format Painter copies
formatting from one place and applies it to another.
1. For example, select cell B2 below.
2. On the Home tab, in the Clipboard group, click Format Painter.

A moving dashed border appears around cell B2 and the mouse pointer changes to a plus and a
paintbrush.
3. Select cell D2.
Note: the Format Painter applies the Currency format, background color and borders of cell B2
to cell D2. That saves time! Instead of selecting cell D2, you can also select a range of cells to
apply the format of cell B2 to a range of cells.
4. Double click the Format Painter button to apply the same formatting to multiple cells.
Note: click the Format Painter button again (or press Esc) to exit Format Painter mode.
iii)Wrap Text
Wrap text in Excel if you want to display long text on multiple lines in a single cell. Wrap text
automatically or enter a manual line break.
Wrap Text Automatically
1. For example, take a look at the long text string in cell A1 below. Cell B1 is empty.
2. On the Home tab, in the Alignment group, click Wrap Text.
Result:
3. Click on the right border of the column A header and drag the separator to increase the
column width.
4. Double click the bottom border of the row 1 header to automatically adjust the row height.
Note: if you manually set a row height (by clicking on the bottom border of a row header and
dragging the separator), Excel does not change the row height when you click the Wrap Text
button. Simply double click the bottom border of a row header to fix this.
5. Enter an extra-long text string in cell B1 and wrap the text in this cell.
Note: by default, Excel aligns text to the bottom (see cell A1).
6. Select cell A1.
7. On the Home tab, in the Alignment group, click Top Align.
Result:
Iv) Shrink to fit long data in a cell
There is a Shrink feature to shrink text in Excel
1. Select the cell with texts that are too long to fully display, and right-click to select
FormatCells.
2. In the Format Cells dialog box, click Alignment tab, and check Shrink to fit.
3. Then click OK to close the dialog box. And now you can see the text is shrank to fit the cell
v) Fill colour in a cell:
You can highlight data in cells by using Fill Color to add or change the background color or
pattern of cells. Here's how:
1. Select the cells you want to highlight.
Tips:
o To use a different background color for the whole worksheet, click the Select All
button. This will hide the gridlines, but you can improve worksheet readability by
displaying cell borders around all cells.
o
2. Click Home > the arrow next to Fill Color , or press Alt+H, H.
3. Under Theme Colors or Standard Colors, pick the color you want.
To use a custom color, click More Colors, and then in the Colors dialog box select the
color you want.
Tip: To apply the most recently selected color, you can just click Fill Color . You'll
also find up to 10 most recently selected custom colors under Recent Colors.
vi) increase column/row height/width:

Set a column to a specific width
1. Select the column or columns that you want to change.
2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click Column Width.

4. In the Column width box, type the value that you want.
Change the row height to fit the contents:
1. Select the row or rows that you want to change.

2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click AutoFit Row Height.

Tip To quickly autofit all rows on the worksheet, click the Select All button and then double-
click the boundary below one of the row headings
40. Generate a table with only RollNumbers till 20 using Autofill concept
Set the following printing options:
i)No. Of
copies 10
ii)Orientation is Landscape
iii)Print on both sides
iv)Size A4
v)insert a page break after Roll No 8
vi)give Wide (Top,bottom,left and right 2.54 cms each) Margins
vii)give appropriate Header and Footer
Step 2: In sheet1, enter the data in the worksheet as given in the above question (Enter Roll
Nos using auto fill up to 20)
Step3:On the ribbon system->click on file menu and select print option
Step4:
i) Give no of copies is 10
ii) Select page orientation is landscape
iii) Size A4
Step5: Insert a Page Break at roll no 8
On ribbon system select Page Layout->Click on Page break
STEP6: Give wide(2.54cm )

Click file menu and select print option->click on page setup -> and select margins
STEP7: Header and Footer
On ribbon system select Insert menu ->select header and Footer

Excel-Practical Record 36-40

Uploaded by

Copyright:

Available Formats

Excel-Practical Record 36-40

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Excel-Practical Record 36-40

Uploaded by

Copyright:

Available Formats

36.

Create the following table:

I) Remove duplicate rows

II) Prepare Subject-wise Sub-Totals

Step1 : Start Ms-Excel in the following way:

37. Create the following table with own data:

i. Open a New Window containing current document

Step4: A new Window appears.

38. Create the following table with your own data:

i) Total by using a Function

Step1 : Start Ms-Excel in the following way:

Step3: To get the total click on D2 cell and type =SUM(B2+C2).

Step5: Finally, You will get the answer.

b. To copy only values from formula and paste in another cell:

Click on enter. You will see that the answer will be 3.

ii) To Subtract two Values, type =SUM(2-1).

39. Show the following concepts by using appropriate examples:

ii)Format Painter iii)Wrap

Iv) Shrink to fit long data in a cell

i) Merge and Center:

Example of Merge & Center:

a. Open your Excel document. Double-click an Excel document to open it in Excel.

ii) Format Painter with Example:

1. For example, select cell B2 below.

2. On the Home tab, in the Clipboard group, click Format Painter.

3. Select cell D2.

Wrap Text Automatically

6. Select cell A1.

7. On the Home tab, in the Alignment group, click Top Align.

Iv) Shrink to fit long data in a cell

There is a Shrink feature to shrink text in Excel

1. Select the cells you want to highlight.

vi) increase column/row height/width:

3. Under Cell Size, click Column Width.

Change the row height to fit the contents:

1. Select the row or rows that you want to change.

3. Under Cell Size, click AutoFit Row Height.

Set the following printing options:

STEP6: Give wide(2.54cm )

You might also like