Lesson 7 – Formatting & Printing

In the last lesson you were exposed to some of Excel’s most commonly used Functions and Formulas, as well as the difference between the two. We went into group in the Function Library, and you were exposed the amazing depth and powerful functionality of the intrinsic functions you have at your fingertips with Excel. We reviewed actual examples throughout the lesson so you could see different functions in action in real business scenarios. We also discussed error management, and how it can be a valuable tool in setting up your workbooks, as well as being able to hide errors from users if the need arises.

Up until now you’ve been primed on the evolution of a workbook, and how to take the necessary steps to bring it to the point where is fits your needs. You’ve:

1. Learned how to put good spreadsheet design elements into place

2. Input your primary row and column headers

3. Populated your worksheet with some sample data

4. Added some functions and formulas to test your sample data

5. Replaced sample data with live data (maybe even using referential functions like VLOOKUP or INDEX/MATCH along the way…

If you’ve been following along with your own example workbook, then by now you’ve designed the layout for the particular kind of workbook model you were creating (finance, marketing, customer management, etc.), All of your information is in your workbook, formulas have been tested, and other than some possible user input, you’re good to go right? Not quite, it’s not set up for distribution yet. It still needs to be formatted to make it easy for your end users to interact with, whether they’re just looking at your information, or they’re actually responsible for driving information back to you. A lot of financial types might tell you that what’s really important is the information. Well, that’s true, but presentation is how you make your information palatable to your recipients, so it’s equally important. If the information is easy for them to get to and absorb, then you’ll have an easier time telling your story, but if you make it hard for people to digest your information then your story loses value. Fortunately, oftentimes just some simple adjustments can make an otherwise boring workbook look great. That’s what we’re going to talk about in this lesson: how can you format your workbooks for the maximum impact. Once you’ve done that we’ll discuss the various ways to print it out.

In this lesson we’re going to show a few different workbook scenarios and ways to improve on them. Again, there is no one way to properly format a workbook/worksheet. It largely depends on 1) your personal style, 2) your recipient’s needs and expectations and 3) any technical or corporate policy limitations. Remember though, if in doubt, you’ll never go wrong with conservative formatting, whereas overdoing it can be just as difficult on users.

Themes

Themes are like the primary structure for your workbooks, and you generally only need to set them once (if you choose to do so at all). Themes govern some of the default formats in your workbooks, like the color scheme that will be applied or the primary font, etc. Hopefully by now you’ve had some time to work with them to see what particular theme(s) fit your style or needs, because as with so many other options that Microsoft gives you, there are just too many possible combinations to cover here. The default theme is the “Office Theme”, which is relatively reserved with a standard white background and unobtrusive colors.

 

Figure 262  

If you decide to change a theme and want to get an immediate idea of how it will look, goto the Home tab and expand the Format as Table gallery, or the Styles gallery. You’ll see that it’s assumed the colors of the Theme you selected. If it’s not what you wanted, just head back to Page Setup, Themes and reset it.

Text, Cell, Row & Column Formatting

Most workbooks will have a standard font throughout with no differentiation, and that’s how every workbook starts out, although many finish that way too. As mentioned previously you’ll save yourself a lot of time if you build your model and get the kinks worked out of it before you start applying a lot of formatting. Otherwise you’ll generally end up redoing it several times before you’re done as you add/remove elements. This doesn’t mean that you can’t add bits of formatting here and there to make it easier to navigate the model when you’re designing it. Rather, don’t spend a lot of time trying hard to add in finished product formatting before you’re done with the end product. Besides, when you are ready to start, formatting a workbook for distribution isn’t necessarily that tough or time consuming. Even if you’re not an artistic savant, you can quickly draw attention to certain key points by making them a bit larger, bolded, or even a different color. You’ll generally see most of that differentiation on header rows and sub-total/totals rows. You might also find that you want to have a currency or other formatting for some of your numbers, being careful to give the user what they need. This can especially hold true with too many decimal places when 2 or 3 would probably do, or not clearly labeling what numbers have what significance. E.G. 225.99 45.67 43.98 1250.93 – How does your user know which of those numbers might be dollar amounts vs. percentages? You don’t necessarily have to add number formatting to each series of numbers, but you’ll find many financial models will number format at least the first row of data in each set.

Here are before and after examples of the Cash Flow summary you’ve worked with previously (remember that the Date and Currency formatting was applied when we added our sample data).

 

Figure 263  

Here’s after, with less than 10 mouse-clicks.

 

Figure 264  

Granted, that’s a very simple example, but it should give you an idea of what you can do quickly. Note in the second example the worksheet’s Gridlines have been turned off, so that the applied shading and borders would be clearer. You’ll find the bulk of your formatting elements back on the Home tab in the Font, Alignment & Styles tabs. Some of the companion workbook examples tried to make use of some of those styles by way of example, and using them makes formatting all the faster. And as with so many other things, you can modify Styles to suit you, so you don’t have to stick with the default settings.

 

Hiding Key Data - One thing you’ll likely run into in your workbooks is the need to have some information not displayed. Let’s say you’ve expanded on the Weekly Cash Flow to have a monthly rollup and you want to include a weekly average by month.

• • Scenario: many organizations work on a 5-4-4 accounting schedule, which means that starting in January each month has 5 weeks, then 4, then 4, then back to 5 until the end of the year. In order to get accurate weekly averages you need to divide the total monthly revenue by the number of weeks. You could use =B8/5, =C8/4, =D8/4, etc., but that gets you into hardcoding values in formulas, which you should avoid at all costs right? Instead you can put the 5-4-4 references in an unused row (usually above your data), and reference that.

 

Figure 265  

• • Unfortunately, now you have some data in your worksheet that no one needs to see, so what do you do?
• • Trick #1 - The most common trick is to just hide that row. HomeCells, Format, Hide/Unhide, Hide Rows (or simply ALT+O+R+H). But what do you do if there’s information on that row or column that does need to remain visible?
• • Trick #2 – You can format the cells’ font the same as the cells’ background. In the case of the Cash Flow example you would just format the font as white. The 5-4-4’s are still there, but your users can’t see them. And fortunately, what they can’t see, they usually don’t bother…

 

Figure 266  

Borders - These can be a great tool for segmenting data, but as with any other elements you need to put some thought into them. If you just format everything with borders then it’s really no different than just leaving gridlines on. Ideally what you’re trying to do is direct the flow of information and make it easy for your user to quickly understand what’s going on. In the Cash Flow example you see that only the Total/Average rows have had borders applied, but this is a very small model. In larger models you might want to use continuous borders for all the data so that it’s easier to keep everything on the right lines (a continuous border is when all four sides have a border applied). Continuous borders can also be useful when your information is going to be utilized in hard copy form, where people don’t’ necessarily have the convenience of highlighting a particular row/column, but need to use their fingers to follow along.

 

Figure 267  

To apply borders first select the range you want to cover, then select the Border tool from the Font group on the Home tab. The standard borders are all very easy to apply by clicking on them, although some borders do need to be applied in sequence. For instance if you were to apply a Thick Box Border to a range, then apply All Borders, you would immediately wipe out the Thick border. A general rule of thumb with borders is to apply all of your inside formatting first, then outer last.

 

Figure 268  

• • The More Borders option will launch the Excel 2003 Format Cells dialog that we’ve already discussed previously. While the general Borders dialog has most of the settings you’ll find here, the Format Cells dialog also exposes Diagonal Borders. In addition you can modify the line styles you apply, for instance the Total rows in the Cash Flow examples used a Top and Bottom Double border, which is a fairly standard financial border.
• • We haven’t discussed yet the Draw Border & Draw Border Grid, and Erase Border tools, which do have some advantages over other border applications methods.
• • Draw Border simply allows you to draw a border around the range of your choice instead of selecting it first. When you invoke the Draw Border tool, the first thing that will happen is your worksheet will suddenly have a case of the Chicken Pox, with bullets at the intersection of each cell. Second, your cursor turns into a pencil symbol. To draw a border, just left-click the mouse and drag to start the border, then release the button when you’re done. It’s a neat trick for one-off borders where it would be faster than selecting a range. Note that Draw Border only gives you an outside border, not inside.
• • Draw Border Grid on the other hand will draw all borders. Are these ground-breaking tools? Of course not, but they can speed up some tedious formatting elements.
• • Erase Border will erase all borders that you applied, whether you used the Draw Border tools or placed the borders in the traditional fashion. This can be substantially faster than selecting a range and selecting No Border.

Cell Shading - and Borders often go hand-in-hand, and it’s rare you’ll see a model that doesn’t have a bit of both (unless you’re looking at boring accounting statements of financial reports, in which case good luck to even have borders at all). Shading is equally easy to place, just select the range you want to apply the shading to then select the Shading tool (it looks like a paint bucket) from the Font group. You can select from the pre-defined Theme or Standard palette colors, or select from the entire range of colors (although this is rarely necessary, unless you just need to desperately match your company colors).

 

Figure 269  

Fill Effects – There additional formats you can apply to cell shading, but they’re not readily exposed on the Ribbon. With your range selected, launch the Format dialog (Ctrl+1), and goto the Fill tab. The two options of note here are Fill Effects, which allow you to apply gradients to your cell shading, and Patterns, which let you apply dot patterns to your cells. Note that Fill effects can end up being more distracting than it is helpful, and should be reserved for non-data ranges, like Dashboards, or an area where the pattern won’t otherwise interfere with text. Some printers have an issue with rendering both properly, so you or your users might not see the output you want with certain Fill effects & Patterns applied. Generally the denser the Fill or Pattern the harder it will be to see text with it.

 

