#Openoffice conditional formatting using formulas

[drivernahas]

Openoffice conditional formatting using formulas

OPENOFFICE CONDITIONAL FORMATTING USING FORMULAS HOW TO

::com::sun::star:: awt:: PointĬontains the position of the top left cell of this range in ::com::sun::star:: util:: XModifyBroadcaster ::com::sun::star:: beans:: XTolerantMultiPropertySet It possible to learn about changes in the cell values. Provides modifying the source data cells of a chart and makes ::com::sun::star:: chart:: XChartDataArray Provide access to a collection of equal-formatted cell ranges. Provides filtering the contents of this range. Provides calculating SubTotal values in this range. Provides functionality to import data from external data sources. Provides access to the Multiple Operations feature. On a start value, step count and fill mode. Provides filling out the cell range automatically with values based Provides computation of a value based on the contents of allĬells of this range and to clear specific cells. Provides access to the cell range address of this range. Provides simultaneous access to the formula contents of all cells. Provides simultaneous access to the contents of all cells. Provides access to the spreadsheet that contains this cell range. Provides applying an AutoFormat to the cell range. ::com::sun::star:: table:: XAutoFormattable Provides access to the collections of columns and rows. ::com::sun::star:: table:: XColumnRowRange Provides merging and unmerging the cells of this cell range. Provides interfaces to find cells with specific properties. ::com::sun::star:: style:: ParagraphPropertiesĬontributes properties for paragraph formatting. ::com::sun::star:: style:: CharacterPropertiesComplexĬontributes properties for character formatting of Complex text. ::com::sun::star:: style:: CharacterPropertiesAsianĬontributes properties for character formatting of Asian text. ::com::sun::star:: style:: CharacterPropertiesĬontributes properties for character formatting of Western text. Provides basic handling of cell ranges and contributes common cell Developers Guide Spreadsheets - Spreadsheet Services - Overview Spreadsheets - Cell Ranges Spreadsheets - Cell and Cell Range Access This service is an extension of the CellRange service for use Click OK.Description represents a rectangular range of cells in a spreadsheet document. Make sure all other options are not selected. To apply the same conditional formatting later to other cells:ġ) Select one of the cells that has been assigned conditional formatting.ģ) Select the cells that are to receive this same formatting.ĥ) On the Paste Special dialog, in the Selection area,select only the Formats option. You can alsoĮnter formulas containing relative references.Ĭhoose the cell style to be applied if the specified condition matches. Here you can choose from conditions including less than, greater than, between, and othersĮnter a reference, value, or formula in the parameter field, or in both parameterįields if you have selected a condition that requires two parameters. If you select cell value is, the Cell Value Condition box is displayed, as shown in the example. The selected cells are now formatted in the relevant style.Ĭell value is / Formula is Specifies whether conditional formatting is dependent on a cell value or on aformula.

OPENOFFICE CONDITIONAL FORMATTING USING FORMULAS HOW TO

An easy way to set up the required styles is toįormat a cell the way you want it and click the New Style from Selection icon in theĪfter the styles are set up, here is how to use them.ġ) In your spreadsheet, select the cells to which you want to apply conditionalĢ) Choose Format > Conditional Formatting from the menu bar.ģ) On the Conditional Formatting dialog (Figure 57), enter the conditions.Ĭlick OK to save. ChooseĬonditional formatting depends upon the use of styles. Note : To apply conditional formatting, AutoCalculate must be enabled. Green and all those below the average in red. ForĮxample, in a table of numbers, you can show all the values above the average in You can set up cell formats to change depending on conditions that you specify.

[lindyhunt]

The 8 Best Excel Alternatives

Excel is one of the world’s most popular spreadsheet programs, with an estimated 750 million people using it for data summary and organization, analysis, and reporting.

While it’s a commendable data system for teams within a business, it’s not free -- a standalone copy of Excel 2016 is $129.99, and the Office personal subscription is $69.99 per year for one user.

Equally important to note, Excel has its fair share of shortcomings, and there are some alternatives that might work better for your team or project.

If you feel you could be better off using a different data spreadsheet system, or if you simply don’t have the means to pay for one, take a look at our list of the eight best Excel alternatives.

Best Excel Alternatives

Google Sheets

Numbers

Thinkfree

Birt Spreadsheet

Zoho Sheet

FreeOffice2016

Apache OpenOffice

Quip

1. Google Sheets

Price: Free

