LibreOffice Logo

Calc Guide 24.2

Chapter 4Formatting Data

Make your data shine

Copyright

This document is Copyright © 2024 by the LibreOffice Documentation Team. Contributors are listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

To this edition

Steve Fanning

Olivier Hallot

B. Antonio Fernández

 

To previous editions

Skip Masonsmith

Dan Lewis

Steve Schwettman

Roman Kuznetsov

Peter Schofield

Alain Romedenne

flywire

Jochen Schiffers

Andrew Pitonyak

Steve Fanning

Robert Großkopf

Jean-Pierre Ledure

Jenna Sargent

Jost Lange

Drew Jensen

Pulkit Krishna

Martin Fox

Randolph Gamo

Jean Hollis Weber

Hazel Russman

Olivier Hallot

B. Antonio Fernández

 

 

Feedback

Please direct any comments or suggestions about this document to the Documentation Team’s forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration is required) or send an email to: loguides@community.documentfoundation.org.

Note

Everything you send to a forum, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted. Emails sent to the forum are moderated.

Publication date and software version

Published March 2024. Based on LibreOffice 24.2 Community.Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS

Some keystrokes and menu items are different on macOS from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this document. For a detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to this guide.

Windows or Linux

macOS equivalent

Effect

Tools > Options on Menu bar

LibreOffice > Preferences on Menu bar

Access to setup options

Right-click

Ctrl+click and/or right-click depending on computer setup

Opens a context menu

Ctrl or Control

and/or Cmd or Command, depending on keyboard

Used with other keys

Alt

and/or Alt or Option depending on keyboard

Used with other keys

Introduction

Cell formatting is an important feature of any modern spreadsheet software including Calc. Calc formatting resources use an extensive set of attributes to enhance the visual display of relevant information of your spreadsheet. Manual and style formatting as well as conditional formatting are addressed in this chapter.

Formatting data

Note

All the settings discussed in this section can also be set as a part of the cell style. See Chapter 5, Using Styles and Templates, for more information.

You can format the data in Calc in several ways, either defined as part of a cell style so that it is automatically applied, or applied manually to the cell. For more control and extra options, select a cell or cell range and use the Format Cells dialog. All of the format options are discussed below.

Multiple lines of text

Multiple lines of text can be entered into a single cell using automatic wrapping or manual line breaks. Each method is useful for different situations.

Automatic wrapping

To automatically wrap multiple lines of text in a cell, use one of the following methods:

Method 1

1)  Select a cell or cell range.

2)  Go to Format > Cells on the Menu bar, or right-click and select Format Cells in the context menu, or press Ctrl+1, to open the Format Cells dialog.

3)  Click on the Alignment tab (Figure 1).

4)  Under Properties, select Wrap text automatically and click OK.

Method 2

1)  Select the cell.

2)  On the Properties deck of the Sidebar, open the Alignment panel (Figure 2).

3)  Select the Wrap text option to apply the formatting immediately.

Method 3

1)  Select the cell.

2)  Click on the toolbar Wrap Text tool.

Figure 1: Format Cells dialog – Alignment tab

Format Cells dialog – Alignment tab

Figure 2: Wrap text formatting

Wrap text formatting

Manual line breaks

To insert a manual line break while typing in a cell, press Ctrl+Enter. When editing text, double-click the cell, then reposition the cursor to where you want the line break. In the Input line of the Formula bar, you can also press Shift+Enter.

When a manual line break is entered, the cell row height changes but the cell width may not change and the text may still overlap the end of the cell. You have to change the cell width manually or reposition the line break.

Shrinking text to fit a cell

The font size of the data in a cell can automatically adjust to fit inside cell borders:

1)  Select a cell or cell range.

2)  Go to Format > Cells on the Menu bar, or right-click and select Format Cells in the context menu, or press Ctrl+1, to open the Format Cells dialog.

3)  Click on the Alignment tab (Figure 1).

4)  Under Properties, select Shrink to fit cell size and click OK.

Formatting numbers

Several number formats can be applied to cells by using icons on the Formatting toolbar (highlighted in Figure 3). Select the cell, then click the relevant icon to change the number format.

For more control or to select other number formats, use the Numbers tab of the Format Cells dialog (Figure 1 on page Figure 1):

Figure 3: Number icons on Formatting toolbar

Number icons on Formatting toolbar

Formatting fonts

To select a font and format it for use in a cell:

1)  Select a cell or cell range.