Figure 270  

Styles – These are incredibly handy to use, especially in workbooks that do require user input, although you’ll probably need to customize them a bit to suit your tastes. The nice thing about Styles is that you can quickly apply them to either a single cell or an entire range. If you do find yourself needing a lot of input from your users it might be well worth the time to create some customized styles of your own.

Font Types – You might also note that the Style gallery uses a mix of fonts, and it’s perfectly acceptable to use the Font itself to make something stand out. Just don’t use too many different fonts, because you can also overwhelm your users with so much variation that it doesn’t make sense.

Font Colors – These will come into play especially if you format rows & columns with cell shading. For darker shades you’ll want to format with lighter colors, and do the opposite for lighter shades. Again, your artistic creativity may not be favorable; for instance, the Blue text on Hot Pink shading in the next example probably isn’t the best combination (it will also give you a headache), so keep that in mind before you send out your workbook for the first time. Another consideration is that some colors don’t render too well on different monitors, so while a certain color combination may look great on your screen, it may be difficult to read on another.

 

Figure 271  

Alignment – This can play a significant role in how easy your worksheets are to interpret. One of the most common alignment faux pas is to align everything center in order to create separation between text. Instead, try to stick with the text’s default alignment, which is numbers to the right, and text to the left. Here’s an all too common formatting scenario with everything Centered. Unfortunately, the example doesn’t do justice to the injustice that this kind of formatting does to a perfectly good report. The text alignment notwithstanding, formatting currency in the center makes it very difficult to read, especially when you get into larger values that will have issues because of where the right side of the numbers line up (or in this case don’t).

 

Figure 272  

Indenting/Outdenting - is often overlooked, but it can be very handy in settling some of your more difficult alignment tasks. For instance, many will use leading spaces to make one row of data start a bit inside another. Unfortunately, this can make it much more difficult to use that data (text or values) down the road, because you then need to get rid of the spaces. The Indent/Outdent tools make short work of those situations. Here’s an example of some of the Cash Flow report’s sub-categories indented for readability.

 

Figure 273  

 

Number Formatting – We discussed the available number formats in the Home Tab lesson, so we’re not going to repeat it, although we will expand on Dates & Times, as well as Special formatting.

• • Dates – Excel’s Standard Date format is M/D/YYYY, or 8/1/2001, but you have a myriad of choices. And if you don’t find one that suits your needs you can create your own Custom formats. Changing the Date format (or any number format for that matter), is as simple as selecting your range, calling the Format Cells dialog (CTRL+1) and select the format that you want. In financial models you’ll often see DD/MM/YY used because it remains a consistent length regardless of the date (e.g., 01/12/10). This can be very helpful in terms of alignment, especially if your date values are in a column. You can also format a date as a long date (Monday, January 1, 2011). As mentioned you can create any of these date or number formats in the Custom settings. For instance, the long date above could be just as easily custom formatted as: dddd, mmmm dd, yyyy. As you’ve seen about Dates you can also perform mathematical functions on them, so we incremented our Cash Flow example daily with a simple formula: =A1+1, where A1 holds a valid date. You can also do certain things like have a standard date but format it to show month or day only, instead of date.

 

Figure 274  

