Printing Worksheets

Have you ever Printed one of your worksheets that you wanted to bring to a meeting, and discovered that it printed on Several Pages of paper, rather than on One (which was wanted you wanted to bring to the meeting…)?

Sure you have; we all have had this happen to us at one time or another (let’s face it, we are not as yet in a totally Paperless work environment…).

A typical example is that some of your data on the right side of your worksheet (perhaps just a column or two) prints on its own page.  This obviously doesn’t make a very appealing handout for your meeting.  Bummer!  So, what do you do?

I’ll bet some of you have tried resizing your fonts or your column/row widths, and hoped for the best when you reprinted your handout.  Whereas that may work, there is a handy tool in Excel that makes it so much easier.

All you have to do is go to View / Workbook Views / Page Break Preview. In this mode, you can easily Control how your worksheet prints with a minimum of effort!

In Page Break Preview you get a look at your worksheet with page breaks shown as Dashed Blue Lines. The dashed lines show the Automatic page breaks which Excel has chosen by default. By hovering you mouse pointer over one of the lines, you can left-click/hold and move it wherever you wish. The lines will then become Solid Blue, indicating the Manual Page Breaks that you have chosen (Cool!).

A Small Word of Caution
This technique is highly useful when you have just a Very Few columns or rows that do not initially print on the single page you wish to distribute.  What happens when there are Many columns or rows that you wish to add to your printed sheet, however?  It may be tempting to manually slide your Page Breaks to include a significant volume of columns or rows, so that you can have an Elegant One-Page Handout for your meeting.  Guess what?  It works!  You will, indeed, get all of your data printed on One Page!  You may, however, need to invest in some Magnifying Glasses so your audience can read it!

Page Break Control; Another Excellent Excel Tool that puts you in Control and saves you time!  (Good Stuff…).

Excel Detective Work

Doing a bit of Detective Work is sometimes necessary in Excel.  For instance, you may have inherited an Excel workbook from a former coworker, wish to investigate the techniques used by a current colleague, or merely refresh your memory on how you created a workbook in the now fuzzy past.

Whereas there are specialized commercial tools for doing hardcore fraud auditing on Excel worksheets, most of us simply need a Couple of Techniques that will provide a glimpse into what is going on in the background of our spreadsheets:

1)  Are there Formulas Employed in My Workbook?

Rather than manually checking each cell or range to reveal the formula in the Formula Bar, (as used to be the case in some of the older versions of Excel), you can reveal all of this information in your entire worksheet with the Keyboard Shortcut:

Ctrl + ~

By selecting any cell within your worksheet and then using this simple keyboard combination, All of the formulas in All of your cells will be revealed.  You can then proceed to investigate further to see if there are any flaws in their construction.

2)  In-Depth Review of Precedents and Dependents

If you need an Omnipotent View of what your data is doing, where it is going, and where it is coming from, try using the tools in the Formula Auditing group on the Formulas tab. The tools include Trace Precedents, Trace Dependents, Show Formulas, Error Checking and more.

One of the great advantages of using the Formula Auditing tools is the fact that the information is returned in Easily-Understood Graphics that help you make Quick Sense of what is going on in your workbook.  Once armed with this information, you can proceed to work the magic that has earned you the Office Excel Guru alias.

Some simply applied tools to help you do your Sherlock Holmes work in Excel.  And it’s all quite Elementary, my dear Watson…

Negative Space and CF

Making your data Come Alive in Excel is much more than mere esthetics.  Indeed, it is all about better communication and the dissemination of information.

We are all familiar with Conditional Formatting, and probably most of us have used it to highlight important information in our workbooks.  A unique method of doing this takes a Reverse (or Negative) approach to this however.  Let’s explore this cool way to use Conditional Formatting:

For instance, let’s say you have a worksheet in which you are entering data (it may be numbers, text, or mixed) in a field, and you want it to be Very Apparent if a cell within that column is Blank (this is a recommended practice for good database maintenance). Here is a truly different way to do this:

For sake of example, let’s say you are going to be putting data in the short range of A1:A16.

1. Your first step is to apply a Fill Color to your range (A dark gray is a good choice.)

2. Then select your range and go to Conditional Formatting / New Formatting Rule

3. Choose Use a formula to determine which cells to format and put the following:

4. =IF(NOT(A1=""), TRUE, FALSE)

5. Finally, for your Format, use Fill / No Color

Now whenever you place data (numbers, text, or mixed) in one of your cells in the range, the dark gray Fill Color is Cleared! Bamm, it’s gone!

The cells that do NOT have data entered into them will retain the Original Fill Color.  Try this out and see if this does not make your worksheet Come Alive!

Combo Charts Revisited

In my current class of Excel Gurus, I once again reiterated one my favorite maxims, “If you have to think about what the chart is trying to say, it’s a Bad chart”.  So how do we communicate better with our charts?  Enter Combo Charts, which are a one of the mainstays when improving your message in your Excel Charts.

Let’s say you want to compare the number of your Customers for two years, and you want to be able to instantly see which months were the big hitters and which were the duds.  This is easily done, and can be enhanced using Combo Charts and the addition of a bit of attentive formatting.

Let’s Look at an Example:

1. Create a standard Column Chart using the table below:

 2. This will produce the totally ugly and Difficult-to-Decipher Standard Column Chart as illustrated below (you really do not want to use this style and format…):

3. Right-click once on any single column for the year 2013. This will select all of the columns for that year.
4. You can then select Change Series Chart Type and choose an Area Chart
5. Double-click on your chart, and bring up the Design Tab Tools.
6. Then take a couple of minutes to add some visual appeal by formatting your chart to create an easy-on-the-eyes chart similar to the one below:

And there it is! You have created a Combo Chart that Communicates Effortlessly with your spreadsheet viewers. Totally Cool!