Quick Analysis toolset - Microsoft Excel Tutorial

- [Instructor] Alright, for this pro tip, I want to talk about how we can instantly explore our data using something called Quick Analysis tools. This is a one-star analytics tip. Very simple to use but a really interesting tool to play with. So most recent versions of Excel, I believe 2013 and onward, include an option called Quick Analysis tools, and all you need to do is select a range of cells kind of like this one. You'll see a pop-up appear in the bottom right. Looks like a lightning bolt with a bar chart underneath it, and when you click that option or press Control + Q, you'll access a Quick Analysis tool menu, and within this menu, you'll find all sorts of analytics tools including conditional formats, chart types, calculated rows and columns. You can convert this range to a table. You can add spark lines. All sorts of very, very interesting and powerful analytics tools all consolidated into this single menu here for quick access, and what that allows us to do is take a raw, basic range of cells like you see at the top of the slide and convert it into something like this, either color scale or adding a calculated column with the average values or inserting a clustered column chart with the click of a button. Anyone who knows me and knows my courses knows that generally speaking, I try to avoid these shortcut tools like this because I guess I'm oldschool. I like to go through the traditional menus, but I'm actually quite impressed by how smooth and how convenient this Quick Analysis tool menu is, and as a result, I've started to use it quite a bit more myself. Now one note here. You won't always see all of these options because those options and those tools may require the data to be in a certain format. So if your selected data isn't compatible with a certain type of tool like a sparkline or pivot, for instance, you may not see that option in your Quick Analysis toolbar. So in most common use cases here, obviously just quickly exploring a variety of popular data analysis tools without having to manually navigate through different ribbon options. And also I've found it's a great way to quickly add calculated rows or columns for things like sums, counts, averages, totals without having to actually type in a single formula. So let's jump into Excel. Got a good range of data that we can use to practice some of these Quick Analysis tools. Alright, so if you'd like to follow along, go ahead and open up your pro tip workbook, head to your table of contents, and we're going to scroll all the way to the right to our analytics tips, and in this case, we want the Quick Analysis tools demo. Go ahead and link straight out to that sheet, and what you'll see is a simple range of cells here. We're looking at movie IMDB scores averaged out by year, 2011 through 2015, and by genre here in column A. Now this is a great sample bit of data to use to practice these Quick Analysis tools because it's two-dimensional. We've got a nice range or table of values here that we can use for value-based conditional formatting, things like color scales and icon sets. We've got time series or trending here with five years of data so that we can show things like running totals or line or column charts. We've got opportunities to add calculated rows in row 13 or a calculated column or sparklines here in column G. So it's a good, versatile data set to really see a wide range of options for these tools. So let's go ahead and select A2 all the way down to F12. Note that I'm including the headers as well, and as you can see, if you hover over that range that you've selected, you'll get that Quick Analysis popup right here in the lower right. Go ahead and click or press Control + Q, and here we are in the Quick Analysis menu, and you've got formatting options, chart options, totals which are calculated rows and columns. You can add tables or pivots, sparklines. So really the most popular and powerful analytics tools packed here into a single convenient menu. Now perhaps my favorite feature is the fact that you don't even have to commit to any option in order to see the output. So you get this quick preview in your selection as you simply hover over these different options, and if you'd like to make customizations which is something that I often do like to do, as you click through these tools, you'll actually access the formatting dialog box where you can customize exactly what you want here, and then press OK. And just like any other change, press Control + Z to undo, and you can jump right back into those tools. So the formatting options really speak for themselves. Very simple. For charts, basically this just inserts a new chart as a workbook object. Got different common options here at the front of our list. In the total section, we can add calculated rows which are shown as this blue shaded row here in the icon, or if we continue to the right, we can add calculated columns, and this uses your most popular aggregation or summarization modes. A sum, an average, a count, percent of total, and a running total. And again, very easy to add without typing a single formula there. We can convert our range to a table, insert a blank pivot table straight from here, or we can add sparklines, and Excel knows right off the bat that the most appropriate place to drop sparklines in this case would be right there in column G. So again, I've been very, very impressed by these tools and by how smooth and convenient they really are. So I'd encourage you to either work with the data shown here or drop in a sample of your own and just explore how these Quick Analysis tools can help you analyze and understand your data.