2)  Click the down arrow on the right of the Font Name box on the Formatting toolbar (highlighted in Figure 4) and select a font in the drop-down list. A font is temporarily applied on selected cells by hovering or navigating in the Font Name drop-down list. The font can also be changed using the Font tab on the Format Cells dialog.

3)  Click on the down arrow on the right of the Font Size box on the Formatting toolbar and select a font size from the drop-down list. The font size can also be changed using the Font tab on the Format Cells dialog.

4)  To change the character format, click on the Bold, Italic, or Underline icons on the Formatting toolbar.

5)  To change the paragraph alignment, click on one of the alignment icons (Align Left, Align Center and Align Right). The Format > Align menu also provides these options in addition to the Justified alignment.

Figure 4: Font Name and Size on Formatting toolbar

Font Name and Size on Formatting toolbar

Note

To specify the language used in the cell, open the Font tab on the Format Cells dialog. Changing language in a cell allows different languages to exist within the same document. For more changes to font characteristics, see “Font effectsbelow.

Tip

To choose whether to show the font names in their font or in plain text, go to Tools > Options > LibreOffice > View and select or deselect the Show preview of fonts option in the Font Lists section. For more information, see Chapter 15, Setting up and Customizing.

Font effects

1)  Select a cell or cell range.

2)  Right-click and select Format Cells in the context menu, or go to Format > Cells on the Menu bar, or press Ctrl+1, to open the Format Cells dialog.

3)  Click on the Font Effects tab (Figure 5).

Figure 5: Format Cells dialog – Font Effects tab

Format Cells dialog – Font Effects tab

4)  Select the font effect you want to use from the options available. The options available are described in Chapter 5, Using Styles and Templates.

5)  Click OK to apply the font effects and close the dialog.

Any font effect changes are applied to the current selection, or to the entire word that contains the cursor, or to any new text that you type.

Text orientation

To change the text direction within a cell, use the Alignment tab on the Format Cells dialog (Figure 1 on page Figure 1):

1)  On the Alignment tab of the Format Cells dialog, select the Reference edge from which to rotate the text as follows:

2)  Click on the small indicator at the edge of the text orientation dial and rotate it until you reach the required degrees.

3)  Alternatively, enter the number of degrees to rotate the text in the Degrees box.

4)  Select Vertically stacked to make the text appear vertically in the cell.

An Asian layout mode checkbox is available on the Alignment tab of the Format Cells dialog when Asian language support is enabled and the text direction is set to vertical. This option aligns Asian characters one below the other in the selected cell(s). If the cell contains more than one line of text, the lines are converted to text columns that are arranged from right to left. Western characters in the converted text are rotated 90 degrees to the right. Asian characters are not rotated.

Using the Formatting toolbar tools

The tools on the Formatting toolbar can be used as follows after the cell has been selected:

Note

The text direction icons can only be made available if the Asian and Complex text layout options are checked under Tools > Options > Languages and Locales > General > Default Languages for Documents. If it is necessary to make the buttons visible, right-click on the toolbar and select Visible Buttons in the context menu, then click on the icon you require and it will be placed on the Formatting toolbar.

Asian typography

If Asian language support is enabled through Tools > Options > Languages and Locales > General > Default Languages for Documents > Asian, an Asian Typography tab is included on the Format Cells dialog (Figure 6). This tab enables setting of typographic options for cells in Asian language documents.

Figure 6: Format Cells dialog - Asian Typography tab

Format Cells dialog - Asian Typography tab

The following options are provided:

Formatting cell borders

To format the borders of a cell or a group of selected cells, you can use the border icons on the Formatting toolbar to apply the default styles to borders, or the Format Cells dialog for greater control. See Chapter 5, Using Styles and Templates, for more information on the options:

Note

Cell border properties apply only to the selected cells and can only be changed if you are editing those cells. For example, if cell C3 has a top border, that border can only be removed by selecting C3. It cannot be removed in C2, even though it appears to be the bottom border for cell C2.

1)  Select a cell or a range of cells.

2)  Go to Format > Cells on the Menu bar, or right-click and select Format Cells in the context menu, or press Ctrl+1, to open the Format Cells dialog.

3)  On the Borders tab (Figure 7), select the options required.

4)  Click OK to close the dialog and save the changes.

Alternatively, use the icons on the Formatting toolbar to apply default styles to borders:

1)  Click the Borders icon and select one of the options displayed in the Borders palette.

2)  Click the Border Style icon and select one of the line styles from the Border Style palette.

3)  Click the Border Color icon to apply the most recently selected color. Click the down arrow to the right of the Border Color icon to select another color from the Border Color palette.

Note

When entering borders with the border icons on the Formatting toolbar, you have two choices: click the required icon to add a border to the present borders or Shift-click to add a border and remove the present borders.

Figure 7: Format Cells dialog – Borders tab

Format Cells dialog – Borders tab

Formatting cell backgrounds

To format the background color for a cell or a group of cells (see Chapter 5, Using Styles and Templates, for more information):

1)  Select a cell or a range of cells.

2)  Go to Format > Cells on the Menu bar, or right-click and select Format Cells in the context menu, or press Ctrl+1, to open the Format Cells dialog.

3)  On the Background tab, click the Color button and select a color from the color palette.

4)  Click OK to save the changes and close the dialog.

Alternatively, click on the Background Color icon on the Formatting toolbar to apply the most recently selected color. Click the down arrow to the right of the Background Color icon to select a different color from the Background Color palette.

AutoFormat of cells and sheets

Using AutoFormat

You can use AutoFormat to format a group of cells:

1)  Select the cells in at least three columns and rows, including column and row headers, that you want to format.

2)  Go to Format > AutoFormat Styles on the Menu bar to open the AutoFormat dialog (Figure 8).

3)  Select the type of format and format color in the list.

4)  Select the formatting properties to be included in the AutoFormat function.

5)  Click OK to apply the changes and close the dialog.

Figure 8: AutoFormat dialog

AutoFormat dialog

Defining a new AutoFormat

You can define a new AutoFormat so that it becomes available for use in all spreadsheets:

1)  Format the data type, font, font size, cell borders, cell background, and so on for a group of cells.

2)  Select a cell range of at least 4x4 cells.

3)  Go to Format > AutoFormat Styles to open the AutoFormat dialog. The Add button is now active (if a range smaller than 4x4 cells is selected, the Add button will be unavailable).

4)  Click Add.

5)  In the Name box of the Add AutoFormat dialog that opens, type a meaningful name for the new format and click OK.

6)  The new AutoFormat is now available in the Format list on the AutoFormat dialog. Click OK to close the AutoFormat dialog.

Note

Autoformatting does not use cell styles. It applies direct formatting to the selected range. The new Autoformat is stored in the user profile and is not part of the spreadsheet document. This means that you can reuse the new AutoFormat in another spreadsheet document using your profile but under a different profile or different user, the new AutoFormat will not be listed. See Chapter 5, Using Styles and Templates.

Value highlighting

Sometimes your spreadsheet can contain data of different types in the same column, row or range. For example, a column with dates can have a string (text) written in the same format as the default date format for the column. Because strings and numbers are treated differently in some important functions like AVERAGE, the spreadsheet can display wrong results if your data has mixed types.

Value highlighting displays cell contents in different colors depending on the type of content. An example of value highlighting is shown in Figure 9:

Figure 9: Example of value highlighting

Example of value highlighting

The value highlighting colors override any colors used in formatting. This color change applies only to the colors seen on a display. When a spreadsheet is printed, the original colors used for formatting are printed.

Go to View > Value Highlighting on the Menu bar, or use the keyboard shortcut Ctrl+F8, to turn the function on or off. When value highlighting is switched off, the original formatting colors are used for display.

You can make value highlighting the default when opening a spreadsheet in Calc, by selecting Tools > Options > LibreOffice Calc > View > Display > Value highlighting. This default mode for value highlighting may not be what you want if you are going to format the cells for printing.

Using conditional formatting

You can set up cell formats to change depending on conditions that you specify. Conditional formatting is used to highlight data that is outside the specifications that you have set. It is recommended not to overuse conditional formatting as this could reduce the impact of data that falls outside those specifications.

Note

Conditional formatting depends upon the use of styles and the AutoCalculate feature must be enabled. If you are not familiar with styles, see Chapter 5, Using Styles and Templates, for more information.

Setting up conditional formatting

1)  Ensure that AutoCalculate is enabled: Data > Calculate > AutoCalculate.

2)  Select the cells where you want to apply conditional formatting.

3)  Go to Format > Conditional > Condition (Figure 14), Color Scale (Figure 19), Data Bar (Figure 20), Icon Set (Figure 22), or Date (Figure 18), on the Menu bar to open the Conditional Formatting dialog. Any conditions already defined are displayed.

4)  Click Add to create and define a new condition. Repeat this step as necessary.

5)  Select a style from the styles already defined in the Apply Style drop-down list. Repeat this step as necessary.

