What is the Comic Sans of Excel spreadsheets?
June 17, 2013 12:50 PM Subscribe
For my new position at work, I'll be putting together a large number of Executive Overviews. This is a new area for me and I'd like to avoid unknowingly committing a spreadsheet faux pas.
I've gotten reasonably good at manipulating the data and presenting it in a simple, clean manner. This leaves about 75% of what Excel (2010) COULD do ignored.
I suspect that good taste will dictate that I continue to avoid most of these "features" but since I have only a couple of weeks' experience in playing around with the various offerings I'm not really sure I understand enough about what should be considered "off limits" (or whether I've already crossed that line).
Bar graphs with drop shadows? Bevelled 3d pie charts? Glow?!
The job at hand is to take a month's worth of extensive data and distill it to a couple of tabs that someone way above my pay grade can look at and inside 60 seconds have an idea of what happened in that month. It should look both attractive and "professional".
The work I've done so far has been met with praise, but I get itchy fingers seeing all these sliders and options that I haven't tried out yet.
What are some style resources to teach an aspiring spreadsheeter what should and shouldn't be used to achieve "professionalism" in my spreadsheets? What layout tips and/or advice does the hivemind have?
I've gotten reasonably good at manipulating the data and presenting it in a simple, clean manner. This leaves about 75% of what Excel (2010) COULD do ignored.
I suspect that good taste will dictate that I continue to avoid most of these "features" but since I have only a couple of weeks' experience in playing around with the various offerings I'm not really sure I understand enough about what should be considered "off limits" (or whether I've already crossed that line).
Bar graphs with drop shadows? Bevelled 3d pie charts? Glow?!
The job at hand is to take a month's worth of extensive data and distill it to a couple of tabs that someone way above my pay grade can look at and inside 60 seconds have an idea of what happened in that month. It should look both attractive and "professional".
The work I've done so far has been met with praise, but I get itchy fingers seeing all these sliders and options that I haven't tried out yet.
What are some style resources to teach an aspiring spreadsheeter what should and shouldn't be used to achieve "professionalism" in my spreadsheets? What layout tips and/or advice does the hivemind have?
Some of the executives looking at your spreadsheets will likely be old school and will print them out rather than reviewing them electronically. Make sure that when you print them out in greyscale, the colors on your bar graphs (or whatever) still look different from each other.
posted by OrangeDisk at 12:57 PM on June 17, 2013 [14 favorites]
posted by OrangeDisk at 12:57 PM on June 17, 2013 [14 favorites]
Stay within the print margins and use consistent page orientation whenever possible if the sheets will be printed.
posted by These Birds of a Feather at 12:59 PM on June 17, 2013 [6 favorites]
posted by These Birds of a Feather at 12:59 PM on June 17, 2013 [6 favorites]
I'd say the most important thing to do is properly and neatly label your spreadsheets. Sometimes I will go so far as to copy equations done in Word as an image next to more complicated calculation to demonstrate what was done. Nothing drives me crazier than trying to divine what everything is in a spreadsheet.
As for the styling of charts, it's ultimately preference. Strive for readability and conveying the data in the least cluttered way possible. Don't get too bogged down with bells and whistles. Simpler is often better, but sometimes having 3D adds that little something extra.
posted by C'est la D.C. at 1:00 PM on June 17, 2013 [2 favorites]
As for the styling of charts, it's ultimately preference. Strive for readability and conveying the data in the least cluttered way possible. Don't get too bogged down with bells and whistles. Simpler is often better, but sometimes having 3D adds that little something extra.
posted by C'est la D.C. at 1:00 PM on June 17, 2013 [2 favorites]
Best answer: Will any of the spreadsheets ever get faxed anywhere? Any field with a greyscale background will turn to illegible mush when it goes through a fax machine.
I highly recommend that you take the time to look at the work of Edward Tufte, who writes some very deep, sensible things about presenting data in the most efficient and readable way.
posted by Multicellular Exothermic at 1:10 PM on June 17, 2013 [3 favorites]
I highly recommend that you take the time to look at the work of Edward Tufte, who writes some very deep, sensible things about presenting data in the most efficient and readable way.
posted by Multicellular Exothermic at 1:10 PM on June 17, 2013 [3 favorites]
Per OrangeDisk and These Birds Of A Feather, also make sure that everything is optimized to print both attractively and functionally on one page. (Or a short easily-skimmed booklet if we're talking about a series of spreadsheets/reports.)
posted by Sara C. at 1:10 PM on June 17, 2013
posted by Sara C. at 1:10 PM on June 17, 2013
Seconding Tufte, he says all the important stuff.
posted by scose at 1:11 PM on June 17, 2013 [1 favorite]
posted by scose at 1:11 PM on June 17, 2013 [1 favorite]
Best answer: Things that seem very, very obvious, but bear repeating.
1. Keep it simple; keep colors/fonts/borders/lines very clean and minimal
2. Make sure whatever you produce prints well (set areas, colors etc)
3. Don't link to outside data
4. Uniform formatting across all tabs
5. Spell check
6. If you have a moderately complex formula, stick it in a comment with a note so that whomever is looking at it knows why you used it.
7. Learn to group data, and carry over formating so that data looks good grouped and ungrouped
8. white out your backgrounds- set fill for everything to white, this automatically makes reports look cleaner onscreen
9. (depending on your audience), Instruction/Summary page
I have 8+ years professional staring at excel experiance.
posted by larthegreat at 1:11 PM on June 17, 2013 [8 favorites]
1. Keep it simple; keep colors/fonts/borders/lines very clean and minimal
2. Make sure whatever you produce prints well (set areas, colors etc)
3. Don't link to outside data
4. Uniform formatting across all tabs
5. Spell check
6. If you have a moderately complex formula, stick it in a comment with a note so that whomever is looking at it knows why you used it.
7. Learn to group data, and carry over formating so that data looks good grouped and ungrouped
8. white out your backgrounds- set fill for everything to white, this automatically makes reports look cleaner onscreen
9. (depending on your audience), Instruction/Summary page
I have 8+ years professional staring at excel experiance.
posted by larthegreat at 1:11 PM on June 17, 2013 [8 favorites]
Bar graphs with drop shadows? Bevelled 3d pie charts? Glow?!
No. Just... no. Resist adding cruft through formatting that detracts from the data. This paper goes through common chart types, and their uses. The examples are more R than Excel, but the important thing is the relative simplicity of the plots; they all have just the information they are trying to convey, labels of what is what, and nothing more. This simple, clean look is also, helpfully, a common trend in general purpose graphic design.
Each table or figure should really only tell one or at most two stories, with the information formatted to encourage that. I'm looking through a (well received) presentation I did last week and the table has the column labels and the overall total highlighted (the former are bold and the latter has a thicker line above it, implying "total"), and some stuff that was less important is actually in greyed out text; I have a column of rates that are all "1 in.." format; I couldn't come up with a concise column label that would clearly explain it, so I left the "1 in" in front of all of them, but grey so the eye skips past it.
The go to person on data and formatting more generally is Edward Tufte. Which I see on preview at least two others have noted while I was typing this.
posted by Homeboy Trouble at 1:15 PM on June 17, 2013 [2 favorites]
No. Just... no. Resist adding cruft through formatting that detracts from the data. This paper goes through common chart types, and their uses. The examples are more R than Excel, but the important thing is the relative simplicity of the plots; they all have just the information they are trying to convey, labels of what is what, and nothing more. This simple, clean look is also, helpfully, a common trend in general purpose graphic design.
Each table or figure should really only tell one or at most two stories, with the information formatted to encourage that. I'm looking through a (well received) presentation I did last week and the table has the column labels and the overall total highlighted (the former are bold and the latter has a thicker line above it, implying "total"), and some stuff that was less important is actually in greyed out text; I have a column of rates that are all "1 in.." format; I couldn't come up with a concise column label that would clearly explain it, so I left the "1 in" in front of all of them, but grey so the eye skips past it.
The go to person on data and formatting more generally is Edward Tufte. Which I see on preview at least two others have noted while I was typing this.
posted by Homeboy Trouble at 1:15 PM on June 17, 2013 [2 favorites]
From my perspective as a sometimes spreadsheet/chart reader: If the bells and whistles do not add to my comprehension or add to the legibility it wastes my few spare brain cells to process them. Make sure your additions enhance the data instead of distracting from it.
posted by SLC Mom at 1:15 PM on June 17, 2013
posted by SLC Mom at 1:15 PM on June 17, 2013
If you are ever talking with someone about your Excel data, never say you "manipulated" the data. Always say you "managed" the data
posted by lstanley at 1:16 PM on June 17, 2013 [10 favorites]
posted by lstanley at 1:16 PM on June 17, 2013 [10 favorites]
Best answer: Readability is your master.
These are three of the things that drive me crazy when I get an excel document from another person.
posted by bilabial at 1:30 PM on June 17, 2013 [4 favorites]
These are three of the things that drive me crazy when I get an excel document from another person.
- Conditional formatting to shade every other row, so that I can follow the row. Use a very pale color for this. Not blood red or forest green.
- Freeze panes on the top and far left of a page so that a person scrolling down or left can still know what the cell they're in is about.
- Setting things to print on one page if it will still look good.
posted by bilabial at 1:30 PM on June 17, 2013 [4 favorites]
Best answer: Silly sounding tip that mattered to one of my managers - leave the cursor in A1 on each tab before you save. That way the next person who opens it starts right at the top instead of down wherever you were last looking the totals.
posted by ansate at 1:33 PM on June 17, 2013 [15 favorites]
posted by ansate at 1:33 PM on June 17, 2013 [15 favorites]
In addition to Tufte, I recommend Stephen Few and his book on Information Dashboard design. That alone will help you choose charts and how to present them.
posted by miasma at 1:34 PM on June 17, 2013 [1 favorite]
posted by miasma at 1:34 PM on June 17, 2013 [1 favorite]
The best take-away I had from Tufte was the concept of "data-ink" vs. "presentation ink". If there's a line on your graph, is it there to make the graph look "pretty" or is it there to show data? Is additional text strictly necessary or can it be simplified. When I started pruning out the complexities on my graphs, I started to get compliments on them for their clarity and comprehensibility.
In general, Excel's default format add a lot of cruft to graphs, presentation ink. It's not an unalloyed evil to do extra formatting, like 3-d or drop shadows, but you should have to think very carefully about why you want that on your graph, rather than Excel's default "why not"?
posted by bonehead at 1:37 PM on June 17, 2013 [2 favorites]
In general, Excel's default format add a lot of cruft to graphs, presentation ink. It's not an unalloyed evil to do extra formatting, like 3-d or drop shadows, but you should have to think very carefully about why you want that on your graph, rather than Excel's default "why not"?
posted by bonehead at 1:37 PM on June 17, 2013 [2 favorites]
I sometimes have to export data into excel and send it to execs that think a pivot table is a piece of furniture. For the technologically impaired, I never send raw data or data that can be manipulated. If a formula is in there they will find a way to break it. If I have a vlookup, they will manage to move the reference table. Gha!
Removing the possibility by either paste special values or making the doc read only can save a lot of headache depending on your audience.
posted by munchingzombie at 1:45 PM on June 17, 2013 [3 favorites]
Removing the possibility by either paste special values or making the doc read only can save a lot of headache depending on your audience.
posted by munchingzombie at 1:45 PM on June 17, 2013 [3 favorites]
In the page setup window, you can leave the 'number of pages tall' blank. This allows you to make your document print for example 1 page wide, and n pages tall.
posted by goethean at 1:45 PM on June 17, 2013
posted by goethean at 1:45 PM on June 17, 2013
Response by poster: bilabial : were you saying you WANT those three things or HATE GETTING those three things? The specific advice you're giving suggests you want it, and I've been implementing 1 & 2 already, but I wanted to clarify just in case.
since these aren't printed (or at the very least are presented in a non-print form initially) I hadn't done any of the print-specific things yet, but it's something to ask about. I may be mistaken in assuming that they aren't printed and referred to after the presentation meeting.
posted by radiosilents at 2:20 PM on June 17, 2013
since these aren't printed (or at the very least are presented in a non-print form initially) I hadn't done any of the print-specific things yet, but it's something to ask about. I may be mistaken in assuming that they aren't printed and referred to after the presentation meeting.
posted by radiosilents at 2:20 PM on June 17, 2013
Best answer: In addition to the EXCELent suggestions above, build your spreadsheets with a hypothetical future user in mind, one with no prior experience with the files. For instance...
Practice good spreadsheet hygiene, e.g., avoid mixing data, calculations and reporting, don't bury assumptions in formulae (other than obvious constants made clear by the labels, like square feet per acre), label everything consistently, choose abbreviations wisely, etc.
Make the age of the relevant data obvious.
Use footnotes (you can superscript a single character in a label) to identify sources, clarify data gaps, explain a label, etc.
Consider label hierarchy for clarity and efficiency, that is, use a broad label stretching over several cells so the sub-labels can be shorter and cleaner. For example, the big label says "Fiscal Year 2013-2014" and has a long line under it that arcs over and embraces the twelve columns showing each month. Clear, short and un-repetitive (vs each label saying March FY 13-14): win-win.
Learn how to manipulate text wrapping, angled text and other tactics for improving/fitting labeling.
Don't be afraid to create files with numerous (properly named) sheets! It's much easier than roaming around the spreadsheet hunting for a sub-section. Limiting each sheet to what can print at 8.5x11 (excluding the ones that store data) will often help you discover when you're trying to pack too much info on a sheet.
Unless the variable itself is being tested, as in a sensitivity analysis, be sure there is only one place to change it so you can avoid missing a spot and introducing an inaccuracy. That said, don't be afraid to repeat assumptions on every relevant sheet/print range (via cell reference only) if you have the room and it would aid reader comprehension. Don't make people flip back and forth over simple (but vital) figures like interest rates.
Similarly, don't be afraid to repeat the last step/end results of a multi-part analysis for clarity. For example, if a previous sheet tallied up X costs, and another sheet tallied up Y costs, it's just fine to have a third sheet summing X and Y or, if that's too lengthy, to reprise the total X costs on the sheet with the detailed Y values en route to showing total project costs .
Consider making presentation pages that link to your analysis if doing so would enable you to improve formatting in ways that don't impinge on functionality. For example, it's often nice to insert tiny columns in between the ones with content for visual separation... but those are a PITA when you want to copy across the columns. Double check cell references for accuracy.
"Accounting" takes a little more effort but is the best number format because it a) right justifies the digits; b) left justifies the currency signs; c)inserts comma separators; and d) shows a "sum line" if you hit the underline button. Use with or without currency signs, your preferred number of decimals, etc.
Speaking of currency signs, if you have a stack of numbers that add to a sum at the bottom, only the top figure and the sum get the currency signs. Conversely, if you're listing factors where there's no relationship from row to row, e.g., dollars per square foot, then the currency sign goes on every cell. Very important.
Make a considered decision about rounding. Using it thoughtlessly is as annoying as portraying false accuracy, e.g., calculating results to the umpteenth decimal point.
Use whatever fonts (within reason) correspond to your company's style manual. If they're really appalling, then just nod to the standard in the titles.
A la the grey scale suggestions above, test your charts, graphs, etc. by printing them in color and then photocopying them in black and white.
Consider designing sheets to accommodate the related graph or reprising key figures.
posted by carmicha at 3:13 PM on June 17, 2013 [3 favorites]
Practice good spreadsheet hygiene, e.g., avoid mixing data, calculations and reporting, don't bury assumptions in formulae (other than obvious constants made clear by the labels, like square feet per acre), label everything consistently, choose abbreviations wisely, etc.
Make the age of the relevant data obvious.
Use footnotes (you can superscript a single character in a label) to identify sources, clarify data gaps, explain a label, etc.
Consider label hierarchy for clarity and efficiency, that is, use a broad label stretching over several cells so the sub-labels can be shorter and cleaner. For example, the big label says "Fiscal Year 2013-2014" and has a long line under it that arcs over and embraces the twelve columns showing each month. Clear, short and un-repetitive (vs each label saying March FY 13-14): win-win.
Learn how to manipulate text wrapping, angled text and other tactics for improving/fitting labeling.
Don't be afraid to create files with numerous (properly named) sheets! It's much easier than roaming around the spreadsheet hunting for a sub-section. Limiting each sheet to what can print at 8.5x11 (excluding the ones that store data) will often help you discover when you're trying to pack too much info on a sheet.
Unless the variable itself is being tested, as in a sensitivity analysis, be sure there is only one place to change it so you can avoid missing a spot and introducing an inaccuracy. That said, don't be afraid to repeat assumptions on every relevant sheet/print range (via cell reference only) if you have the room and it would aid reader comprehension. Don't make people flip back and forth over simple (but vital) figures like interest rates.
Similarly, don't be afraid to repeat the last step/end results of a multi-part analysis for clarity. For example, if a previous sheet tallied up X costs, and another sheet tallied up Y costs, it's just fine to have a third sheet summing X and Y or, if that's too lengthy, to reprise the total X costs on the sheet with the detailed Y values en route to showing total project costs .
Consider making presentation pages that link to your analysis if doing so would enable you to improve formatting in ways that don't impinge on functionality. For example, it's often nice to insert tiny columns in between the ones with content for visual separation... but those are a PITA when you want to copy across the columns. Double check cell references for accuracy.
"Accounting" takes a little more effort but is the best number format because it a) right justifies the digits; b) left justifies the currency signs; c)inserts comma separators; and d) shows a "sum line" if you hit the underline button. Use with or without currency signs, your preferred number of decimals, etc.
Speaking of currency signs, if you have a stack of numbers that add to a sum at the bottom, only the top figure and the sum get the currency signs. Conversely, if you're listing factors where there's no relationship from row to row, e.g., dollars per square foot, then the currency sign goes on every cell. Very important.
Make a considered decision about rounding. Using it thoughtlessly is as annoying as portraying false accuracy, e.g., calculating results to the umpteenth decimal point.
Use whatever fonts (within reason) correspond to your company's style manual. If they're really appalling, then just nod to the standard in the titles.
A la the grey scale suggestions above, test your charts, graphs, etc. by printing them in color and then photocopying them in black and white.
Consider designing sheets to accommodate the related graph or reprising key figures.
posted by carmicha at 3:13 PM on June 17, 2013 [3 favorites]
Excel saves te location of your cursor. So when saving for the final presentation/distribution, navigate to first page/a top cell, otherwise they open to the middle of the document or wherever you last were. Also, if anyone reading this is using accessibility software, merged cells can't be read, I think.
posted by NikitaNikita at 4:00 PM on June 17, 2013 [2 favorites]
posted by NikitaNikita at 4:00 PM on June 17, 2013 [2 favorites]
Adding drop down filters to the columns can be helpful, depending on the data. Click on Filter (in the Sort and Filter group under the Data tab) to add filters to all of the columns headers.
posted by balacat at 4:56 PM on June 17, 2013
posted by balacat at 4:56 PM on June 17, 2013
Best answer: i think bilabial wanted those things, i know i like them. there's a lot of good advice here, i'll just fill in some holes.
if you can, have a sit down with the customers of your report, and ask them what they want. make some examples, draw out the design, whatever. the will appreciate the proactive ... action.
have some co-workers critique it. especially co-workers who aren't familiar with the details of the work so that they can help you spot things you wouldn't have thought of.
do not use pie charts, ever.
do not merge cells, ever. use "center across selection", it's in the menu where you select how the cell is justified.
if there are many sheets, use one sheet as a table of contents, with links to the other sheets. then, put a link on each sheet back to the table of contents.
do copy-paste-values, but only as the final step. keep separate "working" and "values" files.
there's a way to remove the grid lines, i forget exactly how, but it makes it look a little more like a professional document than just an excel workbook.
the most important thing is that the numbers are correct. spot check a few numbers on everything for accuracy. ask yourself if the numbers make sense. if not, figure out why. have a peer give a final look over before it goes out the door.
posted by cupcake1337 at 7:03 PM on June 17, 2013 [1 favorite]
if you can, have a sit down with the customers of your report, and ask them what they want. make some examples, draw out the design, whatever. the will appreciate the proactive ... action.
have some co-workers critique it. especially co-workers who aren't familiar with the details of the work so that they can help you spot things you wouldn't have thought of.
do not use pie charts, ever.
do not merge cells, ever. use "center across selection", it's in the menu where you select how the cell is justified.
if there are many sheets, use one sheet as a table of contents, with links to the other sheets. then, put a link on each sheet back to the table of contents.
do copy-paste-values, but only as the final step. keep separate "working" and "values" files.
there's a way to remove the grid lines, i forget exactly how, but it makes it look a little more like a professional document than just an excel workbook.
the most important thing is that the numbers are correct. spot check a few numbers on everything for accuracy. ask yourself if the numbers make sense. if not, figure out why. have a peer give a final look over before it goes out the door.
posted by cupcake1337 at 7:03 PM on June 17, 2013 [1 favorite]
You are correct, I want those things. If I have to add them myself, it takes me like two minutes. But it's two whole minutes I am silently snippy about how inconsiderate you are. Which is not a good thing. (And I promise, I'm working on it.)
Also, put a footnote that includes the date the item was created, and your name or email address. Set it so that it does not update each time the file is opened.
If you know the cell that the recipient is looking for on a page, save the cursor there. If unknown, save at A1.
And oh god yes, lock down any formulas/make the thing read only if you can.
If you are sending an excel doc along to someone who needs to enter something...you can specify the parameters of what they can enter. So if there's a number between 1 and 10 they can choose, give them a drop down box. To prevent them from accidentally entering 11 when it could never be 11 (but this might bite you in the ass if it ever IS 11).
posted by bilabial at 9:19 PM on June 17, 2013 [1 favorite]
Also, put a footnote that includes the date the item was created, and your name or email address. Set it so that it does not update each time the file is opened.
If you know the cell that the recipient is looking for on a page, save the cursor there. If unknown, save at A1.
And oh god yes, lock down any formulas/make the thing read only if you can.
If you are sending an excel doc along to someone who needs to enter something...you can specify the parameters of what they can enter. So if there's a number between 1 and 10 they can choose, give them a drop down box. To prevent them from accidentally entering 11 when it could never be 11 (but this might bite you in the ass if it ever IS 11).
posted by bilabial at 9:19 PM on June 17, 2013 [1 favorite]
« Older Help a Canadian book flights from the San Fran to... | Should I change the timing belt and water pump? Newer »
This thread is closed to new comments.
posted by Midnight Skulker at 12:56 PM on June 17, 2013 [7 favorites]