Opening a CSV file

Comma-separated-values (CSV) files are spreadsheet files in a text format where cell contents are
separated by a character, for example a comma or semicolon. Each line in a CSV text file
represents a row in a spreadsheet. Text is entered between quotation marks; numbers are entered
without quotation marks.

Tip
Most CSV files come from databases tables, queries, or reports, where further
calculations and charting are required. On Microsoft Windows, CSV files often have the
XLS file name extension to look like an Excel file, but they are still CSV files internally.

To open a CSV file in Calc:

1) Choose File > Open on the Menu bar and locate the CSV file that you want to open.
2) Select the file and click Open. By default, a CSV file has the extension .csv. However,
some CSV files may have a .txt extension.
3) The Text Import dialog (Figure 95) opens. Here you can select the various options
available when importing a CSV file into a Calc spreadsheet.
4) Click OK to open and import the file.

Figure 95: Text Import dialog

Chapter 5 Getting Started with Calc | 121

The options for importing CSV files into a Calc spreadsheet are as follows:
• Import
– Character Set – specifies the character set to be used in the imported file.
– Language – determines how the number strings are imported.

If Language is set to Default for CSV import, Calc will use the globally set language. If
Language is set to a specific language, that language will be used when importing
numbers.
– From Row – specifies the row where you want to start the import. The rows are visible
in the preview window at the bottom of the dialog.
• Separator Options – specifies whether the data uses separators or fixed widths as
delimiters.
– Fixed width – separates fixed-width data (equal number of characters) into columns.
Click on the ruler in the preview window to set the width.
– Separated by – select the separator used in the data to delimit the data into columns. If
you select Other, specify the character used to separate data into columns. This custom
separator must also be contained in the data.
– Merge delimiters – combines consecutive delimiters and removes blank data fields.
– Text delimiter – select a character to delimit text data.
• Other options
– Quoted fields as text – when this option is enabled, fields or cells whose values are
quoted in their entirety (the first and last characters of the value equal the text delimiter)
are imported as text.
– Detect special numbers – when this option is enabled, Calc will automatically detect all
number formats, including special number formats such as dates, time, and scientific
notation. The selected language also influences how such special numbers are
detected, since different languages and regions many have different conventions for
such special numbers.
When this option is disabled, Calc will detect and convert only decimal numbers. The
rest, including numbers formatted in scientific notation, will be imported as text. A
decimal number string can have digits 0-9, thousands separators, and a decimal
separator. Thousands separators and decimal separators may vary with the selected
language and region.
• Fields – shows how the data will look when it is separated into columns.
– Column type – select a column in the preview window and select the data type to be
applied the imported data.
– Standard – Calc determines the type of data.
– Text – imported data are treated as text.
– US English – numbers formatted in US English are searched for and included
regardless of the system language. A number format is not applied. If there are no US
English entries, the Standard format is applied.
– Hide – the data in the column are not imported.

122 | Getting Started with LibreOffice 5.1

Saving spreadsheets
To save a spreadsheet, see Chapter 1 Introducing LibreOffice for more details on how to save files
manually or automatically. Calc can also save spreadsheets in a range of formats and also export
spreadsheets to PDF, HTML, and XHTML file formats; see the Calc Guide Chapter 6 Printing,
Exporting, and E-mailing for more information.

Saving in other spreadsheet formats

If you need to exchange files with users who are unable to receive spreadsheet files in Open
Document Format (ODF) (*.ods), which Calc uses as default format, you can save a spreadsheet
in another format.
1) Save the spreadsheet in Calc spreadsheet file format (*.ods).
2) Select File > Save As on the Menu bar to open the Save As dialog (Figure 96).

Figure 96: Save As dialog

3) In File name, you can enter a new file name for the spreadsheet.
4) In File type drop-down menu, select the type of spreadsheet format you want to use.
5) If Automatic file name extension is selected, the correct file extension for the
spreadsheet format you have selected will be added to the file name.
6) Click Save.
7) Each time you click Save, the Confirm File Format dialog opens (Figure 97). Click Use
[xxx] Format to continue saving in your selected spreadsheet format or click Use ODF
Format to save the spreadsheet in Calc ODS format.
8) If you select Text CSV format (*.csv) for your spreadsheet, the Export Text File dialog
(Figure 98) opens. Here you can select the character set, field delimiter, text delimiter, and
so on to be used for the CSV file.

Chapter 5 Getting Started with Calc | 123

 Figure 97: Confirm File Format dialog

Figure 98: Export Text File dialog for CSV files

Tip
To have Calc save documents by default in a file format other than the default ODF
format, go to Tools > Options > Load/Save > General. In Default file format and
ODF settings > Document type, select Spreadsheet, then in Always save as, select
your preferred file format.

Navigating within spreadsheets

Calc provides many ways to navigate within a spreadsheet from cell to cell and sheet to sheet. You
can generally use the method you prefer.

