|00:01||Welcome to the spoken tutorial on Working with Cells.|
|00:06||In this tutorial, we will learn to:|
|00:09||Enter numbers, text, date and time in a spreadsheet|
|00:15||Use the Format Cells dialog box|
|00:19||Navigate between cells and|
|00:22||Select items in rows and columns|
|00:27|| This tutorial is recorded using
Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
|00:41||First we will learn how to enter data in the cells.|
|00:46||Let’s open our Personal-Finance-Tracker.ods file.|
|00:52||This file has been provided to you in the Code files link on this tutorial page.|
|00:59||Please download and extract the file.|
|01:03||Make a copy and then use it for practising.|
|01:08|| Now type some text in any empty cell by clicking on the cell and typing using the keyboard.
|01:18||The text is left-aligned, by default.|
|01:22||One can change the alignment by clicking any one of the Alignment icons on the Formatting Bar.|
|01:30||Let's undo what we typed.|
|01:33||Press Ctrl+Z keys on the keyboard to undo.|
|01:39||In this spreadsheet, we had already typed the column headings earlier.|
|01:45||In Calc, we can differentiate the headings from other data by adding a style to it.|
|01:53||To do that, click on cell A1.Then hold the left mouse button and drag the cursor upto cell G1.|
|02:04||Now release the left mouse button.|
|02:07||We can see that all the headings got highlighted.|
|02:12||Now click on Styles menu in the menu bar.|
|02:16|| We can see various heading styles.
I will select Accent 2.
|02:24||Now click randomly on any cell to unselect the headings.|
|02:30||Notice that the headings are now highlighted with a grey colour background.|
|02:36|| In the Items column, we’ll type the names of a few items one below the other.
Type the text as shown here.
|02:47|| In order to enter numbers in the cell, click on the cell and type the number.
Then press Enter.
|02:57||Remember to always press the Enter key after every data-entry.|
|03:03||To enter a negative number, either type a minus sign before it or enclose it within parentheses.|
|03:13||By default, numbers are right-aligned and negative numbers have a leading minus symbol.|
|03:20||Let us delete these entries.|
|03:23|| Select all the cells to be deleted by holding the Shift key and clicking each cell.
Now press the Delete key on the keyboard.
|03:36||In the SN column, we want the serial number of each item one below the other.|
|03:43||So click on cell A2 and enter numbers as 1, 2, 3 one below the other.|
|03:53||Remember to press Enter key after typing each number.|
|03:58||In order to auto-fill the serial numbers in the subsequent cells, click on the cell A4.|
|04:06||Click on a little black box that is visible at the bottom-right corner of the cell.|
|04:13||Drag it till cell A7 and release the mouse button.|
|04:19||We see that cells A5, A6 and A7 get auto-populated with the subsequent serial numbers.|
|04:28||We will now enter the cost of each item under the heading Cost.|
|04:35||Click on the cell C3 and type the expense for House rent as Rupees 6000.00|
|04:45||Now, what if we want the number with the Rupee symbol?|
|04:51||Clear the value typed in cell C3 by pressing Ctrl and Z keys together.|
|04:59||Right-click on the cell C3 and select the Format Cells option.|
|05:05||This will open the Format Cells dialog box.|
|05:09||The first tab is Numbers. Click on it if not already selected.|
|05:16||Here we can see various options under Category.|
|05:21||Number, Percent, Currency, Date, Time and many more.|
|05:29||We will select Currency.|
|05:31||Various currency symbols from across the world are displayed in the Format list.|
|05:39||By default, INR Rupee English (India) is selected in the Format drop-down.|
|05:46||If not, click on the drop-down and select it from the displayed list.|
|05:51||Below the Format drop-down, we can see some sample values based on our selection.|
|05:58||We see minus Rupee 1234 decimal zero zero is selected for INR Rupees English India.|
|06:10||We can see the selected format in the small preview area on the right.|
|06:16||Below the Category section, we have another section named as Options.|
|06:22||Using this, we can increase or decrease the number of Decimal places and Leading zeroes.|
|06:30||When we do so, we can see the changes in the sample value and in the preview area.|
|06:37||We will retain the default values.|
|06:41||To add a comma between the values, check the Thousands separator checkbox.|
|06:48||One can also change the font style by clicking on the Font tab.|
|06:54||It has various options for Family, Style and Size.|
|06:59||Explore Font Effects and other tabs to learn more about them on your own.|
|07:07||We will learn about the options in the Alignment tab in a later tutorial.|
|07:13||Let’s click on the OK button at the bottom right corner.|
|07:18||Now type 6000 in cell C3 and press Enter.|
|07:24||Notice that the number 6000 is represented as Rupees 6000 with 2 decimal places.|
|07:34||Now, let's select cells C4 to C7 by holding the Shift key and clicking the cells.|
|07:43||Hold the CTRL key and select cell G2 also.|
|07:48||Notice all the selected cells are highlighted.|
|07:52||Right-click on any of the highlighted cells and select Format Cells.|
|07:59||Choose the same options as before.|
|08:03||Click on the OK button at the bottom right corner.|
|08:07||Click any cell in the spreadsheet to deselect the cells.|
|08:13||Next, we type the amount for all the items one below the other, as shown here.|
|08:22||Under the heading Account, we’ll type the salary for the month as 30,000.|
|08:29|| Let’s move on to the Date column.
Click on the cell F2.
|08:36||For entering date in Calc, select the cell and type the date, as shown.|
|08:42||Here I have used a forward slash between the date elements.|
|08:47||We can also separate the date elements with a hyphen.|
|08:52||Or use text such as 05 June 2020.|
|08:59||Calc recognizes a variety of date formats.|
|09:02||We can also customize the date formats according to our requirement.|
|09:09||To do so, right-click on the cell and select Format Cells option.|
|09:16|| Click on the Numbers tab.
Under the Category section, select Date.
|09:22|| Under the Format section, select the date format based on your requirement.
I will choose 31/12/1999.
|09:36||Notice the display in the preview area.|
|09:40|| Based on our selection, the Format code will get updated.
In my case it is DD, MM and YYYY.
|09:52||Once we have updated the format, click on the OK button at the bottom right.|
|09:58||Next, let us try some time options.|
|10:02|| Click on any random cell.
Type the time as shown.
|10:08||I have separated the time elements with colons.|
|10:12||To further customize the time format, right-click on the respective cell and select Format Cells.|
|10:20||Click on Numbers tab at the top|
|10:23|| Under Category, choose Time and the desired format under Format.
I will choose 13:37:46.
|10:35||Notice the display in the preview area.|
|10:39||Also, the Format code is displayed below as HH:MM:SS|
|10:48||One can change the Format code directly as well.|
|10:51||Click on OK button at the bottom right.|
|10:56||Let us delete all the time entries from our spreadsheet.|
|11:01||We will now learn how to navigate within a spreadsheet from cell to cell.|
|11:07||We can access a particular cell by simply clicking on it with the cursor.|
|11:13||This changes the focus to the new cell.|
|11:17||This method is most useful when the two cells are far apart.|
|11:22||Another method of accessing a particular cell is by using the cell reference.|
|11:28||Click on the Name Box at the top left corner, which is located below the Font Name.|
|11:35||Erase the existing cell reference and type the cell reference of the cell you want to go.|
|11:42||I will type B4 and press Enter.|
|11:47||Observe that cell B4 gets highlighted.|
|11:52||We can also navigate between cells using the keyboard.|
|11:57||Press the Tab key to go to the next cell in a row.|
|12:02||Shift + Tab to go to the previous cell in a row.|
|12:07||Enter to go to the next cell in a column.|
|12:12||Shift + Enter to go to the previous cell in a column.|
|12:17||Next, we’ll learn how to select a range of contiguous cells with the cursor.|
|12:24||First click on a cell and hold down the left mouse button.|
|12:29||Drag the cursor on the sheet.|
|12:32||Once the desired block of cells is highlighted, release the left mouse button.|
|12:38||We see that the selected cells get highlighted.|
|12:42||Next, we’ll learn how to select multiple columns or rows that are contiguous.|
|12:50||Click on the first column or row that you want to select.|
|12:55||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.
|13:08||Let’s learn to select multiple columns or rows that are not contiguous.|
|13:14||Click on the first column or row that you want to select.|
|13:19||Hold down the Control key.|
|13:21||Now click on all of the subsequent columns or rows that you want to select.|
|13:29||We see that the selected cells get highlighted.|
|13:34||Save the file by pressing Ctrl+S keys on the keyboard.|
|13:40||Now close the file by clicking the X icon on the top right corner.|
|13:46||This brings us to the end of this tutorial, let us summarize.|
|13:52|| In this tutorial, we learnt to:
Enter numbers, text , date and time in Calc
|14:01||Use the Format Cells dialog box|
|14:05||Navigate between cells and|
|14:09||Select items in rows and columns|
|14:13|| As an assignment
|14:19||Under SN type the serial number from 1 to 5 one below the other and Align Center.|
|14:27||Add column headings Date and Time with the same text format|
|14:33||Enter some values in those columns|
|14:37||Use the Format Cells dialog box options to format the cells|
|14:43||Refer to the Code files link for the data.|
|14:47||Save and close the file|
|14:50|| The video at the following link summarises the Spoken Tutorial project.
Please download and watch it
|14:58|| We conduct workshops using Spoken Tutorials and give certificates.
For more details, please write to us.
|15:08||Please post your timed queries in this forum.|
|15:13||Spoken Tutorial project is funded by MHRD, Govt. of India.|
|15:19|| 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.