• • Unlike currency and other numeric values like it, Dates need a valid separator between Day, Month and Year when they’re entered: you can use either the forward slash “/” or hyphen “-“. If you want to enter a date in the current year, just enter the day/month. I.E. 12/31. Excel automatically assumes that you’re talking about the current year.
• • Times – Times get less play in Excel than dates unless you’re dealing with things like employee schedules, time cards, etc. Unfortunately, Times can be a bit difficult to enter, and they do follow some rules.
• • Whole times (e.g. 9:00 AM) can simply be entered as “9 a” or 9 p”. Note the space between the number and a/p. Entering a time without the space would be seen as an invalid time, and would result in a null calculation.
• • Fractional times must be entered in full (e.g. 9:15 AM), although you don’t need to enter the “M”, “9:15 a“ would work, but you need to include a space between the time and AM/PM.
• • Valid times must be entered (e.g. 10:70 p is not a valid time). Fortunately most invalid times will revert to 12:00 AM and can be re-entered at any time.
• • Just as with Dates, provided they’re entered properly you can use times in calculations as well. For instance you can calculate the number of hours an employee was on the job each day, including breaks, lunch, etc. You can even use Excel to perform complex Overtime calculations, although that’s not a discussion for this course. You will find plenty of Excel Time resources on the Internet if you need them.
• • Percentages – These are fairly straightforward and the only thing you need to do is choose how many decimals places you want to display. In most cases 2-3 should suffice. Remember to apply a percentage format to a range PRIOR to entering any data or it will be multiplied by 100. There is no negative number format for Percentages, which can sometimes cause a somewhat conflicting view if percentage values are displayed next to figures that have been formatted a particular way for negative values. E.G. the following negative currency & percentages values: ($1,234.56) -12.5%. Fortunately, you can use Conditional Formatting for negative values, which we’ll discuss shortly, when we dive into Conditional Formatting.
• • Text Format – As we’ve already covered, Text format will allow you to use certain value strings that you normally couldn’t if formatted as numbers, such as leading 0’s. In the same theme with Text formatting is the TEXT function, which we already discussed, and it can be a huge boon to you with regards to being able to mix text and referential data. All you need is your text, the & to join it with your reference, and the number format of your choice. E.G. =”Today is: “&TEXT(TODAY(),”MM/DD/YY”)
• • Special – Unfortunately, this series of predefined formats doesn’t get near the use they should. Invariably, if you look at worksheet with customer information, you’ll see a mish-mash of formats with phone numbers and usually zip codes as well. Why? It is generally because the people who entered the data didn’t know that cells could be formatted in such a way. So you might have someone enter a phone number like (760) 552-1212, and another like 760.555.1212 (you see it in websites all the time). The problem is that to do anything with that data you need to parse it out, because with the formatting characters manually entered, Excel no longer sees that as a value, but text. It’s unlikely that you would perform mathematical calculations on either phone numbers or zip codes, but you would use them in mail merges. You don’t want to send out a nice letter with a myriad of different number formats. If you’re going to be dealing with any degree of user input, then these are the seemingly little things can eliminate so many headaches if you take care of them up front. Fortunately, you can also use Data Validation to remind people how to enter their data. Will it stop every piece of mis-entered data? Not likely, but it’s going to catch most of them, and any time saved in not having to manipulate data that’s already been entered it a win. Think about it this way, once data has been entered into a digital environment it should never have to be manipulated again. Sure, it can be used in calculations, and references, but no one should ever need to go back and correct a manually entered format.

 

Figure 275  

• • Custom – When nothing fits, come here. You can get a good idea of how to build a custom format, by changing the format of a cell, then look at it in Custom, which will display that format.

Format Painter - One of the best tools you have in your formatting arsenal is the Format Painter. This will copy the formatting applied to a cell and let you transfer that format to another cell. Even better it will let you select entire rows or columns and replicate their formatting. As a simple example we’ll go back to the unformatted Cash Flow report in the Lesson 7 workbook. Select the first row of data (A1:G1) and apply the formatting that you want. The formatted example used a blue background with white text, but you can do anything you want. Once that’s done select the entire row (click on the row header), then double-click the Format Painter. Now click on the total rows (7 & 33) and your formats have immediately been applied.

• • Single-clicking only enables the Format Painter for one-click.
• • Double-clicking keeps the Format Painter enables until you hit ESC.
• • When copying formats, it’s generally easier to select an entire row or column, than it is a range. Excel can remember every format in the selected row or column and apply it to your destination row/column(s). If you select a range on the other hand, you need to select a like range to apply the formatting or you’ll get some unexpected results. For example, goto the formatted Cash Flow example, select A1:A10, then go back to the unformatted example and apply the formatting to A1. Oops! That’s not quite what you wanted; Excel applied a repeating pattern didn’t it? (FYI - unless you want that formatting to stick you should hit Ctrl+Z now).
• • You can generally select a single cell and apply its format to multiple cells and ranges without issue.
• • Using the Format Painter will wipe out any existing formatting in the destination range(s) to which you apply it.
• • Once you start working with the Format Painter on some of your own worksheets, you’ll quickly find out just how handy it can be.

Worksheet Tab Colors – This one was a long time in the making. While Lotus 1-2-3 allowed you to color worksheet tabs, it took Microsoft a while to finally relent and build that functionality into Excel. Why is this important? Most people generally don’t see the worksheet tabs at the bottom of a workbook unless you point them out. This can be challenging if you’re walking someone through a workbook on the phone and you say “OK, now go to the Cash Flow tab…”, and they say “What?” Instead you can simply say “Just click on the Red tab at the bottom of the screen. It says Cash Flow on it.” You would be amazed at how much easier it is to direct people to the worksheet tabs with just a little bit of color. You can also set up Tab groups by color. E.G. Orange could be for data input worksheets, Blue for data output sheets, Yellow for hidden sheets, etc. While this is a fantastic tool, if you’re going to use it with any regularity, you should definitely come up with a consistent color convention. Otherwise you stand to confuse people if your sheet groupings are different each time. Just right-click on the sheet tab, Tab Color.

 

Figure 276  

