Ever had to fit a tall skinny array into a short plump page? Maybe you did it with scissors and tape, and like me thought, "there's got to be a better way." Well of course there is. With array functions we can wrap columns and cut up the array in many ways. Today I'll show you one, along with more array tips, for example: why doesn't nested SORT and FILTER of a Table work, and is there a workaround? Well of course there is. Read on, my friend.
The core xlegant principles - purpose, integrity, transparency, elegance - are critical in today's saturated information market. We earn trust and loyalty with first impressions, one screen or page at a time. The more coherent each of those rectangles is, the more fluent our message will be.
General Layout Points
The many digital and paper rectangles through which we receive information deserve special attention as we prepare to publish or transmit information.
TV, computer, tablet, phone and paper all share similar ratios, as shown here. Spreadsheet pixels are visible cell estimates, with ribbon and QAT hidden (max) or unhidden (min).
The point is that layout depends on the intended use. If the majority of your audience uses the same type of computer as you do (e.g. in a corporation), and will only view your sheet digitally, then you can design to the standard screen proportions. If needed, give tips to increase visible range (e.g. Ctrl+F1, Ctrl+Shift+F1). To support tablet, phone or print users requires additional care.
Excel Print Tips
If printing the whole sheet, design the sheet with that intent. Use View - Page Layout (Alt-WP) to preview the fit.
To print part of the sheet, use Print Preview (Ctrl+P) to refine your layout.
To print a table, put cursor in the table, Ctrl+P, select Print Selected Table
To print a range, select the range, Ctrl+P, select Print Selection
Use scaling to fit all or fit columns, but don't cram too much into the page.
For tables that span pages, Excel has no option to make the headers repeat on each page (Word does, see below). Some options are
Break up (filter) the table into coherent and print-friendly sections.
Don't print the data, use a pivot or summary instead.
Word Print Tips
Word is superior for printing, and essential if writing more than a few lines of text. Here are some tips for combining Excel tables in Word:
Word does not have the scaling feature. It may take some trial and error to get an Excel table to fit into a Word page.
When you paste a table from Excel to Word, start by fitting it to the window, Alt-JLFW, then decide page orientation. Default is cell wrap, so you might want to truncate some long entries and adjust column widths.
If the table spans pages, select the header row and in the Table Properties dialog, Row tab, click "Repeat as header row at the top of each page."
If the table does not need to be formatted within Word and the data will not be too small to read, paste it as a picture, then scale as needed, keeping the aspect ratio. Set picture layout option, Alt-JPTW, to enable positioning the picture with text wrap.
To change from portrait to landscape for a page, insert section breaks before and after so that layout options will only apply to that page. Then you can also reduce margins to give the data more room.
Purpose
The feature topic today is how to maximize the use of the output rectangle. This is done by repeating or "wrapping" a few select data columns to approximate the output dimensions. This is similar to the WRAPCOLS function, with expanded features. WRAPCOLS can only wrap a single column and does not include the header. Our requirements:
Select table columns and headers for output
Enable sort and filter of the data
Wrap the selected columns
specify the maximum number of rows, and,
specify whether to wrap evenly or to the max rows, or,
alternatively, specify number of wraps
Repeat headers for each group of wrapped columns
I'll be using Array functions with a source Table. The output will be manually formatted for column widths and conditionally formatted for fill and border style.
Select, Sort, Filter
There are many ways to sort and filter, most evidently within the Table itself. However, to select output fields and control the process efficiently I'll let the Table be, using SORTBY and FILTER functions with a selection dashboard to generate the data set. Headers will be generated in parallel.
The dashboard input cells are named for reference in the data array formula, cell M4. Range names are distinguished from internal LET names by color, also by use of underscore.
Notes
The use of INDIRECT is necessary to turn a string into a reference.
SORTBY converts the table to an array; therefore FILTER must reference the filter field by column index, which is derived by matching the field name to the table header.
CHOOSECOLS allows picking columns in any order.
This example has limited functionality, not intended for full flexibility.
The header array in cell M2 picks the same columns from the table header.
The Wrapping
The final output is the wrapped layout array, controlled by another dashboard. In order to best understand how to use the parameters, form controls are added as two spinners and a checkbox. Here's a small set of countries with population > 100M for demonstration.
The formula uses two levels because of a frustrating issue when working with arrays in LAMBDA, which I'll explain below. Here is the internal function that does all the work.
The function is recursive in order to wrap as many times as needed, where ocx is a wrap counter from 0 to owraps-1. Steps:
line 9, owraps, count the number of wraps from maxrows and/or minwraps
line 12, orows, count the number of rows from Boolean even
line 15, stack, assemble the wrap stack
DROP the top orows*ocx rows of the array
TAKE the top orows of what's left
VSTACK the headers on top
line 19, recurse and HSTACK the wrap stacks
The note to self on line 17 explains the issue which I tried many ways to resolve. The output always had an extra column at the right. When I did DROP(output,0,-1) outside the function, it removed the extra column, but not within the function. Hence I took the easy way out for now, using another function to do the drop:
Sample Pages
A filter of population > 10M returns 90 countries, too many for a single column to fit on one page, but there's a good chance that wrapping will make it fit. Adjusting the parameters shows how we can test the fit of various layouts.
The easiest way to judge the fit is to select the output array and Print Preview the selection. The easiest way to select the output array is Ctrl+*. I inserted a hidden, empty row 2 so that Ctrl+* will only select the array, not the row 1 header. With the cursor in the array, the key sequence for each test is Ctrl+*, Ctrl+P.
A letter size page with default margins will hold 47 rows in portrait, 34 in landscape, including the header, with no scaling and default font size.
When columns span pages, they keep their headers; when rows span pages, they lose them.
Therefore, I might be okay with column spill, if what spills to page 2 is a complete wrap stack, i.e. a full set of the selected output columns; I would not accept column spill in the middle of a stack, nor would I accept row spill with no headers. If the spill is minimal, than scale to fit might be okay. Otherwise, I'd consider reducing the data set to fit, or other options.
Sample 1
Wrap settings: Maxrows=46, uneven
Print settings: portrait, no scaling
This fits one page and fills the usable space on the left, but I see that even-wrapping or maxrows=45 would give me two even columns.
Sample 2
Wrap settings: Maxrows=35, uneven
Print settings: landscape, no scaling
This spills two rows to page two. To fit one page, I could reduce maxrows to 33, or select even wrapping. If I had a reason to, scale to fit would probably not shrink it too much.
Sample 3
Wrap settings: minwraps=4
Print settings: landscape, no scaling
The fourth wrap stack spills to page two. Depending on the document context, this could be acceptable, since it's a complete three-column set and can stand alone. I can also fit columns to the page to see how small it gets.
Sample 4
With "Fit All Columns on One Page"
This looks too small. I could optimize column widths and try again or explore other options.
Formatting
It's always best to format headers to contrast with the data. If you're using the page maker for print layout only, the formatting doesn't have to look pretty on screen. Estimate how many columns and rows you'll ever need, then format the header row and the data rows to that extent. Wrapped tables look best with consistent column widths by field.
If you want the screen to look good as well, use Conditional Formatting. These rules turn the formatting on when the cell is not empty. In addition to differentiating headers and data rows, they also puts vertical borders on the left and right of each wrap stack.
Hey You!
How about some feedback? Like this? What else? Who are you, what do you do? Write me, please.
Comments