Manager question: excel formatting

June 25, 2016 9:47 AM

I have a question on how to set standards for an ambiguous process. Part of my job involves having my directs "body-shop" powerpoint/excel charts - i.e. organizing things on the page, making tables look nice, filling in / fleshing out commentary, etc. (I'm in consulting). How can I help them do this better?

Often times I find myself re-working what they put together to get the formatting right. I'd like to give them feedback but I'm having a lot of trouble defining an external, quantifiable standard that I can hold them to. Too often I can only say "make this look better", or spend 15 minutes going over the slide with a red pen and detailing exactly what I'd like done. I've been doing this a while, so for me it's more of an art than a science, but just saying "hey that looks bad, make it better" is very ineffective.

Any thoughts on best practices for setting MSOffice formatting standards?

posted by The Ted to Work & Money (8 answers total)

Could you set up some kind of a corporate style guide? I have one in my current job and it specifies how our reports and graphics look like. To start thinking about it, think of the common things you correct on a regular basis. Include examples of things that work (I. E. Graph that works). I'm glad to have a guide, makes my life way easier when doing formatting and reduces my bosses workload when doing quality control on my deliverables.
posted by snowysoul at 10:10 AM on June 25, 2016 [7 favorites]

Teach them how to use pivot tables? They all end up looking like "Excel reports" that way.
posted by fiercekitten at 11:05 AM on June 25, 2016

Give your staff the job of compiling the style guide. First ask them to create a portfolio of some "bad...better...good" progressions from their actual work, say over the next month. Then schedule a couple of meetings where everyone goes over the portfolios together and articulates what's different in the "good" products. Assign someone to take notes. Compile notes and examples into training guide.
posted by Ausamor at 11:29 AM on June 25, 2016 [2 favorites]

There's potentially several separate issues here... First, you definitely need a style guide that reflects your company's preferences (fonts, colors, shading, underlining, etc.) for Excel tables/charts as well as PowerPoint shows, reports, proposals, etc. A corporate style guide accompanied by a bunch of templates will help. I would also add that you need spreadsheet modeling guidelines to ensure proper Excel hygiene, e.g. never bury assumptions in equations, only one table per worksheet, etc. so other people can adapt them later.

But some consultants produce documents that are supposed to mimic the client's corporate dress or have a project-specific look and feel. Obviously if you're following the client's style guide, get all the logo files, fonts, templates etc. from them, early in the process. If you're developing an independent look and feel, start with some early low-stakes products--memos, interim reports, etc.--and build the project-specific practices. Make the client approve the fonts, colors, layout regimes, etc. before you get to the end game, when budget stresses and project exhaustion make it more difficult than it needs to be.

I used to have an employee who produced the most hideous Excel charts and graphs ever: garish colors, no consistency, nothing matched, etc.. It turned out he was color blind; the rest was explained by his alleged design prowess (studied architecture) and self-indulgence ("I get bored making them all the same"). If this guy is working for you now, lay down the law about following the style guidelines, using the templates, etc. Otherwise you'll be up at 3 am reformatting stuff. Ask me how I know.
posted by carmicha at 3:06 PM on June 25, 2016 [1 favorite]

Towards a template or style guide, here's what I think is important vis-a-vis Excel formatting:

Naming and numbering conventions for tables, charts, sheets, etc.

Titling Hierarchy formatting regime (e.g., font size, styling, indent, etc. and justification regime for sub-totals and totals)

A Column Formatting Regime, e.g., when are spacer columns required, are there super-titles, if so do underlines extend across the sub-categories, etc.

Number formatting practices, e.g. using the "accounting" settings vs the $ sign results.

Standardized row heights, justification, vertical placement, etc.

Target widths for portrait and landscape orientations, plus established practices for splitting a chart vs making it conform to page height or width.

Elimination of grid lines.

How to draw attention to totals or significant figures, e.g., shading, boxes, etc.

When to use underlines vs cell boundaries

Use of rotated text

For every kind of chart you use, colors, font/placement for labels, lines/points, keys, background shading, values, etc.

How much to repeat findings from one table to the next so people don't have to flip back and forth

Whether to import the Excel chunks as images (yes please) .

posted by carmicha at 3:21 PM on June 25, 2016 [3 favorites]

Best answer: ahhh...the DWIM(x) problem. With unrecognized input, the system doesn't know what to do. the user hopes the system will just "Do What I Meant with X".

also a case of the pornography problem of Justice Potter Stewart:

I shall not today attempt further to define the kinds of material I understand to be embraced within that shorthand description ["hard-core pornography"], and perhaps I could never succeed in intelligibly doing so. But I know it when I see it...[my bold, ed.]

+1 style guide. but even more, modify your own assessment of the task from "art" to "science". that is, get real with yourself and expend some energy on visualization, then define a rubric. the goal must be objectively attainable - even when evaluated by a third party that is not you. anything else is fairy dust and wishful thinking.

from a purely practical approach:
- it's possible to use excel to create data driven reports that use the same template/style, but read dynamic information from an external data source.
- all the MS Office programs support templates and styles.
- Crystal Reports is sort of a behemoth, but I've seen good, readable stuff generated.
posted by j_curiouser at 6:41 PM on June 25, 2016 [1 favorite]

Why not start with existing guides and adapt them as needed? Even if they don't express your particular standard, they can at least give you examples of what parameters you'll want to define. Here are two good ones:

This Darkhorse Analytics blog post on formatting tables may be a good starting place. They have other posts on similar topics as well.

Edward Tufte's books are classics for a reason. I'd suggest starting with The Visual Display of Quantitative Information, but you can't go wrong with any of his books.
posted by ourobouros at 4:51 AM on June 26, 2016 [1 favorite]

If the issue is with clarity, there are books and courses and online guidelines for data presentation that might be useful for you and your team. You do have two problems: one, they are not consistent in output; two, you are likely not consistent in requirements. Other suggestions here can help for colors but for things like, "how many fields can you show in a table before people glaze over?" It's worth consulting some guidelines to pin some more specifics on your intuitions.
posted by Lady Li at 10:41 PM on June 26, 2016