Basic Introduction to Spreadsheets

Cells, Sheets, and Workbooks

Spreadsheet software allows for interactive data entry and manipulation. Launch a spreadsheet application, and create a new blank workbook. This will contain a single spreadsheet, but you can add many spreadsheets to a single workbook. A workbook collects together several spreadsheets and provides facilities for these sheets to interact.

Visually, a spreadsheet is a rectangular collection of cells, which may hold data or formulas (or be empty). Each cell in a spreadsheet has an address, which combines its column letter with its row number. For example, A1 is the address of the left-most, top-most cell. By means of a formula that uses these addresses, the value in a cell may depend on the values in other cells. For example, a cell may contain a formula that computes the mean of the values in some other cells.

Entering Values

To enter a value in a cell, select the cell and type in the value. The information you enter will be stored as a number, a string, or a boolean value (True or False). A cell may display a number in various ways, e.g. formatted as a date, but numbers are always stored in the same way. (Specifically, as double-precision floating point values.)

Entering a Formula

Instead of a value, a cell may hold a formula. To begin entering a formula in an empty cell, select it and enter an equals sign (\(=\)). The calculation may be entirely numeric—for example, =2+2. However, a formula typically refers to the values of other cells. Refer to a cell by using the row and column of that cell. Use standard operators for addition, subtraction, multiplication, and division. Use ^ for exponentiation.

Local Variables with LET

In Office 365, the LET command can give names to specified values. This can make formulas easier to read by using these names to produce the results. To use LET, introduce a sequence of variable names and values, and finally enter the expression using the names. The variable names are local: they have meaning only inside the LET command. They do not have meaning elsewhere in the spreadsheet.

Example: =LET(x,5,3+2*x+x^2)

Example: =LET(x,A1,3+2*x+x^2)

Example: =LET(x,A1,y,A2,3*y^2+2*x*y+x^2)

Ranges

A rectangular group of adjacent cells is known as a range of cells. Two cell addresses separated by a colon define a range of cells (e.g. A1:D4). The colon separates the top left cell and the bottom right cell of the range.

Copying Formula

Exercise: In cell B1 enter a name for a transformed sequence, say myseq2. Select cell B2 and enter =LET(x,A2,x*x). Copy cell B2, select cells B2 through B1, and paste. This creates squared values for all of the original sequence values.

Exercise: Annualizing Data

Entering a LAMBDA Formula

Video: How to use LAMBDA to make Custom Functions in Excel

https://www.youtube.com/watch?v=yDNX7V0eZ8U

Documentation:

https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/

Run-Sequence Line Chart

Run-sequence charts are used with ordered univariate data. The data is considered to be a sequence: order matters. The values are therefore plotted in order.

Dimensionality:

2D

Points:

\((i, x_i)\) where \(i\) is the index and \(x_i\) is the i-th data value

Horizontal axis

index values

Vertical axis

data units

If the points are plotted but not joined, we often call the chart a run-sequence plot. If the points are joined, we often call the chart a run-sequence line chart.

A run-sequence chart may allow easy detection of behavior regularities or changes in the behavior of the sequence

• cyclicity

• autocorrelation

• shifts in level

• shifts in dispersion

Resource: http://www.itl.nist.gov/div898/handbook/eda/section3/runseqpl.htm

Time-Series Line Chart

Time-series charts are also used with ordered univariate data, when they are indexed by time. Time-series data is inherently sequenced: order matters and is determined by the time index. The values are therefore typically plotted in order.

Dimensionality:

2D

Points:

\((t, x[t])\) where \(t\) is the time and \(x_t\) is the data value at time \(t\).

Horizontal axis

time units (e.g., date-time values)

Vertical axis

data units

If the points are plotted but not joined, we often call the chart a time-series plot. If the points are joined, we may call the chart a time-series line chart. Since the data for a time-series chart typically includes time as well the values of the variable of interest, we use a spreadsheet scatter plot for the chart.

Real GDP Time-Series Chart

Univariate Descriptive Statistics

Excel includes a tool for descriptive statistics in the Analysis ToolPak add-in. Pick Data » Data Analysis » Descriptive Statistics. Input the range for your data and whether there are labels in the first row. Check Summary Statistics and click OK. For each data column, you will see the following values.

Mean (=AVERAGE(range))

A measure of central tendency; the arithmetic average of the values.

\begin{equation*} \bar{x} = \frac{1}{N} \sum_{n=1}^{N} x_n \end{equation*}

Sample Variance (=VAR.S(range))

A measure of dispersion around the mean; includes the Bessel correction.

\begin{equation*} s^2=\frac{1}{N-1}\sum_{n=1}^{N} (x_n - \bar{x})^2 \end{equation*}

Sample Standard Deviation (=STDEV.S(range))

Another measure of dispersion around the mean; the square root of the sample variance.

