Analyzing Data with Tables and Charts in Microsoft Excel 2013

• Sorting out your data analysis options

• Using tables to organize and analyze data

• Sorting and filtering data

• Using conditional formatting to highlight cells based on their content

• Using sparklines to visualize trends within a range

• Turning data into charts

EXCEL is no one-trick pony. Yes, the program works wonders with number-crunching tasks, but its rows and columns are also tailor-made for managing data that goes beyond basic bean counting. With minimal effort, you can keep address lists and membership rosters, track temperatures and rainfall, monitor stock prices, and record your performance in whatever sport or hobby you happen to fancy.

In this chapter, we look at the many options you have for entering, storing, sorting, filtering, cross-tabulating, and summarizing that data.

We also explain how to highlight trends and patterns in a sea of gray data to make it more interesting. You can use conditional formatting to add colors and custom text treatments, and you can make at-a-glance analysis easier by inserting tiny trend lines and markers called sparklines.

When that’s not enough to tell a story, you can turn a collection of data into an elegant, information-based graphic and let it do the talking. In this chapter, we look at Excel’s extraordinarily versatile charting engine and explain how to communicate a situation or a series of events in a single visual impression, with only a few well-chosen words required.

Building a visually compelling, information-rich chart from a series of numbers and dates is part science, part art. The science involves recognizing which series of data on a worksheet represent the patterns you’re trying to describe. The art is in arranging and fine-tuning the colors, shading, shapes, labels, and other pieces of your chart so that they tell the story most effectively.

Sorting out your data analysis options

The single most important new feature of Excel 2013 is the Quick Analysis tool, which puts formatting, charting, tables, and other options in an easy-to-access place. You can still create charts, insert tables, and add totals manually, but this tool dramatically simplifies the process.

To get started, select a range (at least two cells containing data) and click the Quick Analysis tool that appears in the lower-right corner of the selection. (You can also press Ctrl+Q, or right-click and click Quick Analysis on the shortcut menu. If you choose the latter option, you can select a single cell and Excel will expand the selection to include the current region.)

Figure 13-1 shows the Quick Analysis tool in action. Each of the five headings at the top of the box leads to a selection of options that vary slightly depending on the selection.

Figure 13-1 The Quick Analysis tool consolidates five common options in one place and offers live previews of their effects.

To use the Quick Analysis tool, choose a category and then move your mouse pointer over any of the options available beneath the headings. When you let the pointer hover over an option, the selection changes to preview the effect of that option. If you like what you see, click to apply the selected option; otherwise, move the mouse pointer to another option (or click a different category).

The following list briefly describes each of your options and what you should and shouldn’t expect from each one using Quick Analysis. We provide in-depth details about these analytical options in the remainder of this chapter.

• Formatting The options available here depend on whether your selection contains only text or whether it also includes numbers. For all-text ranges, the options allow you to identify duplicates, unique values, or entries that include a specific text string. If you’ve included even a single number, you’ll see the choices shown earlier in Figure 13-1, which allow you to add data bars, color scales, and icons or highlight specific values.

• Charts Excel offers a selection of one-click charts based on the type of selection you make. The list of available chart types is determined by whether you’ve chosen a single column of values or multiple columns with labels. The preview chart appears above the Quick Analysis tool, as shown in this example, based on a selection that includes a column of labels and a column of data.

  

• Totals Assuming you’ve selected a range that includes numbers, you can add automatically calculated totals in the row beneath your selection, or in the column to its right. For numbers, the list of options scrolls to the right, with row-wise choices listed first, followed by their column-wise equivalents.

  

• Tables This category is a bit of a catch-all; it includes regular tables and PivotTables (we cover the former in much more detail shortly, and the latter in Chapter 14). What you see beneath the Tables heading will always include the Table option and, depending on the arrangement of data, one or more buttons allowing you to preview various PivotTable layouts. Note that in this example, because the Excel window extends to the bottom of the screen, the Quick Analysis tool appears above its launcher.

  

• Sparklines These clever little analytical elements are mini-graphs that appear in a single cell to summarize the trend in a row of data. They’re most useful when you want an at-a-glance comparison of a series of numbers in multiple dimensions. We’ll discuss sparklines in more detail shortly.

The Quick Analysis tool offers a useful starting point, but it’s far from perfect. For anything but the simplest tasks, you’ll need to fine-tune the settings and formatting for tables and charts.