Excel allows you to refer to cells as just B6 or C17:D26 (for example), but ranges can also be given meaningful names like Market_Share. Here is a video to introduce named ranges and then using INDEX to access cells within a named range:
Creating graphs of data with the use of INDEX is quite useful. Here are some examples:
There are many kinds of graphs in Excel, and this page looks at a few of the most common types of graphs.
The first is just to do a scatterplot of all the data points. This allows us to “see” the variability of the data:
Similar to this is to graph each point as a column. This gives us a bit more sense of how “big” each value is:
Similar to a column chart (one column per data point) is a histogram. Instead of plotting each point, the graph shows the number of points in various ranges (for example, 1-10, 11-20, etc.). There is a built-in tool for creating histograms in the Data Analysis tool pack:
Histograms using the Data Analysis Tool Pack
Histograms can also be constructed using the Excel function FREQUENCY():
Histograms using the FREQUENCY() function
And the last graph type we will look at is an XY scatterplot:
There are a number of videos for using Excel for data and decision analysis. They are organized into small pages, and this page is a table of contents. If you don’t find what you need, let me know and I’ll either add a new video or make video easier to find. Thanks.
Descriptive Statistics using Excel
Named Ranges, INDEX(), and Simple Graphs using the INDEX() function
Analytic Solver Platform Add-in for Excel
We can use Excel to create descriptive statistics for a data set. Here are a few videos with some help on how to do some basic tasks.
The filter command is often a good place to start with a new dataset:
In the Analysis Toolpack, there is a way to quickly calculate the descriptive statistics for a dataset:
Analysis Toolpack Descriptive Statistics
Sometimes, the Analysis Toolpack is missing from the Data menu tab. If it is, here is the way to install it:
Descriptive statistics can also be computed using the built-in functions Excel provides:
This is a quick start for people new to Excel.
Here is a quick overview of the features of Excel, focusing on those we will use in the business school:
There are short-cut keys for navigating around Excel:
Referring to cells in Excel has some conventions that are useful to understand: