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

Title: Using Charts and Graphs

Author: Arvind PillaiKeywords: LibreOffice, Calc, Ubuntu, Creating charts in Calc, Editing charts in Calc, Formatting charts in Calc, Resizing charts in Calc, Moving charts in Calc

Slide: Title Welcome to the Spoken Tutorial on Using Charts and Graphs in Spreadsheets.
Slide: Learning Objectives In this tutorial, we will learn how to:
  • Create
  • Edit
  • Resize
  • Format
  • Move and
  • Delete

charts in LibreOffice Calc

Slide: System Requirements This tutorial is recorded using
  • Ubuntu Linux OS version 18.04 and
  • LibreOffice Suite version 6.3.5
Slide: Charts
  • 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
Open Personal-Finance-Tracker.ods Let us 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
  • Please download and extract the file
  • Make its copy and use it for practising
Point to the border Notice that all cell borders are incomplete.

Let us sort this out first.

Click on cell A1 >> holding the Shift key, click on cell H8 Select cells A1 to H8.
Click on the Borders icon Dropdown

Click >> Last border option

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

Select the option that will display all borders.

Click on any empty cell Click anywhere outside the selected area to deselect the cells.
Only Narration Our chosen border style is now applied to all the selected cells.
Hold Ctrl key and click on D and G columns

Click on the Column icon in the Standard toolbar >> select Delete entire Columns option

We do not need the Days and Date columns anymore.

So, let us delete them as shown.

Point to Spent column

Click on D3 and type>> 6500

Type 1000 in D4

Type 625 in D5

Type 310 in D6

Type 2700 in D7

Let us fill the amount spent for each item listed in the Spent column.
Point to Received column

Click on E3 and type 500

Type 200 in E4

Type 75 in E5

Type 10 in E6

Type 700 in E7

Next, we’ll fill the amounts received for each item listed in the Received column.
Point to the amounts Let us now format the values in Spent and Received columns to INR currency format.
Click on cell D3 >> Press Shift key >> click on Cell E7 Select the cells D3 to E7, by holding the Shift key and clicking like this.
Click on >> Format as Currency

Scroll down and click on INR ₹ English India

On the Formatting toolbar, select the dropdown of the Format as Currency icon.

Scroll down and select the option INR ₹ English India.

Click on any empty cell Click anywhere outside the selected area to deselect the cells.
Only Narration Now our spreadsheet has the necessary data we need to proceed ahead.
Only Narration So, let us see how to create a chart for this data.
Click Cell A1

Press and hold Shift key >> Click on cell E7

Point to the Name Box

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.

Point >> Insert Chart icon Now, click on the Insert Chart icon in the Standard toolbar.
Click on Insert

Click on Chart

Alternatively, we can click on the Insert menu in the menu bar.

And then click on the Chart option.

Point to Chart Wizard dialog box Either ways, the Chart Wizard dialog box opens.

It shows several options to select.

Click on the Finish button For now, keep the default settings and click on the Finish button at the bottom.
Point to the chart in the spreadsheet A chart gets inserted in the spreadsheet based on our data.
Narration only But, the chart is covering up some of the data.

So we have to resize the Chart.

Point to the handles on the chart When the chart is selected, we see handles around it.
Click on the corner handle and increase/decrease the size of the chart Click and drag any of the corner handles to resize the chart proportionally.
Point to the chart Now let’s move the chart.
Click on the chart

Click and move the chart to the left of the spreadsheet >>

Release the mouse button

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 outside the Chart Click anywhere else outside the Chart to deselect it.

Now, we can see our data and the Chart clearly.

Double click on the Chart

Point to Formatting toolbar

To customise the Chart, double-click on it.

Immediately, the Formatting toolbar shows only the options related to the Chart.

Only Narration If you wish to exit the Chart edit mode, click anywhere outside the Chart.
Only Narration First, we will learn to add a Title to this chart.
Click the Titles icon Click on the Titles icon on the Formatting toolbar.
Point to Titles dialog box The Titles dialog box opens.
Type Finance Tracker

Only Narration

Click on OK

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.

Point to the title Observe that the title Finance Tracker, is displayed at the top center of the chart.
Only Narration Next, let us learn how to change the Chart Type.
Click the Chart Type icon Click on the Chart Type icon on the Formatting toolbar.
Point to Chart Type dialog box The Chart Type dialog box opens.
Point to Chart Types

Point to Column

On the left we can see a list of available Chart Types.

Column type is selected by default.

Point to the preview and hover the mouse on each one to display the tool tips

Point to Normal

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.

Check the 3D Look option >> point to the spreadsheet in the background also If we check the 3D Look option, the chart will get displayed in three dimensions.
Point to Shape option Immediately we see that the Shape option gets enabled.
Select the Cylinder option >> Subtype as Deep

Click on OK button

Select the shape as Cylinder and also select the sub-type as Deep.

Then click on the OK button at the bottom.

Point to the Chart Observe the change in the chart on the spreadsheet.
Only Narration Explore the other combinations on your own later.
Only Narration Next, let us learn how to change the Data Range.
Click the Data Ranges icon Click on the Data Ranges icon on the Formatting toolbar.
Point to Data Ranges dialog box The Data Ranges dialog box opens.
Point to Data range text field Using this option, we can manually edit the range of data we want to represent in the chart.
Point to Data series in columns The default option for plotting data is Data series in columns.
Click on Data series in rows But we can change how we want to plot the data.

Let’s change this and use Data series in rows.

Only Narration This is useful if we use a style of chart such as Column to display our data.
Point to First row as label

Check First column as label

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.

Click on Data Range text field

Change E to D in the Data range field.

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.

Only Narration The data under the column Received will no longer be represented when the chart is ready.

Click on Data Series

Next, we’ll see the functions of the Data Series.

So, click on the Data Series tab.

Point to Data Series list in the middle Notice there are 6 fields representing the column headings of our data.
Point to the Add and Remove buttons The Add and Remove buttons will allow us to add or remove rows of data from the chart.
Click on the Up button and then on the Down button We can also sort the data by using the Down and Up buttons.

Click on OK

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.

Point to the chart Observe all the changes we made in the chart.
Only Narration Next, let us learn how to further customise the Chart area.
Point on Chart Area icon Click on the Chart Area icon from the Formatting toolbar.
Point to tabs Borders, Area and Transparency

Point to Borders tab

There are 3 tabs at the top - Borders, Area and Transparency.

By default, the Borders tab is selected.

Point to the Preview area There’s a large white box here which is the preview area.
Only Narration Let us change the style and color of the chart's border.
Click on Style >> choose Continuous

Click on Color >> choose Green

To do so, click on the Style drop-down and choose Continuous.

Then in the Color drop-down, we’ll choose Green.

Point to the Preview area Notice the changes in the preview area.
Click on OK Now click on the OK button at the bottom.
Click outside the chart Click outside the chart to see all the changes.
Point to the chart border Notice that the style and color of the chart border has changed.
Only Narration Let us check out a few more formatting options.
Double click on the chart Make sure the chart is in Edit mode only.
Point to the 4 axis icons.

Only Narration

On the Formatting toolbar, there are 4 axis icons.

These axis options help to format the lines that create the chart.

Only Narration 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 >> X Axis dialog box Click on the X Axis icon in the Formatting toolbar.

The X Axis dialog box opens.

Point to options Here we can see various tabs at the top to customise the X Axis.
Press Esc Key or Click on Cancel button Press the Esc key or click on the Cancel button at the bottom to exit the dialog box.
Point on the Y Axis icon Likewise, we can also format the Y Axis by clicking on the Y Axis icon in the Formatting toolbar.
Only Narration Explore the other axis icons on your own later.
Only Narration Calc provides an option for changing the colour of the chart wall.
Point to the chart The chart wall is the border surrounding the chart graphic, including the main title and key.
Point to Chart Wall icon

Point to Chart Wall dialog box

To format, click on the Chart Wall icon in the Formatting toolbar.

The Chart Wall dialog box opens up.

Point to Borders tab Go to the Borders tab if not already selected.
Point to Continuous Under the Line Properties option, we can see that the Style is set to Continuous.
Click on Color->choose Red From the Color drop-down, let’s choose Red.
Click on Width >> Click + icon 0.10cm Then change the width to 0.10cm by clicking the plus icon.
Click OK Now click on the OK button at the bottom.
Point to chart ; Click anywhere in the spreadsheet We can see the style and colour of the chart wall has now changed.
Only Narration

Point to the Legend in the chart

Next, we will learn how to remove and add the Legend.

In our chart, this is the Legend.

Click on Legend ON\OFF icon

Point to the legend area

Click on the Legend ON/OFF icon in the Formatting toolbar.

The legend disappears.

Click on Legend ON/OFF icon >>

Point to the legend

Click again on the Legend ON/OFF icon and the legend reappears.
Narration only 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.

Right click on the chart

Choose Position and Size

Now right-click on the chart.

From the context menu, select Position and Size.

Point to Position and Size dialog box The Position and Size dialog box appears.
Point to Position and Size tab Select the Position and Size tab at the top.
Point to the X and Y positions

Point to the Width and Height fields

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.

Set X coordinate to 1.00

Set Y coordinate to 0.80

Click on OK

Let’s set the X coordinate to 1.00 and Y coordinate to 0.80.

And click on the OK button at the bottom.

Point to the sample chart in the spreadsheet Notice that the position of the chart has changed according to the assigned values.

Click Format menu

Right click on the chart

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.

Click on the Chart >>Press Delete key

Point to the spreadsheet

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.

Ctrl + S Now let’s 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:
  • Create charts, resize and move them within the spreadsheet
  • Edit and format the charts and
  • Delete a chart
Slide: Assignment 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
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 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, Pravin1389