Making pretty reports - Advice please

  • Thread starter Thread starter mdeboard
  • Start date Start date
M

mdeboard

I am fairly proficient in the use of formulas to produce data that
need for a fairly complex mortgage and real estate investmen
spreadsheet, but I'm lacking in presentation. I need to be able t
print a presentable report for my clients and I've had a difficult tim
importing the data from my spreadsheet to Word in a reliable manner (
must not be doing something right, because it seems too tedious an
time-consuming). I've tried to format a worksheet in Excel to be abl
to present the data in a professional and eye-pleasing manner, bu
frankly I'm just bombing-out. DOES ANYONE HAVE A GOOD SUGGESTION FO
WEBSITE TUTORIALS AND/OR READING MATERIAL TO LEARN HOW TO MAKE PRETT
EXCEL REPORTS?

Thanks in andvance
 
Firstly, I wouldn't just think of it as pretty ;-) The same data presented in
two different ways can give two completely different meanings. Also, whether it
be a customer or a meeting, how you present a report can make the difference
between making a sale, or getting approval etc. One of the problems you will
have when copying and pasting into Word is that it will sometimes just go all
over the place, so you may want to consider copying what you have in Excel, and
then paste special in Word as a Device Independent Bitmap. This means you will
get exactly what yous ee in Excel, albeit it will be only in a picture format.
The fact that it is a picture means you can drag it and resize it, although if
you take that to too much of an extreme, the results will not be 'pretty' :-)

The one thing you do need to do is to make sure that the information/message you
want to get across, is what really comes out of the report. There is an old
adage called KISS - 'Keep It Simple Stupid', and it really does work. Think of
all the websites you have visited where there are a 101 different things on the
page and you have no idea what to look at first. Now compare those to the sites
where there are only a few things visible, but they are grouped logically, and
it is instantly obvious what they want you to focus on. Don't put hundreds of
figures on a chart, summarise the data, focus on key aspects/figures. By all
means have annexes with more detail, and make reference to them if you want, but
remember that 80% of the real impact will likely come from just 20% of your data
or less.

Colour works if it is done so sparingly. A 100 different colours and fonts is a
loser from the word go. Stick to a couple of main fonts, and a simple colour
scheme, perhaps using colour to highlight areas you want to focus their
attention on and you should be OK. Never use colour just for the sake of it, as
it will detract from the areas where you use it for a purpose.

Just my £0.02p worth
 
I am using Excel to generate the raw data and ideally would like t
present it in a Word format. But I have been having alot of issue
getting the data exported to Word, and I figured the solution would b
to just create another worksheet and try to format that worksheet int
a printable, 8.5" x 11" final product. I've figured out how to impor
my company logo into the worksheet, but the column formatting to mak
the report look professional, and lining up the text with the formul
cells, is a bear. I figure there must be a solution that allows you t
create a worksheet that is text-formatting friendly and dynamic enoug
to handle changing sizes of values... otherwise I end with awkwar
gaps that are far from the polished product that I'd like to be able t
print and present to a client. :
 
Don't wish to be rude, but what you are trying to do sounds like Basic Excel
presentation to me. Perhaps you would be well served by taking an Excel
course, learn the fundamentals, and than seek to apply those fundamentals to
an automated solution.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
When you say lining up text with cells, you aren't trying to line up text in
word with cells in Excel are you?

I would put everything that was going to be on that page of the report into the
Excel sheet, text as well, format it as you need and then dump the entire page
into a single page in Word as an image. You can get rid of gridlines as a whole
and then use Borders to put back just the ones you want, which will make it look
a lot neater. Logos if top and centered are better left in the Word dcoument
itself as opposed to putting in the spreadsheet as you will never get it to
match identically the rest of the logos in terms of size/placement. All headers
and footers should be in the word document, but anything that has to line up
with cells should really also be in cells and in the Spreadsheet prior to
copying/pasting.
 
FWIW,

*Forget* about your data at the outset!

First, place the "ToggleGrid" icon on your toolbar:
Right click in the toolbar and choose "Customize", and click on the
"Commands" tab.
Scroll down in the "Categories" window and click on "Forms".
Then, scroll down almost to the bottom of the "Commands" window and click
and drag the "ToggleGrid" icon to your toolbar.

Second, place the "SetPrintArea" icon on your toolbar.
Same procedure as above, except find it in "File" in the "Categories"
window.

Now, start in the unused portions of your WS (ColAA, ColBA, ... whatever)
and format 40 or 50 columns to a width of 2
..
This gives a "graph paper" appearance to this part of the WS, permitting you
to more accurately place titles and headings and line lengths where they
would be the most appealing.
When your text runs over 5 or 10 or 20 columns, select these and
"CenterAcrossSelection", *DO NOT* merge cells!

Click in the top right cell of your proposed form, navigate to the bottom
left cell, hold down shift and click in it to select the entire future print
area.
Just guess at this point.
Changing it as you go along is easy.
Now, while this area is selected, click in the name box and type a short
name, maybe "Print1" (no quotes) and hit <Enter>.

Now, start adding your borders and lines.
Toggle your grid off and on to see what the printout would look like.

As you go along, click in the name box and select the form, and click on the
"SetPrintArea" icon, and print out a sheet.
Add or subtract columns and/or rows, resize your "Print1" selection, or
create a "Print2" selection and use the "SetPrintArea" icon to print out
this test version.

What you are doing here, is creating a presentable form, with no concern
whatever as to what or where your formulas and data are located.

When you have something that you think might work, *now* add your data.

Just use links!

For example, from the main data section of the WS, right click in the cell
for the totals for August, choose "Copy", navigate to the cell in the print
form that is to display this total, and right click in it and choose
"PasteSpecial", and click the "PasteLink" button.
This cell will now display whatever is contained in the August total cell,
even if subsequent calculations change/update it.

When everything is completed to your liking, just print out this
presentation sheet.
If you wish to keep a record, one that will *not* update with future
calculations, just copy this sheet *down* the columns, (to preserve the
columnar formats), and "PasteSpecial" selecting "Values" to eliminate the
links and preserve the existing data.

A little practice and a little playing around should give you something
that's presentable.
 
Back
Top