Cell navigation
When a cell is selected or in focus, the cell borders are emphasized. When a group of cells is
selected, the cell area is colored. The color of the cell border emphasis and the color of a group of
selected cells depends on the operating system being used and how you have set up LibreOffice.
• Using the mouse – place the mouse pointer over the cell and click the left mouse button.
To move the focus to another cell using the mouse, simply move the mouse pointer to the
cell where you want the focus to be and click the left mouse button.
• Using a cell reference – highlight or delete the existing cell reference in the Name Box on
the Formula Bar (Figure 92 on page 118). Type the new cell reference of the cell you want
to move to and press Enter key. Cell references are case-insensitive: for example, typing
either a3 or A3 will move the focus to cell A3.

124 | Getting Started with LibreOffice 5.1

 • Using the Navigator – press the F5 key to open the Navigator dialog (Figure 99) or click
the Navigator Tab button in the open Sidebar. Type the cell reference into the Column and
Row fields and press the Enter key.
• Using the Enter key – pressing Enter moves the cell focus down in a column to the next
row. Pressing Shift+Enter moves the focus up in a column to the previous row.
• Using the Tab key – pressing Tab moves the cell focus right in a row to the next column.
Pressing Shift+Tab moves the focus to the left in a row to the previous column.
• Using the arrow keys – pressing the arrow keys on the keyboard moves the cell focus in
the direction of the arrow pressed.
• Using Home, End, Page Up and Page Down
– Home moves the cell focus to the start of a row.
– End moves the cell focus to the last cell on the right in the row that contains data.
– Page Down moves the cell focus down one complete screen display.
– Page Up moves the cell focus up one complete screen display.

Figure 99: Navigator dialog in Calc

Sheet navigation
Each sheet in a spreadsheet is independent of the other sheets, though references can be linked
from one sheet to another. There are three ways to navigate between sheets in a spreadsheet:
• Using the Navigator – when the Navigator is open (Figure 99), double-click on any of the
listed sheets to select the sheet.
• Using the keyboard – use key combinations Ctrl+Page Down to move one sheet to the
right and Ctrl+Page Up to move one sheet to the left.
• Using the mouse – click on one of the sheet tabs at the bottom of the spreadsheet to
select that sheet.
If your spreadsheet contains a lot of sheets, then some of the sheet tabs may be hidden behind the
horizontal scroll bar at the bottom of the screen. If this is the case:
• Using the four buttons to the left of the sheet tabs can move the tabs into view (Figure 100).
• Dragging the scroll bar edge to the right may reveal all the tabs.

Chapter 5 Getting Started with Calc | 125

 • Right-clicking on any of the arrows opens a context menu where you can select a sheet
(see Figure 101).

Note
When you insert a new sheet into a spreadsheet, Calc automatically uses the next
number in the numeric sequence as a name. Depending on which sheet is open when
you insert a new sheet, and the method you use to insert a new sheet, the new sheet
may not be in numerical order. It is recommended to rename sheets in a spreadsheet
to make them more recognizable.

Move to the first sheet

Move to the next sheet on the left
Move to the next sheet on the right
Move to the last sheet
Insert a new sheet after the last sheet

Sheet tabs

Figure 100: Navigating sheet tabs

Figure 101: Right-click any arrow button

Keyboard navigation
To navigate a spreadsheet using the keyboard, pressing a key or a combination of keys. For a key
combination, press more than one key at the same time. Table 4 lists the keys and key
combinations you can use for spreadsheet navigation in Calc.
Table 4. Keyboard cell navigation
Keyboard
Cell navigation
shortcut
→ Moves cell focus right one cell.
← Moves cell focus left one cell.
↑ Moves cell focus up one cell..
↓ Moves cell focus down one cell
Ctrl+→ Moves cell focus to the first column on the right containing data in that row if cell
focus is on a blank cell.
Moves cell focus to the last column on the right in the same range of occupied
cells in that row if cell focus is on a cell containing data.
Moves cell focus to the last column on the right in the spreadsheet if there are no
more cells containing data.

126 | Getting Started with LibreOffice 5.1

 Keyboard
Cell navigation
shortcut
Ctrl+← Moves cell focus to the last column on the left containing data in that row if cell
focus is on a blank cell.
Moves cell focus to the first column on the left in the same range of occupied
cells in that row if cell focus is on a cell containing data.
Moves cell focus to the first column in that row if there are no more cells
containing data.
Ctrl+↑ Moves cell focus from a blank cell to the first cell above containing data in the
same column.
Moves cell focus to the first row in the same range of occupied cells if cell focus is
on a cell containing data.
Moves cell focus from the last cell containing data to the cell in the same column
in the last row of the spreadsheet.
Ctrl+↓ Moves cell focus from a blank cell to the first cell below containing data in the
same column.
Moves cell focus to the last row in the same range of occupied cells in that
column if cell focus is on a cell containing data.
Moves cell focus from the last cell containing data to the cell in the same column
in the last row of the spreadsheet.
Ctrl+Home Moves cell focus from anywhere on the spreadsheet to Cell A1 on the same
sheet.
Ctrl+End Moves cell focus from anywhere on the spreadsheet to the last cell in the lower
right-hand corner of the rectangular area of cells containing data on the same
sheet.
Alt+Page Down Moves cell focus one screen to the right (if possible).
Alt+Page Up Moves cell focus one screen to the left (if possible).
Ctrl+Page Down Moves cell focus to the same cell on the next sheet to the right in sheet tabs if the
spreadsheet has more than on sheet.
Ctrl+Page Up Moves cell focus to the same cell on the next sheet to the left in sheet tabs if the
spreadsheet has more than on sheet.
Tab Moves cell focus to the next cell on the right.
Shift+Tab Moves cell focus to the next cell on the left.
Enter Moves cell focus down one cell (unless changed by user).
Shift+Enter Moves cell focus up one cell (unless changed by user).

