- 1 Sources
- 2 Documentation Team Weekly Meeting
- 3 Preface
- 4 Introducing Calc
- 5 Entering, Editing, and Formatting Data.
- 6 Using Charts and Graphs.
- 7 Using Styles and Templates.
- 8 Using Graphics in Calc.
- 9 Printing, Exporting, & E-mailing.
- 10 Formulas and Functions.
- 11 Using Pivot Tables.
- 12 Data Analysis.
- 13 Linking Calc Data
- 14 Sharing and Reviewing.
- 15 Calc Macros
- 16 Calc as a Simple Database
- 17 Setting Up & Customizing Calc
- 18 Appendix A - Keyboard Shortcuts.
- 19 Appendix B - Calc Functions.
- 20 Appendix C - Calc Error Codes.
- 21 Out of Topics
Documentation Team Weekly Meeting
Our documentation team meeting takes place every Monday at 18:00 CET (Berlin time).
We will use the following JITSI meeting room: https://meet.jit.si/tdfdocteam
The meeting room is supposed to work will all browsers without the need of a specific plugin.
The documentation pad for the minute is http://pad.documentfoundation.org/p/documentation
Main recurring topics
- Check & balance of the task assigned
- Issues found, Q&A
- continuation of the topics evaluation for 6.x
- Improvements in Calc 4.2:Right-click context menu on bottom left scrollbar arrows to switch sheets (Caolán McNamara)
- Improvements in Calc 4.3 : Number selected rows and columns in status bar. When selecting cells, the number of selected rows and columns is shown in the status bar. tdf#64290 (Manmeet Singh)
- Improvements in Calc 4.3: Sheet tab area: when expanding the space available for sheet tabs make better use of that. (Michael Meeks)
- Improvements in Calc 4.3: Start cell edit with content of cell above: Allow starting of cell edit with the content of the cell above it as its initial content. It is bound to the Ctrl+' (single quote) key by default. (Note: On some locales this keyboard shortcut may conflict with the system default shortcuts. If the default shortcut does not work due to conflict, bind it to a different shortcut). (Kohei Yoshida)
- Improvements in Calc 4.4:Toolbar improvements. The “Standard” and “Formatting” toolbars were reorganized by removing uncommonly used commands and replacing them with frequent features, in order to reduce the need for users to hunt through menus or customize toolbars. Additional buttons have been added to the toolbars, but are hidden by default and can be enabled by using the toolbar right-click context menu “Customize Toolbar...” option or Tools ▸ Customize.... tdf#85594 (Yousuf Philips)
- Improvements in Calc 4.4: Sheet context menu. The right-click context menu that appear for managing sheets now has icons for sheet operations. tdf#86476 (Yousuf Philips)
- ReleaseNotes-4.4 Technical Dictionary:Technical Dictionary. Various updates have been made to the technical dictionary. New Ubuntu and Debian release names have been added, as well as more Operating System names. Additionally, Obsolete Ubuntu release names (before Ubuntu 10.04 LTS) were removed from the technical dictionary.
- ReleaseNotes-4.4 Media file formats: Media file formats. More media file types are now accessible from Insert ▸ Audio/Video: Real Audio (.ra), Real Media (.rm), Digital Video (.dv), Audio Codec (.ac3), Ogg Opus (.opus), Advanced Systems (.asf), MPEG Audio (.m4a). tdf#82994 (Daniel Sikeler and Caolán McNamara)
- ReleaseNotes-4.4 Context menu changes: Context menu changes.Cut, Copy and Paste actions are now at the top of the context menu. tdf#71770 tdf#84293 (Jeffrey Stedfast, Xamarin; Yousuf Philips)
- ReleaseNotes-4.4 Edit / Read-only mode: Edit / Read-only mode. LibreOffice will now display an infobar to indicate visibly when a document is being opened in read-only mode. This infobar replaces the “Edit file” icon in the main toolbar, as its labeling and usage patterns proved confusing for users. Activation and deactivation of read-only mode is also available from within the menu Edit ▸ Edit Mode or through the keyboard shortcut Ctrl+⇧ Shift+M. tdf#80538 tdf#80536 (Yousuf Philips, Samuel Mehrbrodt and Joren De Cuyper)
- Improvements Calc 5.0 Formula Engine A:A / 1:1 entire column/row references tdf#44419 (Eike Rathke)
- Improvements Calc 5.0 Filters Calc can now import Apple Numbers files (Numbers '09 and older), via libetonyek. (Anurag Kanungo, 2015) This can import only very simple spreadsheets currently. Wait for 5.1 for more. Calc can now import Lotus 123 (wk3, wk4) and Quattro Pro files (wq1, wq2), via libwps. (Laurent Alonso)
- Improvements Calc 5.0 Sidebar changes Sections of the Properties tab in Calc were reorganized to remove unnecessary features and make it fit on smaller screen resolutions. tdf#90888 (Rishabh Kumar, Yousuf Philips)
- Improvements Calc 5.1 A quick look at some of the new features in LibreOffice Calc 5.1: -New commands to add rows below and columns right. tdf#90882 (Philippe Jung) - Data Sources keyboard shortcut was changed to Ctrl+⇧ Shift+F4 and F4 was assigned to toggling cell references. tdf#59418 (Dennis Francis)- Organize sheet level context menus. commit (Yousuf Philips)
- Improvements Calc 5.1 Filters New import filters. Gnumeric is now an officially supported import format (does not work on Windows, see tdf#94834) (It was supported as part of the experimental features for some time) (Markus Mohrhard)
- Improvements Calc 5.1 New Menu The main menus in Writer, Calc and Impress were reorganized to improve usability, group similar functions together, add functions that were not present and sorting items based on their usage and importance. blog entry
- Improvements Calc 5.1 Sidebar Increase and decrease font size buttons added to sidebar properties pane in Calc. tdf#86179 (Maxim Monastirsky) , *-Cell reference edge added to alignment section of sidebar in Calc. tdf#91802 (Rishabh Kumar, 2015) *-Cell styles added to properties pane in Calc. tdf#73071 (Rishabh Kumar, 2015; Yousuf Philips; Maxim Monastirsky)
- Improvements Calc 5.3 Toolsbars Toolbars: "Open Template" has been added to the "Open" dropdown (Samuel Mehrbrodt, CIB) commit
- Improvements Calc 5.4 Calc : Increased amount of rows available in the Online Calc to 10.000 (Marco Cecchetti, Collabora)
- Improvements Calc 6.0 : Protection, cells, sheets. Added new command to select unprotected cells on protected or unprotected sheet. Located Edit▸ Select▸ Select Unprotected Cells. tdf#95883 (Gülşah Köse, Eike Rathke). If a tab is protected, the lock symbol (🔒) appears at the beginning of the tab name. tdf#95880 (Gülşah Köse, Eike Rathke)
- Improvements Calc 6.0 : Filters. Implemented a new import filter from QuarkXPress v.3, v.4. For this, the libqxp library was created (Alex Pantechovskis).
- Improvements Calc 6.0 : Server part: Use seccomp’s Berkley Packet Filters to drastically restrict the kernel APIs surface available to our contained document processes (Michael Meeks, Ashod Nakashian, Collabora). Support for hashed admin passwords, and loolconfig tool (Michael Meeks, Pranav Kant, Jan Holesovsky, Collabora). New authorization method via a custom http header via WOPI (Jan Holesovsky, Collabora). Extended document conversion API (Jan Holesovsky, Collabora). General:New Insert Special Characters dialog (Henry Castro, Collabora) . State of the Change tracking is now visible in the menu via check marks (Pranav Kant, Collabora) . Improved animation of the comments (Henry Castro, Collabora) . Content Security Policy now includes the Referer too, for easier integration in multi-host environments (Pranav Kant, Collabora) . New file types that can be edited were added or improved, including PDF and HTML (Ashod Nakashian, Collabora) . Support for avatars: The integrators can specify a pictures / avatars for the users of the documents via WOPI extension. (Ashod Nakashian, Pranav Kant, Collabora). Find & replace dialog (in addition to the previously available Search functionality) (Aditya Dewan, GsoC). Support for custom buttons in the toolbar (Pranav Kant, Collabora) . Automatic detection of links in the comments (Pranav Kant, Collabora) . Improvements in the Admin Console (Aditya Dewan, GSoC) . List of users of a given document . Indication whether documents are modified. Switch to follow the editor in the list of users (Aditya Dewan, GSoC) . Better handling of conflict between editing and uploading documents (Pranav Kant, Collabora) . Spell checking support in all Writer, Calc and Impress (Michael Meeks, Henry Castro, Collabora) . Status bar indicates the current language (Henry Castro, Collabora)
Save As support + document name in the user interface (Aditya Dewan, Jan Holesovsky, Collabora). Calc : Increased amount of rows available in the Online Calc to half million (Marco Cecchetti, Collabora) . Active cell reference (Name Box) (Marco Cecchetti, Collabora) . Basic editing of charts (Marco Cecchetti, Collabora)
Entering, Editing, and Formatting Data.
- Improvements in Calc 4.2: Implemented 'Find All' also in case of 'search in all sheets' option - creates a dialog describing matching cells tdf#39881 (Matúš Kukan)
- Improvements in Calc 4.4: Simplification of Special Character dialog. Special characters are now added to an editable field so special characters can be inserted/removed at the location of the cursor in the field. tdf#64302 tdf#85907 (Szymon Kłos; Adolfo Jayme Barrientos)
- Improvements Calc 5.0 : Scientific Format and Number Format (Laurent Balland-Poirier and Eike Rathke)
• Engineering notation: tdf#30716 ◦ enable exponent multiple of 3 (or other value) with format ##0.00E+00: set the number of digits (# or 0) before decimal separator. ◦ add format ##0.00E+00 to scientific format list in UI dialog Format ▸ Cells ▸ Numbers ▸ Scientific for all localizations. ◦ replace Thousands separator checkbox with Engineering notation for scientific formats. tdf#90258 • General format using scientific, and default scientific format, keep 2 minimum digits (instead of 3) in exponent for all localizations. tdf#88835 • Trend line equation with 1 minimum digit in exponent (instead of 3). tdf#88835 • Add/remove decimal digits of scientific format: with buttons Add Decimal Place/Delete Decimal Place tdf#88960; with Sidebar: Properties ▸ Number Format ▸ Decimal places tdf#88999; with UI dialog Format ▸ Cells ▸ Numbers ▸ Decimal places. tdf#88999 • Allow variable decimal with format 0.0##E+00 for scientific numbers and 0.0## for numbers (decimal part can contain some 0 and then some #). This format is now correctly saved in ODF files. tdf#90133 • Sign of exponent can be skipped for positive exponent with format 0.00E00 (remove sign after E). This format is now correctly saved in ODF files. tdf#90133
- Improvements Calc 5.1 Use Format mode to search data into cells: Option to search formatted display strings using find bar and also "Find & Replace" dialog. tdf#48456 (Dennis Francis)
- Improvements Calc 5.1 Custom number formats with question mark in decimal part (such as 0.???), to get decimal alignment without '0', are now saved in ODF tdf#40517 (Laurent BP)
- Improvements Calc 5.2 Text Layout
• Improve text alignment for vertical text in table. tdf#101729. (Mark Hung)
• Added Find Previous button. tdf#98417 (Akshay Deep; Yousuf Philips) • New wide layout for the Find & Replace dialog. tdf#98539 (Akshay Deep; Yousuf Philips)
• Fraction Number Formats (Laurent BP) ◦ any of #, ? or 0 can be used for any of integer, numerator or denominator format string. For instance # ?/#00 will display 3.5 as 3 1/02 and PI as 3 16/113 tdf#100755 tdf#31449 • any string can be used as delimiter between integer and fraction: #" plus fraction "?/? will display PI value as 3 plus fraction 1/7 tdf#100834 • more accurate representation of fraction with a new algorithm tdf#99996 (Wolfgang Jäger) • new default formats with forced denominator tdf#100592 • if denominator has less digits than places, it is now left aligned tdf#102507: with format # ?/??? value 3.5 is displayed as 3 1/2 (with two spaces after fraction), instead of 3 1/ 2 (old behavior) • format options available through UI dialog Format ▸ Cells ▸ Numbers and Sidebar
• Merge non empty cells Format ▸ Merge cells ▸ Merge cells brings a new option: tdf#30456 (Laurent BP)
- Improvements in Calc 5.4 : Format Cell
• Calculate with Precision as shown option (Laurent Balland-Poirier) now works also with ◦ fraction format tdf#105657 ◦ several subformats tdf#106052 ◦ engineering notation tdf#106252 ◦ thousands divisors tdf#106253
- Improvements Calc 5.4: New cell comment commands added
• Comments toggle command to show/hide all comments on currently selected sheet(s) added to View ▸ Comments. tdf#107258 (Gülşah Köse, Eike Rathke) • Delete All Comments commands available under menu Sheet ▸ Cell Comments acting on the currently selected sheet(s) tdf#107143 (Gülşah Köse) • Show All Comments and Hide All Comments commands acting on the currently selected sheet(s) added. tdf#84837 (Gülşah Köse)
- Improvements Calc 5.4 : Support negative year date (BCE) in cell input and date display. (Eike Rathke (Red Hat, Inc.))
• Accepted inputs include the ISO 8601 format -YYYY-MM-DD and locale dependent input like -Y/M/D or M/D/-Y or D.M.-Y • For BCE dates years are displayed negative unless a specified date format already includes an era code, e.g. G or GG • Last day BCE is -0001-12-31, the next day CE is 0001-01-01 • Previous versions could already calculate such dates, but did not display negative years
- Improvements Calc 5.4 : A new Cell Protection toggle command was added to Edit ▸ Cell Protection to change the protection status of the cell. tdf#95885 (Gülşah Köse)
- Improvements Calc 5.4 : Cell styles now accessible in their own dedicated Styles menu in Format ▸ Styles. tdf#91820 (Yousuf Philips)
• Standard and Percent buttons of number format became toggle. tdf#86119 (Gülşah Köse)
- Improvements Calc 6.0 : Various
-Default 2-entry color scale conditional formatting colors changed to Yellow and Green. tdf#86508 (Yousuf Philips) -Enhanced "Links" dialog tdf#113807 (Serge Krot, CIB) -Number format: accept English syntax keywords; some languages use localized keywords (AAAA for YYYY in French for instance). Now these languages can use English keywords to get valid format in any UI language tdf#33689 (Laurent Balland-Poirier) -Pasting: unformatted text The text/plain Unformatted text format results in unquoted/unescaped content as expected for external pastes. For single cell copy&paste embedded line breaks and tabs are preserved, for multiple cells they are replaced with spaces, effectively being a tab-separated-values (TSV) format. For intra-Calc on-cell pastes using the paste special toolbar button the Unformatted text [TSV-Calc] format can be used, which preserves embedded line breaks and tabs across multiple cells. tdf#113571 tdf#32213 (Eike Rathke (Red Hat, Inc.)) Added "Paste unformatted text" command with its hot key Ctrl+⇧ Shift+Alt+V tdf#50746 (Serge Krot, CIB)
Special characters dialog was reworked tdf#34882 (Akshay Deep, GSoC 2017; Samuel Mehrbrodt (CIB); Heiko Tietze; Yousuf Philips) Added search field to filter symbol's table Added list of Recent characters Added list of Favorite characters Implemented context menu for symbol's table and Recent and Favorite characters lists
Using Charts and Graphs.
- Release notes 4.2 Chart: Chart.
-Import charts from xlsx which don't have proper references but have internal data (Muthu Subramanian) -Toolbar buttons for vertical/horizontal grids change behavior to variate grid between major, major&minor and off. tdf#66422 (Tomaž Vajngerl) -Trend lines enhancements:
Support more than one trend line per series (Tomaž Vajngerl) Force intercept for trend lines tdf#40314 (Tomaž Vajngerl) Extrapolation of trend lines tdf#40316 (Tomaž Vajngerl) Polynomial trend lines tdf#35712 (Tomaž Vajngerl) Moving average trend lines tdf#40315 (Tomaž Vajngerl)
- Release notes 4.3 Chart: Chart. Property Mapping: Added property mapping functionality for charts, allowing to change data series properties based on spreadsheet values. For more details see this blog entry (Markus Mohrhard)
- Improvements Calc 5.0 Charts : Trendlines: Force Intercept option is now also available for exponential trendlines. tdf#72361 (Laurent Balland-Poirier)
- Improvements Calc 5.1 Charts : Trend Line
• Exponential and Power trend lines accept negative Y values tdf#70673 (Laurent BP) • Improve equation rendering by removing some useless 1: (Laurent BP) ◦ polynomial: f(x) = 1x^2 + 1x +1 => f(x) = x^2 + x +1 ◦ exponential: f(x) = exp( 1 x ) => f(x) = exp( x )
• Automatic Y log scale is no more forced to 1 tdf#96871 (Laurent BP) • Trend line: improve rendering of polynomial equation (Laurent BP) ◦ before f(x) = 2x^2 - x + 1 ◦ after f(x) = 2x² − x + 1 • Wrap trend line equation if it is longer than chart width tdf#94004 (Laurent BP)
- Improvements Calc 5.3 Chart : Chart
• Trend line (regression) equation: ◦ Y name (default "f(x)") and X name (default "x") can be replaced by names defined by the user tdf#100547 (Laurent BP)
Using Styles and Templates.
- Improvements Calc 5.3 Styles : -New options Styles to identified how is a result: Good, Bad, etc. Watch Video
- Improvements Calc 5.3 : -Default Cell Styles
• Default cell styles are loaded from a file in the user profile (Jaskaran Singh ()) ◦ Syntax is based on ODF and loaded through orcus ◦ Defining own default cell styles is now easy • New and improved default cell styles, tdf#90937 (UX team, Jaskaran Singh)
- Improvements Calc 6.0 : Dialogs. Calc: Width and height of page style scaling mode "Fit print range(s) to width/height" can now be set to unspecified tdf#113455 (Caolán McNamara).
Using Graphics in Calc.
- Release notes 4.4 Drawing objects: Drawing objects. You can move only the point next to the rectangle in "line callout" drawings, with ⇧ Shift key pressed. (Matúš Kukan, Collabora)
- Release notes 4.4 Image scaling:*Release notes 4.4: Image scaling. Images are now scaled proportionally by default in Calc and Impress/Draw as they were already in Writer. tdf#83808 (Samuel Mehrbrodt)
- Improvements Calc 6.0 : Save images modified in place
In previous LibreOffice versions when the user tried to save an embedded image through the "Save..." entry available in the context menu, the saved image was the original one more eventually applied filters (which are not removable). Further applied transformations like rotations, cropping, color effects were not included in the saved image. Starting from this release, LibreOffice offers the user to choose if saving the original image or the modified version through a simple pop-up dialog. The new feature is available in Writer, Calc and Draw. (Marco Cecchetti - Collabora)
Printing, Exporting, & E-mailing.
- Release notes 4.4 Digitally signed PDF export: Digitally signed PDF export. PDF's generated by LibreOffice can now be digital signed directly from LibreOffice during the export. It works on Windows, macOS, and Linux, and on Windows, the built-in certificate functionality is used to store your certificates for signing. (Gökçen Eraslan - 2012, Markus Wernig - Wilhelm Tux, fund raising, Tor Lillqvist - Collabora)
- Improvements Calc 5.0 Pdf export : PDF export supports Time-Stamp Protocol
• Export to PDF now supports the Time-Stamp Protocol (IETF standard RFC 3161). The address of a Time-Stamp Authority can be specified in the security settings of PDF options tdf#84881 (Tor Lillqvist, Collabora sponsored by Wilhelm Tux crowd-funding)
- Improvements Calc 5.0 : Maintaining invisible content in Emails
• Option added to preserve document invisible content when sending a document by email. tdf#87441 (Stephan Bergmann)
- Improvements Calc 5.1 : PNG export in LibreOffice Calc was added, as in Writer and Impress. blog entry (Miklos Vajna, Collabora)
- Improvements Calc 5.2 : Document signing
• Added support for signature descriptions and signing with the same certificate multiple times. blog entry. (Miklos Vajna, Collabora)
- Improvements Calc 5.3 : Document signing
• The bundled libxmlsec library has been upgraded to 1.2.23, including our "OOXML Relationships Transform Algorithm" implementation upstream. blog entry (Miklos Vajna, Collabora) • Added support for PDF signature import/export. Now it is possible to digitally sign existing PDF files. blog entry. (Miklos Vajna, Collabora)
- Improvements Calc 5.4 : CSV export. CSV export settings are now remembered. tdf#55236 (Bernhard Widl, CIB)
- Improvements Calc 5.4 : Document signing
• Under Linux, you can now use OpenPGP keys to sign ODF documents. tdf#89037 (Katarína Behrens, Samuel Mehrbrodt, Thorsten Behrens - CIB) • Added support for keys on Linux and macOS blog entry (Miklos Vajna - Collabora) • The signature status is now shown with colored infobars tdf#105566 (Katarína Behrens, Samuel Mehrbrodt - CIB)
- Improvements Calc 6.0 : Exporting images
A cell range selection or a selected group of shapes (images) can be exported to PNG or JPG graphics format with File ▸ Export... if the Selection checkbox is marked in the file dialog. tdf#108317 (Eike Rathke (Red Hat, Inc.))
- Improvements Calc 6.0 : New Filters. Implemented a new export filter to format EPUB from Writer. tdf#33226 [blog] (Miklos Vajna)
- Improvements Calc 6.0 : OpenPGP document signing and encryption
on all desktop platforms, you can now use OpenPGP keys to sign ODF documents. tdf#89037 (Katarína Behrens, Samuel Mehrbrodt, Thorsten Behrens - CIB) . There is (experimental) support for OpenPGP-based document encryption as well, that we hope to get into production shape until 6.0 you need auxiliary software to make use of that feature (LibreOffice will transparently merge available keys if you have the below installed): for Windows, download , for Mac, download  (some howto is here ) , for Linux, usually installing a gnupg/gpg package will be enough, perhaps with an additional keymanagement frontend like kleopatra or seahorse. Package names will depend on your Linux distribution
Formulas and Functions.
- Improvements 4.2 Calc: Extend formula range from all corners tdf#67592 (Tomaž Vajngerl)
- Improvements 4.2 Calc: Random number generation (Edit ▸ Fill ▸ Random Number...) tdf#66477 (Tomaž Vajngerl).
- Improvements 4.2 Calc: Statistics functions Data ▸ Statistics (alternative to Excel's Add-in "Analysis ToolPak") tdf#66477 (Tomaž Vajngerl): Sampling , Descriptive Statistics , Analysis of Variance (ANOVA) , Correlation , Covariance , Exponential Smoothing ,
- Improvements 4.2 Calc: Formula engine:
Added WEBSERVICE and FILTERXML spreadsheet functions for access to web data. (Markus Mohrhard) Added LEFTB, LENB, MIDB and RIGHTB spreadsheet functions working on DBCS double byte character strings. (Zhang Lu) Added COVARIANCE.P and COVARIANCE.S spreadsheet functions for MS-Excel interoperability. tdf#70000 (Winfried Donkers) Added STDEV.P and STDEV.S spreadsheet functions for MS-Excel interoperability. tdf#44134 (Winfried Donkers) Added VAR.P and VAR.S spreadsheet functions for MS-Excel interoperability. tdf#61002 (Winfried Donkers) Added BETA.DIST and BETA.INV spreadsheet functions for MS-Excel interoperability. tdf#71008 (Winfried Donkers) Added BINOM.DIST and BINOM.INV spreadsheet functions for MS-Excel interoperability. tdf#71081 (Winfried Donkers) Added CONFIDENCE.NORM and CONFIDENCE.T spreadsheet functions for MS-Excel interoperability. tdf#71350 (Winfried Donkers) Added F.DIST, F.DIST.RT, F.INV, F.INV.RT and F.TEST spreadsheet functions for MS-Excel interoperability. tdf#71436 (Winfried Donkers) Added EXPON.DIST, HYPGEOM.DIST, POISSON.DIST and WEIBULL.DIST spreadsheet functions for MS-Excel interoperability. tdf#71722 (Winfried Donkers) Added CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT,CHISQ.TEST, commit. (Winfried Donkers) Improved CRITBINOM spreadsheet function to handle large sample quantities. (Winfried Donkers) Added a new formula interpreter to enable massive parallel calculations of formula cells using GPU via OpenCL. (Kohei Yoshida, Tor Lillqvist, Michael Meeks, Markus Mohrhard, AMD, MultiCoreWare)
- Improvements 4.3 Calc: Random number generation:In random number generation dialog box Edit ▸ Fill ▸ Random Number, now user can optionally limit the number of decimal places in the generated random number. tdf#76718 (Tomaž Vajngerl)
- Improvements 4.3 Calc: Smarter highlighting of cell in a formula: Border highlight on multiple instances of the same cell, when selecting a formula, now get same color. After calculating a long formula, the cells can be easily referenced with the formula, as they get the same color i.e., the highlight of the cell and the name of the cell in the formula. tdf#52461 (Rachit Gupta)
- Improvements 4.3 Calc: Formula engine:
Added GAMMA.DIST, GAMMA.INV and GAMMALN.PRECISE spreadsheet functions for Excel interoperability. tdf#71936 (Winfried Donkers) Added LOGNORM.DIST, LOGNORM.INV, NORM.DIST, NORM.INV, NORM.S.DIST and NORM.S.INV spreadsheet functions for Excel interoperability. tdf#72158 (Winfried Donkers) Added T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T and T.TEST spreadsheet functions for Excel interoperability. tdf#72793 (Winfried Donkers) Added PERCENTILE.EXC, PERCENTILE.INC, PERCENTRANK.EXC, PERCENTRANK.INC, QUARTILE.EXC, QUARTILE.INC, RANK.EQ and RANK.AVG spreadsheet functions for Excel interoperability. tdf#73146 (Winfried Donkers) Added MODE.SNGL, MODE.MULT, NEGBINOM.DIST and Z.TEST spreadsheet functions for Excel interoperability. tdf#72197 (Winfried Donkers) Added FLOOR.PRECISE, CEILING.PRECISE and ISO.CEILING spreadsheet functions for Excel interoperability. tdf#71720 (Winfried Donkers, Eike Rathke) Added NETWORKDAYS.INTL and WORKDAY.INTL spreadsheet functions for Excel interoperability. tdf#73147 (Winfried Donkers) Added ERF.PRECISE and ERFC.PRECISE spreadsheet functions for Excel interoperability. tdf#73149 (Winfried Donkers)
- Improvements 4.3 Calc: Statistics Wizard (alternative to Excel's Add-in "Analysis ToolPak"). In Statistics Wizard Data ▸ Statistics
added Two factor Analysis of Variance (ANOVA) (Tomaž Vajngerl) added F-test. tdf#74663 (Tomaž Vajngerl) added t-test. tdf#74668 (Tomaž Vajngerl)
- Improvements 4.4 Calc: Statistics Wizard (alternative to Excel's Add-in "Analysis ToolPak")
In Statistics Wizard in Data ▸ Statistics: Added Quartile in Descriptive statistics. tdf#81801 (Tomaž Vajngerl, Collabora) Added z-test. tdf#74669 (Tomaž Vajngerl, Collabora) Added Chi square test. tdf#76868 (Tomaž Vajngerl, Collabora)
- Improvements 4.4 Calc: Direct conversion of formulas into static values
A new feature was added to convert formula cells into static values (via the menu Tools ▸ Cell Contents ▸ Formula to Value). Previously, you would need to cut the cells and paste them back into place to have the same outcome. (Kohei Yoshida, Collabora)
- Improvements Calc 4.4 : Formula engine. Added AGGREGATE spreadsheet function for Excel interoperability. tdf#73148 (Winfried Donkers and Eike Rathke)
- Improvements Calc 5.0 : Conditional formatting
UI for Data bars with a solid background. (feature without UI has been available since 3.6) tdf#87356 (Katarina Behrens) UI for Data bars without showing their underlying values. (feature without UI has been available since 3.6) tdf#89199 (Katarina Behrens) Minimum and maximum bar lengths for data bars (available for vertical axis set to Middle or None) tdf#90197 (Benjamin Ni and Markus Mohrhard) Additional icon sets: 3 triangles, 3 stars, 5 boxes (Markus Mohrhard)
- Improvements Calc 5.1 :Formula Wizard shows the values of parameters and results on the Structure page. (Benjamin Ni)
- Improvements Calc 5.1 -New Statistic Option : Regression:
Statistics regression: linear, logarithmic, power: Add a new statistics dialog for calculating regression. tdf#74667 commit (Tomaz Vajngerl, Collabora)
- Improvements Calc 5.1 : INDIRECT function: better compatibility for legacy OOo documents, improved interoperability with Excel tdf#92256,tdf#93688 (Katarina Behrens, CIB)
- Improvements Calc 5.1 : Calc, SUM function now uses optimized arithemetic and SSE3 if present, giving a ~5x win for large sums (Michael Meeks, Collabora, Tomaz Vajngerl, Collabora), Formula Group SoftwareInterpreter used for simple functions: +, -, *, /, Sum, Product giving a 2x+ win for large function runs (Jan Holesovsky, Collabora)
- Improvements Calc 5.2 : New drawing tools were added (that were previously available only in Draw), including Filled Curve/Polygon/Freeform Line, as well as Polygon (45°). tdf#89646 (Gülşah Köse). All of the toolbar conditional formatting buttons have been merged into a single conditional formatting group button. tdf#86567 (Maxim Monastirsky). Easy ability to freeze the first column or first row of a sheet through the toolbar or menu View ▸ Freeze Cells. tdf#91013 (Gülşah Köse; Maxim Monastirsky; Yousuf Philips). The button to open the multiline input now has the same size in all systems (was very thin in some environments). tdf#99701 (Samuel Mehrbrodt, CIB). Pressing ⇧ Shift+Return in the multiline input will now insert a new line instead of moving to the previous cell. tdf#63994 (Samuel Mehrbrodt, CIB). Cell References keyboard shortcut ⇧ Shift+F4 was removed in favor of F4, but is still available in OpenOffice.org Legacy keybindings. tdf#97906 (Dennis Francis; Yousuf Philips). Column Select keyboard shortcut Ctrl+⇧ Shift+Space was replaced with Ctrl+Space. tdf#96969 (Yousuf Philips). Keyboard shortcut Ctrl+, to insert date and Ctrl+⇧ Shift+, to insert time is available to various European keyboard layouts. tdf#88027 (Yousuf Philips).The Standard (Single Mode) toolbar was created to be used as an single toolbar alternative to the default double toolbar arrangement and it contains the most used function, insert, and formatting operations. It can be activated by enabling View ▸ Toolbars ▸ Standard (Single Mode) and disabling View ▸ Toolbars ▸ Standard and View ▸ Toolbars ▸ Formatting. tdf#92218 (Yousuf Philips)
- Improvements Calc 5.2 New Spreadsheets Functions : New spreadsheet functions
RAWSUBTRACT to subtract numbers without canceling out small roundoff errors. tdf#71459 (Eike Rathke (Red Hat, Inc.)) =RAWSUBTRACT(a,b,c) is like =a-b-c, but the - minus operator cancels out small roundoff errors that are due to the fact that not every decimal number can be exactly represented as a binary floating point number and effectively the operation snaps to zero. RAWSUBTRACT uses the IEEE754 operation the platform/compiler provides. For example, =0.3-0.2-0.1 results in 0, whereas =RAWSUBTRACT(0.3,0.2,0.1) results in 2.77555756156289E-17 . Note: Uncheck Tools ▸ Options ▸ Calc ▸ Calculate ▸ Limit decimals for general number format to get this result. This can be used to compare whether a result exactly matches an expected number, i.e. if =RAWSUBTRACT(result,expected) equals 0.0 FORECAST.ETS a set of forecasting functions that use triple and double exponential smoothing and handle seasonal effects. tdf#94635 (Winfried Donkers) . The Holt-Winters algorithms are used. Both Additional and Multiplicative methods are supported for triple exponential smoothing. The set consists of the functions FORECAST.ETS.ADD*, FORECAST.ETS.MULT, FORECAST.ETS.SEASONALITY*, FORECAST.ETS.PI.ADD*, FORECAST.ETS.PI.MULT, FORECAST.ETS.STAT.ADD* and FORECAST.ETS.STAT.MULT. The functions marked with * are interchangeable with MS Excel2016. CONCAT an enhanced version of CONCATENATE, allowing ranges, columns and references as arguments tdf#97831 (Winfried Donkers) . The function is compatible with MS Excel 2016 function CONCAT. TEXTJOIN to concatenate strings with delimeters between the strings. tdf#97831 (Winfried Donkers) . TEXTJOIN allows the same string arguments as CONCAT and has a string argument that will be used as delimiter between the strings to be joined. Also, it is possible to opt for empty cells to be skipped. The function is compatible with MS Excel 2016 function TEXTJOIN. IFS a cascaded IF-function tdf#97831 (Winfried Donkers). IFS works like if A then B else if C then D else if (etc.). The function is compatible with MS Excel 2016 function IFS. SWITCH a multiple choice selection function tdf#97831 (Winfried Donkers) . SWITCH compares one expression with 1 to n possible values and returns the result belonging to the first values that equals expression. The function is compatible with MS Excel 2016 function SWITCH. MINIFS, MAXIFS tdf#97831 (Winfried Donkers) . These statistical functions work like SUMIFS and AVERAGEIFS, but return the minimum respectively maximum value of the resulting range. The functions are compatible with MS Excel 2016 functions MINIFS and MAXIFS.
- Improvements Calc 5.2:Changed spreadsheet functions. WEEKDAY handles additional type argument values. tdf#100396 (Eike Rathke (Red Hat, Inc.)). For interoperability with other spreadsheet implementations the following new argument values for the second Type parameter are accepted and let WEEKDAY return numbers from 1 to 7: 11: Monday 1 to Sunday 7 , 12: Tuesday 1 to Monday 7 , 13: Wednesday 1 to Tuesday 7 , 14: Thursday 1 to Wednesday 7 , 15: Friday 1 to Thursday 7 , 16: Saturday 1 to Friday 7 , 17: Sunday 1 to Saturday 7 . Previous versions accepted all integer values as Type argument, but any number not 1 or 2 or 3 was handled the same as 3, returning values starting with Monday=0. Now all values not of the set 1,2,3,11,12,13,14,15,16,17 let WEEKDAY return an error.
- Improvements Calc 5.2 : Support Wildacards
Simple wildcards as known from other spreadsheet applications can now be used in formula expressions. (Eike Rathke (Red Hat, Inc.)) Wildcards are * (asterisk) any sequence of characters, including empty sequence ? (question mark) any character, exactly one ~ (tilde) escapes the special meaning of the following asterisk, question mark or tilde See also tdf#72196 and related section in ODF 1.2 table:use-wildcards and OpenFormula Host-Defined Behaviors. Wildcards are supported in all functions that already supported regular expressions, which are: all database functions DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR and DVARP; the functions COUNTIF, COUNTIFS, HLOOKUP, LOOKUP, MATCH, VLOOKUP, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS and SEARCH. Accordingly the setting was changed, under Menu ▸ Tools ▸ Options ▸ Calc ▸ Calculate there is now a Enable wildcards in formulas radio button grouped with Enable regular expressions in formulas and No wildcards or regular expressions in formulas. When reading ODF spreadsheet documents with table:use-wildcards enabled or importing Microsoft Excel spreadsheet documents the option is set to wildcards enabled.
- Improvements Calc 5.2 : Using named expressions and ranges with sheet-local scope on other sheets. It is now possible to use a defined name with a scope local to a sheet in a formula on any other sheet. tdf#96915 (Eike Rathke (Red Hat, Inc.)). For example, a named range LocalName defined with scope Sheet1 can be used in a formula on Sheet2 as =SUM(Sheet1.LocalName), usual sheet name constraints for references apply, e.g. if the sheet name contains a blank it has to be enclosed in single quotes, as in =SUM('Other Sheet'.LocalName)
- Improvements Calc 5.2 : Copying named expressions and ranges while copying sheets. While copying entire sheets, named expressions and ranges that point to the copied sheet and are used by any formula cell that is copied along with the sheet are copied to new sheet-local scoped names. tdf#75372 (Eike Rathke (Red Hat, Inc.))
- Improvements Calc 5.2 : Number Format. Date/Time Formats (Laurent BP). as minute and month have same code M, disambiguation have been improved tdf#95339 tdf#101147
- Improvements Calc 5.3 : Arrows Toolbox. New drawing tools were added (that were previously available only in Draw and Impress). tdf#101390 (Gülşah Köse). Changed spreadsheet functions: The UI name of EFFECTIVE was changed to EFFECT to be the same as in other spreadsheet applications. tdf#100641 (Eike Rathke (Red Hat, Inc.)). Compatibility with ODF 1.2 : ISOWEEKNUM and WEEKNUM_OOO. Releases prior to 5.1 saved WEEKNUM wrongly as ISOWEEKNUM to ODF. Release 5.3 resolves the interim solution introduced with release 5.1 (see ReleaseNotes/5.1#Compatibility_with_ODF_1.2). (Eike Rathke (Red Hat, Inc.)) . When importing ISOWEEKNUM, if the call uses one parameter it is kept as the correct ISOWEEKNUM. If the call uses two parameters it is determined if the second argument mode is a literal numeric value unequal to 1 in which case it is mapped to ISOWEEKNUM with one argument, as the old WEEKNUM function implemented exactly that for those modes. If the second argument is something different, i.e. a numeric value 1 or an expression, the call is mapped to the WEEKNUM_OOO compatibility function introduced in LibreOffice 5.1. WEEKNUM_OOO is now saved as ORG.LIBREOFFICE.WEEKNUM_OOO which releases 5.1 and 5.2 can read. Functions ( Insert ▸ Function ) can now be searched from the available list tdf#67974. Calc now supports scrolling through the Scroll key tdf#46200.
- Improvements Calc 5.3 : Option settings. In new installations the default setting for new documents is now Enable wildcards in formulas instead of regular expressions. tdf#88581 (Eike Rathke (Red Hat, Inc.)) . This is for better interoperability with other spreadsheet applications and a better experience for users who are not familiar with regular expressions. Wildcards are more widespread and the large set of regular expression metacharacters often makes queries too complicated for casual users. See also ReleaseNotes 5.2 for wildcards.
- Improvements Calc 5.4 : Priority of conditional formatting rules can be changed with new up/down buttons. tdf#74074 (Manfred Blume, CIB)
- Improvements Clac 5.4: While building formulas, when selecting a cell or range on another sheet with the pointer, absolute sheet references are now created instead of relative. tdf#90799 (Eike Rathke (Red Hat, Inc.))
- Improvements Calc 5.4: New spreadsheet functions. New function ROUNDSIG to round a value at a number of significant digits. tdf#105931 (Winfried Donkers) . Changed spreadsheet functions: The semantics of the COUNTIF, SUMIF and AVERAGEIF Criteria argument has been changed. The equals empty "=" and not-equals not-empty "<>X" criteria now also match empty cells of a given range like they are evaluated in other spreadsheet applications.
- Improvements Calc 6.0 : New spreadsheet functions
New ODFF1.2 compliant functions SEARCHB, FINDB and REPLACEB added (commit1, commit2, commit3). (Winfried Donkers) FINDB returns the starting position of a given text, using byte positions. FINDB is case sensitive. SEARCHB returns the starting position of a given text, using byte positions. REPLACEB returns text where an old text is replaced with a new text, using byte positions.
Using Pivot Tables.
- Improvements 4.3 Calc : Pivot Table layout improvement. "Data" field is now on columns by default, and can be moved to rows if needed. This makes it much faster to generate useful pivot tables from large data sources. tdf#77927 (Tomaž Vajngerl)
- Improvements Calc 5.0: Item labels in Pivot Tables can be repeated now. This is optional. When you create a pivot table, double-click on the desired field in the list "Row fields". In the dialog box "Data Field", then click button "Options". In the next dialog box, select "Repeat item labels". Click OK. tdf#87972 (Deena Francis) Screenshot.
- Improvements Calc 5.3 : Pivot tables.Median is added to functions available in pivot tables, useful for data fields and for row/column fields (subtotals) tdf#61700 (Tamás Zolnai) Only ODS file format is supported to save/load such pivot tables.
- Improvements Calc 6.0 : Various
Pivot table interop fixes (Mike Kaganski (Collabora), Tamás Zolnai (Collabora), Bartosz Kosiorek)
- Improvements 4.3 Calc :Enable CoinMP solver: Previously LibreOffice shipped the lpsolve solver, now we can build both the lpsolve and coin-mp solvers. (Matúš Kukan)
Linking Calc Data
Sharing and Reviewing.
Calc as a Simple Database
Setting Up & Customizing Calc
- Improvements in Calc 4.3 : User selectable text conversion models. Under Tools ▸ Options ▸ LibreOffice Calc ▸ Formula in Detailed calculation settings ▸ Custom ▸ Details the user can select how textual cell content shall be treated in arithmetic operations. See also this blog entry for some details. tdf#37132 and tdf#75834 related (Eike Rathke)
- Release notes 4.4 Firefox Themes : Firefox Themes. New browser for Firefox themes with search and preview. (Rachit Gupta, 2014)
- Release Notes 4.4 Firefox Themes : Firefox themes now apply also to bottom toolbars. tdf#59299 (Michael Jaumann, )
- Release Notes 4.4 Icon improvements : Icon improvements. New and improved icons have been added to the Tango, Sifr, and Oxygen icon themes. tdf#75256 tdf#75359 (Mirek Mazel; Yousuf Philips; Matthias Freund; Alexander Wilms; and Adolfo Jayme Barrientos) . Large icons are shown by default on Windows, as they are on Linux and macOS. tdf#82309 (Jan Holešovský, Collabora; and Yousuf Philips)
- Improvements Calc 6.0 : Added support for user icon themes via extension manager tdf#110742 (Caolán McNamara)
- Improvements Calc 6.0 : Customize. It is now easier than ever to customize LibreOffice UI based on your needs, thanks to improved user experience and accessibility of the newly reworked Customize dialog (Tools ▸ Customize...). tdf#88896 (Muhammet Kara (Pardus), GSoC 2017; Katarína Behrens (CIB); Heiko Tietze; Yousuf Philips) . The Menus, Toolbars, and Context Menus tabs have been revamped to provide a modern and intuitive design which is highly accessible and user friendly. With the well-established two-pane design of the revamped dialog, it is possible to customize toolbars, menus, and context menus following a nice work-flow, without leaving the dialog. A search box has also been introduced to filter the functions which can be assigned to UI elements, so you can easily and quickly find exactly what you are looking for without navigating through a list of too many categories and functions. The Keyboard tab has also got the new search box. tdf#63966 . Last but not least improvement is the cleaned-up and re-factored code under the hood, which eliminates some bugs of the past and prepares for the features of the future.
- Change Calc 6.0 : Configuration Memory. Memory configuration page has been removed from Options dialog tdf#110448 (Mike Kaganski, Collabora).
- Improvements Calc 6.0 : Configuration Extension Manager
Now extension installation and removal via the Extension Manager dialog (Tools ▸ Extension Manager...) can be dynamically enabled/disabled from the expert configurations (Tools ▸ Options ▸ Advanced ▸ Open Expert Configurations). tdf#36019 (Muhammet Kara, Pardus; Heiko Tietze) . This also affects the installation by double-clicking the extension file in the file manager, but doesn't affect the unopkg commands. When ExtensionSecurity::DisableExtensionRemoval is set in expert configurations, context menu entry of 'remove' for installed extensions is also disabled along with the "Remove" button on the dialog.
- Improvements Calc 6.0 : New variant of Notebookbar - Groupedbar Full for Writer, Calc and Impress (experimental feature) tdf#106035 (Andreas Kainz)
Appendix A - Keyboard Shortcuts.
Appendix B - Calc Functions.
Appendix C - Calc Error Codes.
Out of Topics
- Improvement Calc 4.2: Prevent increase indent from running outside the cell (Abdulmajeed ahmed)
- Improvement Calc 4.2: Formula editor. Make use of ZoomSlider in Math tdf#63351 (Thomas Arnhold)
Auto Closing of brackets tdf#43090 (Marcos Paulo de Souza) Math: New feature to add colors directly from the Elements Dock by clicking their name (Marcos Paulo de Souza)
- Improvements Calc 4.2: Performance. Calc:
Calc's cell storage structure has been completely re-written, to store contiguous cell values in contiguous memory region to improve locality of reference as well as the cost of storage. The new structure also enables hardware accelerated parallel calculations of a large number of cells. (Kohei Yoshida) Calc core now shares all string instances stored in cells and several other places in order to significantly speed up both case sensitive and case insensitive string comparisons, and also to allow string equality check in OpenCL-based formula interpreter. This change also speeds up execution of several cell functions (such as VLOOKUP) that perform a large number of string equality comparisons. Filtering of rows via AutoFilter, Standard Filter, and Advanced Filter should run faster thanks to shared strings. (Kohei Yoshida) Vertically adjacent formula cells containing identical formula token set now share their formula token array instance. This can drastically reduce run-time memory usage of Calc, and also speeds up automatic reference updates when moving cells. (Kohei Yoshida) XLSX import filter has been re-worked to achieve significant boost on the import performance on XLSX documents. The speed up was achieved by moving much of the temporary string allocations and deallocations that occur during the XML parsing phase to a separate thread, and also by re-working the handler logic on the Calc side to reduce inefficiencies, especially with regard to importing of formula cells. (Kohei Yoshida, Michael Meeks, Matúš Kukan) Added an experimental multi-threaded import for XLSX documents. When the experimental features are enabled, Calc tries to import XLSX documents using multiple threads, to import multiple sheets concurrently. (Kohei Yoshida, Michael Meeks) Significant speed up on the export of ODS document that contains a large number of rich-text cells. Refer to this blog post for more details. (Kohei Yoshida) Cell note storage has been re-worked to use multi_type_vector, in order to improve the performance of import and export of documents containing large number of cell comments. (Laurent Godard) Improved performance of VLOOKUP function with use of external references. (Pierre-Eric Pelloux-Prayer) Improved performance of SUM, COUNT, and SUMIFS function. (Kohei Yoshida)
- Improvements Calc 4.3: New import filters. Microsoft Works spreadsheets and databases (Laurent Alonso)
A host of legacy Mac formats (Laurent Alonso) spreadsheets: BeagleWorks, ClarisWorks, Claris Resolve, GreatWorks, MacWorks, Wingz vector and bitmap images: BeagleWorks, ClarisWorks, GreatWorks, MacPaint, MacWorks, SuperPaint
- Improvements Calc 4.3: Color pickers improvements: Line color picker now uses a color palette instead of a list. tdf#46839 (Maxim Monastirsky). * Several color pickers were converted to split buttons, making it possible to apply the last used color with one mouse click. This includes: Font color (Impress, Draw), Font color of drawing objects (Writer, Calc), Line color, 3D Extrusion color. commit (Maxim Monastirsky)
- Improvements Calc 4.3: Performance. Algorithm of reordering sorted data has been overhauled. Sorting of large data set is now substantially faster. (Kohei Yoshida, Michael Meeks) Importing OOXML documents with lots of relations is much faster. (Michael Meeks)
- Improvements 4.4: Math. Color support. Now Math supports the basic 16 HTML colors. (Regina Henschel)
The newly added colors can be inserted from the Elements docked bar. (Marcos Paulo de Souza)
- Improvements Calc 4.4: Help content. Local help page button for Calc in Edit ▸ Fill ▸ Random Number. tdf#80453 (Olivier Hallot, EDX) Local help page button for Calc in Data ▸ Statistics. tdf#80338 (Olivier Hallot, EDX) Local help entries for various Calc functions. tdf#80656, tdf#80452, tdf#80451, tdf#80450, tdf#80449, tdf#80447, tdf#80446, tdf#80445, tdf#80444, tdf#80443, tdf#80442, tdf#80440, tdf#80438, tdf#80436, tdf#80433, tdf#80432, tdf#80430 (Olivier Hallot, EDX)
- Improvements 4.4: Connection to SharePoint and OneDrive. It is possible to connect to SharePoint 2010/2013 directly from LibreOffice. blog entry (Mihai Varga, 2014)
- Improvements Calc 4.4: Improved MS Works Spreadsheet support. An old Lotus LICS character encoding is now recognized and converted. (Laurent Alonso)
- Improvements Calc 4.4: Tab changes. Sheet tabs (Calc) and layer tabs (Draw) were moved to an independent row and are no longer displayed next to the horizontal scrollbar. As many visual styles under Linux (in addition to the default macOS configuration) use very thin scrollbars, these rendered the tabs too small to be clickable and, most importantly, readable. tdf#36772 (Tomaž Vajngerl, Collabora and Samuel Mehrbrodt) The tabs have an updated, flatter look. (Tomaž Vajngerl, Collabora)
- Improvements Calc 5.0 Improvements for XLSX : Export of formatting for conditional formatting to XLSX fixed. (Markus Mohrhard) Import of Data bars and Color Scales color values handles theme colors. tdf#90511 (Markus Mohrhard) Import and export of custom icon set definitions (including MSO extensions to OOXML) (no UI) (Markus Mohrhard)
- Improvements Calc 5.0 : Table structured references. tdf#85063 (Eike Rathke) Table structured references in spreadsheet formula expressions are imported from OOXML spreadsheet documents. Usable with defined database ranges, which OOXML tables (content of elements) are imported to. The following restrictions apply: The column names must be present as cell content in the table's header row; column-header-less tables are not supported. Saving to ODF or OOXML, structured references are not written, but transformed to A1 style reference notation instead. The references continue to work, but the table context is lost on export. Saving to OOXML, the defined database ranges are not exported as elements. Saving to binary .xls (Microsoft Excel 97-2003 Format), formulas using table structured references are lost.
- Improvements Calc 5.0 : Formula engine. New spreadsheet functions. Changes related to FLOOR and CEILING spreadsheet functions. tdf#69552 (Winfried Donkers and Eike Rathke) The second parameter of FLOOR and CEILING is now optional, as defined by OpenFormula (ODFF). Additional FLOOR.XCL and CEILING.XCL functions were introduced that expect only two parameters and both parameters have to be specified. When importing Microsoft Excel documents, their old FLOOR and CEILING functions are imported as FLOOR.XCL and CEILING.XCL.New functions FLOOR.MATH and CEILING.MATH were added for Microsoft Excel interoperability. tdf#87342 (Winfried Donkers) Exporting to Microsoft Excel file formats, Calc FLOOR and CEILING are written as Excel FLOOR.MATH and CEILING.MATH, and Calc FLOOR.XCL and CEILING.XCL are written as Excel FLOOR and CEILING . Added ENCODEURL spreadsheet function for Microsoft Excel interoperability. tdf#76870 (Winfried Donkers) . Added ERROR.TYPE spreadsheet function according to OpenFormula (ODFF) and for Microsoft Excel interoperability. (Winfried Donkers)
- Improvements Calc 5.0 : See also: Development/HiDpi . Scale "add Sheet" button for Calc tabs (Tomaž Vajngerl, Collabora).
- Improvements Calc 5.1 The Spelling Dialog now has a toolbar for pasting and inserting special characters. tdf#95682 (Caolán McNamara, Red Hat, Inc.)
- Improvements Calc 5.2 : Classification according to TSCP-standards Improvements. In Writer, Calc and Impress there is the tool bar Classification that allows choosing the classification level. Choosing the classification level will result in an info bar, and in Writer (depending on the level) also in a watermark and a field in the header and footer. All showing the level of classification. Of course the classification is stored in document properties. In the current implementation there are three categories to choose from. The first one (named "Intelectual property" according to TSCP_BAILSv1) sets the header/footer and watermark (for certain levels). This is likely to be modfied in future versions. Look for Classification Bar in the local or on line Help . Also see this blog entry. (Miklos Vajna, Collabora)
- Improvements Calc 5.2 Remote Files Dialog (Google Drive). Added Icon View and buttons for switching between modes (Szymon Kłos; Yousuf Philips; Heiko Tietze) . Google Drive's Two-Factor Authentication (2FA) now supported tdf#87938 (Szymon Kłos)
- Improvements Calc 5.3 : Math. Dynamically-sized integral intd now allows sub/superscripts. (Takeshi Abe) tdf#53472 tdf#102268 . New command "Evaluated At" in "Brackets" section of Elements window has been added. (Takeshi Abe) tdf#44839 . Improved MathML import (Takeshi Abe) Import MathML's <mspace> tdf#76296 . Import stretchy integral symbol from MathML tdf#103500 . Improved MathML export (Takeshi Abe)
Export dynamically-sized integral intd to MathML tdf#97049 . Export Greek symbols to MathML with correct mathvariant attribute tdf#101022
- Improvements Calc 5.3 : Change of color palettes. see this blog entry. Fix recent colors.
The custom palette is newly added, which allows colors to be added directly in the area style dialog. Custom palette makes the manipulation of factory settings via Tools ▸ Options ▸ LibreOffice ▸ Colors obsolete ‒ and hence we deleted this option. The palettes gallery, web, cmyk, and scribus were removed because of the non-standard and rather arbitrary collections with inappropriate names Tango and html palettes received minor updates for labels and arrangement of colors . The standard palette was also refreshed. The first row now starts with 12 shades of gray followed by 12 basic colors from the color wheel. The next rows are variations of these basic colors in respect to saturation and luminance by 66%, 50%, and 25%. The final 12×8 arrangement fits perfectly into our color picker grid . The palette breeze has been added to the default set. It comprises all values known from the KDE human interface guidelines as an alternative to tango . Completely new is the tonal palette. It aims to provide a set of colors with the same luminance respective color contrast . The palette freecolour-hlc based on the color model has been added. Its purpose is to provide a cross-media safe set of colors targeting expert publishers .
- Improvements Calc 5.3 : Filters. Improvements in OpenXML filter. Changes in import/export into .xlsx or .xls:
Fix for loss of width for hidden/collapsed grouped columns after export to .xlsx or .xls tdf#51524 (Bartosz Kosiorek) . Landscape orientation is now properly saved after export to .xlsx (MS Excel compatible) tdf#48767 (Bartosz Kosiorek) .Hidden and empty rows became hidden when exported to .XLSX or .XLS file tdf#98106 (Bartosz Kosiorek) .Outline columns and rows are saved correctly after export to XLSX tdf#100347 (Bartosz Kosiorek) During .xlsx export, XML_outlineLevelRow, XML_outlineLevelCol keys are now saved (Office365 compatible) tdf#101135 (Bartosz Kosiorek) . Fix column width on MS Excel for macOS, after export to XLSX and XLS tdf#100946 (Bartosz Kosiorek; Markus Mohrhard) . Fix precision of column width according to MS specification tdf#101363 (Bartosz Kosiorek) . Fix for User Defined Custom Formatting which was not applied during importing XLSX documents tdf#70565 (Bartosz Kosiorek) . Fix exporting of coloured empty cells to .xlsx and .xls documents tdf#46738 (Bartosz Kosiorek) . Fix compatibility issue with Excel 2007 after export to .xlsx tdf#101059 (Bartosz Kosiorek) . Fix slow import of grouped pivot tables in .xlsx tdf#102694 (Tamás Zolnai) . Number formats: preserve escape characters tdf#81939 (Laurent BP) . fraction format accepts '0' in addition to '#' and '?' tdf#100755 (Laurent BP) . Extended LCID can be imported. Extended LCID is used to export calendar and local numerals tdf#36038 (Laurent BP) Improvements in the PDF filter. PDF can be now inserted as an image into documents. blog entry. (Miklos Vajna, Collabora)
- Improvements Calc 5.3 : Area Dialog. Redesign of the Colors, Gradients, Hatching and Bitmap tabs and the addition of a Pattern tab. tdf#94551 (Rishabh Kumar, 2016; Katarina Behrens, CIB; Heiko Tietze; Yousuf Philips) . Default set of patterns. tdf#94547 (Yousuf Philips)
- Improvements Calc 5.4 : Implement context menu edit commands: cut, copy, paste for the Formula command input panel tdf#106479 (Maxim Monastirsky)
- Improvements Calc 6.0 : General. State of the Change tracking is now visible in the menu via check marks (Pranav Kant, Collabora)
- Improvements Calc 6.0 : General. Improved animation of the comments (Henry Castro, Collabora)
- Improvements Calc 6.0 : General. Content Security Policy now includes the Referer too, for easier integration in multi-host environments (Pranav Kant, Collabora)
- Improvements Calc 6.0 : General. New file types that can be edited were added or improved, including PDF and HTML (Ashod Nakashian, Collabora)
- Improvements Calc 6.0 : General. Support for avatars: The integrators can specify a pictures / avatars for the users of the documents via WOPI extension. (Ashod Nakashian, Pranav Kant, Collabora)
- Improvements Calc 6.0 : General. Find & replace dialog (in addition to the previously available Search functionality) (Aditya Dewan, GSoC)
- Improvements Calc 6.0 : General. Support for custom buttons in the toolbar (Pranav Kant, Collabora)
- Improvements Calc 6.0 : General. -Automatic detection of links in the comments (Pranav Kant, Collabora)
- Improvements Calc 6.0 : General. Improvements in the Admin Console (Aditya Dewan, GSoC)
- Improvements Calc 6.0 : General. List of users of a given document. Admin user list. Indication whether documents are modified . Switch to follow the editor in the list of users (Aditya Dewan, GSoC) . Better handling of conflict between editing and uploading documents (Pranav Kant, Collabora) . Spell checking support in all Writer, Calc and Impress (Michael Meeks, Henry Castro, Collabora) . Status bar indicates the current language (Henry Castro, Collabora) . Statusbar showing the current language in LibreOffice Online
Save As support + document name in the user interface (Aditya Dewan, Jan Holesovsky, Collabora)