How to make Ireland's rugby ranking look good....

Well, pretty at least. When viewed on a spreadsheet. What am I on about, you may wonder. Bear with me...

Have you ever seen those photographs of celebrities where the poor girl has dared to leave the house without a bucket of makeup on? The horrors! The tabloids point out that she looks pretty normal without all the airbrushing and uplighting, and we all think, "We could walk right past her on the street and not notice".

The same goes for spreadsheets. They like a little lipstick and airbrushing. It makes them stand out. Don't believe me? Compare the two sets of exact same data below, showing the IRB top 8 rankings for the last 3 years, and the movements year on year:

Example A:

Example B:

Now, feel free to disagree with me, but I find example B easier to read (especially when its a 1000 lines of data over 30 columns you're analysing). You can immediately see who has moved up, who has moved down, and who has stayed consistently at the top (damn those Kiwis).

It took me all of about one and a half minutes to re-format the boring numbers  in Example A to the clear and concise numbers in Example B. Here is how:

1) Lose the gridlines: See my earlier blog on how to do this...

2) Choose your theme (this only applies to Excel 2007 and 2010): A theme basically sets a colour chart and font for your workbook. It influences the colours you can pick for cells, graphs, pivot tables etc., so choose wisely. If you haven't done this before, here is how:

Go to Page Layout - Themes. The drop down arrow will show you all the theme choices with their basic colour range. Select your particular fancy, and you're set. Note that next to the 'Themes' box, you also have options to change only the colour, font or effects. Play away to your heart's content until you're happy.

Note that if you decide halfway through that you would prefer a different colour chart, change your theme. Everything will follow suit, but note your columns and rows may change size too, so check your print preview before printing.

Note, also, that you can skip this step. Your worksheet will show the standard Office colours.

3) Highlight your headings: Select cell B2, and using your menu options on your Home menu tab, change your background colour, text colour and make the text bold.

4) Use your format painter: For anyone who hasn't discovered this little gem, be prepared for a time saver of note. Go to your home menu tab and find this:

Point to it, right click, and add it to your your Quick Access toolbar, because you should be using this tool all the time. What this tiny little brush does is copy all your formatting in your highlighted cell and will apply this formatting to all other cells you select afterwards.

So you can use this to format all your headings in Row 4 in one go, by simply clicking on Cell B2, clicking on the format painter, and then selecting Cells B4:G4.

5) Center your data:  by using the alignment buttons on your Home menu tab.

6) Center and merge:  your main heading by selecting Cells B2:G2, then clicking the Merge & Center button in the alignment segment on the Home menu tab.

7) Use Conditional Formatting: to show the positives and negatives in the YOY columns. Select cells D5:D12. Still on the Home menu tab, drop down on the arrow on Conditional Formatting and select Highlight Cells - Greater than

Type in 0 in the box provided, and select your colour choice. I went for Green for anything that had a movement greater than 0. Then repeat for Less Than.
With your cells still selected, use the format painter to apply the same conditional formatting to cells F5:F12.

8) Apply borders: Note I left this till last. You really want to be happy that you have finished adding to your data before you apply borders, otherwise if you add anything later, you can spend a lot of wasted time redoing them.

I have chosen to add blue borders, as opposed to the standard black ones. To do this, select the cells you want a border around, and then go to
Home - Borders - More Borders.... Select your border style and colour FIRST, and then select where you want your border applied.

And you're done. The neat formatting might even distract you from the fact that Ireland has dropped four places in one year. Or not.

These are VERY simple formatting techniques. It can get a whole lot more complicated, but it very much depends on your data and what you will use it for.

For some helpful tips on other formatting techniques I have found handy over the years, sign up to our newsletter.

Happy Spreadsheeting!