How Excel 2013 can make your data beautiful and easy to understand
There are hundreds of fine business reasons to invest in Office 365, but the one product that could make the biggest difference to your staff on a daily basis is Excel 2013. For an overview of why read our introduction to Excel 2013, but for this guide we wanted to focus on data presentation.
If you've struggled in the past to create clear and readable tables or charts that pop from the page, Excel's new presentation features will be exactly what you need to make your data more beautiful and easy to understand.
In any large table of data there's a good chance you have a column or two of complex data that, if you could start over, would probably have made more sense as multiple columns. Say, for example, you have a single column containing your customers' full names, but you'd like to be able to sort and filter it by either first name or surname. With the new Flash Fill feature, it takes no time at all to split your complex data into simpler and easier to use columns.
Just create a new blank column next to your existing customer names column, and type in the top customer's first name; after the second entry you'll see that Excel quickly works out what you're attempting to do and begins suggesting the first names for the whole column. You can accept the changes with a simple press of the Enter key.
Then create another column and do the same for the customer surnames, and in less than a minute your first names and surnames will be separated out into separate columns, with Excel 2013 having done all of the legwork.
Excel is also able to spot the patterns in more complicated data, so take a look at the data you handle every day and see if you can take advantage of Flash Fill.
Say you have a large table of data and you need a quick summary of its contents. Here, features such as conditional formatting have long been a useful way to visually separate out and display data in a table - but they haven't always been as simple as they could be to understand and apply. The new Quick Analysis tool changes that.
To use it, you simply select the range of cells you'd like analysed, then click on the Quick Analysis button that appears in the bottom-right of the selection. From here you have several options. You can easily create a chart from the data, add running totals or averages, or sort and filter the data as required.
Or you can quickly and directly apply conditional formatting, with presets ranging from data bars and colour schemes, to icon sets and custom formulae. Much of this customisation existed in previous versions of Excel, but moving it all into a single quick menu makes manipulating your data as simple as a couple of clicks.
Possibly the most interesting option, though, is to insert sparklines. These are mini-graphs that can sit inside a single cell and show an at-a-glance analysis of trends in the data - perfect for summarising a complex point to a busy audience in a simple way.
Whether line graphs, bar charts or win/loss-style visualisations, sparklines mean your audience only needs to look at that one single cell to understand a range of data being displayed. This is a great way to express a complicated set of underlying data in a simple way to an audience that doesn't need to know the exact values - say a strategy meeting where you want to show the sales trends in a number of different regions.
If you give it a selection of raw data, Excel 2013 will make an educated guess as to which kind of graph is likely to be most suitable. It will present you with its recommendations, complete with a live preview of how the resulting chart would look, and if you agree with the choice the work is done for you.
If you don't agree, or need to make some changes, you can browse through the other options and see how the look of the chart changes on the fly. You can even include animations in your charts, to clarify changes in the data.
When your chart is sitting on the page and selected, three buttons appear in its top-right corner. With these you can quickly add or alter the axis titles and settings, play with the new richer and refreshable data labels, and also customise the look of the chart. If you need more control than that, the ribbon interface for charts now has greatly simplified Design and Format tabs with plenty of options.
As with many objects in Excel 2013, when a chart is selected a task pane automatically appears on the right of the window. This has all sorts of customisation tools, including changing the width and style of data lines, or the colour and labelling of data bars and pie charts. It also offers the ability to connect data labels to data points using leader lines - a visual tool that can instantly clarify a complicated graph. As with all customisation options, you'll see the effects immediately on your live graph.