Welcome to the Spoken Tutorial on Using Charts and Graphs in Spreadsheets.
In this tutorial, we will learn how to:
Create, Edit, Resize
Format, Move and Delete charts in LibreOffice Calc
This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Charts can powerfully convey information to the reader
LibreOffice Calc offers a variety of chart formats for our data
Using Calc, we can customize charts to a considerable extent
Let us 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 its copy and use it for practising
Notice that all cell borders are incomplete. Let us sort this out first.
Select cells A1 to H8.
Then go to the Formatting toolbar and select the dropdown next to the Borders icon.
Select the option that will display all borders.
Click anywhere outside the selected area to deselect the cells.
Our chosen border style is now applied to all the selected cells.
We do not need the Days and Date columns anymore.
So, let us delete them as shown.
Let us fill the amount spent for each item listed in the Spent column.
Next, we’ll fill the amounts received for each item listed in the Received column.
Let us now format the values in Spent and Received columns to INR currency format.
Select the cells D3 to E7, by holding the Shift key and clicking like this.
On the Formatting toolbar, select the dropdown of the Format as Currency icon.
Scroll down and select the option INR ₹ English India.
Click anywhere outside the selected area to deselect the cells.
Now our spreadsheet has the necessary data we need to proceed ahead.
So, let us see how to create a chart for this data.
To create a chart, first we have to select the data to be included in the chart.
Select cells A1 to E7.
The selection can be verified in the Name Box at the top left.
Now, click on the Insert Chart icon in the Standard toolbar.
Alternatively, we can click on the Insert menu in the menu bar. And then click on the Chart option.
Either ways, the Chart Wizard dialog box opens. It shows several options to select.
For now, keep the default settings and click on the Finish button at the bottom.
A chart gets inserted in the spreadsheet based on our data.
But, the chart is covering up some of the data. So we have to resize the Chart.
When the chart is selected, we see handles around it.
Click and drag any of the corner handles to resize the chart proportionally.
Now let’s move the chart.
Place the cursor on any one of the borders till the cursor turns into a hand.
Click and move the chart to the desired location and then release the mouse button.
Click anywhere else outside the Chart to deselect it.
Now, we can see our data and the Chart clearly.
To customise the Chart, double-click on it.
Immediately, the Formatting toolbar shows only the options related to the Chart.
If you wish to exit the Chart edit mode, click anywhere outside the Chart.
First, we will learn to add a Title to this chart.
Click on the Titles icon on the Formatting toolbar.
The Titles dialog box opens.
In the Title text field, type Finance Tracker.
Leave the rest of the fields as default.
Then click on the OK button at the bottom.
Observe that the title Finance Tracker, is displayed at the top center of the chart.
Next, let us learn how to change the Chart Type.
Click on the Chart Type icon on the Formatting toolbar.
The Chart Type dialog box opens.
On the left we can see a list of available Chart Types.
Column type is selected by default.
On the right side, we can see three sub-types of Column charts.
This will vary based on the selected Chart type.
We will retain the Normal sub-type Column chart.
If we check the 3D Look option, the chart will get displayed in three dimensions.
Immediately we see that the Shape option gets enabled.
Select the shape as Cylinder and also select the sub-type as Deep.
Then click on the OK button at the bottom.
Observe the change in the chart on the spreadsheet.
Explore the other combinations on your own later.
Next, let us learn how to change the Data Range.
Click on the Data Ranges icon on the Formatting toolbar.
The Data Ranges dialog box opens.
Using this option, we can manually edit the range of data we want to represent in the chart.
The default option for plotting data is Data series in columns.
But we can change how we want to plot the data.
Let’s change this and use Data series in rows.
This is useful if we use a style of chart such as Column to display our data.
Next, we can choose whether to use First row as label, or First column as label, or both as labels on the axes of the chart.
This is my selection.
Now, suppose we wish to remove the data under the column Received.
To do that, first click inside the Data range field.
Then change the range from $A$1 is to $E$7 to $A$1 is to $D$7.
The data under the column Received will no longer be represented when the chart is ready.
Next, we’ll see the functions of the Data Series.
So, click on the Data Series tab.
Notice there are 6 fields representing the column headings of our data.
The Add and Remove buttons will allow us to add or remove rows of data from the chart.
We can also sort the data by using the Down and Up buttons.
Now we have finished selecting the data and how to represent the data in the chart.
So we will click on the OK button at the bottom.
Observe all the changes we made in the chart.
Next, let us learn how to further customise the Chart area.
Click on the Chart Area icon from the Formatting toolbar.
There are 3 tabs at the top - Borders, Area and Transparency. By default, the Borders tab is selected.
There’s a large white box here which is the preview area.
Let us change the style and color of the chart's border.
To do so, click on the Style drop-down and choose Continuous.
Then in the Color drop-down, we’ll choose Green.
Notice the changes in the preview area.
Now click on the OK button at the bottom.
Click outside the chart to see all the changes.
Notice that the style and color of the chart border has changed.
Let us check out a few more formatting options.
Make sure the chart is in Edit mode only.
On the Formatting toolbar, there are 4 axis icons.
These axis options help to format the lines that create the chart.
These also format the font of the text that appears on both the X and Y axes. Let’s check these out.
Click on the X Axis icon in the Formatting toolbar. The X Axis dialog box opens.
Here we can see various tabs at the top to customise the X Axis.
Press the Esc key or click on the Cancel button at the bottom to exit the dialog box.
Likewise, we can also format the Y Axis by clicking on the Y Axis icon in the Formatting toolbar.
Explore the other axis icons on your own later.
Calc provides an option for changing the colour of the chart wall.
The chart wall is the border surrounding the chart graphic, including the main title and key.
To format, click on the Chart Wall icon in the Formatting toolbar. The Chart Wall dialog box opens up.
Go to the Borders tab if not already selected.
Under the Line Properties option, we can see that the Style is set to Continuous.
From the Color drop-down, let’s choose Red.
Then change the width to 0.10cm by clicking the plus icon.
Now click on the OK button at the bottom.
We can see the style and colour of the chart wall has now changed.
Next, we will learn how to remove and add the Legend. In our chart, this is the Legend.
Click on the Legend ON/OFF icon in the Formatting toolbar. The legend disappears.
Click again on the Legend ON/OFF icon and the legend reappears.
Next we will learn how to resize charts by using the Position and Size dialog box. Click anywhere outside the Chart to exit the edit mode.
Now right-click on the chart. From the context menu, select Position and Size.
The Position and Size dialog box appears.
Select the Position and Size tab at the top.
The Position section has various fields that can set the X and Y positions of the chart.
We can also adjust the width and height of the chart under the Size section.
Let’s set the X coordinate to 1.00 and Y coordinate to 0.80.
And click on the OK button at the bottom.
Notice that the position of the chart has changed according to the assigned values.
Apart from these options available in the Formatting toolbar, we can format the chart in two other ways.
Either go to the Format menu in the menu bar. Or right click on the chart.
To see the formatting options, make sure that the chart is in edit mode. Explore these options on your own later.
Lastly let’s learn how to delete a chart.
Click on the chart and press the Delete key on the keyboard.
Notice that the chart is now deleted from the spreadsheet.
Now let’s save and close the file.
This brings us to the end of this tutorial. Let us summarize.
In this tutorial, we learnt to: Create charts, resize and move them within the spreadsheet
Edit and format the charts and Delete a chart
As an assignment Open Spreadsheet-Practice.ods file
Insert a Pie chart for the data . Resize and move the chart to the bottom right corner of the sheet
The video at the following link summarizes the spoken tutorial project. Please download and watch it.
The Spoken Tutorial Project Team conducts workshops and gives 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.