Welcome to the spoken tutorial on Working with Cells.
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
This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
First we will learn how to enter data in the cells.
Let’s open our Personal-Finance-Tracker.ods file.
This file has been provided to you in the Code files link on this tutorial page.
Please download and extract the file.
Make a copy and then use it for practising.
Now type some text in any empty cell by clicking on the cell and typing using the keyboard. Press Enter.
The text is left-aligned, by default.
One can change the alignment by clicking any one of the Alignment icons on the Formatting Bar.
Let's undo what we typed.
Press Ctrl+Z keys on the keyboard to undo.
In this spreadsheet, we had already typed the column headings earlier.
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.
We can see that all the headings got highlighted.
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.
Notice that the headings are now highlighted with a grey colour background.
In the Items column, we’ll type the names of a few items one below the other. Type the text as shown here.
In order to enter numbers in the cell, click on the cell and type the number. Then press Enter.
Remember to always press the Enter key after every data-entry.
To enter a negative number, either type a minus sign before it or enclose it within parentheses.
By default, numbers are right-aligned and negative numbers have a leading minus symbol.
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.
In the SN column, we want the serial number of each item one below the other.
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.
In order to auto-fill the serial numbers in the subsequent cells, click on the cell A4.
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.
We see that cells A5, A6 and A7 get auto-populated with the subsequent serial numbers.
We will now enter the cost of each item under the heading Cost.
Click on the cell C3 and type the expense for House rent as Rupees 6000.00
Now, what if we want the number with the Rupee symbol?
Clear the value typed in cell C3 by pressing Ctrl and Z keys together.
Right-click on the cell C3 and select the Format Cells option.
This will open the Format Cells dialog box.
The first tab is Numbers. Click on it if not already selected.
Here we can see various options under Category.
Number, Percent, Currency, Date, Time and many more.
We will select Currency.
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.
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.
We can see the selected format in the small preview area on the right.
Below the Category section, we have another section named as Options.
Using this, we can increase or decrease the number of Decimal places and Leading zeroes.
When we do so, we can see the changes in the sample value and in the preview area.
We will retain the default values.
To add a comma between the values, check the Thousands separator checkbox.
One can also change the font style by clicking on the Font tab.
It has various options for Family, Style and Size.
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.
Let’s click on the OK button at the bottom right corner.
Now type 6000 in cell C3 and press Enter.
Notice that the number 6000 is represented as Rupees 6000 with 2 decimal places.
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 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 any cell in the spreadsheet to deselect the cells.
Next, we type the amount for all the items one below the other, as shown here.
Under the heading Account, we’ll type the salary for the month as 30,000.
Let’s move on to the Date column. Click on the 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.
We can also separate the date elements with a hyphen.
Or use text such as 05 June 2020.
Calc recognizes a variety of date formats.
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 on the Numbers tab. Under the Category section, select Date.
Under the Format section, select the date format based on your requirement. I will choose 31/12/1999.
Notice the display in the preview area.
Based on our selection, the Format code will get updated. In my case it is DD, MM and YYYY.
Once we have updated the format, click on the OK button at the bottom right.
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.
To further customize the time format, right-click on the respective cell and select Format Cells.
Click on Numbers tab at the top
Under Category, choose Time and the desired format under Format. I will choose 13:37:46.
Notice the display in the preview area.
Also, the Format code is displayed below as HH:MM:SS
One can change the Format code directly as well.
Click on OK button at the bottom right.
Let us delete all the time entries from our spreadsheet.
We will now learn how to navigate within a spreadsheet from cell to cell.
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 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.
Observe that cell B4 gets highlighted.
We can also navigate between cells using the keyboard.
Press the Tab key to go to the next cell in a row.
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.
Next, we’ll learn how to select a range of contiguous cells with the cursor.
First click on a cell and hold down the 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 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. We see that the selected cells get highlighted.
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.
We see that the selected cells get highlighted.
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.
This brings us to the end of this tutorial, let us summarize.
In this tutorial, we learnt to: Enter numbers, text , date and time in Calc
Use the Format Cells dialog box
Navigate between cells and
Select items in rows and columns
As an assignment Open Spreadsheet-Practice.ods
Under Serial Number 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
The video at the following link summarises the Spoken Tutorial project. Please download and watch it
We conduct workshops using Spoken Tutorials and give certificates. For more details, please write to us.
Please post your timed queries in this forum.
Spoken Tutorial project is funded by MHRD, Govt. of India.
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.