Conditional Formatting - This is where you can add a lot of value to your workbooks by identifying particular areas and pointing them out to your users. How do most people highlight areas of importance in documents? Right, they use highlighters, and it probably wouldn’t be too much of a stretch to think that most every desk in the country has at least one in a drawer somewhere. Well, people do exactly the same thing in Excel without the slightest hesitation: they’ll go into a workbook and manually highlight certain cells or ranges that have particular characteristics, like being over/under a certain value. Notwithstanding the fact that finding and marking all those areas can be like finding the proverbial needle in a haystack, you’ll probably also miss some things along the way, which means that you may not accurately present your message, and it’s time consuming. If one of the main points of the electronic spreadsheet is for it to save you time, this certainly isn’t a good use.

 

Figure 277  

Conditional Formatting allows you to define rules for cells and ranges, and then apply particular formats when it identifies cells that meet those criteria. Let’s say you have an inventory tracking sheet that helps you see when you reach certain product reordering thresholds. You could look through each line item, do a little mental headcount, and say, “Oh, we’re down to 5 of that, I need to reorder”. Or you can set a rule in Excel to tell you when that happens by giving you a visual clue. For instance when a particular product gets to 10 pieces, the cell turns yellow, 5 it’s red, and above 10 it’s green. Visual clues are a lot easier than trying to manual search cells for data.

• • Conditional Formatting allows up to 64 different scenarios, which is a substantial increase over the 3 conditions you had in earlier versions. But the same rules apply to Conditional Formatting as to IF statements: just because you can do something doesn’t mean that you should. In this case, you’d be hard pressed to create 64 formats that are different enough from each other for your users to notice the difference between them. If the purpose of Conditional Formatting is to quickly showcase differences, then overwhelming the user with minute differences won’t help. Conditional Formatting is no exception to so of Excel’s many methods that has a primary Ribbon interface, as well as an underlying Excel 2003 dialog. We’ll discuss the Ribbon method first, then the more traditional dialog. The Ribbon interface for Conditional Formatting is very graphically oriented, and exposes five primary Rules.
• • Highlight Cells Rules – In terms of trapping conditions, like the inventory control example, this is probably where you’ll turn first, as it is probably the most versatile. With this option you’re formatting cells based on their values, and it’s relatively straightforward. Each option you select will launch a secondary dialog where you’ll enter your specifics. Here is the dialog for the Less Than option, and the resulting format. Note that you have some pre-defined formats, or you can choose the custom format, which will launch the Format Cells dialog. Each condition’s dialog is slightly different depending on the condition. For instance Between, which will highlight all values between two values, is going to have an option for the low value, and another for the high value.

 

Figure 278  

• • There are two additional conditions you can add that previously had to be custom written with formulas in the secondary Conditional Formatting dialog.
• • A Date Occurring – Which allows you to format cells based on what a cell’s date is in relation to the system date
• • Duplicate Values – Which can identify duplicate/unique values in a range.

 

Figure 279  

• • Top/Bottom Rules – This is a very fast way to determine the rank of certain items in a list. The Ribbon options limit you to either the Top or Bottom 10/10% or Above/Below the Average value of the values list, but you can easily adjust the value range in the expanded Conditional Formatting dialog.

 

Figure 280  

• • Data Bars – These are in-cell bars that display horizontally, with the length and gradient based on each cell’s value in relation to the others. They’re very simple to set up, although sometimes they do need a bit of manual adjustment to have the scale properly. Otherwise certain figures seem to get too much weight and others not enough. Data bars are very easy to adjust, but if the defaults aren’t enough for you, there are always the expanded options exposed by the More Rules selection.

 

Figure 281  

• • Color Scales – These are similar to Data Bars except that instead of displaying an in-cell bar to represent the value, the entire cell is shaded, with the intensity of the shade being determined by the cell’s value in relation to the others in the section. If you don’t like the default color selections you can goto the More Rules dialog and add your own custom colors.

 

Figure 282  

• • Icon Sets - This is a way to add some graphics cells instead of the shading formats you’ve seen up to this point. Something to note is that the scale in which the various icons will be applied is directly related to how many objects are in your selection. If you choose the stop light pattern in the example then the breaks will be at green when the value is greater than or equal to 67, yellow when the value is less than 67, but greater than or equal to 33, and red when the value is less than 33. So three objects equates to splitting the values in thirds, 4 would be quarters as seen in the advanced selection options below, and so on. You can also change the default values to your own, although the splits aligned with the number of objects makes a lot of sense.

 

Figure 283  

Secondary Conditional Formatting dialog – When you use this dialog, instead of being presented with graphical formats, instead you start with a list of 6 rules which you can apply to your data. The particular format you apply is added at the bottom of the dialog

 

Figure 284  

• • Format all cells based on their values – This let you make multiple decisions about what kind of values you want to format, what scale to use as the condition and format with either a 2 or 3-Color scale, Data Bars or Icon Sets. The following example has two scenarios, or Rules applied.

 

Figure 285  

• • Format only cells that contain – Here you have multiple options, although as you’ll see in the example your actual formatting options are limited.
• • Cell Values - that fall within range parameters that you set (<, <=, =, <>, >=, >)
• • Specific Text - (Containing, Not Containing, Beginning With, Ending With).

 

Figure 286  

• • Dates Occurring – With in a given range of options (Yesterday, Today, Tomorrow, etc.) The options are the same as from the Ribbon menu.
• • Blanks/No Blanks – Highlight all blank cells in a range or vice versa.
• • Errors/No Errors – Highlight all Errors in a range or vice versa.

 

Figure 287  

• • Format only top of bottom ranked values – This is the same as the Ribbon Top/Bottom rules, but you have the ability to change the Top/Bottom values, where the Ribbon is limited to the Top/Bottom 10.
• • Format only values that are above or below average – This is the same as the Ribbon Averages in the Top/Bottom Rules, except for the statistically oriented, you can select levels of Standard Deviation.
• • Format only unique or duplicate values – This is the same as the Ribbon’s Highlight Cells Rules, Duplicate Values.
• • Use formula to determine which cells to format – This is the Rule that isn’t exposed on the Ribbon, although a lot of its functionality has been replaced in 2010, especially with the Date formatting options. With this method you actually write the formula that Conditional Formatting needs to evaluate. Previously this was the only way to create complex evaluations, but there’s little need to know it now with so many more Formatting options.

 

Preparing to Distribute your Workbook - Before you print or distribute your document you need to make sure it shows as much or as little detail as you want. We’ve already discussed hiding certain elements from users, by hiding rows, columns, entire worksheets, or even something as seemingly mundane as changing a font color to match a cell’s background. These options all have bearing on distributing your documents electronically, or printing them. There are some additional steps you can take to manipulate the views that your users have when you distribute your documents electronically.

• • Custom Views – Often times when you’re creating financial statements, sales reports, even shift schedules, there are times when you don’t want certain people to see information specific to others. Let’s say you’ve got production report that shows production performance numbers by shift. You have to send it out to the shift leaders on a regular basis, but you only want each shift leader to see their own information. What do you do? Do you really want to go to the effort of creating and managing a different workbook or worksheet for each? No, because then that segments your data and makes it harder to analyze side-by-side. To you want to do a bunch of cutting and pasting, or hiding/rehiding in order to send out the information? Probably not. But you can use custom views in which you display only the information specific to each shift and name it, and repeat for each shift. Then when you’re ready to send the report, you simply pull up each saved view and send that. It does require that you hide/show the relevant information one time to set it up, but that beats doing it routinely, which people do all the time.
• • In this example you’ll see sample financial information for a company that has different geographic divisions and facilities.

 

Figure 288  

• • Let’s say that you want to create a view for each division, Central, East and West. Start by unhiding all of your rows and columns, not including that which no one should see, so you have an unfettered view of everything. First you have to establish your baseline view, so goto the View tab, select Custom Views, then add the name for your current View. Afterwards, decide which region you’re going to start with and hide the information for the other regions. In this case we’ll choose Central. Once your information is hidden and the sheet looks the way that you want it, you’ll repeat the Add View process. Repeat as many times as necessary to create all of your views.

 

Figure 289  

• • In the future when you want to retrieve a custom view, it’s as simple as going to Views, Custom View, Select the one you want, Show.

 

Figure 290  

• • Auto-Filter - This is one of the most powerful tools you can give users, especially if you’re giving them a lot of data. In the previous example you were hiding information from users; with Auto-Filter you give yourself and users the ability to temporarily hide information that they don’t want to see. Using the same example as Custom Views, goto Data, Filter. You’ll immediately see drop-downs appear in the header row for each column. What has happened is Excel just gave you the ability to selectively hide or show data, by almost whatever criteria you want. In this case let’s see we just want to see the information for the Central region. Select the “Group” dropdown, uncheck “Select All”, then check “Central” and watch what happens.
• • This is just a simple example, but it’s a tool that shouldn’t be overlooked because of its flexibility.

 

Figure 291  

You’ll also note that the Auto-Filter dialog also contains sorting options, as well as additional filters depending on if your data is numeric or text. You can even sort and filter by Text or Cell color!

 

Figure 292  

• • Page Setup Options - Imagine printing a worksheet, and your printer starts going wild, spitting out pages that are mostly blank, although they might vaguely resemble some horribly disfigured image of your worksheet. Does it sound kind of hard to believe that this could happen? Unfortunately it happens all the time if you forget to go through the steps of setting your workbook up for printing. So what happened? Well, since you didn’t tell Excel what to do it made some assumptions on your behalf. The first is that you want to print in Portrait orientation. The second that you want to keep the document the same size it is now, or 100% scale, and lastly that you didn’t want to change any of Excel’s default print options. Let’s just say that you have a rather large worksheet that should be printed in Landscape orientation (most financial statements or anything dealing with months will be printed in Landscape orientation). When Excel stuck to its defaults, it automatically put page breaks where they thought they should go, so your worksheet that you wanted to print on 1 page wide by maybe 2 pages tall instead printed on 27 portrait pages. And because of the scaling not a single one of those pages is usable.

 