\(s = \sqrt{s^2}\)

Standard Error (of the mean)

=STDEV.S(range) / SQRT(COUNT(range)) A measure of the dispersion of the sampling distribution of the mean.

Median (=MEDIAN(range))

The middle-most value after sorting the data. (Calculated as the average of the two middle-most values, if there are en even number of data points.)

Mode (MODE.SNGL(range))

The most frequently occuring value, if one exists.

Kurtosis (=KURT(range))

A measure of the likelihood of extreme values. Excel reports excess kurtosis (relative to the normal distribution). Positive kurtosis means the data contain has more extreme values than a normal distribution. Negative kurtosis means the data contain fewer extreme values than a normal distribution.

Skewness (=SKEW(range))

Measures asymmetry around the mean. Positive skewness means that tail is longer and/or fatter to the right. Negative skewness means that tail is longer and/or fatter to the left.

Minimum (=MIN(range))

The minimum value of the numbers in the range.

Maximum (=MAX(range))

The maximum value of the numbers in the range.

Quartile (=QUARTILE(range, n))

The \(n\)-th quartile of the numbers in the range.

Range (=MAX(range) - MIN(range))

The range of values, found by subtracting the minimum value from the maximum value.

Sum (=SUM(range))

The sum of the values.

Count (=COUNT(range))

Count of the number of observations in this dataset.

Not reported:

Sample Variance (without Bessel correction)

=VAR.P(range) A measure of dispersion around the mean:

\begin{equation*} {\hat\sigma}^2=\frac{1}{N}\sum_{n=1}^{N} (x_n - \bar{x})^2 \end{equation*}

Sample Standard Deviation (without Bessel correction)

=STDEV.P(range) A measure of dispersion around the mean: \({\hat\sigma} = \sqrt{{\hat\sigma}^2}\)

Five-Number Summary

On first encounter with a univariate data set, researchers commonly consider its five-number summary. The five numbers are the minimum, first quartile, median, third quartile, and maximum. Excel does not build in a five-number summary, but the functions listed above can easily produce one. In fact, since these five numbers are quartiles 0, 1, 2, 3, 4 of the data, the QUARTILE function is adequate on its own.

Box and Whisker Charts

A box and whisker chart provides a useful summary of the distribution of a variable. The box shows bounds the interquartile range, with an interior line for the median, while the whiskers show variation beyond the interquaritle range. (See the Basic Statistics supplement for details.) to produce and box and whisker chart in Excel, select a column of data and then click Insert » Insert Statistic Chart » Box and Whisker. See the Microsoft documentation for more details.

Multivariate Descriptive Statistics

Categorical Bar Chart: String vs Numerical Categories

Suppose you have the following data in a spreadsheet.

In the spreadsheet, select this data and then insert a 2d column chart, which is also called a vertical-bar chart. The first column will be treated as the categories and you will get a nice vertical-bar chart for you categorical data. Note that the order of the categories is determined by their order in our data set. They are not considered to have a separate sort order.

Numerical Categories

If you try to use numbers for your category names, it may create extra work. Suppose you have the following data:

If you select this data and insert a 2d column (vertical bar) chart, a spreadsheet will generally treat the first column as numerical data, rather than as category names. In this case, you may get a “clustered” vertical bar chart. If the first-column numbers are actually your categories (e.g., bin edges), you will need to choose a different graph style.

Excel:

Choose "more column charts" and find the right type.

Google Sheets:

Check "Use column A as labels"

Data-Based Bar Chart

Create a bar chart for the components of GDP. Then remake the chart in percentage terms.

Basic Concepts

Recall that to iterate is to repeat an action. Function iteration is the repeated application of a function to produce a sequence of values. (See the Glossary for details.)

Exercise: Exponential Population Projection via Function Iteration

Look up the world population. Assuming a growth rate of 1% per year, produce annual projections for the next 50 years. Create a simple line chart of your projections.

Exercise: Logistic Population Projection via Function Iteration

Look up the world population. Assume population growth depend on the current population, according to:

where \(p\) is the current population relative to the carrying capacity of the planet. Assume a carrying capcity of 11.5 billion. Produce annual projections for the next 50 years. Create a simple line chart of your projections.

Structured Reference Tables: Some Basics

Since Excel 2007, Excel provides a structured data type within a sheet, known as a structured reference table. This is sometimes just called a “table”. (In ancient times, is was called a “list”.) A table is rectangular and generally has column labels.

Tables provide convenient data handling. As a simple example, a formula entered in a column is treated as an array formula and immediately autofilled. Tables can also be named, allowing structured references to their data. For example, formulas can simply refer to column names (with a bracket notation).

Documentation

https://support.office.com/en-us/article/Create-or-delete-an-Excel-table-in-a-worksheet-867f3add-0522-493c-94ab-b1b9933f46ca

Helpful Video:

https://www.youtube.com/watch?v=tTYQrk3hSYo

Creating a Table

• Select a rectangular array of cells that has a header row of labels and then the data below.

• From the menu, pick Insert » Table, check the “My table has headers” box, and click OK.

Name a Table

• Select any cell in the table.

• From the menus pick Design » Table Name.

• Enter a new table name.

Append a Column to a Table

Approach 1:

• Select the cell beside your table’s column headers.

• Enter a name for your new column header.

• The new column is automatically added to your table.

Approach 2:

• Select any cell in the last column of your table.

• Pick from the menus, Home » Cells » Insert » Insert Table Columns to Right

After you create a new column, you will usually want to change its name.

Table Example: Refer to Column by Name

Table columns can be accessed by name. E.g., if the table is named data and the column is names c1, you can refer to the column by name.

data[c1]

(When you are working within a single table, you can omit the table name.)

This is called a structured reference.

Table Example: Function Plot

We will illustrate the use of tables by doing a function plot of \(\sin(x)\). Note that the SIN function takes one argument: an angle measure in radians. We will create points in \([0..2\pi]\), and evaluate the function at each of these points.

1. Open a blank workbook.

2. Use the Name Box to select A1:B102

3. Pick Insert » Table or click ctrl+t, check “My table has headers”, and click OK.

4. Change the headers to x and sinx.

5. In A2 enter the formula =2*PI()*(ROW()-2)/100; note how the rest of the column is filled in, creating the domain values for our function.

6. In B2, enter the formula =SIN([@x]); note how the rest of the column is filled in, creating the range values for our function.

Note how we were able to refer to another column in the table by placing its header in brackets. We are now ready to create a chart from the table data.

1. Click anywhere in your table.

2. Insert a scatter chart (Insert » Charts » Insert Scatter).

3. Be careful: in early versions of Excel 2016, moving this chart to a new sheet can cause Excel to hang.

Documentation

https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

Edit the Chart

Make your charts look good.

1. Select the graph and move it up to the top of the worksheet near the list of values. (Or move it to its own sheet.)

2. Drag the boundaries to get a pleasing aspect ratio.

3. Get rid of the grid unless your have a need for it.

4. Choose a background color. (Do not be dramatic.)

Table Example: Rectangular Data

• Import rectangular data into a spreadsheet.

• Select the data

• press ctrl-t to open the table dialogue; create the table

• rename the table (Design » Table Name)

Table Alternatives

Excel tables are great, and they are missing from many other spreadsheets. But they are not irreplaceable. Think of a table as a of mini sheet with automatic named ranges via structured references. The handling of formulas is also just a convenience; it is largely equivalent to array formulas.

Sheets: named ranges

https://support.google.com/docs/answer/63175

Named Ranges

Most spreadsheets support named ranges, which allow easy array-oriented computations. A named range is just what is sounds like: a range of cells that can be referenced by name.

Example:

• choose from the menus Formulas > Define Name and fill out the dialog

• tap the OK button to make a named range from the selected cells

Note: The menu location for named-range creation is application specific. (E.g., Data » Named Ranges ... in Sheets.)

Named Ranges Example: Function Plot

Example:

We will create 101 evenly-spaced values in \([-1,1]\) for our domain (\(x\) values) and then plot a function over this domain.

1. Open a blank workbook and enter x in cell A1 and y in B1.

2. Create the \(x\)-values.

   1. Tap in the Name Box (to the left of the Formula Bar) and enter A2:A102; the range of cells that you want to fill.

   2. Type =(ROW()-52)/50 in the Formula Bar, and then ctrl-shift-enter This copies the formula to all cells; and gives you the domain values for your function.

3. Make a named range for your 101 new cells; name it x.

4. Select B2:B102, type the formula =x*x, and then press ctrl+shift+enter; this gives you the range values for your function

5. select a location for your scatter plot, and insert a scatter plot. Right click on the plot body, and choose Select Data, and set the range to x,y. (European users may need a semicolon rather than comma for the union operator.)

6. Edit the chart as before.

Array Formulas

We can use array formulas to fill spreadsheet ranges with an entire array at one go. An array formula that fills multiple cells is called a multi-cell formula; all the filled cells depend on the single formula.

Documentation:

https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Examples:

http://www.cpearson.com/excel/arrayformulas.aspx

Simplest Array Formula Example

In a new sheet, select A1:A3 and type the formula:

={1;2;3}

To enter this as an array formula, use Ctrl+Shift+Enter. (For this reason, array formulas are sometimes called CSE formulas.) Note that we needed to select the proper output area for the result.

The value in braces is called an array constant. We can do elementwise arithmetic operations with array constants. In a new sheet, select A1:A3 and type the following formula, entering it with Ctrl+Shift+Enter.

