Microsoft optimize

20 Excel tips for creating stylish spreadsheets

Formatting is an overlooked skill when it comes to Excel. It is essential for communicating results clearly and powerfully.

By Carlo Pandian

excel_tricks.png
Most Excel users would probably agree that the program is a godsend when it comes to creating spreadsheets, yet the results can often be boring to look at. Thankfully, Excel also provides the tools to make your spreadsheets look polished, professional, and pleasing to the eye. Chris Littlewood is Strategy & Research Director at Filtered, an online learning platform for Excel, and he says that "formatting is an overlooked skill when it comes to Excel. This is a shame because it's essential not only to communicating results clearly and powerfully, but also to reducing the risk of error. Consistent and meaningful formatting makes formulas more comprehensible and bugs more apparent".

Create style

Here are a few ideas, suggestions, and tips to get you started.

1. Check out a template

If you're not sure where to start, there are plenty of templates available on the web. Have a look around for inspiration.

2. Choose the right font

Choosing a clear, readable font such as Arial or Calibri is a simple first step towards creating an attractive, business-like spreadsheet.

3. Limit use of different fonts

Two different font styles can help to differentiate between headers and main text. More than two, however, can be distracting.

4. Center your title

Use the "Merge Cells" or "Center Across Selection" options to create a professional, centred title for your spreadsheet.

5. Start in b2

Leaving row 1 and column A blank is an easy way to create some space in your document.

6. Bold your headers

Headers that stand out from the main text can help to make your spreadsheet easier to read. Bolding them is a quick and easy way to do this.

7. Vary your font sizes

Using a larger font size for headings and sub-headings makes for a more readable spreadsheet. It's also a good idea to keep your basic font size large enough to read clearly.

8. Align text

Format your cells and select options that allow you to align your text as best suits your needs. For instance, text aligns to the left by default, while numbers align to the right - so you might need to tidy up tables by giving headings the same alignment as the table contents.

9. Create space

You can manipulate the height and width of cells to keep your spreadsheet from looking too cramped.

10. Leave some cells empty

If your spreadsheet is fairly complex, leaving a row or column empty at appropriate breaks in the data can help to improve readability.


Also read: The 10 most useful Excel keyboard shortcuts


11. Play with grid lines

All those lines separating all those numbers can look very confusing. By opting to show only the lines around your results column, for example, you can make a big difference to the readability of your spreadsheet.

12. Use color sparingly

Colors can be used in Excel to highlight key rows or columns, making the spreadsheet more attractive and easier to read. Don't overdo it though, as this can produce the opposite effect.

13. Keep to dark text on a light background

If you do choose to introduce color, be sure to stick to dark text on a light background, as this is easier to read and prints better.

14. Try some subtle decoration

Simple background decoration such as an understated dip-dye effect can make your spreadsheet more visually appealing. However, be careful not to insert any decoration that might distract from the data.

15. Consider zebra stripes

If you're creating a spreadsheet with a lot of columns, it can sometimes be difficult for readers to match up data on the far right of the document with the categories listed on the left. Subtly shading alternate rows helps the eye to follow information across the page.

excel_sample_graph.gif

16. Use the table function

If your data requires it, you can insert tables into your Excel spreadsheet, giving the eye a break from the main information in the document. Use an accent color to connect the table with the rest of the spreadsheet.

17. Align any graphs or tables

If you're creating a document with multiple tables or graphs, take care to line them up so that they start in the same row. This will make your finished document easier on the eye.

18. Add an image

A simple image such as your company logo can help to make your spreadsheet look professional and attractive.

19. Name your worksheets

While this won't impact the look of your spreadsheet, it certainly helps to keep everything well-organized and professional.

20. Exercise restraint!

Once you start experimenting with Excel's design features, you might be tempted to get a little too creative. Keep in mind that your main objective is to make your spreadsheets look professional and easy to read, so stay away from anything too elaborate.


Carlo Pandian is a freelance writer interested in productivity, online learning and social media. Connect with him on Twitter @carlopandian.

8 comments
kaostricks
kaostricks

How can i get more detailed information on 

table function
info
info

I rarely comment on such articles but, in this case, I couldn't DISAGREE more as you are talking style over substance  I take time in my workshops to discourage people from spending excessive time formatting Excel sheets as you describe, and instead concentrate on good structural design that harnesses the power and efficiencies of Excel while also reducing the opportunity for simple errors that will occur with manual slicing/dicing of data.

The commonest (and worst) way to lobotomise a worksheet is to have labels/headers occurring vertically throughout, with or without blank rows and columns to visually 'separate' data.  This destroys the utility that Tables and hence Pivot Tables provide, as well as efficiency tools such as Autofill.

I recommend people keep data strictly symmetrical. Once in Tables it is easy to extract filtered data somewhere else for specific prettying if required, or just use Pivot Tables/Charts and format THEM.

PS. If you MUST have a 'separator', simply make that Row taller (bottom aligned). This does the job visually without destroying the efficient 'matrix' design.

GSG
GSG

Don't forget to format for printing.  One of my biggest pet peeves is getting a spreadsheet and it's not formatted, so I have to spend time doing this.  Yes, we should all be looking at it online, but the reality is that we still use paper, and not everyone has a laptop to bring to meetings.

tehurley
tehurley

Thank you for the nice list. I used to create columns that were 1-2 units wide to create breaks in the information.  And for graphs, because I have two friends who are color blind, I use both colors and patterns.  I print a lot of financial info from the Internet and it is obvious that the creators have not thought about readers who print their information on a B&W printer or who may be color blind.

vinitpatel
vinitpatel

Hey @Mark W. Kaelin, I think these three are useful 'quick wins'

1. Templates. I'm a big fan but they do take some time setting up.

2. Cell styles. More practical might be to have some 'cell styles' you use frequently (e.g. input cells are always light yellow fill and blue font).

3. Contents page. Too many spreadsheets are littered with formulae and data all over the place. Have a contents page and that way your story can be told dead easily and effectively.

Mark W. Kaelin
Mark W. Kaelin moderator

How much time do you spend on making your Excel worksheets more readable? Over the years, have you developed a few quick and easy techniques for making your worksheets look polished and professional? What advice can you give us?

GraemeLeggett
GraemeLeggett

@info I think you are jumping in too hard. The author says specifically if you introduce some formatting to make it easier to read and understand what to consider. Pivot tables etc are about trying to extract meaning automatically from data but the end result still needs to properly presented.

Chris_Lit74
Chris_Lit74

Formatting spreadsheets after they've been set up can be time consuming. It's more efficient to present them well as you work. If you stick to a consistent key or palette - e.g. Bold for totals, bold italic for column headings, italic for comments... - then it becomes second nature.

Becoming familiar with keyboard shortcuts can also help, so you can apply formatting as you type. This avoids having to switch back and forth between mouse and keyboard so frequently.

We teach these practical tips (and lots more!) in our Filtered Excel course: filtered.com