Formatting is like fashion, it’s how you make yourself look good. We say a person has good fashion sense when their appearance is enhanced by their attire. Type, color, pattern, accessories and grooming are all coordinated well with an aesthetically pleasing effect.
Even for people who have decent personal fashion, aesthetic sensibility does not always translate to good spreadsheet formatting. Let’s face it, it is not easy to make Excel both function well and look good. But don’t underestimate the impact of formatting on your first and lasting impression! Here we’ll see some built-in help and some simple techniques.
Coordination
Office tries to help us groom and dress up our documents by packaging elements that look good together. It gives us freedom to choose while protecting us from too many choices. This gentle guidance comes in two voices, themes and templates. I’ll deal with templates in a later section.
A theme provides a consistent appearance throughout a document. This plays out as a combination of complementary colors, fonts and object effects. Think of each theme as a department store mannequin dressed in an ensemble that clicks. You can simply point and say, “I want to look like that.” Excel, Word and PowerPoint all have the same 34 themes, and allow you to create your own.
For fonts, Alt-H,F,F, there are two Theme Fonts at the top of the list, labelled with (Headings) and (Body). Some themes are the same font for both, some are different. Some themes have fonts that are larger and can cause the loss of five or more rows of screen space.
For colors, Alt-H,H, Theme Colors provide an ample palette of 60 colors; 10 base colors in the top row defined as 2 Background, 2 Text and 6 Accent colors, plus 5 shade variations, 3 lighter and 2 darker of each base color.
The Themed Cell Styles gallery, Alt-H,J gives 24 of the 60 Theme Colors, based on the 6 theme accent colors with the 3 lighter shades of each. Be aware that the Themed Cell Styles are named with the percent shade value that is opposite the one on the Theme Colors dialog tooltip. For example, the lightest shade is “20%” in Styles, and “80% lighter” in Colors. This matters if you are programming the shade property in VBA, which uses the value from the Theme Colors tooltip.
The main benefit of using Cell Styles is the ability to define custom styles (Alt-H,J,N) that can be merged (Alt-H,J,M) across open files. Thus, you can fine-tune the coordination of formats for specific purposes, like input cells, formula cells, background, etc. If you define styles in your startup file they’ll be accessible to import to any other file. Still, be conscious of whether you are using themed formats or non-themed formats in your styles.
You might be satisfied with the default theme and never use the other 33, but you should at least check them out. The gallery gives you a miniature encapsulation of each, and you can easily scroll the themes with the arrow keys to see the effect on your sheet. Chapter 4 Startup Themes Sheet provides an overview.
If you do decide to change theme, be aware of their behavior. As you apply formatting to your sheet, if you stay within the theme fonts, colors and styles, then they will change when you apply a different theme. If you stray outside of these theme-controlled elements then you must manage each style element individually, or create your own theme. If you never change theme, then it doesn’t matter. If you share the file, others might choose a different theme, but it’s unlikely and not a good reason to avoid themed elements.
The lesson for me on themes is to respect the default. I’m not an artist, and I appreciate the helpful suggestions the theme gives me. It has plenty of nice coordination, and I seldom feel the need to explore outside of it. Then I can focus my attention on applying its nuances to enhance and balance the total picture that the sheet presents in both static and Conditional Formatting. That said, themes don’t have a monopoly on aesthetic coordination, and it is fair game to use any other features, particularly if you have artistic talent.
Not to be confused, there is another type of theme called Office Theme. Office Theme is much less interesting and is only for you. It sets how your screen looks as you are using Office, with four options. This can be set in File – Options – General in each app, or File – Account once for all apps.
Alignment
More than most formatting, alignment has a way of exposing the common spreadsheet malpractice of “too many words.” Long text is meant to flow freely across the page, as in a book. When it’s crammed into a cell, juxtaposed with columns of short text and numbers, it strains against its bounds, demanding unwanted attention.
A common case is a sheet used to track project tasks, or meeting notes. You would think to use Excel – why not? It starts off with some generic columns – task, priority, etc. – until the one with all the notes begins to take over. Soon you find that by wrapping long text, the row height grows, exposing alignment inconsistencies in other columns.
There are better ways to manage notes without excessive text in any cell. The most obvious is to use fewer words. Then, if you designate columns for things you might want to filter for, such as dates and names, you start the journey back to Excel’s strength. Still, a small amount of wrapped text can look ok and help manage space. If you use any wrapping, be intentional about alignment.
Consider the appearance of the table below. The left shows Word table alignment, showing the normal wrapped, top-left alignment for any data type. The right shows Excel wrapped alignment, with the default bottom-left-text, bottom-right-number alignment.
The point is that Word and Excel are cousins with very different talents. Word treats letters and numbers the same for best readability, wrapping paragraphs and table cells alike with consistent left-alignment, easy on the eyes. Excel, on the other hand, clearly distinguishes numbers from letters, because they may be subject to transformation and validation. A number in a cell is likely to be accompanied by other numbers in the same column. Naturally then, numbers are right-aligned, as we learned in school, to clearly show their significance.
Excel always defaults text to the left and numbers to the right, and everything to the bottom. When you have text headers for numeric columns, the headings are left and the data are right. The wider the column and taller the row, the worse it looks. A quick answer is to align headings of numeric columns to the right, but the best answer depends on the case.
If numbers in a column are all the same number of digits, like percentages, then alignment is not an issue and might be right, left or center. A column with various orders of magnitude should be aligned right. Similarly, a column with variable-length text should be aligned left. Sometimes an indent looks good, to pad the cell edge a bit.
Variable decimal places can be made to line up on the decimal point by custom number format with a “?” placeholder per decimal place. For example, 0.???? will line up the decimal point for 0 to 4 decimal places. However, it may be a better choice to format a constant number of decimal places, such as 0.00.
While I can logically understand the rationale for right and left alignment differences, I can’t say the same for the default bottom alignment. Tall rows are ugly enough to start with. With wrapped text in one column determining the height, and a single word or number at the bottom of another, it looks just awful.
I try to minimize row height and keep it uniform if possible. Again, if you’re writing paragraph-length cells then stop and think about what you’re trying to do. You might do better using Word, or splitting out the text into smaller cells. If I need to wrap at all, I select the whole sheet and top-align.
Clean Screen
An Excel sheet has more than 17 billion cells. One screenful has less than 1000 cells (on most screens), and even that can tax a user’s grasp if not designed well. I design for one screenful whenever possible, but the key objective is to “un-busy” the screen. There should be enough space and format variety to naturally draw attention to the point. If the screen is filled with data then there is no room for information.
An introduction or summary sheet can help provide the stepping stones to the detail. A few clean sheets with good names is better than one that looks crammed or requires scrolling. If one screenful is not possible, try for one screen width, so that only vertical scrolling is required. Think about how it will look on different computers with different screen sizes. You can’t please everyone, but you can try to please most.
View Options
Various parts of the screen can be hidden, to gain space or just to look clean. Hiding headings and gridlines are sheet-level settings, and transfer to other users. They are thus design options for a dashboard, instructions, or other sheet in which the user has no use for columns and rows. Both can be hidden per sheet from the View tab, Alt-W,V,H to hide/unhide headings, and Alt-W,V,G to hide/unhide gridlines.
I leave both on during design and build, then decide in refinement whether to hide or not. I sometimes hide headings, and I normally either hide gridlines or cover them with full-sheet background color. Formula bar can also be hidden, Alt-W,V,F, but this is an application-level setting so it is not a good design option.
You can also leave tips for users on how to enhance sheet visibility with user-level settings. There are various ways to manage the space on the screen to give a comfortable, uncluttered appearance without the need to zoom out. This table lists several ways to increase screen space, with the number of rows gained, beginning with the placement of the QAT.
All of these options retain headings, and all except Full Screen retain the formula bar. Auto-hide keeps the ribbon accessible, by Alt key or by click at top; Full Screen does not, and is canceled by Esc key.
Grouping
Groups are applied to complete rows or columns to enable summarizing and convenient hiding and unhiding of the subordinate rows and columns. When you group a range of rows, you get a group bracket outside the row header, and a button at the bottom to expand or collapse the group.
If the grouped rows contain numbers, you can use =SUBTOTAL() in the summary row, the one with the button, to subtotal the group. Then, all groups can again be grouped to a grand total row, where =SUBTOTAL() ignores the subordinate subtotals within the range, to give an accurate grand total. The groups can then be collapsed to show just the total or subtotals, and the same can be done with columns.
This is a neat effect, especially with the automatic Subtotal function in the Data ribbon, Alt-A,B. Once you have a proper data table, that is, where the first row has headings and every record has every field filled, the Subtotal function groups by the primary sort column, inserting subtotal rows at each change in that column. For example, if you have economic data by country, sorted by region, it could give you subtotals by region, with your choice of statistical function.
As cool as that sounds, it is dated and has limited value. You cannot use it If you have formatted the data as a table, versus a range that’s structured like a table, and you must ensure that the data are sorted by the summary field. And oh yeah, there’s this thing called pivot table that does all that and a lot more. I would go ahead and format as table, and spend the time to learn how to work with pivots.
The reason for this background is to explain how grouping is still useful as a screen management tool. However, because it is based on the subtotaling model, the summary row is by default below the group, and the summary column to the right. This is awkward for outlining, in which the more significant rows are followed by the less significant.
I sometimes use row groups to create a text outline, and column groups to hide configuration data columns. In either case, I uncheck both summary checkboxes in Outline Settings, Alt-A,L, to reverse the summary position – i.e., summary row above the group, summary column to left of the group.
To create a group, select the range of subordinate rows or columns, then Shift+Alt+→, or Alt-A,G,G. To ungroup, Shift+Alt+←, or Alt-A,U,U. If you forget to select, it will prompt you to select either columns or rows.
Group buttons consume space and add clutter when not needed. You can regain that space by hiding them with Ctrl+8. Grouped rows will usually contain a heading, and grouped columns can use a single character or symbol in a small header column, with a cell note or Data Validation message as a reminder that there is a group and how to expand and collapse it.
If the project will contain VBA code, it is simpler and more versatile to hide sheet sections with code. This is used in Startup Colors Sheet, Project Tracker and Fun and Games. The benefit of learning to use grouping is when you want to avoid VBA.
Camera Tool
One of clunkiest limitations of Excel is the way that column width and row height affect the entire sheet. It’s a common need to have multiple areas on one sheet that you would like to format independently, yet keep close together. You have to choose between formatting and positioning, right? Not necessarily. You can use the camera tool to get a remote view of another area or another sheet.
For example, you have one table that wraps text and has variable-height rows, and you want to put another one next to it that has standard-height rows. Or maybe you have a flow chart on a sheet that has a tight grid like graph paper, and you want to combine it with a freely-sized column area. With camera you can copy an area and paste it as a live view – not as a static picture, but as a true remote image. When the copied area changes, the image dynamically reflects the change.
The camera tool works well when one area is user-interactive, and the other is responsive to the first, or for setting up the screen for PDF or print. Navigation between areas may be acceptable for a developer, but a hindrance for a user. If both areas are interactive then you might need to provide navigational aids, or a different solution.
The camera tool is not in the ribbon. To try it, copy some data and paste somewhere as linked picture, as shown below. Then change the source data and see the copy change. The copy can be formatted like any picture, enlarged, reduced, etc. If you like it, add the tool to the QAT via Alt-F,T,Q, select “Commands Not in the Ribbon”, and scroll to find Camera.
Examples of linked images are in Team Calendar and Fun and Games Poker.
Custom Number Format
Custom number formats are convenient and powerful ways to enhance the display, both as static and conditional formats. They’re like a miniature set of conditional formats for four conditions: >0; <0; =0; text. Each of the four conditions can be formatted differently, combined with text, or hidden, and additional criteria can be entered as well. Here are a few simple examples:
Furthermore, custom conditions can be entered into the formatting string. The conditions are entered in brackets and are applied in the order entered, and are limited to two conditions. For example, this string formats millions with “M”, thousands with “K”, and anything smaller as entered. [>=1000000]0.0,,"M";[>=1000]0.0,"K";0
The number format string can set conditional font color as well, also in brackets, such as [Red]. More details can be learned by reading the custom codes that are included in Excel, and from online or other sources. The value of using number format alone versus Conditional Formatting depends on how many conditions and formatting options are required.
Hiding data is a common need for control parameters or for whatever the user doesn’t need to see. Column or row groups and hidden sheets can be used to store data out of sight, and format can simply hide the data from view. An example is to hide column data with “;;;” when it’s the same as the cell above, such as year and month in a calendar table.
Formatting Navigation
Most of the Home tab is all about formatting. It’s a rich set of options, with multiple ways to use them. First, the ribbon has them neatly laid out, with 17 buttons and 10 drop-downs just in the Font, Alignment and Number groups. The right-click context menu includes a small palette chock full of the most common format options, 11 buttons and 6 drop-downs. There are at least 13 direct shortcut keys to common number formats, font styles and borders. Finally, the Format Cells dialog has every possible option, too many to number.
The most important tool to master is the Format Cells dialog, Ctrl+1. It gives you all formatting types and options in one place, some that you might easily miss with other methods. For example, the ribbon Fill Color palette, Alt-H,H, has all colors available, but the Format Cells dialog adds gradient and pattern fill effects. If you only use the ribbon palette you might never realize these options exist. The ribbon Font group has most font settings, but the Format Cells dialog adds effects like subscript and superscript, as well as a one-click reset to normal font.
Spend time practicing navigation within the Format Cells dialog. Use → and ← between tabs, or hit the first letter of the tab name; use Tab key into the tab and between options; use any underlined character to jump to options, or with Alt key to jump from a text box to the option; use ↓ and ↑ within picklists. Practice setting options with Space or Enter, across multiple tabs, before hitting OK.
Here's an example key sequence to hide cell contents: Ctrl+1, N, Tab, ↓ (hold it down until it reaches the last item, “Custom”), Tab, ;;;, Enter. The whole thing takes maybe three seconds. The other tabs take a little more practice, or mouse clicks, to use efficiently.
All font formats – font, size, style, effect, and color – can be set on selected text within a text cell as well as on the whole cell. This can be useful for emphasis or if symbols are used.
留言