={1;2;3}*{4;5;6}

Note: Google sheets has the ARRAYFORMULA command: (It also supports Ctrl+Shift+Enter, like Excel.)

ARRAYFORMULA

https://support.google.com/docs/answer/3093275

Array Formulas with Functions

Many functions can be use in array formulas to produce elementwise results. Just select the proper output range, and enter the formula with Ctrl+Shift+Enter.

Example:

In a new sheet, select A1:A3. Then enter the following array formula. (Remember: Ctrl+Shift+Enter!)

=ROW()

You will see the row numbers entered in column A. Next select B1:B3 and enter the following array formula. (Remember: Ctrl+Shift+Enter!)

=SQRT(A1:A3)

You will see the square roots entered in column B.

Core Random Number Generators

The core random number generators are RAND and RANDBETWEEN.

function:

RAND()

ouput:

a random decimal number in \([0,1)\).

documentation:

https://support.office.com/en-us/article/RAND-function-4cbfa695-8869-4788-8d90-021ea9f5be73

function:

RANDBETWEEN(min,max)

ouput:

a random integer between min and max, inclusive.

documentation:

https://support.office.com/en-us/article/RANDBETWEEN-function-4cc7f0d1-87dc-4eb7-987f-a469ab381685

Additional options are usually available (e.g., in Excel, through the Analysis ToolPak).

Random Decimal in \([x_\min,x_\max)\)

=xmin + (xmax-xmin)*RAND()

Recalculation

The random number functions are “volatile”: they are called each time the spreadsheet is recalculated.

Example:

• in a new worksheet, enter =RAND() in A1

• enter =RAND() in A2; note that the value in A1 changes

• press shift+F9 for force recalculation of the sheet; note that the values in A1 and A2 change

We can replace formulae with values.

• select A1:A2

• copy

• pick from menus Home » Paste » Values

Random Integer Data

Example:

• Start with a new worksheet.

• In A1, enter RANDBETWEEN(0,10), then select and copy (ctrl-c) the cell.

• Enter A2:A101 in the Name Box.

• Paste (ctrl-v) the copied formula into the cell.

You now have random data, but there is an underlying formula. This means that it will be recalculated every time your spreadsheet is recalculated. We usually do not want this dynamic recalculation. So select all the data, copy it (with ctrl+c), and then paste the values (using Paste Special).

Random Integer Data (Table Version)

Example:

• Start with a new worksheet.

• Enter data in the A1 cell.

• Enter A1:A101 in the Name Box.

• From the menus, pick Insert > Table, check the “My table has headers” box, and click OK.

• In A2, enter :code`=RANDBETWEEN(0,10)`.

Random Variates: Analysis ToolPak

The Analysis ToolPak (ATP) provides several additional distributions for sampling.

Example: sampling from the standard uniform distribution.

Preliminary: install the Analysis ToolPak: https://support.office.com/en-us/article/load-the-Analysis-ToolPak-305c260e-224f-4739-9777-2d86f1a5bd89

Example:

1. Choose Data Analysis from the Data tab

2. Pick Random Number Generation

3. Choose Binomial with paramters \(p=0.1\) and \(n=5\)

4. Set an Output Range determined by the number of variates you want

5. Click OK

Pivot Table Exercise

Pivot tables provide power functionality to regroup and aggregate your data. For example, enter the following lifetable data into a spreadsheet. (More data is available from the Social Security Administration.)

Select the entire table, pick Insert»PivotTable, and select the New worksheet option. Using the dialog that opens, drag Age to Rows and Sex to Columns. The totals are meaningless, so remove them as follows: right-click the table, select PivotTable Options, and in the Totals & Filters tab, uncheck the Grand Totals. The result is a quick and easy two-way table.

Pivot tables provide the basis for pivot charts. For example, select the table and pick Insert»PivotChart. Pick a line chart to get a nice plot, for each sex, of life expectancy for each age.

Simple video introduction:

https://www.youtube.com/watch?v=9NUjHBNWe9M

Value Counts

Create random integer data as above, except this time you must include a column header. Suppose the header is mydata. We want to determine the number of times each value occurs in the mydata column. Pivot tables make this easy, as long as our data has a column header.

• Select the data, including the header.

• Pick Insert » Pivot Table » OK; this should create a new worksheet with a pivot table.

• In the Fields dialog, check the mydata box so that this item appears in the Values area.

• Drag mydata to the Rows area, of the Fields dialog, so that it appears there too. Now your pivot table should show each value in the mydata column along with its sum.

• In the Value area, click on the arrow beside mydata and change the Value Field Settings to Count.

You now have a table of value counts.

Simple-Minded Frequency Chart

Make a table of value counts, as above. We are going to make a frequency chart from this table. This will be a vertical bar chart, which spreadsheets often call a “column chart”. There will be one column for each unique value, with that value's frequency as the column height.