Figure 293  

• • Here’s an exercise. In a blank worksheet, scroll down and across to AC250 or someplace out of the way and enter anything in a single cell, then Ctrl+Home to get back to A1. You just defined Excel’s print area, or the area that it will print unless you tell it otherwise. Now Ctrl+P to bring up the print dialog. How many pages does Excel say you’ll have? In this example it should be around 15, and 14 of those will be blank, while the final page will have the character that you entered. Now hit escape to get back to the worksheet and goto View, Page Break Preview. You’ll see an odd grid display of your worksheet with the page numbers listed, and a gray area to the right, which is the area outside of the Print Range, so Excel’s not going to print that. The blue dashed lines you see are the actual page breaks, and right now they’re laid out based on what Excel thought you wanted to do even with that single character in AC250. Excel doesn’t know what you’re printing; all it checks for is that there is a single printable character in the defined print range. It doesn’t care what or where it is, just that there’s something there to print.

 

Figure 294  

• • Fortunately, Excel’s new automatic print preview has put a halt to a great deal of errant printing, because you get a preview of what you’ll print before you actually print. That doesn’t mean you don’t have to set up for printing though, so we’re going to go through those steps now.
• • Go back to the Cash Flow model and use Ctrl+P to launch the Print dialog. It’s Portrait and 4 pages right? So you need to set it up for printing. You can do it right there, or from the Ribbon Page Setup menu. Since we’re already here we’ll set it up here first.
• • Print Dialog Options
• • Print/# of Copies
• • Printer selection (with a link to specific properties for the selected printer)
• • Settings

1. What to print – You can choose from the active worksheet, or any grouped sheets, the entire workbook, or just an area that you might have selected.

 

Figure 295  

2. Pages – If the worksheet is more than one page, you can choose which pages you want to print, so you could choose 1-3, 2-3, 4, etc.). Strangely enough, you can tell Excel to print more than one page even if you’re only set up for one, although if you try to print and the range outside of your print area is blank Excel will halt because it can’t find anything to print.

 

Figure 296  

3. Collated – If you’re printing multiple copies of a multi-page document this can come in handy by printing each complete document one at a time. Uncollated would print each page multiple times, then proceed to the next, leaving you with a big stack of paper that needs to be shuffled by hand. Fortunately Collated is the default.

4. Orientation – Portrait or Landscape

5. Paper size – Here are all of your standard paper sizes. The default paper size is 8.5” x 11”.

6. Margins – This sets how close the printable area of your worksheet will be to the sides of the page. It’s generally acceptable to set left & right margins ate .25”, but you need to be careful with top & bottom margins if you intend on using Page Headers & Footers, which we’ll discuss momentarily.

7. Scaling – This determines how much of the worksheet you want to fit on a page. Custom Scaling Options will launch the Excel 2003 Page Setup dialog, which phrases the fit options a bit differently.

a. Fit Sheet on One Page would be Fit to 1 Page Wide x 1 Page Tall

b. Fit All Columns on One Page would be Fit to 1 Page Wide x (blank) Page Tall

c. Fit All Rows on One Page would be Fit to (blank) Page Wide x 1 Page Tall

 

Figure 297  

• • In the case of our Cash Flow example, we want to print the worksheet on one page tall by one page wide, and Landscape orientation, so select Landscape from the Orientation selection, and Fit Sheet on One Page from the scaling dialog. You should then see the Print Preview adjust to the new settings. Once nice thing about setting up from the Print dialog is that you immediately see you the changes. This is not the same as Live Preview though, so if you don’t like a change you’ll need to undo it by making a different selection.
• • Page Setup – From the Ribbon

 

Figure 298  

