Printing blank pages with conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1869814%22%20slang%3D%22en-US%22%3EPrinting%20blank%20pages%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1869814%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20cells%20A16%3AY315%20that%20are%20conditionally%20formatted%20to%20have%20a%20border%20around%20them%20if%20data%20is%20put%20into%20the%20cells.%26nbsp%3B%20I%20have%20it%20set%20to%20print%20rows%201%3A15%20for%20all%20sheets%20of%20data.%26nbsp%3B%20If%20there%20is%20no%20data%2C%20the%20cell%20is%20blank%20and%20I%20do%20not%20want%20it%20to%20print.%26nbsp%3B%20However%2C%20when%20I%20print%20preview%20it%20is%20showing%20that%20I%20will%20be%20printing%2010%20pages%20of%20data%2C%20rather%20than%20one.%26nbsp%3B%20How%20do%20I%20work%20around%20this%3F%26nbsp%3B%20I%20do%20not%20want%20to%20have%20borders%20on%20the%20cell%20if%20it%20is%20blank%2C%20which%20is%20why%20I%20did%20the%20conditional%20formatting.%26nbsp%3B%20I%20have%20several%20people%20who%20will%20be%20using%20this%20workbook%20who%20can%20barely%20input%20the%20data%20and%20cannot%20manipulate%20the%20pages%20(e.g.%2C%20print%20pages%201%20of%201%20or%20use%20a%20filter).%26nbsp%3B%20If%20anyone%20could%20help%2C%20I%20would%20be%20most%20grateful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1869814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1870448%22%20slang%3D%22en-US%22%3ERe%3A%20Printing%20blank%20pages%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1870448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863348%22%20target%3D%22_blank%22%3E%40NV_Social_Worker%3C%2FA%3E%26nbsp%3BI%20would%20suggest%20a%20redesign%20of%20the%20form.%20Avoid%20conditional%20formatting%2C%20avoid%20merged%20cells.%20In%20stead%2C%20use%20a%20structured%20table%20to%20capture%20input.%20Center%20texts%20across%20selected%20cells%20(this%20is%20in%20the%20alignment%20options%20the%20Home%20ribbon).%20This%20will%20create%20the%20same%20visual%20effect%20as%20merged%20cells%20without%20the%20problems%20that%20often%20arise%20with%20merging.%20I've%20also%20used%20some%20radio%20buttons%20to%20select%20the%20type%20of%20therapy%20given.%20More%20intuitive%20and%20you%20can%20only%20select%20one%20at%20the%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20needed%2C%20you%20can%20add%20Data%20Validation%20to%20the%20cells%2Franges%20that%20require%20input.%20Like%20the%20date%20column%20shall%20only%20accept%20dates%20between%20a%20start%20date%20and%20an%20end%20date%2C%20minutes%20must%20be%20a%20number%20between%201%20and%20600%20(as%20an%20example)%20and%20the%20service%20columns%20may%20only%20contain%20%22x%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20expands%20automatically%20once%20a%20new%20entry%20is%20typed%20directly%20beneath%20it.%26nbsp%3B%3C%2FP%3E%3CP%3ESet%20the%20page%20lay-out%20to%20print%20one%20page%20wide%20by%20%3CBLANK%3E%20tall.%20This%20will%20automatically%20print%20the%20used%20range.%3C%2FBLANK%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20your%20users%20seem%20to%20be%20therapists%20of%20some%20kind%2C%20they%20must%20be%20capable%20of%20learning%20a%20few%20simple%20rules%20for%20filling%20in%20a%20form.%20They%20don't%20have%20to%20become%20Excel%20wizards.%20See%20if%20this%20works%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a workbook with cells A16:Y315 that are conditionally formatted to have a border around them if data is put into the cells.  I have it set to print rows 1:15 for all sheets of data.  If there is no data, the cell is blank and I do not want it to print.  However, when I print preview it is showing that I will be printing 10 pages of data, rather than one.  How do I work around this?  I do not want to have borders on the cell if it is blank, which is why I did the conditional formatting.  I have several people who will be using this workbook who can barely input the data and cannot manipulate the pages (e.g., print pages 1 of 1 or use a filter).  If anyone could help, I would be most grateful.

 

Thank you!

2 Replies
Highlighted

@NV_Social_Worker I would suggest a redesign of the form. Avoid conditional formatting, avoid merged cells. In stead, use a structured table to capture input. Center texts across selected cells (this is in the alignment options the Home ribbon). This will create the same visual effect as merged cells without the problems that often arise with merging. I've also used some radio buttons to select the type of therapy given. More intuitive and you can only select one at the time.

 

If needed, you can add Data Validation to the cells/ranges that require input. Like the date column shall only accept dates between a start date and an end date, minutes must be a number between 1 and 600 (as an example) and the service columns may only contain "x".

 

The table expands automatically once a new entry is typed directly beneath it. 

Set the page lay-out to print one page wide by <blank> tall. This will automatically print the used range.

 

Since your users seem to be therapists of some kind, they must be capable of learning a few simple rules for filling in a form. They don't have to become Excel wizards. See if this works for you.

Highlighted

@Riny_van_Eekelen Thank you, so much!  I will try this to see if it works.