Customizing the Enter key

You can customize the direction in which the Enter key moves the cell focus by going to Tools >
Options > LibreOffice Calc > General. Select the direction cell focus moves from the drop-down
list. Depending on the file being used or the type of data being entered, setting a different direction
can be useful. The Enter key can also be used to switch into and out of editing mode. Use the first
two options under Input settings in Figure 102 to change the Enter key settings.

Chapter 5 Getting Started with Calc | 127

 Figure 102: Customizing the Enter key

Selecting items in a spreadsheet

Selecting cells
Single cell
Left-click in the cell. You can verify your selection by looking in the Name Box on the Formula Bar
(Figure 92 on page 118).

Range of contiguous cells

A range of cells can be selected using the keyboard or the mouse.
To select a range of cells by dragging the mouse cursor:
1) Click in a cell.
2) Press and hold down the left mouse button.
3) Move the mouse around the screen.
4) Once the desired block of cells is highlighted, release the left mouse button.
To select a range of cells without dragging the mouse:
1) Click in the cell which is to be one corner of the range of cells.
2) Move the mouse to the opposite corner of the range of cells.
3) Hold down the Shift key and click.

Tip
You can also select a contiguous range of cells by first clicking in the Selection mode
field on the Status Bar (Figure 94 on page 120) and selecting Extending selection
before clicking in the opposite corner of the range of cells. Make sure to change back
to Standard selection or you may find yourself extending a cell selection
unintentionally.

To select a range of cells without using the mouse:

1) Select the cell that will be one of the corners in the range of cells.
2) While holding down the Shift key, use the cursor arrows to select the rest of the range.

128 | Getting Started with LibreOffice 5.1

 Tip
You can also directly select a range of cells using the Name Box. Click into the Name
Box on the Formula Bar (Figure 92 on page 118). To select a range of cells, enter the
cell reference for the upper left-hand cell, followed by a colon (:), and then the lower
right-hand cell reference. For example, to select the range that would go from A3 to C6,
you would enter A3:C6.

Range of non-contiguous cells

1) Select the cell or range of cells using one of the methods above.
2) Move the mouse pointer to the start of the next range or single cell.
3) Hold down the Ctrl key and click or click-and-drag to select another range of cells to add to
the first range.
4) Repeat as necessary.

Selecting columns and rows

Single column or row
To select a single column, click on the column header (Figure 91 on page 117).
To select a single row, click on the row header.

Multiple columns or rows

To select multiple columns or rows that are contiguous:
1) Click on the first column or row in the group.
2) Hold down the Shift key.
3) Click the last column or row in the group.
To select multiple columns or rows that are not contiguous:
1) Click on the first column or row in the group.
2) Hold down the Ctrl key.
3) Click on all of the subsequent columns or rows while holding down the Ctrl key.

Entire sheet
To select the entire sheet, click on the small box between the column headers and the row headers
(Figure 103), or use the key combination Ctrl+A to select the entire sheet, or go to Edit on the
Menu bar and select Select All.

Figure 103: Select All box

Chapter 5 Getting Started with Calc | 129

Selecting sheets
You can select one or multiple sheets in Calc. It can be advantageous to select multiple sheets,
especially when you want to make changes to many sheets at once.

Single sheet
Click on the sheet tab for the sheet you want to select. The tab for the selected sheet becomes
white (default Calc setup).

Multiple contiguous sheets

To select multiple contiguous sheets:
1) Click on the sheet tab for the first desired sheet.
2) Move the mouse pointer over the sheet tab for the last desired sheet.
3) Hold down the Shift key and click on the sheet tab.
4) All tabs between these two selections will turn white (default Calc setup). Any actions that
you perform will now affect all highlighted sheets.

Multiple non-contiguous sheets

To select multiple non-contiguous sheets:
1) Click on the sheet tab for the first desired sheet.
2) Move the mouse pointer over the sheet tab for the second desired sheet.
3) Hold down the Ctrl key and click on the sheet tab.
4) Repeat as necessary.
5) The selected tabs will turn white (default Calc setup). Any actions that you perform will now
affect all highlighted sheets.

All sheets
Right-click a sheet tab and choose Select All Sheets from the context menu.

Working with columns and rows

Inserting columns and rows

Note
When you insert columns or rows, the cells take the formatting of the corresponding
cells in the next column to left or the row above.

Single column or row

Using the Sheet menu:
1) Select a cell, column, or row where you want the new column or row inserted.
2) Go to Sheet on the Menu bar and select either Insert > Columns > Columns Left or >
Columns Right or Insert > Rows > Rows Above or > Rows Below.

130 | Getting Started with LibreOffice 5.1