If you have a Gmail account, you already have access to Google Sheets. Google Sheets, an almost equal competitor to Excel in both layout and features, has a few benefits that Excel lacks. For one thing, your files on Google Sheets are saved in the Cloud. That means if you use multiple devices for business purposes, you can’t beat the automatic accessibility of Sheets.

Second, you can share your Sheets with anyone on Slack or email and make edits simultaneously, allowing for optimal collaboration and teamwork on projects. Lastly, with Sheets, you have the advantage of other Google tools, like Google Translate, GoFinance, and Import Feed/Import Data functions, which enable you to pull data straight from web page’s URLS.

2. Numbers

Price: Free (for Mac users)

This program comes free with every Mac, and is essentially Mac’s own version of Microsoft’s Excel. Numbers provides high-quality data analysis and reporting functions, as well as plenty of visual tools. The program uniquely provides pre-built templates, dynamic donut charts and graphs, and a library with more than 600 shapes to turn your data into presentation-ready visuals. Since it’s in the iOS system, you can use the program on any Mac device, making it easy to access your data on the go or from home. In a show of great sportsmanship, Numbers also enables you to save your spreadsheet as an Excel file, to share with anyone who prefers to stick with Excel.

3. Thinkfree

Price: 2/months free trial; Enterprise version $79.99

ThinkFree offers three primary features, which are compatible with office programs: Write, Calc, and Show. The Excel alternative, Calc, lets you create spreadsheets and presentations and provides 1 GB of free online storage, and like Google Sheets, you can collaborate with colleagues on ThinkFree documents. Most notably, ThinkFree allows you to edit and view your documents on your mobile device, if you want to make changes to your spreadsheet away from your computer. ThinkFree doesn’t require any Office installation.

4. Birt Spreadsheet

Price: Free

This Excel alternative is particularly helpful for data analysis, with features including live formulas, pivot tables, hyperlinks, and data-driven layouts. Birt Spreadsheet (formerly Actuate e.Spreadsheet) automates and centralizes all your spreadsheet production, maintenance, and security. Birt also provides a Spreadsheet Designer, which creates reports to retain formulas, formatting, live charts, and data connections.

5. Zoho Sheet

Price: Free for teams of up to 25 users

One of the biggest perks of Zoho is that it’s Cloud-based, so you can access your data wherever and whenever you want. It offers functions including charts and pivot tables comparable to Excel, and you can also switch easily between .xlsx, .xls, .csv, and .ods. Plus, Zoho allows you to edit a spreadsheet with colleagues simultaneously, making collaboration easy. Along with functions to simplify formulas, and filters to analyze data, Zoho also provides visual charts and tools to create presentation-ready graphics.

6. FreeOffice 2016

Price: Free

The features in FreeOffice’s Excel alternative, PlanMaker, are very similar to Excel, including conditional formatting, pivot tables, and data visualization tools to create presentations. Best of all, FreeOffice’s programs are compatible with Microsoft Office file formats, so if you tend to use Microsoft Office, this alternative makes for easy transfers to any Microsoft product. The FreeOffice tools are clean and intuitive, and they’re also fast, even with dense data. However, if you want to save files as .doc, .xlsx, or .ppt., you’ll need to purchase a paid plan.

7. Apache OpenOffice

Price: Free

Apache OpenOffice, used on over 100 million computers, offers free software for word processing, spreadsheets, presentations, graphics, and more. It’s an open-source program, which means you can modify the program to meet your business’s needs, and it’s downloadable, which means you can work on your spreadsheets without wifi. Data is stored in an open standard format, and Calc, Excel’s alternative, performs data analysis.

8. Quip

Price: Free trial/$30 yearly for five members

If your team collaborates on budgeting or data reporting, Quip is a useful tool -- it enables your team to operate and manage spreadsheets together, simultaneously. With over 400 functions, the Excel alternative has the capability to handle complex projects, and is used by companies including Facebook, CNN, and Pinterest. If you’re hesitant, Quip offers a free trial so you can try it out.

[professorexcel]

XLSX, XLSM or XLSB: Which File Type Is the Best for You?

Probably 99% of all Excel workbooks are saved in the XLSX file type these days. But there are various other file extensions available: For example XLSM, XLSB or “older” Excel users might still remember the XLS file type. What are the differences of these file types? And even more important: Which one should you use?

    File types

Excel provides 29 different file types when you save your Excel workbook.

When you save an Excel workbook for the first time or use the “Save As” function, you are asked for a folder, file name as well as the file type. By default, Excel suggests the XLSX file type (unless your file doesn’t have VBA macros). The window looks similar to the screenshot on the right side.