Approach 1: Pivot Chart

• click anywhere in your pivot table

• pick Insert » Pivot Chart » Column to make what Excel calls a Clustered Column chart.

Approach 1: Column Chart

• Select the data in your pivot table.

• Pick Insert » Pivot Chart » Column to make what Excel calls a Clustered Column chart.

• Change the vertical axis bounds as needed.

We call this approach “simple-minded” because we do not worry about values that have zero frequency.

Frequency Chart Redux

A table of value counts supports a simple-minded frequency chart. This approach “simple-minded” because it does not worry about values that have zero frequency. To do a little better, display cells with zero counts. To illustrate, make a pivot table of counts for the data 1,2,3,5,3,2. Note that the value \(4\) does not occur in this data.

1. In your pivot table of counts, right-click on one of the the row labels and choose Grouping.

2. Enter 1,7,1 as the start, stop, and increment values for grouping, and click OK.

3. Right-click on one of the the row labels and choose Field Settings » Layout and Print. Check the Show items with no data box.

4. Right-click a pivot table cell and choose PivotTable Options. Fill in For empty cells show: with a \(0\).

5. Make a column chart (i.e., vertical bar chart) from the pivot table.

6. Click on the data labels on the horizontal axis, and uncheck out the useless categories.

The overextended range allows us to work around undesirable features of the default Excel display. (That is why we picked an end value of \(7\) instead of the more obvious value of \(5\).) Using the filter to manually unselect the out of range values produces a better display.

Pivot Tables and Ordinary Charts

Fortunately, you can make a regular chart from your pivot table.

1. Select an empty cell that is not immediately adjacent to your pivot table and insert a blank chart.

2. Right click on the chart and pick Select Data from the context menu.

3. Click the Add button, to add a new series. For a vertical bar (column) chart, select the y-axis values.

4. Once you select the data, you can edit the axis labels in the Select Data Source dialog. For a horizontal bar chart, set the data range to the range containing your labels.

3D Plots

Excel 2016 includes very weak 3D plotting capabilities, but it can do simple surface plots.

Grided Data

To create a surface plot, Excel 2016 needs a filled rectangular grid of data. In real data, we face the following problems:

• we usually have data records \((x,y,z)\) rather than gridded data

• there may be missing grid points

We can use a pivot table to turn a complete set of data into grid data.

• Select the \((x,y,z)\) data

• Insert a pivot table

• Drag the \(z\) data to the values

• Drag the \(x\) data to the rows

• Drag the \(y\) data to the columns

This gives us gridded data. Now we can:

• Select the data, including the row and column labels

• Insert a surface chart

Cumulative Sum

Consider a sequence of numbers. A cumulative sum is a running total of the sequence elements. For example, the values 1,2,3,4 have a running total of 1,3,6,10.

The cumulative sum sequence will have the same length as the original sequence.

Cumulative Sum (Running Total): Simplest

Oddly, spreadsheets often lack a function to produce a cumulative sum (also called a running total).

• Generate random integer data as described above. We will assume this is in A1:A100 and that column B is empty.

• Select cell B1 and enter the formula =SUM(A$1:A1). Note the use of both relative and absolute coordinates.

• Select and copy cell B1. Select B1:B100 and paste in the formula.

Exercise: make a run-sequence line chart of your cumulative sum.

Related video: https://www.youtube.com/watch?v=iWBl--aIhig

Cumulative Sum (Running Total): Better

The first method is the simplest approach and with a small data set it is fine. However, note that we repeatedly sum over the column of data, repeating a lot of the computational work already done. If you have a lot of data, the can cost time.

Here is a better approach, using the same random integer data. We assume column C is empty.

• in C1 enter =$A$1

• in C2 enter =$A2+C1

• copy the formula from B2 to the remaining rows of `B`

Comment: Pay careful attention to the distinction between absolute and relative cell references.

Related video: https://www.youtube.com/watch?v=iWBl--aIhig

Add a Column to a Table

Select a data cell in your table. Right click to get the context menu. Pick Insert » Table columns to the Right.

Cumulative Sum: Table

Assume a table with column x. Add a column to the table and enter the formula:

=SUM(INDEX([x],1):[@x])