• • The Page Setup elements here are quite different than in the Print Preview mode, because this doesn’t deal directly with printer settings, like Collating, Paper Size, or what to print. It does however; address some things that aren’t exposed on the Print Preview dialog.
• • Print Area – This lets you override Excel’s default print area and set your own. Doing this lets you exclude certain areas that you don’t want to print. Many times you’ll have ancillary information on a worksheet that you don’t want to print, and this allows you to do that without having to hide those areas. You can also adjust the Print Area from Page Break Preview by dragging the solid blue lines in or out from their current positions.
• • Breaks – This is where your pages will be cut off and forced onto another page. You have the choice of Inserting/Removing Page Breaks, which can be done by selecting a row or column where you want a break and selecting the option you want. You also have the choice of Resetting page breaks, but that will not clear any breaks naturally associated with the worksheet. You can also adjust Page Breaks by dragging them from Page Break Preview, but they will still fall within the confines of the current page setup. I.E. you can’t drag a page break past where it would normally fall (like trying to force two pages into one), as Excel will just put it back, but you can drag a page break inwards (like creating two pages out of one). Most times you adjust page breaks so that they follow natural breaks in your data.
• • Background – As you saw in the Ribbon lesson you can add a background behind your data. Just remember that people have to read past whatever you add, so make sure that it doesn’t obscure your data.
• • Print Titles – This allows you to choose if you want to repeat Row & Column headers on each sheet. In the case of a multi-page report, like one that had dates across the top, you probably want to do this so that people know what they’re looking at on later pages. Print Titles will launch the Excel 2003 Page Setup dialog. Oddly enough, even though you can launch it from the Print dialog, the option to set Titles is disabled from that way.
• • Secondary Page Setup Dialog
• • In many ways this method of page setup is more complete than the other two, so you might just find yourself coming here first to set up printing instead of doing it in several places. The Page Setup dialog consists of 4 primary tabs, as well as some secondary options.
• • Page – Here you set your Orientation, Scaling, Paper size, etc.
• • Margins – This sets your Margins, but it also allows you to center the worksheet on the page both Horizontally and Vertically. When you select either option you’ll see the gridlines in the margin example move accordingly.

 

• • Header/Footer - This is the same as using the Header & Footer dialog from the Insert menu. It lets repeat text at the top and bottom of each worksheet. A common example is the FileName at the top, and Date/Page # at the bottom. Selecting either the Header or Footer Buttons will launch another custom dialog. The buttons, from left to right, are:

 

Figure 299  

• • Format Text – This is easiest done after you have added any text you want, then select it and change the format.

 

Figure 300  

• • Insert Page Number
• • Insert Number of Pages - You can also use combinations of AutoText entries and your own text, like “&[Page] of “&[Pages]
• • Insert Date
• • Insert Time
• • Insert File Path
• • Insert File Name
• • Insert Sheet Name
• • Insert Picture
• • Format Picture
• • Sheet – If there was any compelling reason to use this dialog over the others, then this is it. This exposes Page Setup elements that you’ll only find here, like repeating Rows and Columns. You also have some other options that are worth considerations.

 

Figure 301  

• • Print Area - You can manually adjust the Print Area
• • Print Titles – Again, you can define how many Rows or Columns you might want to repeat on each page.
• • Gridlines – Another handy tool when first setting up a model because it helps you line up your elements on the printed page without using a ruler to keep track of everything.
• • Black & White – If you have a color printer always check this one unless you’re printing a final copy. You’ll save yourself a lot of ink/toner that way.
• • Row & Column Headings - You can toggle printing sheet titles on and off. This is handy when you’re testing a document and need don’t necessarily need the extra information, just the data.
• • Comments/Cell Errors – These can be handy if you don’t necessarily want to take the time to suppress anything in the worksheet itself, but don’t want them to print.
• • Page Order – This is rarely changed, unless you have a document that follows a very specific reading pattern.
• • The Buttons at the bottom should be easy to figure out (Print & Print Preview will both launch the Print dialog, but Options is a little bit more complicated. The Options button is going to bring up the printer settings for your selected printer.

 

Figure 302  

So there you have it for Page Setup and Printing. Most documents will be very straightforward and simple to set up with a few clicks and choices, but there will be some documents that require a little bit of extra time on your part.

Unit Summary: Getting Started

• • In this lesson you:
• • Again saw how to apply and modify Themes
• • Learned about different elements of worksheet formatting, including Text, Cells, Rows & Columns
• • Saw how you can use Number Formatting to make numbers stand out
• • Saw Conditional Formatting in detail and the many options it presents
• • Setup some Custom Views, and made showing only relevant data easy for people with Auto Filter
• • Went through the various ways to set up your worksheet for printing & distribution

Review Questions – Lesson 7 – Formatting & Printing

1. Why would you format some Cells/Rows/Columns differently than other?

a. __________________________________________________

2. What are two ways you can hide key data from users?

a. __________________________________________________

b. __________________________________________________

3. What’s the difference between Gridlines and Borders?

a. __________________________________________________

4. What’s the standard alignment position for Text? What about numbers?

a. __________________________________________________

b. __________________________________________________

5. Why shouldn’t you use spaces to indent text?

a. __________________________________________________

6. How would you add Data Bars to a range of data?

a. __________________________________________________

7. How would you highlight all cells in a range that are >= 0?

a. __________________________________________________

Lesson Assignment – Lesson 7 – Formatting & Printing

Your assignment is to:

• • Test worksheet formatting in the companion workbook, and develop a style that fits you.
• • Practice using different Cell shades vs. Font colors.
• • Try aligning numbers and text differently so you can see the differences.
• • Work with Indenting/Outdenting sub categories.
• • Using the Condition Formatting examples as reference, come up with your own data sets and apply the various Conditional Format to it.
• • Practice setting up a worksheet to print, using Print Preview to determine if you’re ready to print beforehand.