So which file type are you going to use? The answer – like so often: It depends. Before we conclude in a decision tree, we we take a look at the most important file extensions in Excel with their advantages and disadvantages first.

  XLSX

The “default” file extension is XLSX. The large majority of Excel workbooks uses this format these days. Microsoft says in the Excel help text about the XLSX file format:

The default XML-based file format for Excel 2007-2013. Cannot store Microsoft Visual Basic for Applications (VBA) macro code […].

Most of the time you use the XLSX format: It’s save (can’t store malicious code), has the maximum number of rows and columns and is best known. XLSX is available since Excel 2007 and replaces the old XLS file type. It uses the open XML standard so which is documented well.

Knowing these quick facts, there are the following advantages and disadvantages for the XLSX file format:

Advantages Disadvantages

The default file format and because of that the best known and widest spread file type.

Can use up to 2^20 (1048576) rows and 2^14 (=16384) columns.

Uses the open file format and is in general accessible with other applications.

Secure because it can’t save VBA macros.

Can use and save all the functions available in Excel.

A little bit contradicting: But not being able to include VBA macros can be an advantage and disadvantage at the same time.

Needs more disk space than other file formats (e.g. XLSB).

Can’t be opened with Excel versions before 2003 (e.g. 2003, at least without any further conversion of Excel extension/ add-in).

    XLS

The XLS file type has been the default Excel file extension and format before Excel 2007. In Excel 2007, XLSX replaced the XLS format. Therefore, XLS is kind of outdated right now. In the daily life you won’t find it often any more. But some data base tools still export data as XLS files.

The XLS file type has some disadvantages towards the newer file types XLSX or XLSM: It’s rather unsafe as it can contain VBA macros with malicious code. Also, it needs more disk space than the other file types and at the same time has less rows and columns.

On the other side the only advantage: You can easily open XLS files with older versions of Excel.

Advantages Disadvantages

Can be opened with Excel versions before 2007 (e.g. 2003).

Unsafe as it can contain VBA macros.

Needs more disk space than most of the other file formats (e.g. XLSX, XLSB).

Can’t use all newer Excel functions, e.g. formulas or functionality (you will be warned when saving an XLS file though).

Can only use up to 65,536 (2^16) rows and 256 (2^8) rows.