We use INDEX to get the row and column in the spreadsheet. The at-sign (@) provides a shorthand for [[#THIS ROW],[x]]. Without this shorthand, we would have written:

=SUM(INDEX([x],1):[[#This Row],[x]])

The redundant summing is unfortunate, but there does not seem to be a better way to do partial sums with structured references.

Cumulative Moving Average (Running Average)

Using the same data as for our running total, just change the formula slightly: divide each cell result by Row().

Or if you have a header cell, so you data starts in row 2, divide each cell result by (Row() - 1).

Cumulative Proportion

To produce the cumulative proportion of data, first produce a cumulative sum. Then deflate the cumulative sum by the total.

This produces an increasing sequence of fractions with the same length as the original sequence and a final value of 1.0.

A cumulative proportion usually has most interest if the data is first sorted.

Sorting

Excel does not provide sorting functions. Instead, we sort a column in place. Often it is best not to manipulate the original data, so first copy it to a new column, which you will sort.

Note: the sorted column will not automatically update when the data column changes! If you want to do dynamic sorting, consider the LARGE function. http://excelbyjoe.com/using-excel-functions-to-dynamically-sort-data/

To sort a column:

1. Select the data, and then choose Data » Sort and sort smallest to largest

Lorenz Curve

We now think of our data as representing income or wealth. Each observation represents one population unit. (E.g., a household.) A Lorenz curve plots the cumulative proportion of the data against the cumulative proportion of the population.

• sort the data

• produce a cumulative proportion of the sorted data

• plot against \(i/N\), where \(i \in [1 .. N]\) indexes the population

Refinements:

• a Lorenz curve diagram looks best with an aspect ratio of 1

• a nice Lorenz curve includes the point \((0,0)\).

• usually we provide a 45 degree line as a reference line for the Lorenz curve; one way to produce this is with a second plot of either series against itself

Histogram Statistical Chart

We now define a related data-display concept: the frequency histogram. This categorizes data into mutually exclusive bins, and then displays the counts of the data points in each bin. The bins are usually equal-sized, non-overlapping intervals.

In earlier versions on Excel, a basic histogram bar chart had to be created from a categorical bar chart, where the categories are bins and the values are frequency counts. However, Excel 2016 added Histogram as a Statistical Chart. For produce a simple histogram as follows.

1. Select a column of data.

2. Pick Insert » Insert Statistical Chart » Histogram.

3. Right click on the horizontal axis and adjust the bins as desired.

Histogram: The Old Way

The following discussion of how to produce a histogram prior to Excel 2016 is included only for its use in illustrating other spreadsheet features.

=COUNTIF($A$1:$A$1000,$B1)

=NORM.INV(RAND(),0.0,0.25)

Empirical Distribution

The empirical distribution function based on the data is a distribition function which has jumps at observed values \(X_n\) of size equal to 1/n multiplied by the number of sample points equal to that value.

Given \(t\), we divide the \(X_n\) into two groups: those to the left of t (or equal), and those to the right of t. We count up those in the first group to get \(\hat{F}(t)\). Equivalently, we assign a 1 to observations in the first group, and a 0 to observations in the second group, and then add up these values. (We can formalize this with an indicator function, I[X_n ≤ t].)

Let us construct an empirical distribution function based some univariate data, \(X_1 ,\dots,X_N\).

Suppose our data sample is independently drawn from a single distribution, F. Then our data is a sequence of independent random variables which are all ‘identically distributed’. Given t, the probability of falling in the first group is each time

The value \(N \hat{F}(t)\) is the sum of N iid binary (‘coin-toss’) random variables, all with the same probability p = P(X i ≤ t) = F(t) of being 1.

Thus \(N \hat{F}(t) ~ Binom(N, F(t))\).

The Law of Large Numbers implies that \(\hat{F}(t) \rightarrow F(t)\), with probability close to 1 when n gets large.

Plots of empirical CDFs from data overlaid with theoretical d.f.’s are among the best simple ways of checking visually that the data really fit the theoretical CDF well.

Cobweb Chart

Cobweb charts are also called step charts. Spreadsheets are not the easiest places to produce cobweb charts. You may find this description helpful:

http://archives.math.utk.edu/ICTCM/VOL17/C005/paper.pdf

Tornado Charts

Tornado charts are commonly produced as horizontal bar charts. See https://excelchamps.com/blog/tornado-chart/ for an example. Also see https://vimeo.com/34389151

Heat Map

Unfortunately, Excel does not include a heat-map chart (as of 2016). A heat map uses color to enable visualization of a retangular array of data. However, data in a range can be color coded, giving an approximation to the desired visualization.

1 1 0 0 0 0 0 0 0 0
1 1 0 0 0 0 0 0 0 0
0 0 1 1 0 0 0 0 0 0
0 0 1 1 0 0 0 0 0 0
0 0 0 0 1 1 0 0 0 0
0 0 0 0 1 1 0 0 0 0
0 0 0 0 0 0 1 1 0 0
0 0 0 0 0 0 1 1 0 0
0 0 0 0 0 0 0 0 1 1
0 0 0 0 0 0 0 0 1 1

Set Up

Start a new spreadsheet.

Put your amplitude parameter (say, 2.75) in cell A1.

Create the Data

1. Decide on the range and the number of values. We will use [0,1] with 101 evenly-spaced points.

2. Select cell B1 and enter the formula =ROW(A1).

3. Select and copy cell B1.

4. Select down to B101.

   • In Excel, you can use the GoTo command:

   • press ctrl-g (or function key f5) and enter destination B101

   • hold the shift key while clicking OK.

5. Paste your fomula into your selected range (e.g., ctrl-v).

6. Make a named range for your 101 new cells; name it idx.

   • If not already selected, select by selecting B1, then press holding crtl+shift+down

   • Make a named range from the selected cells by choosing from the menus Formula > Define Name.

7. Select cells C1:C101.

   You should know how now, but here's another way. Go to cell B1 and press ctrl+down. This takes you to B101. Press the right arrow. This takes you to C101. Press ctrl+shift+up. This selects up to C1.

8. type the formula =(idx-1)/100 and then press ctrl+shift+enter; this gives you the input values for your function

9. make a named range for these new cells; call it domain

10. select cells D1:D101

11. type the formula =A1*domain*(1-domain) and then press ctrl+shift+enter; this gives you your function values over your domain

Create Your Graph

1. Select the cells C1 through D101

2. From the “Insert” menu, choose a Scatter chart (with lines but no markers)

3. Experiment with changing the amplitude parameter

Refine Your Graph

1. Change the x-axis and y-axis minimum and maximum to 0.0 and 1.0

2. Select the “Titles” tab and use x for the x-label and y for y-label.

3. Change the chart title to Logistic Map

4. Change the aspect ratio of the plot to be more square.

5. Select the “Legend” tab and un-check “Show legend”

6. Get rid of the gridlines

Scalar Operations

Let mA be a rectangular array. Let's add a constant to every element of mA. Select an area the same shape as mA. Then simply include mA in a formula, as in:

= mA + 100

and then press CTRL-SHIFT-ENTER. Subtraction, multiplication, and division are handled in the same way.

Elementwise Operations

Assume that mA and mB are two ranges of the same size (say, {20*1}). To create the vector sum, select an empty {20*1} range on the sheet, type in the formula:

= mA + mB

and then press CTRL-SHIFT-ENTER.

You might wish to name the resultant range (e.g. mC) for future reference.

Any two arrays of the same size can be added in this manner, with the result placed in a range of the same size.

Elementwise subtraction, multiplication, and division are handled in the same way.

Matrix Operations: Matrix Multiplication, Inverse, and Transpose

To multiply two matrices, use the MMULT function. Thus, if mA and mB are compatible for multiplication, you could compute the value of a portfolio with the formula:

= MMULT(mA, mB)

If mA is an invertible matrix, produce the inverse of a matrix with MINVERSE:

= MINVERSE(mA)

TRANSPOSE(mA) produces the matrix transpose.

Example:

Suppose mA and mB are both {20*1}. The matrix product is not defined. But you produce the scalar product as

= MMULT(TRANSPOSE(mA), mB)

with ctrl+shift+enter or, more simply, as

= SUMPRODUCT(mA, mB)

with just enter.

Exercise: Frequency Chart

For this exercise, you need a file temp.csv containing one column of integers with some duplicates.

1. Open temp.csv as a spreadsheet; it should have one column. Copy these to column B.

2. Reduce column B to unique values

   https://subversion.american.edu/aisaac/notes/spreadsheet-intro.xhtml#unique-values-remove-duplicates

3. Sort column B.

4. In column C, create a count for each unique value (using COUNTIF)

5. Make a frequency chart for your values.

   https://subversion.american.edu/aisaac/notes/spreadsheet-intro.xhtml#frequency-chart

Choice of Software

Major spreadsheet applications include Microsoft Excel (the current industry leader), Google Sheets, LibreOffice Calc, and Gnumeric. All but the first are gratis, and the last two are free and open source software (FOSS).

Spreadsheets share many features, but menu layout and keyboard shortcuts vary by application. The keyboard or menu details used below assume a Windows operating system and Excel 2016. Often the variations required on other operating systems or with other spreadsheet implementations are minor.

Comparison Resources:

Windows vs Mac keystroke comparisons:

http://www.mtholyoke.edu/lits/labs/docs/MacWinKBeq.htm

Calc vs Excel

https://wiki.openoffice.org/wiki/Documentation/UserGuide/Migration_Guide/Calc_and_Excel

Analysis ToolPak

Some material in this appendix relies on Excel’s Analysis ToolPak. Up to Excel 2016, the Data Analysis ToolPak is not installed by default. The installation procedure has changed across versions, so do a websearch for you version. In Excel 2016 for Windows, you can install it by going to File » Options » Add-Ins » Manage Add-Ins.

Download HTML Data from Web Pages

Excel can extract data from the tables in web pages. You just need the web address (URL) of the page.

1. Open the spreadsheet for this new data.

2. In the Data tab, click From Web.

3. Paste the web address (URL) into the popup box.

4. Connect to the page anonymously.

5. One connected, locate the desired table data under Display options.

6. Check that the data is displayed correctly and then Load it into the spreadsheet.

Data from FRED

The Federal Reserve Economic Database allows programmatic access, but you need to

• register for an API key.

• install the FRED add in (video)

• use the add in to get data (1, 2)

• explore the use of 1date

Introductory Videos

Many good videos discuss spreadsheet use at all levels. I consider the GCFLearnFree Excel tutorials to offer accessible coverage of introductory and intermediate topics. Microsoft also produces some helpful introductory videos.

Formulas:

Microsoft’s Getting Started with Formulas tutorial

Basic usage:

GCFLearnFree’s Getting Started with Excel

Relative vs absolute cell references:

GCFLearnFree’s Relative and Absolute Cell References

Line-Chart Introduction:

The GCFGlobal introduction to Excel Charts is very good and is supported by a helpful video.

Correlation and Regression

Video: Correlation and Regression

Glossary of Spreadsheet Terms

Absolute Cell Reference

A reference that does not change when it is copied. This produced by putting a $ before the reference letter and number (ex: $A$1). (Also see: Relative Reference.)

Active Cell

The cell listed in the Name box, which is currently selected for data entry. It is typically highlighted by a distinct border.

Anchor Cell

The first cell that is selected in a range. If only one cell is selected in the sheet, it is the anchor cell. It is typically highlighted by a distinct (white) background color.

Automatic recalculation

This is the typical spreadsheet default, which eliminates the need to explicitly request value recalculation after a change to a cell. May be turned off in a very large spreadsheet.

Bar or Column Chart

Used to summarize and compare categorical data. The length of each bar represents the value assigned to that particular category. Bars run horizontally; columns run vertically.

Cell

The rectangular area formed by the intersection of a column and a row. A cell may a numerical values, a strings, or a formula. Identify a cell with a letter-number cell reference, giving its row and column. (Cells may also be named.)

Cell Reference

Typically the row and column of the cell, but a cell may also be reference by an assigned name.

Column

An Excel spreadsheet can have 1,048,576 rows and 16,384 columns. A column displays vertically and is labeled with letters of the alphabet A–Z, then AA–AZ, etc.

Column Chart

See Bar Chart.

Back To Top

Fill

Quick data copy from the anchor cell to an adjoining range, updating the data as appropriate. For example, if the anchor cell contains a formula with relative cell references, those references will automatically update relative to their position when copied to a new location.

Fill Handle

he small bold square in the bottom right corner of a cell, be used to copy (fill) data to adjacent cells in the same row or column. Hover over the fill handle box until the mouse pointer changes to a plus sign. Then hold the left mouse button while selecting the adjacent cells to copy to. Releasing the mouse button will fill the content.

Filter

Show only data that meet a criterion.

Formula

An expression in a cell, beginning with an equal sign "=" and using cell references and Excel functions. For example, in cell C1 enter =A1+B1 to display that sum in C1.

Formula Bar

The display line above the column headings of a spreadsheet. It displays the contents of the active cell.

Freezing

Force some cells to display. For example, if a spreadsheet contains many rows, freezing the row containing the heading labels allows scrolling down while still viewing the headings.

Gridlines

The horizontal and vertical lines displayed to separate cells in a spreadsheet.

Label

Non-numeric text typed into a spreadsheet cell.

Locked cell

A cell (or many) can optionally be given a read-only status, in order to prevent accidental overwriting. Uses this for cells containing important formulas or data that should never change (e.g., model parameters). This feature must be activated in the file preferences.

Name Box

The box to the left of the formula bar, which displays the name of the current cell. This is typically the letter-number cell reference, unless the cell is assigned a special name.

Pie Chart

A circular chart that visually represent proportions of a whole by the relative size of sections (“slices”).

Adjacent Range

A rectangular group of cells. identified by two colon-separated cell references that are opposite corners of the rectangle (e.g., A1:B3). A range may be assigned a name.

Relative Reference

A cell reference that changes when copied. Relative references do not contain dollar signs ($).

Rows

An Excel spreadsheet can have 1,048,576 rows and 16,384 columns. A row displays horizontally and is numbered.

Sheet Tabs

Tabs below the worksheet grid, used to switch from one worksheet to another in a workbook.

Sort

Impose an ordering on existing data. Sorts can be ascending or descending. Ties can be resolved by nested criteria.

Value

The actual value contained by the cell. This may differ from the displayed value (e.g., due to rounding).

Workbook

A collection of worksheets that bundled together a a single file.

Worksheet

A single grid of columns and rows. A workbook may contain multiple worksheets, which can reference each other by name.

References

Copyright © 2016–2022 Alan G. Isaac. All rights reserved.

version:

2022-12-05