Conditional Formatting & Binary Worksheets

[Caid]

Morning all.

I've been tasked with reducing the size and load times of several worksheets.

In addition to converting the reference document to SQL to the import into excel, I've also changed all the documents to binary worksheets. This is where the issue begins.

I had conditional formatting on a range of values which would compare each row to the limit. (I.e. if we have a value of 80% and a limit of 75% then the cell will turn red). I've not really changed the document other than this and I'm not too sure on the actual advantages / disadvantages of binary worksheets. Do I need to keep the document as it was before, or do you think the issue lies elsewhere and binary worksheets can still have conditional formatting?

Kind regards,
Caid

 

[etaf]

not an expert on XLSB - but it is supposed to save time on load and space

I have a couple of spreadsheets saved in XLSB and they work ok when opened with quite a lot of conditional formatting , but no external links or VBA ( I understand you dont get the VBA security warning when opening XLSB with macros

XLSX or XLSB: Why to save a workbook in binary format

Facts & Myths About Binary Excel Workbooks: Learn the advantages & disadvantages of Excel's binary file format XLSB

www.spreadsheet1.com

Excel XLSB vs XLSX file format. The Pros and Cons of XLSB Files

Why use the .XLSB file format when working with large Excel files. The .xlsb format and other tips to enhance your Excel experience

analystcave.com

You are using excel 2016 based on your profile

Do I need to keep the document as it was before, or do you think the issue lies elsewhere and binary worksheets can still have conditional formatting?

as i say Mine work OK with conditional formatting

if you save as XLSX - the formatting works fine
when saved as XLSB it stops working

I'm not sure what is stripped out - but according to various searches it should still work , as its just a form of compressing the file -

 

[MARK858]

I understand you dont get the VBA security warning when opening XLSB with macros

Yes you do as long as they aren't in a trusted location and your security settings aren't to allow all macro's, all my files that I create are xlsb and I get the below if not in a trusted location

 

[etaf]

@MARK858​

thanks for the clarification, as i said I dont use VBA in my files , so was not sure if that was the case - some websites gave a warning about VBA in Binary files....
But thanks for the facts

I'm assuming in your experience conditional formatting is OK, I only have 2 files i used in the past , which have used XLSB and conditional formatting worked OK -

 

[MARK858]

only have 2 files i used in the past , which have used XLSB and conditional formatting worked OK

I personally haven't had any issues with conditional formatting in xlsb, not saying that there aren't any just I haven't had any issues specific to xlsb files.

 

[etaf]

(I.e. if we have a value of 80% and a limit of 75% then the cell will turn red). I've not really changed the document other than this and I'm not too sure on the actual advantages / disadvantages of binary worksheets

how is the conditional formatting set-up - using a formula ?

In addition to converting the reference document to SQL to the import into excel,

Is the import into excel causing an issue - perhaps overwriting the formatting in cells ?

as mentioned in my post

if you save as XLSX - the formatting works fine
when saved as XLSB it stops working

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed