When A Number's More Than A Number, Use Excel

Workers who are adept at seeing relationships in statistics may be the ones who like to sit down with a mixture of figures to organise them into a coherent report. Others could prefer to steer clear of making calculations fearing they will make errors and miss important trends. Whether you fall into either of these categories or not, Microsoft Excel could help you make the most of number crunching - if you're a fan of statistics - as well as making calculations less painful, if you're not.

Training formats focussing on Excel (such as our advanced course in Excel) can help number crunching addicts and those shy of statistics to get to grips with all the tools available via the software. Excel is usually a firm fixture within the working days of many people, simply because it can be used in so many different contexts.
It transforms simple spreadsheets into meaningful collections of numbers, so you can spot trends and identify important statistics easily. One of the main ways it does this is via conditional formatting, which adds emphasis to figures using several different methods, mentioned below.

Get colouring

So, you have a worksheet filled with numbers of products your firm sells, for example. The data reflects the items that sell the best and the least, as well as those in between. When taking this kind of information to a meeting, or storing it for later use, you may want to call attention to specific figures.

Excel gives you the opportunity to add colour to cells, which each contain a figure. The type of colour is chosen by you and its strength/gradient depends on the statistic. If you want the highest numbers to be in green and the lowest in red then chose this option and the cell with the most popular products will be tinted green and the least popular red. These figures will then immediately jump out at you and make good visual representations of, dry, dusty data.

Go to the bar

Another way to add emphasis to figures is to use conditional formatting tools to apply bar graphs within spreadsheets, so each statistic has its own bar. This is useful and is a good way to show negative values. Going back to the previous example where you compare sales figures, perhaps this time you'd like to look at average sales figures from this year and the year before.

Those products that are below average in sales can be identified by a red bar jutting away from the statistics, while above average figures show a green bar jutting towards the figure concerned. Companies offering Excel training formats usually show you how this works and how effective it can be.

Be iconic

The advantage to conditional formatting is that it is a simple way of showing the importance of the statistics. Basic methods are used to draw attention to figures so they're easy to find and see. Icons are another option if you prefer not to use colour scales or bars.

Depending on the version of Excel you are using, there might be a different number of icons available to reflect differences in the statistics. Some icons resemble arrows, others circles, with each icon varying in colour. So, if you wanted to display red, upwards arrows to highest selling products and green downwards pointing arrows for lowest selling goods then this is possible.

From top to bottom

These conditional formatting rules are a good way to give a visual impression of all the data contained in a spreadsheet. But sometimes you might only be concerned about the statistics that reflect the best and least performing items in your firm.

Say a meeting you're attending is focussing on those goods that fall in the top ten per cent of sales. You can emphasize these figures by using the Top/Bottom Rules function. This has added flexibility, so you don't have to just stick to highlighting above average figures, or those in the top/bottom ten per cent, but at any level.