LibreOffice-Suite-Calc-6.3/C2/Formatting-Data--in-Calc/English

From Script | Spoken-Tutorial
Jump to: navigation, search

Title: Formatting Data

Author: Arvind PillaiKeywords: LibreOffice,Calc,Ubuntu,Format cell borders in Calc,Format cells background colour in Calc,Automatic Wrapping in Calc,Merging cells in Calc,Shrinking cell text in Calc


VISUAL CUE NARRATION
Slide: Title Welcome to the spoken tutorial on Formatting Data in Calc.
Slide: Learning objectives In this tutorial, we will learn to:
  • Format cell borders and cell background colors.
  • Format multiple lines of text using Automatic Wrapping.
  • Merge cells.
  • Shrink the text to fit inside the cell.
Slide: System Requirements This tutorial is recorded using
  • Ubuntu Linux OS version 18.04 and
  • LibreOffice Suite version 6.3.5
Open “personal finance tracker.ods” file. First let us learn about formatting cell borders in Calc.

Open our Personal-Finance-Tracker.ods file.

Highlight to “SN”, “Item”, “Cost”, “Spent”,

Received”, “Date”, “Account

Formatting of borders can be done on a particular cell or a block of cells.
Select >> A2 to G2



Go to Formatting toolbar

Select >> Borders icon

Select the cells, A2 to G2 by holding the left mouse button.


Then go to the Formatting toolbar and select the Borders icon.

Only Narration If you have resized your LibreOffice window, some of the icons may not be visible.
Click on Double arrow >> icon at the end of the toolbars


Click >> Borders icon

In that case, click on the double arrow icon at the end of the toolbars to see the hidden icons.


Click on the Borders icon.

Point to border styles A drop-down box opens up containing several border styles.


Click on the last border style option.

Click on any one of the styles you wish to apply on the borders.


I will select the last option.

Click on any random cell Now click anywhere on the spreadsheet to deselect the cells.
Point to the cells A1 to G1 Notice that the cell borders are formatted according to the selected border style.
CTRL + Z Let us undo this change by pressing Ctrl+Z keys.



Right-click >> Format Cells

We can also do the same thing using the Format cells dialog box.


Right-click on the selected cells and choose Format Cells option.


Or press Ctrl+1 keys on the keyboard.

Point to Format Cells


Click >> Borders tab.

Either way, the Format Cells dialog box opens up.


Now go to the Borders tab.

Point to Line arrangement, Line, Padding

and Shadow style.

We see the options for Line arrangement, Line, Padding and Shadow style.

One can change any of these as per our preference.

Point to User-defined >> Point to the preview window.


Click on the third option under Presets


Point to the preview window.

In the Line Arrangement section, we can see a small preview window named: User-defined.


Under Presets, I will click on the third option.



You can see that it gets reflected in the preview window.

Point to Style, Width and Color section


Click Style and select dotted lines option


Set the Width as 2.00pt >> Set the color as red

I will also change the Style, Width and Color from the Line section, as shown.


From the style drop-down, I will select the dotted lines.



I will set the width as 2.00 pt. and select the color as Red.

Point to Preview window Observe the changes in the preview window.
Point to Padding section Next, go to the Padding section.


Observe that all the values are the same.

Click - or + buttons to change values.


Point to Synchronize option


If we try to change the padding value of any one of the sides, all of them get changed.


This is because the Synchronize option is checked.


If we wish to set different values for each side.


Then the Synchronize option needs to be unchecked.

Uncheck >> Synchronize option


Under Padding, set Top and bottom margins to 1.5 mm

Under Padding, set right and left margins to 1 mm

Let’s uncheck the Synchronize option.


I will set the padding value for Top and Bottom margins to 1.5 mm.


And the Left and Right margins to 1 mm.

Point to shadow styles You can explore the various Shadow styles on your own later.
Click on OK.


Click on any random cell

Click on the OK button at the bottom.


Now click anywhere on the spreadsheet to deselect the cells.

Point to the Headings Notice that our chosen border style is now applied to all the selected cells.
Point to Headings



Narration Only

In our earlier tutorial, we had set one of the default styles to the headings.


Now let us set some other colors to the headings, using the Background Color option.

Select A1 to G1


Go to the Formatting toolbar

Select drop-down next to Background Color icon

Now select the cells A1 to G1 by holding the left mouse button.


Then go to the Formatting toolbar and select the drop-down next to the Background Color icon.