6)  Alternatively, select New Style to open the Cell Style dialog (Figure 23) and create a new cell style. Repeat this step as necessary.

7)  Click OK to save the conditions and close the dialog. The selected cells are now set to apply a result using conditional formatting.

Figure 10: Conditional formatting toolbar icon

Conditional formatting toolbar icon

Tip

Although each condition type can be accessed using a different option in the Format > Conditional menu of the Menu bar, the five variants of the Conditional Formatting dialog shown in Figures Figure 14 to Figure 18 are not distinct. Once the dialog is open you can create conditions of all types without interacting with the Menu bar. For example, you might create Condition 1 to select a cell style to be used if the cell takes a certain value (Condition 1 is of type “Condition”). You might then press the Add button to create Condition 2 by selecting All Cells in the condition’s upper left drop-down and then selecting Data Bar in the adjacent drop-down (Condition 2 is of type “Data Bar”). You might then press the Add button to create Condition 3 by selecting Date is in the condition’s upper left drop-down (Condition 3 is of type “Date”). In this way you can create many conditions of different types to control the conditional formatting of the selected cells.

Types of conditional formatting

The Condition dialog is the starting point when using conditional formatting. Here you can define what formats to use to highlight any data in the spreadsheet that meets the conditions you have defined.

Cell value

Applies the selected style to the cell or cell range controlled by the condition set in the drop down list. The formatting is applied to each cell individually and the condition may depend on other cells values of the selected range. Valid conditions are described in tables 1, 2 and 3.

Figure 11: Conditional Formatting dialog – Cell value

Conditional Formatting dialog – Cell value

Table 1: Conditions for number and text in cells

Condition

Applies the selected style to the cell...

is equal to

...when the cell value equals the user defined value in text box in the right. Use text inside quotes if you compare text values.

is not equal to

the cell value is not equal (different) to the user defined value in the text box in the right. Use text inside quotes if you compare text values.

is duplicate

Applies the selected style to the cell when at least one other cell in the range has equal contents.

is not duplicate

Applies the selected style to the cell when the cell contents is unique in the range.

begins with

Applies the selected style to the cell when the cell contents begins with the text or number defined in the right text box.

See note on text conversion below.

ends with

Applies the selected style to the cell when the cell contents ends with the text or number defined in the right text box.

See note on text conversion below.

contains

Applies the selected style to the cell when the cell contents contains the text or number defined in the right text box.

See note on text conversion below.

does not contain

Applies the selected style to the cell when the cell contents does not contains the text or number defined in the right text box.

See note on text conversion below.

Note on text conversion conditions

The condition applies to the internal text conversion of the cell contents. Numeric values are compared with their equivalent text representation. Numeric cell formats (currency, scientific, user-defined ...) are not considered for comparisons.

Table 2: Conditions for number-only cell values

Condition

Applies the selected style to the cell when the cell value is...

is less than

strictly less than the user defined value in the text box in the right.

is greater than

strictly greater than the user defined value in the text box in the right.

is less than or equal to

less than or equal to the user defined value in the text box in the right.

is greater than or equal to

greater than or equal to the user defined value in the text box in the right.

is between

between the two values defined in the text boxes on the right – lower and upper values – including the boundary values themselves.

is not between

not between the two values defined in the text boxes on the right – lower and upper values – including the boundary values themselves.

is in top N elements

Between the maximum value in the range and the Nth greater element of the same. Enter the value of N in the text box on the right.

is in bottom N elements

Between the minimum value in the range and the Nth lower element of the same. Enter the value of N in the text box on the right.

is in top N percent

In the top N percent of the number of cells in the range. For example, in a 20 cells range and N equals 20, the style is applied to the 4 last cells of the range. Enter the value of N in the text box on the right.

is in bottom N percent

In the bottom N percent of the number of cells in the range. For example, in a 20 cells range and N equals 20, the style is applied to the 4 first cells of the range. Enter the value of N in the text box on the right.

is above average

strictly greater than the average of the cell range values.

See note below on average function.

is below average

the cell value is strictly less than the average of the cell range values.

See note below on average function.

is above or equal average

greater or equal than the average of the cell range values.

See note below on average function.

is below or equal average

is less or equal than the average of the cell range values.

See note below on average function.

Note on AVERAGE function in conditions

The AVERAGE function ignores any text or empty cell within a data range. If you suspect wrong results from this function, look for text in the data ranges. To highlight text contents in a data range, use the Value highlighting feature above.

Table 3: Conditions for errors in cells

Condition

Applies the selected style to the cell...

is error

When the cell is in an error condition defined in the right text box. See Appendix B "Error Codes".

Tip

A cell that references another cell with an error condition is not in error itself.

is not error

When the cell is not in an error condition defined in the right text box. See Appendix B "Error Codes".

Color Scale

Use Color Scale to set the background color of cells depending on the values of the data in those cells. Color Scale can only be used when All Cells has been selected for the condition. You can use either two or three colors for the color scale or gradient.

Figure 12: Conditional Formatting dialog – Color Scale

Conditional Formatting dialog – Color Scale

The Color Scale (2 entries ) requires the definition of the colors for the minimum and the maximum values in the range. These two values can be calculated in several ways:

Figure 13: Color scale with 2 and 3 colors

Color scale with 2 and 3 colors

The Color scale (3 entries) allows a third color for an intermediate data value. The third color can be defined as for the Color scale (2 entries) above and with two more options:

Data Bar

Data bars provide a graphical representation of data in the spreadsheet. The graphical representation is based on the values of data in a selected range. Click on More Options in the Conditional Formatting dialog to define how the data bars will look. Data bars can only be used when All Cells has been selected for the condition.

Figure 14: Conditional Formatting dialog – Data Bar

Conditional Formatting dialog – Data Bar

To format all cells with data bars, you must set the minimum and maximum values of the data range. The condition options are the same as the Color Scale above with the addition of:

Figure 15: Data bars

Data bars

Data bar options

Click on the More Options button to select attributes of the data bars. The Data Bar dialog opens (Figure 16):

Entry values

Defines the minimum and maximum values for the data bar formatting. See the dropdown items described in the minimum and maximum of the Color Scale above.

Bar Colors

Set the color for positive and negative values. Set the bar fill mode:

Axis

Set the position of the vertical axis for the data bar. Values are:

Bar Lengths

Sets the minimum (maximum) length of the data bars, as a percentage of the column width.

Display bars only

Do not display values in the cell, only the data bars.

Figure 16: Data bars options

Data bars options

Icon Set

Icon sets display an icon next to the data in each selected cell to give a visual representation of where the cell data falls within the defined range that you set. The icon sets available include colored arrows, gray arrows, colored flags, colored signs, symbols, bar ratings, and quarters (Figure 19 and Figure 20). Icon sets can only be accessed when the Conditional Formatting dialog has been opened and All Cells has been selected for the condition (Figure 17).

Figure 17: Conditional Formatting dialog – Icon Set

Conditional Formatting dialog – Icon Set

Figure 18 show the icon set used in conditional formatting to show the evolution of data in two datasets.

Figure 18: Arrows icon set used to indicate evolution in data

Arrows icon set used to indicate evolution in data

Figure 19: Icon sets with 3 icons

Icon sets with 3 icons

Figure 20: Icon sets with 4 and 5 icons

Icon sets with 4 and 5 icons

Date

Date applies a defined style depending on a date range chosen in the drop-down menu. Date range options are formed by applying This, Last, and Next adjectives to the available periods for Day, Week, Month, or Year with. Last 7 days is another option available. Examples include Tomorrow (the word for next day), Last 7 days, This week, Next month, Last year.

Figure 21: Conditional Formatting dialog - Date

Conditional Formatting dialog - Date

Note

The start and end of the week is locale dependent (set in Tools > Options > Languages and Locales > General).

Figure 22: Condition for dates in next week.

Condition for dates in next week.

Formula is

Applies the selected style to the cell when the formula expression in the text box in the right is not zero.

The formula is expressed similar to a test condition evaluating to TRUE or FALSE.

Figure 23 shows different cell styles applied based on conditions for dates before and after the current date. The formulae are expressed in Table 4.

Figure 23: Conditions to set current, past, and future dates using formulas

Conditions to set current, past, and future dates using formulas

Table 4: Current, past and future deadlines with formulas

Deadlines

Formula is

Style applied

Current date

F3 = TODAY()

"Neutral"

Past date

F3 < TODAY()

"Bad"

Future date

F3 > TODAY()

"Good"

Conditional formatting management

To see all the conditional formatting defined in the spreadsheet and any styles used:

1)  Go to Format > Conditional > Manage on the Menu bar to open the Manage Conditional Formatting dialog (Figure 24).

Figure 24: Manage Conditional Formatting dialog

Manage Conditional Formatting dialog

2)  Select a range in the Range list and click Edit to redefine the conditional formatting.