Outdated…

  .aq_block_33554 h3 { color: #2b803f; } .aq_block_33554 p { color: ; }

Save time and impress your co-workers? Become a keyboard shortcut expert! Download our big 45 pages keyboard shotcuts guide!

Get any keyboard shortcut you want. With 2 simple tricks.

Learn 15 most important keyboard shotcuts in detail.

Stick the top 33 keyboard shortcuts under your screen with a handy print out.

That's not enough? Check out the long lists of keyboard shortcuts.

The best: It's free! Just sign up for our free newsletter and you can download the complete keyboard package instantly.

  XLSM

If you include VBA macros in your XLSX file, you will be asked to change the file extension to XLSM. Only that way you can save your macro within the Excel file (there are other file types possible, e.g. XLSB – but you can’t use XLSX for VBA macros). Therefore, it has most of the advantages and disadvantages of the XLSX file type. Positive: You can immediately identify Excel files with VBA macros.

Advantages Disadvantages

The default Excel file type for workbooks with VBA macros.

Can use up to 2^20 (1048576) rows and 2^14 (=16384) columns.

Uses the open file format and is therefore accessible with other applications.

Can use and save all the functions available in Excel.

Uses the open XML standard.

Unsafe: Can contain malicious code within the VBA part.

Needs more disk space than other file formats (e.g. XLSB).

Can’t be opened with Excel versions before 2003 (e.g. 2003, at least without any further conversion of Excel extension/ add-in).

    XLSB

Many people don’t know that there is an actual alternative to the XLSX and XLSM file type: The XLSB file extension. XLSB files store the data a little bit different than the XLSX or XLSM file types: They don’t use the XLM file structure. Instead, XLSB files try to save disk space because the data is stored in the binary structure. The main difference: Binary files are computer- but not human-readable. For more information on binary files in general, please refer to this Wikipedia entry.

The main disadvantage: Binary Excel files can contain VBA macros. So unless you don’t know the origin of a file, please consider well before opening them. Besides that: All the other disadvantages seem minor.

Advantages Disadvantages

Smaller file size.

Faster opening and closing of files.

Formulas with more than 8192 characters allowed.

Can use and save all the functions available in Excel.

Security: Files can contain VBA code whereas XLSX files can’t.

You can’t change the Excel ribbon.

Some third party tools (e.g. OpenOffice) might not be able to open your file.

You can’t open your files with Excel 2003 or earlier (which nowadays should not be a problem any more…).

Not very well known. So people might get confused receiving XLSB files from you.

  CSV

CSV stands for comma separated values. These files are basically plain text – so there are no formulas or formatting. CSV is often used for data exchange. Excel provides the functionality to import or even directly open CSV files.

The basic application for CSV files: Import or export data. Data base programs often export data as CSV files. Also if you want to transport Excel data into a database program, often the CSV file type works.

Advantages Disadvantages

Smaller file size.

Faster opening and closing of files.

Unlimited rows and columns.

No code or macros possible and therefore save to open.

CSV files are basically text documents and therefore can’t contain formulas or formatting, pure data.

No more than one “worksheet”.

  ODS

ODS stands for Open Document Spreadsheet. The file type is designed to work on both Excel and other applications, e.g. LibreOffice. From the Excel point of view, ODS doesn’t support all the features. Text and data is usually saved well though. But formatting and many other features (for example charts, tables, conditional formatting) might run into problems.

Advantages Disadvantages

Can be opened in Excel and LibreOffice.

You might lose formatting or features which are not supported. For a detailed lists of the features supported, please refer to this website.

  XLSX (Strict)

Besides the regular XLSX format, Excel offers to save your Excel workbook as “Strict Open XML Spreadsheet”. So what is the difference between those two? Basically, the two file types use the same structure. But:

The Strict variant has less support for backwards compatibility when converting documents from older formats.

Furthermore:

The Strict variant of XLSX disallows a variety of elements and attributes that are permitted in the more common Transitional variant […]

Advantages Disadvantages

Same as XLSX.

Additionally: Opening in other applications than Excel might work better.

Some backward compatibility problems might occur.

  PDF

As you’ve probably heard of PDF files, we won’t go much into detail here. PDF stands for portable document format and has the reputation of not easily being manipulated. Also, it’s quite save to say that how a PDF file looks on your computer, it will most probably look like this on most other computers.

And that’s it for the advantages. I recommend using it when you present your final results in the following cases:

The recipient of your file should not edit it any more.

Formatting should be exactly preserved.

There is no database included, just the summarized results.

That could be the case if somebody wants to check your results on an iPad or a similar device. But: In many cases it would be professional (and nice) to also send at least parts of your Excel file. That way, your recipient could at least take a look at the calculation process.

Advantages Disadvantages

Static, therefore contents can not (easily) be manipulated.

The document always look the same.

No macros.

Formulas etc. will be lost.

Difficult (and troublesome) to further edit the file.

(Depending on the contents) large file size.

  XLAM

XLAM is the file extension for Excel add-ins. Also our add-ins (for example the popular “Professor Excel Tools“) are XLAM files. They can contain VBA macros, worksheets as well as forms, images and individual ribbons. If you double click on such file, it will open in Excel and you can use the funcationality of the add-in. But next time you open Excel again, it won’t be available any longer. You rather have to enable the add-in within the options (File –> Options –> Add-Ins).

As the XLAM file type is not really an alternative for all the other file extensions above, we skip the advantages and disadvantages.

  Comparison of file types

So if we put all the advantages, disadvantages and facts from above together, we come to the following comparison:

  .aq_block_23038 h1 { color: #2b803f; } .aq_block_23038 p { color: ; }

EXCEL ADD-IN: PROFESSOR EXCEL TOOLS

.aq_block_33038 h1 { color: ; } .aq_block_33038 p { color: ; }

Save a lot of time in Excel!

'Professor Excel Tools' extend your Excel with great new functions. The goal: Make you saving time. Help you with your every day work. Try it for free now - usually it's worth within the first days of use.

Add more than 60 great functions to Excel.

Extremely easy to use, lightweight.

No sign-up, no installation. Try it for free!

.aq_block_43038 h1 { color: ; } .aq_block_43038 p { color: ; }

.aq_block_53038 h1 { color: ; } .aq_block_53038 p { color: ; }

Overview Features Download (No sign-up, download starts directly, no installation)

  Which file type should you use?

Decision Tree

In order to define the best file type for your Excel workbook, please follow the decision tree.

Follow the Professor Excel decision tree for finding the best file type for your Excel workbook.

The first question: Do you just want to display your results only? Then you could also consider the PDF format. But if the recipient of your work wants to edit or follow up your calculations, you shouldn’t use PDF.

Does your file contain VBA macros?

If yes, choose XLSB if you have a large file. Choose XLSM for a file size smaller than app. 10 MB.

If no and you got a large file (larger than app. 10 MB), also choose XLSB.

If no (you don’t have macros) and your file small, follow the tree on the right hand side.

The lower two levels of the decision tree are just about compatibility: If you want to open and edit your workbook with another software, choose the strict XLSX or ODF format.

If you want to make sure that you can open it with versions of Excel 2003 or earlier, go for XLS.

  Summary

In conclusion, you can distill a quite short table (if you don’t need to consider old file types and other applications as LibreOffice). You just have to answer two questions: Do you need VBA macros and do you work with a large amount of data?

Der Beitrag XLSX, XLSM or XLSB: Which File Type Is the Best for You? erschien zuerst auf Professor Excel.

from Professor Excel http://ift.tt/2kGZjx4

[file-formats-programming]

Remove Existing Printer Settings in Excel File & PDF/A-1a Compliance Support using Java

What’s new in this release?

Aspose team is pleased to announce the new release of Aspose.Cells for Java 17.5.0. This release includes many new features and enhancements along with some critical bug fixes that further improve the overall stability of the API. It is also recommend for clients to use the powerful Aspose for Java APIs directly in their Maven Projects with simple configurations. Aspose.Cells now allows users to create PDF with PDF/A-1a – Level A (accessible) conformance. It already supports PDF/A-1b – Level B (basic) conformance. Following code snippet demonstrates how to convert an Excel file to PDF compatible with PDFA-1a. Sometimes developers want to remove existing Printer Settings in Excel file. These settings are found inside the “[file “root”]\xl\printerSettings” folder in the form of .bin files. Aspose.Cells can remove existing printer settings in the Excel workbook. There is no direct option available to create custom paper sizes in MS Excel, however users can set custom paper size of the desired worksheets when rendering Excel file to PDF file format. The documentation article explains how to set custom paper size of a worksheet using Aspose.Cells APIs. Earlier, Aspose.Cells does not shift first row down when inserting rows. But for some users’ requirements, it has supported this feature now. When users copy worksheet into another worksheet, then page setup settings of the source worksheet are not copied to destination worksheet. Please use PageSetup.copy() method to copy the setting of the page setup from source to destination worksheet. Below are some important new features, enhancement and bug fixes part of this release.

Change the language of the predefined words for Pivot Table

Options to embed the hyperlink in an Excel cell

NullPointerException occurs when the filter exists outside of the named range

Copying a worksheet unhides filtered rows in the output HTML file

Contents are shown differently (some text is missing) in non IE browsers (e.g Google chrome) - Excel to HTML rendering

Conditional formatting is lost when refreshing PivotTable in the spreadsheet

Conditional formatting style is broken

Excel formula is not working properly - it is shown as 6 instead of 0

Saving XLS file with graphs uses 100% CPU

Title is obscured by the trend labels in the output PDF

Workbook.getFonts() shows additional fonts after reload the same spreadsheet

Merging/Copying to Excel sheets - Spaces in the beginning of the cells are not getting retained

Invalid string in the file - error occurs on opening an Excel file

Names of some public methods parameters changed in the newer version

Worksheet.autoFitColumns() does not work well with the cells having line breaks

Sorting the Excel file containing comments corrupts the output Excel file

Sorting comments causes "Excel found unreadable content...." error when opening the output file into MS Excel

Subscript and superscript issues in OpenOffice ODS file when converting to HTML or PDF

Other most recent bug fixes are also included in this release

Newly added documentation pages and articles

Some new tips and articles have now been added into Aspose.Cells for Java documentation that may guide users briefly how to use Aspose.Cells for performing different tasks like the followings.

Convert Excel file to PDF format compatible with PDFA-1a

Remove Existing PrinterSettings of Worksheets in Excel file

Overview: Aspose.Cells for Java

Aspose.Cells is a Java component for spreadsheet reporting without using Microsoft Excel. Other features include creating spreadsheets, opening encrypted excel files, macros, VBA, unicode, formula settings, pivot tables, importing data from JDBC ResultSet and support of CSV, SpreadsheetML, PDF, ODS and all file formats from Excel 97 to Excel 2007. It is compatible with Windows, Linux & Unix and supports all advanced features of data management, formatting, worksheet, charting and graphics

More about Aspose.Cells for Java

Homepage of Aspose.Cells for Java

Download Aspose.Cells for Java

Online documentation of Aspose.Cells for Java