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. You can read this article Analyze your data instantly to learn more or just fool around. That's how geeks like me do it.

--Emily Warn

• I think the Quick Analysis tool is great, and that it will be useful in many circumstances. Nevertheless, it might be handy to turn it off as situations warrant. In Excel 2010, for example, you can turn off the Quick Formatting tool by going into Options from the Backstage view and disabling it. After that, you have to right-click a cell to make the Quick Formatting tool show up. I'm pretty excited about Excel 2013. I downloaded it from my office 365 account this week. I'm going to enjoy seeing what I can do with it. By the way, I am also a writer and an Excel geek. :)

• Save the Pies for Dessert

www.perceptualedge.com/articles/08-21-07.pdf

• I have two tabs:  Tab1 (E12) I want to automatically populate from the last entered cell in tab2.  Tab2 is an incremental worksheet where a text value is selected from a drop list D2:D111 (supplier name) & I want to insert from the last selected cell.  Please give me a formula or a way to do this, as I have been scratching my head & not succeeding.

Example below;  Bulldog Abrasives must be automatically pulled from tab 2 through to tab1 as it is selected from a drop list.

Thanks Rob

Tab1:

SUPPLIER CODE: SUAB13C24 NAME: Bulldog Abrasives & Auxiliaries

Tab2:

# Order Number Supplier Code Supplier Name

1 ORD244131 SUKI13A50  F.I.T (Furniture Installation Techniques)

2 ORD244132 SUAL13C32 Conways Randburg

3 ORD244133 SUPA13A143 Top Tapes and Packaging

4 ORD244134 SUGL13C60 Glass Partners Gauteng (Pty) Ltd.

5 ORD244135 SUGL13A107 Ophirton glass works

6 ORD244136 SUBO13A19 Boschetto Timbers

7 ORD244137 SUAL13C32 Conways Randburg

8 ORD244138 SUBO13C15 Board king

9 ORD254139 SUPA13C118 Robot paint

10 ORD2541310 SUAB13C24 Bulldog Abrasives & Auxiliaries

• Congratulations for your Excel Geek-dom, Emily (as you so charmingly put it). Being able to promote your adeptness with Excel is an impressive and highly-valued skill, indeed. Especially for someone skilled in content creation, to dip into the more analytic side of things. I also write and have a more philosophic side, but now owning a <a href="www.mydepartmentplan.com

">business budgeting software</a> company, and having worked with spreadsheets and financial data for years in the corporate world, I find myself more and more often doing these types of "geeky" tasks as you put it. With budgeting, you just have to be skilled with all this arranging. Thankfully, Excel and other products make it possible to organize and analyze data in the way you want to.