3)  Select a range in the Range list and click Remove to delete the conditional formatting. The deletion is immediate with no confirmation.

4)  Select Add to create a new definition of conditional formatting.

5)  Click OK to save the changes and close the dialog.

Copying cell styles

To apply the style used for conditional formatting to other cells later:

1)  Click one of the cells that has been assigned conditional formatting and copy the cell to the clipboard.

2)  Select the cells that are to receive the same formatting as the copied cell.

3)  Go to Edit > Paste Special > Paste Special on the Menu bar, or right-click and select Paste Special > Paste Special in the context menu, or press Ctrl+Shift+V, to open the Paste Special dialog (described in the “Paste Special dialog” section in Chapter 2, Entering and Editing).

4)  Make sure that only Formats is selected and click OK to paste the conditional formatting into the cell.

Hiding and showing data

In Calc you can hide elements so that they are neither visible on a computer display nor printed when a spreadsheet is printed. However, hidden elements can still be selected for copying if you select the elements around them; for example, if column B is hidden, it is copied when you select to copy columns A to C. When you require a hidden element again, you can reverse the process and show the element.

Hiding data

Sheets

Select Sheet > Hide Sheet on the Menu bar, or right-click on the sheet tab for the sheet to be hidden and select Hide Sheet in the context menu. There must always be one sheet that is not hidden.

Rows and columns

1)  Select a cell in the row or column you want to hide.

2)  Go to Format on the Menu bar and select Rows or Columns.

3)  Select Hide from the menu and the row or column can no longer be viewed or printed.

4)  Alternatively, right-click on the row or column header and select Hide Rows or Hide Columns in the context menu.

Tip

To enable/disable a visual indicator of hidden columns and rows, go to View > Hidden Row/Column Indicator.

Cells

Hiding individual cells is more complicated. First, you need to define the cells as protected and hidden; then you need to protect the sheet:

1)  Select the cells you want to hide.

2)  Go to Format > Cells on the Menu bar, or right-click and select Format Cells in the context menu, or press Ctrl+1, to open the Format Cells dialog (Figure 25).

3)  Click the Cell Protection tab and select an option for hiding and printing the cells.

4)  Click OK to save the changes and close the dialog.

5)  Go to Tools > Protect Sheet on the Menu bar, or right-click on the sheet tab and select Protect Sheet in the context menu, to open the Protect Sheet dialog (Figure 26).

6)  Select Protect this sheet and the contents of protected cells.

7)  Create a password and then confirm the password. The dialog provides a password strength meter to indicate the strength of the entered password. This incorporates a colored bar to reflect password strength, with red indicating a weak password and green indicating a strong password. In addition, the longer the colored bar, the greater the strength of the password.

8)  Select or deselect the options in the Allow all users of this sheet to area so that users can select protected or unprotected cells.

9)  Click OK to save the changes and close the dialog.

Figure 25: Format Cells dialog – Cell Protection tab

Format Cells dialog – Cell Protection tab

Note

When content in cells is hidden, it is only the content contained in the cells that is hidden and the protected cells cannot be modified. The blank cells remain visible in the spreadsheet.

Figure 26: Protect Sheet dialog

Protect Sheet dialog

Showing data

Sheets

Select Sheet > Show Sheet on the Menu bar, or right-click on any sheet tab and select Show Sheet in the context menu. Choose which hidden sheets to show from the list on the Show Sheet dialog. If there are no hidden sheets, the Show Sheet option will not appear in the context menu and will be grayed on the Menu bar.

Rows and columns

1)  Select the rows or columns on each side of the hidden row or column.

2)  Go to Format on the Menu bar and select Rows or Columns. Select Show in the menu and the row or column will be displayed and can be printed.

3)  Alternatively, right-click on a row or column header and select Show Rows or Show Columns in the context menu.

Cells

1)  Go to Tools > Protect Sheet on the Menu bar, or right-click on the sheet tab and select Protect Sheet in the context menu, to open the Protect Sheet dialog (Figure 26).

2)  Enter the password to unprotect the sheet and click OK.

3)  Go to Format > Cells on the Menu bar, right-click and select Format Cells in the context menu, or press Ctrl+1, to open the Format Cells dialog (Figure 25).

4)  Click the Cell Protection tab and deselect the hide options for the cells. Click OK.

Note

When protecting a sheet using the Protect Sheet dialog, you can leave the password fields blank. In this case, the Protect Sheet dialog is not presented at step above and step is not necessary.

Contents