Point to Background Color popup




Click >> Orange

The Background Color popup menu opens up.


Select the color you wish to apply as a background to the cells.


I will click on the color Orange.

Point to Headings Observe the change.


Now the background color for the headings is set to Orange.

Slide: Automatic Wrapping Calc provides various options for formatting multiple lines of text.

One of them is Automatic Wrapping.

It allows a user to type multiple lines of text into a single cell. Let us try this.

RightClick >> Cell B9

Select >> Format Cells

Right-click on cell B9 and select Format Cells option.


Then go to the Alignment tab.

Check on Wrap text automatically


click on OK

Under the Properties section, check the Wrap text automatically option.


Then click on the OK button at the bottom.

In Cell B9 Type >> “This is a personal Finance Tracker. It is very useful”



Press Enter

Now type the following text in cell B9.


This is a personal Finance Tracker. It is very useful”.


And press Enter.

Point to cell B9 We see that the multiple statements get wrapped into the same cell.
Ctrl + Z Undo these changes by pressing Ctrl+Z keys.
Only Narration Next we will learn how to merge cells in Calc.
Click on 1 under the heading SN.



Click and hold Shift key >> click Salary


Point to highlighted Cells

First click on the cell with value 1 under the heading SN.

Now hold down the Shift key on the keyboard and click on the cell which has the word Salary.


The selected cells get highlighted.

Click on the Merge and centre cells icon Click on the Merge and centre cells icon in the Formatting bar.
Point to Merge Cells dialog box Merge Cells dialog box opens up.
Point to Merge cells options Here three options for merging the cells are displayed along with a preview.


We can select any one as per our preference.



Select the Move the content of hidden cells into the first cell radio button.


Click Ok

I want to move the contents of both the cells into a single cell.


So, I will select the Move the content of hidden cells into the first cell radio button.


Then click on the OK button at the bottom of the dialog box.

Point to cell A2 Observe that the selected cells and their values are merged together.
Go to Format menu >> Merge Cells >> Merge Cells Alternatively, we can merge cells by clicking on the Format menu in the menu bar.


From the sub-menu, go to Merge Cells and select any one preferred option.


Explore these on your own later.

Press Ctrl + Z Undo these changes by pressing Ctrl+Z keys.
Only Narration: Next we will learn how to shrink text to fit into a cell.

The font size of the data in a cell can be automatically adjusted to fit the data into the cell.

Let us learn how to do it.

In Cell B10 Type >> “This is for the month of June”


Press Enter

First type the following text in cell B10 - This is for the month of June”.


Then press Enter.

Right click >> B10



Select >> Format Cells

In order to shrink the text so that it fits, right-click on cell B10.


Then select the option Format Cells.

Point to Format cells dialog box Format cells dialog box opens up.
Go to the Alignment tab.


Click on Shrink to fit cell size check box


Click on OK.

Go to the Alignment tab.


Under the Properties section, click on the Shrink to fit cell size check-box.


Then click on the OK button at the bottom of the dialog box.

Point to Cell >> B10


Observe that, the entire text inside the cell shrinks and fits within the cell.


It adjusts itself by decreasing its font size so that the text fits into the cell.

CTRL + Z Undo the changes by pressing Ctrl+Z keys together.
Ctrl + S and click on x button at top right corner Then save and close the file.
Narration: This brings us to the end of this tutorial, let us summarize.
Slide: Summary In this tutorial, we learnt to:
  • Format cell borders and cell background colors.
  • Format multiple lines of text using Automatic Wrapping.
  • Merge cells.
  • Shrink the text to fit inside the cell.
Slide: Assignment As an assignment:
  • Open “spreadsheet-practice.ods” sheet.
  • Select all the headings.
  • Give Grey background color to the headings.
  • Using “Automatic Wrapping” type the text in cell A10 - “This is a Department Spreadsheet”.
  • Shrink this text to fit inside cell A11.
Slide: About Spoken Tutorial Project
  • The video at the following link summarizes the Spoken Tutorial project.
  • Please download and watch it.
Slide: Spoken Tutorial Workshops
  • The Spoken Tutorial Project Team conducts workshops and gives certificates.
  • For more details, please write to us.
Slide: Forums
  • Please post your timed queries in this forum.
Slide: Acknowledgement Spoken Tutorial projects are funded by MHRD, Govt. of India.
Slide: Thank you This is Arvind from IIT Bombay signing off. Thank you for watching.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey