Get An Instant Statistical Summary Of Any Selected Range in Excel

The past few weeks I've been leading an Excel VBA boot camp for the company where I work. In our last session, I showed how easy it is to create subroutines that can be available to you in any workbook you open.

In this post, I'll show you how to create a macro that will allow you to select a range in any workbook, hit Ctrl + Shift + S, and get an instant statistical summary of the data plus a line graph to visualize the data.

Let's get started:

First, you'll want to open a new workbook in Excel. If the Developer tab is not available in your ribbon, you will want to add it.

On the Developer tab, click "Record Macro".

Image 1. Select "Record Macro"

After you that you will be presented with an options screen. You need to give your macro a name. Here, I have chosen to call mine "Statistical_Summary". Then, set the shortcut key you want to use to access your macro in any workbook you open. I chose Ctrl + Shift + S, but you can choose whatever combination is easy for you to remember. The one part of this that is not up to your preference is that you need to select "Personal Macro Workbook" on the "Store macro in:" combobox. This macro will only work on every workbook you open if you select your Personal workbook. Then, type in the description of your macro, as seen in Image 2.

Image 2. Set the "Record Macro" Options

Once you have selected "OK", Excel will begin recording your actions within the workbook and converting that into code. But, since we are going to do the code part ourselves, just move your cursor toward the bottom left corner of the screen and click on the stop button (it's just a little gray square, cf. Image 3.)

Image 3. Stop Button

If you didn't already have a PERSONAL.XLSB workbook set up on your computer, you do now. On the Developer tab, in the Code group, select "Visual Basic" as seen in Image 4.

Image 4. Select "Visual Basic"

You will now see the VBA editor, and on the left side will be the Project Explorer window as seen in Image 5. Select Module 1 (assuming you have never worked with the personal workbook before; otherwise, your recorded macro may be in another module).

Image 5. Select Module 1.

After you select the appropriate module, you will see the code window in the center of the screen. You should see something like Image 6.

Image 6. Code Editing Window.

Sub is short for subroutine. After that you will see the name of your macro, the description you typed in earlier, and the phrase "End Sub", indicating the end of the macro. If you have anything that isn't colored green between the title of your macro and the End Sub command, you will need to remove it. 

Now type in (or copy and paste) the following code:

Dim min As Double
Dim max As Double
Dim avg As Double
Dim stDev As Double

min = Application.WorksheetFunction.min(Selection)
max = Application.WorksheetFunction.max(Selection)
avg = Application.WorksheetFunction.Average(Selection)
stDev = Application.WorksheetFunction.stDev(Selection)

'Message box with the statistical data
MsgBox "Min: " & min & vbNewLine & _
"Max: " & max & vbNewLine & _
"Avg: " & avg & vbNewLine & _
"StDev: " & stDev, _
VbMsgBoxStyle.vbOKOnly, _
"Statistical Summary"

'Create the line graph
Dim cht As Object
Set cht = ActiveSheet.Shapes.AddChart2
cht.Chart.SetSourceData Source:=Selection
cht.Chart.ChartType = xlXYScatterLines

   

You should end up with something that looks like Image 7.

Image 7. The Completed Macro.

Explaining exactly how the code above works is outside the scope of this article, but if you are new to VBA, just Google any of the parts you don't understand. It's very easy to learn about VBA. 

Basically what this does is it finds the minimum, maximum, average and standard deviation of the values within your selected range and it presents those statistical values to you in a message box. It also creates a line graph of the values in your selected range. 

Try it out now. If you followed all the instructions exactly, you should be able to select a range in any Excel workbook, press the keyboard shortcut keys that you selected, and then see a message box that looks like Image 8. 

Image 8. Message Box with Selection Stats

You may notice that the Max, Avg and StDev figures in my example look strange.That's because I also selected some dates, which, when converted to their integer value, are quite large. Just know that if you select dates, the macro will return some odd values in the statistical analysis. If you have data arranged as I do in the example, and you want dates as your x-axis labels, go ahead and select the dates column, enter your shortcut key, click "OK" on the message box, and then you will have a well-labeled graph. Repeat the process, this time not selecting the dates column, and you will have good data for your statistical summary in the message box. Doing this is how I created the image at the top of this post. Of course, you could handle these kinds of situations in your code, but I wanted to keep this post simple.

Once you click "OK", the line graph will be created, which should look something like Image 9.

Image 9. Line Graph of the Selection Values

And there you go! You can now use that keyboard shortcut on any range in any Excel workbook and it will do the same thing. Enjoy.

Did you have any issues? Let me know in the comments section and I'll try to help you resolve them.