Difference between revisions of "LibreOffice-Suite-Calc-6.3/C2/Working-with-Cells-in-Calc/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "Title: Working with cells Author: Arvind Pillai Novice reviewer: Nikita Misal Domain reviewer: Praveen S Keywords: LibreOffice Calc, Enter numbers in Calc spreadsheet, Ent...")
 
 
(One intermediate revision by one other user not shown)
Line 199: Line 199:
  
  
−
|| Right-click on the '''cell C3 '''and from the '''context menu''', select the '''Format''' '''Cells''' option.  
+
|| Right-click on the '''cell C3 '''and select the '''Format Cells''' option.  
  
  
−
This will open the '''Format''' '''Cells''' dialog box.
+
This will open the '''Format Cells''' dialog box.
 
|-
 
|-
−
|| Point to the '''Format''' '''Cells''' dialog box >> '''Numbers '''tab.
+
|| Point to the '''Format Cells''' dialog box >> '''Numbers '''tab.
 
|| The first tab is '''Numbers'''. Click on it if not already selected.
 
|| The first tab is '''Numbers'''. Click on it if not already selected.
 
|-
 
|-
Line 265: Line 265:
 
|| To add a comma between the values, check the '''Thousands separator''' checkbox.
 
|| To add a comma between the values, check the '''Thousands separator''' checkbox.
  
−
 
−
Once again, notice the change in the preview area.
 
 
|-
 
|-
 
|| Click on '''Font'''.
 
|| Click on '''Font'''.
Line 277: Line 275:
  
  
−
It has various options for '''Font''' '''Family''', '''Style''' and '''Size'''.
+
It has various options for '''Family, Style''' and '''Size'''.
 
|-
 
|-
 
|| Slide cursor across '''Font''' '''Effects''' and the other tabs.
 
|| Slide cursor across '''Font''' '''Effects''' and the other tabs.
Line 321: Line 319:
  
 
Click on '''OK'''.
 
Click on '''OK'''.
−
|| Right-click on any of the highlighted '''cells''' and select '''Format''' '''Cells.'''
+
|| Right-click on any of the highlighted '''cells''' and select '''Format Cells.'''
  
  
 
Choose the same options as before.
 
Choose the same options as before.
−
 
−
 
  
  
Line 359: Line 355:
 
Click on the '''cell F2'''.
 
Click on the '''cell F2'''.
 
|-
 
