How To Spark Up An Excel Workbook

Sun 25th March 2012

Whether you're pitching to a new client, or presenting the latest time and cost information to your team, sparklines can add dynamics to your data trends or sales patterns. We look at how to add the vital spark to your Excel 2010 workbooks.

Images are impactful; they can add interest to any dull data and are easily understood by an audience. By replacing text and data with a visual reference, suddenly something that's immensely complex is represented in a simple chart or a drawing - and we all love images. Just to let you know, we regularly run VBA courses.

Think about the success of a recently launched app that involves drawing something on your mobile and sharing with family and friends. Using the free app, you write a word in picture form, using your finger as the paintbrush and your phone as the canvas. Your phone buddy then has to guess what you are drawing. Before each turn, the app plays back in real time your opponent's last guessing attempt at your miserable effort - complete with errors and false starts.

To date, the game has racked up 30 million downloads and more than £63,000 in revenue from players buying "bombs", which allow them to cheat by skipping words that are impossible to draw in favour of easier options. At the last count more than 20.5 million users are drawing something every day. Now, I'm not one to encourage a slump in workplace productivity, so I'm not suggesting that you rush to your phone to join the network of doodlers, but I do recognise the merits of using visual communication.

Microsoft Excel 2010 allows you to make fast, effective comparisons from lists of data with features such as sparklines, slicers, and pivot tables. And when you apply these features, you can display your data in much more interesting ways to any audience.

Sparklines are tiny charts that fit into a cell in your workbooks, and you can use sparklines to visually summarise trends alongside any data to help convert that data into a meaningful chart. Because sparklines show trends in a small amount of space, they are especially useful for dashboards or other places where you need to show a snapshot of your business in an easy-to-understand visual format, providing an at a glance synopsis of any type of data.

For example, you can use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. And by positioning a sparkline near its data, you get even greater impact.

Data presented in a row or column can be useful, but trends and patterns can be hard to spot at a glance. The context for these numbers can be provided by inserting sparklines next to the data. So, if you feel the need to vamp up your worksheet, you can apply a colour scheme to your sparklines by choosing a built-in format from the Style gallery (you'll find this in the Design tab, which becomes available when you select a cell that contains a sparkline). You can use the Sparkline Colour or Marker Colour commands to choose a colour for the high, low, first, and last values (such as green for high, and orange for low).

You can quickly see the relationship between a sparkline and its underlying data, and when your data changes you can see the change in the sparkline immediately. In addition to creating a single sparkline for a row or column of data, you can create several sparklines at the same time by selecting multiple cells that correspond to underlying data. And if you need to update your worksheets, it's easy to create sparklines for rows of data that you add later by using the fill handle on an adjacent cell that contains a sparkline. Unlike charts, sparklines are printed when you print a worksheet that contains them.

To create a sparkline, select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create; for example, Line, Column, or Win/Loss. Then in the Data box, type the range of the cells that contain the data on which you want to base the sparklines.

After you create sparklines, you can control which value points are shown (such as the high, low, first, last, or any negative values), change the type of the sparkline (Line, Column, or Win/Loss), apply styles from a gallery or set individual formatting options, set options on the vertical axis, and control how empty or zero values are shown in the sparkline. And you can show or hide data markers: on a sparkline that has the Line style, you can show data markers so that you can highlight individual values.

Select a sparkline. In the Show/Hide group, on the Design tab, select any of the check boxes to show individual markers (such as high, low, negative, first, or last), or select the Markers check box to show all markers.

The Date Axis Type (in the Group group, click Axis) command allows you to format the shape of the chart in a sparkline, and you can reflect any irregular time periods in the data. In a line sparkline, applying the Date Axis type can change the slope of a plotted line and the position of its data points in relation to each other. In a column sparkline, applying the Data Axis type can change the width of and increase or decrease the distance between the columns.

You can also use these Axis options to set minimum and maximum values for the vertical axis of a sparkline or sparkline group. Setting these values helps you control the scale so that the relationship between values is shown in a more meaningful way. You can increase the height of the row that contains the sparkline to more dramatically emphasize the difference in data values if some are very small and some are very large.

You can also use the Plot Data Right-to-Left option to change the direction in which data is plotted in a sparkline or sparkline group. And if your worksheet contains empty cells or zero values, it's easy to determine how a sparkline will handle empty cells in a range (and also how the sparkline will be displayed) by using the Hidden and Empty Cell Settings dialog box.

There are even more tools to help you analyse, manage, and share workbook information with Excel 2010. Not only do the new analysis and visualisation tools help with tracking and highlighting important data trends, but you can now easily access your important data on the go from almost any Web browser or Smartphone. You can even upload your files to the Web and work simultaneously with others online. Whether you're producing financial reports or managing personal expenses, Excel 2010 gives you more efficiency and flexibility to accomplish your goals. Now that is a spark of genius.

Author is a freelance copywriter. For more information on excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2065-how-spark-up-excel-workbook.html