Excel Geek Express—otherwise known as Quick Analysis
Guest blogger Emily Warn is co-owner of Two Pens, which provides blogging services and teaches business how to write content that grows a social media following.
My writer friends think I’m a geek. You can do what?! I can create a spreadsheet with book sales numbers that can be sorted by name and number. I know that is only the first rung of Excel Geek-dom, and unlike my compatriots, I aspire to climb into the ranks of those who know how to create snazzy charts and perform other formatting feats. Perhaps I’ll be able to brand myself as a writer who plays with numbers.
I don’t want to show off so much as make my data tell a story by presenting it visually. That way even my writer friends can see, and so analyze, which author is booting the others off the bestseller list.
Earning geek bragging rights just got easier with the new Quick Analysis feature in Excel 2013. It lets you transform your data into visual formats really easily.
How to find and view Quick Analysis
When you select a range of data, Quick Analysis appears in the lower-right corner of your data set:
Click it and you’ll see options for converting your data into visual forms: charts, tables, sparklines, and formatting. (Another option lets you calculate totals.)
Using the Chart Option
For this exercise, we’ll focus on using the Chart option and use a sample spreadsheet of book sales by 19th and early 20th century authors. (I chose forever deceased authors in the public domain to avoid copyright issues.) Here is the imaginary sales data for their books from 2009-2011:
I select all the data, click Quick Analysis, and then choose Charts:
There are lots of choices here. Which chart will best show the authors’ sales rank relative to another author’s for three different years? I hover over each type of chart to see how it presents my data. I settle on the Clustered Column chart because I like how it shows year-to-year sales data per author.
Obviously, in 2009 students were snoozing, but by 2011 the ancients had caught on-across the board. (A country of readers instead of tweeters?)
I click on the chart to add it to my spreadsheet. My choice does not irreversibly muck up my spreadsheet if I decide I don’t like it later. I can delete it, open Quick Analysis, and try again.
Recommended Charts
There’s another reason that I could choose the optimal chart so quickly: the options that Excel displays are based on the types of data in your spreadsheet. For example, you’ll notice that Quick Analysis didn’t recommend a pie chart. Why? There would be too many slices (authors) to create a meaningful picture, and that shape is difficult to relate to sales per year.
If you need more help choosing a chart, click More Charts. In the Recommended Charts box, it explains why you would use one chart over another.
Other Quick Analysis Options
The way you work with the other Quick Analysis options–formatting, tables, and sparklines–acts much the same way.
–Emily Warn