|-
−
|| Under '''Date''' type '''05/06/20''' in the cell '''F2''' ['''Enter]'''
+
|| Under '''Date''' type '''05/06/20''' in the '''cell F2 [Enter]'''
 +
 
 +
Point to '''cell F2'''
 
|| For entering date in '''Calc''', select the '''cell''' and type the '''date, '''as shown.
 
|| For entering date in '''Calc''', select the '''cell''' and type the '''date, '''as shown.
  
  
−
Here I have used a '''forward''' '''slash '''between the date elements.
+
Here I have used a '''forward slash '''between the date elements.
 
|-
 
|-
 
|| '''Cell F3''' >> type '''05-06-20''' in the cell '''F3''' ['''Enter]'''
 
|| '''Cell F3''' >> type '''05-06-20''' in the cell '''F3''' ['''Enter]'''
 
|| We can also separate the date elements with a '''hyphen'''.
 
|| We can also separate the date elements with a '''hyphen'''.
−
 
−
 
  
 
|-
 
|-
Line 403: Line 399:
 
|-
 
|-
 
|| Point '''Format''' '''code''' below.  
 
|| Point '''Format''' '''code''' below.  
−
|| Based on our selection, the '''Format code''' will also get updated.
+
|| Based on our selection, the '''Format code''' will get updated.
  
−
In my case it is '''DD, MM and YYYY.'''
+
In my case it is '''DD, MM''' and '''YYYY.'''
 
|-
 
|-
 
|| Click on '''OK'''
 
|| Click on '''OK'''
Line 411: Line 407:
 
|-
 
|-
 
||  
 
||  
 +
  
  
Line 428: Line 425:
 
I have separated the time elements with '''colons'''.
 
I have separated the time elements with '''colons'''.
 
|-
 
|-
−
|| Right-click on cell '''F8'''→ Click on '''Format''' '''Cells'''.
+
|| Right-click on cell '''F8'''→ Click on '''Format Cells'''.
 +
 
  
  
−
Click→ '''Numbers''' '''tab'''
+
Click→ '''Numbers tab'''
 
|| To further customize the time format, right-click on the respective cell and select '''Format Cells'''.
 
|| To further customize the time format, right-click on the respective cell and select '''Format Cells'''.
  
  
−
Click on '''Numbers''' tab''' '''at the top
+
Click on '''Numbers''' tab at the top
 
|-
 
|-
 
|| Click on the option '''Time''' under '''Category'''  
 
|| Click on the option '''Time''' under '''Category'''  
Line 474: Line 472:
 
|-
 
|-
 
||  
 
||  
 +
|| Another method of accessing a particular '''cell''' is by using the '''cell''' reference.
  
−
 
+
|-
−
 
+
||Click on the down arrow to the right of the '''Name Box'''.
−
 
+
||Click on the '''Name Box''' at the top left corner, which is located below the '''Font Name.'''
−
 
+
|-
−
Click on the down arrow to the right of the '''Name''' '''Box'''.
+
||Delete→ Existing '''Cell Reference'''
−
 
+
||Erase the existing '''cell reference '''and type the '''cell reference''' of the '''cell''' you want to go.
−
 
+
|-
−
Delete→ Existing '''Cell Reference'''
+
||Type '''B4''' in the cell reference and press '''Enter'''.
−
 
+
−
 
+
−
 
+
−
 
+
−
Type '''B4''' in the cell reference and press '''Enter'''.
+
  
  
 
Point to '''B4'''
 
Point to '''B4'''
−
|| Another method of accessing a particular '''cell''' is by using the '''cell''' reference.
+
||I will type '''B4''' and press '''Enter'''.
−
 
+
−
 
+
−
Click on the '''Name''' '''Box''' at the top left corner, which is located below the '''Font Name.'''
+
−
 
+
−
 
+
−
Erase the existing '''cell''' '''reference '''and type the '''cell''' '''reference''' of the '''cell''' you want to go.
+
−
 
+
−
 
+
−
I will type '''B4''' and press '''Enter'''.
+
  
  
Line 506: Line 491:
 
|-
 
|-
 
||  
 
||  
−
 
−
 
−
 
−
Press '''Tab'''. Press '''Shift''' + '''Tab'''.
 
−
 
−
 
−
 
−
 
−
Press '''Enter'''. Press '''Shift''' + '''Enter'''.
 
 
|| We can also navigate between '''cells''' using the keyboard.  
 
|| We can also navigate between '''cells''' using the keyboard.  
 +
|-
 +
||Press '''Tab'''.
 +
||Press the '''Tab''' key to go to the next '''cell''' in a row.
 +
|-
 +
||Press '''Shift''' + '''Tab'''.
 +
||'''Shift + Tab''' to go to the previous '''cell''' in a row.
  
 +
|-
 +
||Press '''Enter'''.
 +
||'''Enter''' to go to the next '''cell''' in a column.
  
−
Press the '''Tab''' key to go to the next '''cell''' in a row.
+
|-
 +
||Press '''Shift''' + '''Enter'''.
 +
||'''Shift''' + '''Enter''' to go to the previous '''cell''' in a column.
  
−
'''Shift''' '''+''' '''Tab''' to go to the previous '''cell''' in a row.
 
−
 
−
 
−
'''Enter''' to go to the next '''cell''' in a column.
 
−
 
−
'''Shift''' + '''Enter''' to go to the previous '''cell''' in a column.
 
 
|-
 
|-
 
||  
 
||  
−
 
−
 
−
 
−
 
−
 
−
Click on '''A4''' >> press and hold down the left mouse button.
 
−
 
−
 
−
 
−
 
−
Move the mouse till '''B10'''. Release left mouse button.
 
 
|| Next, we’ll learn how to select a range of contiguous '''cells''' with the '''cursor'''.
 
|| Next, we’ll learn how to select a range of contiguous '''cells''' with the '''cursor'''.
  
 +
|-
 +
||Click on '''A4''' >> press and hold down the left mouse button.
 +
||First click on a '''cell''' and hold down the '''left mouse button'''.
  
−
First click on a '''cell''' and hold down the '''left mouse button'''.
+
|-
 +
||Drag the mouse till '''B10'''.  
  
−
Drag the '''cursor''' on the '''sheet'''.
+
Release left mouse button.
 +
||Drag the '''cursor''' on the '''sheet'''.
  
  
 
Once the desired block of '''cells''' is highlighted, release the left mouse button.
 
Once the desired block of '''cells''' is highlighted, release the left mouse button.
 +
|-
 +
||
 +
||We see that the selected '''cells''' get highlighted.
  
−
 
+
|-
−
We see that the selected '''cells''' get highlighted.
+
||
 +
|| Next, we’ll learn how to select multiple '''columns''' or '''rows''' that are not '''contiguous'''.
 
|-
 
|-
 
|| Click on first cell of first row '''Column''' '''A'''
 
|| Click on first cell of first row '''Column''' '''A'''
−
 
+
||Click on the first '''column''' or '''row''' that you want to select.
−
 
+
|-
−
Hold down the '''Shift''' key.
+
||Hold down the '''Shift''' key >> Drag till '''Column D.'''
−
 
+
||Now hold down the '''Shift''' key and click the last '''column''' or '''row''' that we want to select.
−
Drag till '''Column''' '''D.'''
+
−
|| Next, we’ll learn how to select multiple '''columns''' or '''rows''' that are '''contiguous'''.
+
−
 
+
−
 
+
−
Click on the first '''column''' or '''row''' that you want to select.
+
−
 
+
−
 
+
−
Now hold down the '''Shift''' key and click the last '''column''' or '''row''' that we want to select.
+
  
  
Line 571: Line 542:
 
||  
 
||  
  
 +
|| Let’s learn to select multiple '''columns''' or '''rows''' that are not contiguous.
  
−
 
+
|-
−
 
+
||Click on first cell of first row '''Column A'''
−
 
+
−
Click on first cell of first row '''Column''' '''A'''
+
  
 
Hold down the '''Control''' key  
 
Hold down the '''Control''' key  
 +
||Click on the first '''column''' or '''row''' that you want to select.
  
 +
Hold down the '''Control''' key.
  
 +
|-
 +
||Click→ '''Columns C,D & F '''while holding down the '''Control''' key.
  
  
−
Click→ '''Columns C,D & F '''while holding down the '''Control''' key.
+
||Now click on all of the subsequent columns or rows that you want to select.  
−
|| Let’s learn to select multiple '''columns''' or '''rows''' that are not contiguous.
+
−
 
+
−
 
+
−
Click on the first '''column''' or '''row''' that you want to select.
+
−
 
+
−
Hold down the '''Control''' key.
+
−
 
+
−
 
+
−
Now click on all of the subsequent columns or rows that you want to select.  
+
  
  
Line 597: Line 562:
 
|-
 
|-
 
|| Press '''Ctrl+S''' keys
 
|| Press '''Ctrl+S''' keys
 +
 +
  
 
Press X
 
Press X
Line 618: Line 585:
 
|| As an assignment
 
|| As an assignment
 
* Open '''Spreadsheet-Practice.ods'''  
 
* Open '''Spreadsheet-Practice.ods'''  
−
* Under '''Serial Number''' type the serial number from '''1 '''to''' 5''' one below the other and '''Align''' '''Center'''.
+
* Under '''SN''' type the serial number from '''1 '''to''' 5''' one below the other and '''Align''' '''Center'''.
 
* Add column headings '''Date''' and '''Time '''with the same '''text format'''
 
* Add column headings '''Date''' and '''Time '''with the same '''text format'''
 
* Enter some values in those columns
 
* Enter some values in those columns

Latest revision as of 20:37, 17 July 2020

Title: Working with cells

Author: Arvind Pillai

Novice reviewer: Nikita Misal

Domain reviewer: Praveen S

Keywords: LibreOffice Calc, Enter numbers in Calc spreadsheet, Enter text in Calc spreadsheet, Enter date in Calc spreadsheet, Enter time in Calc spreadsheet, Use Format Cells option in Calc


VISUAL CUE NARRATION
Slide: Title Welcome to the spoken tutorial on Working with Cells.
Slide: Learning objectives: In this tutorial, we will learn to:
  • Enter numbers, text, date and time in a spreadsheet
  • Use the Format Cells dialog box
  • Navigate between cells and
  • Select items in rows and columns
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 we will learn how to enter data in the cells.


Let’s open our Personal-Finance-Tracker.ods file.

Slide: Code files
  • This file has been provided to you in the Code files link on this tutorial page.
  • Pls download and extract the file.
  • Make a copy and then use it for practising.
Click on the cell C7 >> type the text sample >> Press Enter. Now type some text in any empty cell by clicking on the cell and typing using the keyboard.


Press Enter.

Point to the cell C7

Point to the Alignment icons on the Formatting Bar.

The text is left-aligned, by default.


One can change the alignment by clicking any one of the Alignment icons on the Formatting Bar.

Press Ctrl+Z keys Let's undo what we typed.


Press Ctrl+Z keys on the keyboard to undo.

Scroll the cursor across the headings. In this spreadsheet, we had already typed the column headings earlier.



Click on cell A1 >>drag the mouse till cell G1 >>

release the left mouse button


In Calc, we can differentiate the headings from other data by adding a style to it.


To do that, click on cell A1.


Then hold the left mouse button and drag the cursor upto cell G1.


Now release the left mouse button.

Highlight this We can see that all the headings got highlighted.
Click on Styles menu >> select Accent 2





Click on cell C4

Now click on Styles menu in the menu bar.


We can see various heading styles.


I will select Accent 2.


Now click randomly on any cell to unselect the headings.

Highlight this Notice that the headings are now highlighted with a grey colour background.
Under the Items, type Salary, House rent,

Electricity bill, Phone bill, Laundry and Miscellaneous.

In the Items column, we’ll type the names of a few items one below the other.


Type the text as shown here.

Click on the C7 cell and type 123 >> Press Enter. In order to enter numbers in the cell, click on the cell and type the number.


Then press Enter.

Text on screen: Press the Enter key after every data-entry. Remember to always press the Enter key after every data-entry.
Click on D7 cell and type -123 >> Press Enter.

Click on E7 cell and type (123) >> Press Enter.

To enter a negative number, either type a minus sign before it or enclose it within parentheses.
Point to the two cells one after the other. By default, numbers are right-aligned and negative numbers have a leading minus symbol.
Select all the cells to be deleted by holding the Shift key and clicking each cell.



Click on the Delete button.

Let us delete these entries.


Select all the cells to be deleted by holding the Shift key and clicking each cell.


Now press the Delete key on the keyboard.

Point to the cell A1 In the SN column, we want the serial number of each item one below the other.
Under SN type 1, 2, 3 in the cells A2 to A4 one below the other. And press Enter So click on cell A2 and enter numbers as 1, 2, 3 one below the other.

Remember to press Enter key after typing each number.

Click on the cell A4


In order to auto-fill the serial numbers in the subsequent cells, click on the cell A4.
Click on the little black box at the bottom-right corner of the cell


Drag it till cell A7 and release the mouse button.

Click on a little black box that is visible at the bottom-right corner of the cell.


Drag it till cell A7 and release the mouse button.

Highlight A5, A6 and A7 We see that cells A5, A6 and A7 get auto-populated with the subsequent serial numbers.
Point to Cost column. We will now enter the cost of each item under the heading Cost.
Click on C3-> type Rs.6000.00 Click on the cell C3 and type the expense for House rent as Rupees 6000.00
Only Narration Now, what if we want the number with the Rupee symbol?
Press Ctrl + Z keys Clear the value typed in cell C3 by pressing Ctrl and Z keys together.
Right-click on C3-> click on Format Cells


Right-click on the cell C3 and select the Format Cells option.


This will open the Format Cells dialog box.

Point to the Format Cells dialog box >> Numbers tab. The first tab is Numbers. Click on it if not already selected.
Point to Category

Then to Number, Percent, Currency, Date, Time.


Click on Currency.

Here we can see various options under Category.

Number, Percent, Currency, Date, Time and many more.


We will select Currency.

Point to INR ₹ English (India) under Format section

Point to the drop down arrow.

Various currency symbols from across the world are displayed in the Format list.


By default, INR Rupee English (India) is selected in the Format drop-down.


If not, click on the drop-down and select it from the displayed list.

Point to the Format options section



Point to -Rs. 1,234.00

Below the Format drop-down, we can see some sample values based on our selection.


We see minus Rupee 1234 decimal zero zero is selected for INR Rupees English India.

Point to the Preview area (the small white rectangle) on the right. We can see the selected format in the small preview area on the right.
Point to Options below the category section. Below the Category section, we have another section named as Options.
Point to + or - buttons of Decimal places and Leading zeroes. Using this, we can increase or decrease the number of Decimal places and Leading zeroes.
Click→ + or - buttons of Decimal places and Leading zeroes.


Change it to default values

When we do so, we can see the changes in the sample value and in the preview area.


We will retain the default values.

Click on Thousands separator.

Point to the Preview area.

To add a comma between the values, check the Thousands separator checkbox.
Click on Font.



Point to Family, Style and Size.

One can also change the font style by clicking on the Font tab.


It has various options for Family, Style and Size.

Slide cursor across Font Effects and the other tabs.

Point to Alignment tab.

Explore Font Effects and other tabs to learn more about them on your own.


We will learn about the options in the Alignment tab in a later tutorial.

Click on the OK button Let’s click on the OK button at the bottom right corner.
Type 6000 in cell C3.

Point to ₹6000.00.

Now type 6000 in cell C3 and press Enter.


Notice that the number 6000 is represented as Rupees 6000 with 2 decimal places.

Select cells C4 to C7 with cursor.



Press CTRL key and select cell G2.

Point towards selection

Now, let's select cells C4 to C7 by holding the Shift key and clicking the cells.


Hold the CTRL key and select cell G2 also.


Notice all the selected cells are highlighted.

Right-click on cell G2→ click on Format Cells


Choose the same options as before


Click on OK.

Right-click on any of the highlighted cells and select Format Cells.


Choose the same options as before.


Click on the OK button at the bottom right corner.

Click→ Random Cell Click any cell in the spreadsheet to deselect the cells.
Click on C4-> type 800 [Enter]


Click on C5-> type 600 [Enter]


Click on C5-> type 300 [Enter]


Click on C7-> type 2000 [Enter]

Next, we type the amount for all the items one below the other, as shown here.


Click on G2-> type 30000 [Enter] Under the heading Account, we’ll type the salary for the month as 30,000.

Click on cell F2.

Let’s move on to the Date column.

Click on the cell F2.

Under Date type 05/06/20 in the cell F2 [Enter]

Point to cell F2

For entering date in Calc, select the cell and type the date, as shown.


Here I have used a forward slash between the date elements.

Cell F3 >> type 05-06-20 in the cell F3 [Enter] We can also separate the date elements with a hyphen.
Cell F4 >> type 05 June 2020 in the cell F4 [Enter] Or use text such as 05 June 2020.

Calc recognizes a variety of date formats.



Right-click F2 >> Select Format Cells Option

We can also customize the date formats according to our requirement.


To do so, right-click on the cell and select Format Cells option.

Click Numbers tab >> Under Category select Date Click on the Numbers tab.

Under the Category section, select Date.

Click on 31/12/1999 under Format. Under the Format section, select the date format based on your requirement.


I will choose 31/12/1999.

Point to the Preview area (the small white rectangle) on the right. Notice the display in the preview area.
Point Format code below. Based on our selection, the Format code will get updated.

In my case it is DD, MM and YYYY.

Click on OK Once we have updated the format, click on the OK button at the bottom right.



Click on a cell F8


Type 10:43:20 and press Enter.

Next, let us try some time options.


Click on any random cell.


Type the time as shown.

I have separated the time elements with colons.

Right-click on cell F8→ Click on Format Cells.


Click→ Numbers tab

To further customize the time format, right-click on the respective cell and select Format Cells.


Click on Numbers tab at the top

Click on the option Time under Category

Click on 13:37:46 under Format

Under Category, choose Time and the desired format under Format.

I will choose 13:37:46.

Point to the Preview area


Point to Format code

Notice the display in the preview area.

Also, the Format code is displayed below as HH:MM:SS

Point to the Preview area >> Format code >> edit >>click on OK. One can change the Format code directly as well.


Click on OK button at the bottom right.

Select cells and press Delete Let us delete all the time entries from our spreadsheet.
Click on one cell and then the next cell on the same row. We will now learn how to navigate within a spreadsheet from cell to cell.
Click on cell A3. We can access a particular cell by simply clicking on it with the cursor.


This changes the focus to the new cell.


This method is most useful when the two cells are far apart.

Another method of accessing a particular cell is by using the cell reference.
Click on the down arrow to the right of the Name Box. Click on the Name Box at the top left corner, which is located below the Font Name.
Delete→ Existing Cell Reference Erase the existing cell reference and type the cell reference of the cell you want to go.
Type B4 in the cell reference and press Enter.


Point to B4

I will type B4 and press Enter.


Observe that cell B4 gets highlighted.

We can also navigate between cells using the keyboard.
Press Tab. Press the Tab key to go to the next cell in a row.
Press Shift + Tab. Shift + Tab to go to the previous cell in a row.
Press Enter. Enter to go to the next cell in a column.
Press Shift + Enter. Shift + Enter to go to the previous cell in a column.
Next, we’ll learn how to select a range of contiguous cells with the cursor.
Click on A4 >> press and hold down the left mouse button. First click on a cell and hold down the left mouse button.
Drag the mouse till B10.

Release left mouse button.

Drag the cursor on the sheet.


Once the desired block of cells is highlighted, release the left mouse button.

We see that the selected cells get highlighted.
Next, we’ll learn how to select multiple columns or rows that are not contiguous.
Click on first cell of first row Column A Click on the first column or row that you want to select.
Hold down the Shift key >> Drag till Column D. Now hold down the Shift key and click the last column or row that we want to select.


We see that the selected cells get highlighted.

Let’s learn to select multiple columns or rows that are not contiguous.
Click on first cell of first row Column A

Hold down the Control key

Click on the first column or row that you want to select.

Hold down the Control key.

Click→ Columns C,D & F while holding down the Control key.


Now click on all of the subsequent columns or rows that you want to select.


We see that the selected cells get highlighted.

Press Ctrl+S keys


Press X

Save the file by pressing Ctrl+S keys on the keyboard.


Now close the file by clicking the X icon on the top right corner.

Only Narration This brings us to the end of this tutorial, let us summarize.
Slide: Summary In this tutorial, we learnt to:
  • Enter numbers, text , date and time in Calc
  • Use the Format Cells dialog box
  • Navigate between cells
  • Select items in rows and columns
Slide:Assignment As an assignment
  • Open Spreadsheet-Practice.ods
  • Under SN type the serial number from 1 to 5 one below the other and Align Center.
  • Add column headings Date and Time with the same text format
  • Enter some values in those columns
  • Use the Format Cells dialog box options to format the cells
  • Refer to the Code files link for the data.
  • Save and close the file
Slide: About Spoken Tutorial Project
  • The video at the following link summarises the Spoken Tutorial project.
  • Please download and watch it
Slide: Spoken tutorial workshops
  • We conduct workshops using Spoken Tutorials and give certificates.
  • For more details, please write to us.
Slide: Forums Pls post your timed queries in this forum.
Slide: Acknowledgement Spoken Tutorial project is funded by MHRD, Govt. of India.
Slide: Thank you

Acknowledgement to DesiCrew

This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011


This is Arvind along with Spoken Tutorial team from IIT Bombay signing off.


Thank you for watching.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Calc-6.3/C2/Working-with-Cells-in-Calc/English&oldid=52999"