Kimel's spreadsheet is a beautiful thing

Mike Kimel's spreadsheet contains a page where he pasted BEA Table 3.2. The table shows a breakdown of Federal government revenue and spending for the 1929-2010 period. Then at the far right, beyond the table, he pulls out and gathers the data on "current revenues" and "current expenses" and uses those numbers to calculate a surplus or deficit for government spending.

Where Kimel pulls out and gathers the numbers, he doesn't do it by copying and pasting. He does it by setting cells equal to values in the table. "Referencing" cells in the table, it's called.

This is kind of important. I captured part of Kimel's spreadsheet page in Zoho so you can look at it. Column C contains the BEA's breakdown of Federal receipts and expenditures. You don't have to memorize it, but you should note that the breakdown contains a lot of line items -- 46 of them, according to the numbers in Column B. In the spreadsheet, you can scroll down to see the whole list. You can press CTRL+HOME to get back to the upper left cell A1.

You can scroll to the right. To the right of Column C, I've included the first five years of the data that run from 1929 to 2010 on Kimel's sheet. The numbers in this table are numbers, not calculations.

At the top of the spreadsheet below, just down from the title "Kimel Fragment" is a white field less than an inch wide, then the letters FX in some fancy font, and then a white field about 3 inches wide. That wide white field is the "formula bar". That field displays the contents of the cell you select, and it changes as you move the cell selector.

If you move the cell selector to column D or E or F or G or H, you can see that the entries for "Current receipts" and "current tax receipts" and "Personal current taxes" and the others are actual numbers. Not calculations or formulas, I mean.

If you scroll a little more to the right you come to a blue area. (I made the background blue so I could describe it easily and you could find it easily.) Scroll a little more to the right, so you have the whole blue area on the screen.

If you move the cell selector around in the blue area, you will notice that the titles on Row 2 ("current receipts" for example) appear in the formula bar when those cells are selected. That tells you that Mike Kimel (or somebody) actually typed those words into those cells. Or maybe that they copied those words from somewhere else and pasted them into those cells.

If you move the cell selector along the left edge of the blue area, on the year values, you will notice that the year values appear in the formula bar. These numbers also were typed in, or pasted in.

That could be done differently. You could type 1929 in cell J3, and do the numbers below it just by adding 1 each time you go down a line. For example, in cell J4, where you want the value 1930, you want a value that is one more than the value in cell J3. So you could add 1 to the value in cell J3.

To do that you would select cell J4, then type an equal sign, the number 1, a plus sign, the letter J, the number 3, and then you would hit the ENTER key. (Try it if you want. You won't mess up the sheet. If you do mess it up, just refresh the page and start over. No harm done.)

When you hit ENTER the cell selector moves down a row, onto the value 1931 (which is a typed-in number). Move the cell selector up a row, onto the value 1930. Notice that in the blue area, the cell shows 1930 but in the formula bar it shows the formula that you typed in: =1+J3 .

That's why I like Zoho. It lets you use the formula bar online.

Now if you wanted to recreate the value 1931 in the next cell down, you could do it with a formula just the same way. Actually, it would be the same formula. (And now we're getting somewhere!) In cell J5 you would want the value 1 plus the value in the cell above. But you don't have to type the formula again. There's an easier way.

Move the cell selector to cell J4, where your formula is. Right-click on that cell, and select COPY from the little menu that shows up.

Select the next cell down -- cell J5 -- and press CTRL-V.

Nothing happens in the blue area. But the formula bar changes.

Do it again. Select the next cell down and look at the formula bar to see that it contains a year number. Then press CTRL-V, and the formula bar will show your formula instead.

Okay, now for the good part. Click on the next cell down, but don't release the mouse button. Instead, drag the mouse down several rows. Yes, yes, do go three or four rows off the blue area, yes!

Now press CTRL-V.

All the cells you selected get the blue background color. And, if you stayed in Column J, all the cells you selected now show year values, in sequence.

So I hope you already knew how to do that. But if you didn't, wow. You should learn. I'll help you if you want. We can do more of this spreadsheet stuff. Just not in this post.

So anyway, look at the values for "current receipts" and "current expenditures" for 1929 to 1933, in the blue area on the spreadsheet. In the blue area, there are values. But in the formula bar, there are formulas. The one for cell K3 shows =$D$3 for example.

It starts with an equal sign, just like the formula we entered to get the year values.

There are a couple dollar signs in it though. That's okay. We're not doing any more spreadsheet lessons in this post. Overlook the dollar signs in the formula. If you ignore those dollar signs, the formula looks like =D3. That means the value in cell K3 comes from cell D3. If you scrolled back over to Column D, you'd see the same value in that cell as in cell K3.

And in fact, if you changed the value in cell D3, the value in cell K3 would also change! That's pretty neat. It's a powerful feature of spreadsheets, and it's one reason people use them. I made use of that feature for this series of posts.

What I did was, I got the latest values from the BEA site, their Table 3.2 that shows current receipts and current expenditures. I added that sheet as an extra sheet in Mike Kimel's spreadsheet. And then I went into Mike Kimel's Table 3.2 and used formulas to set the "current receipts" and "current expenditures" values in Kimel's table equal to the new 2014 values.

I only had to create formulas for the 1929 values. Then I copied those formulas over to the later-year columns. Easy as that, Kimel's sheet contained the new 2014 values.

And then, because Mike Kimel did his spreadsheet the right way, using formulas that refer to to other cells, all of his numbers got updated. (I only changed "Line 1" current receipts and "Line 20" current expenditures. But those are the only numbers Kimel uses from Table 3.2. And every place he used those numbers, they got updated.)

So I made an easy change, updating Mike Kimel's data. And the prize I got for that was an updated version of his graph, as you can see below.

Graph #1: Kimel's Graph with Kimel's BEA Data

Graph #2: Kimel's Graph with Current BEA Data

Related Files

1. Mike Kimel's original Excel file, which he very generously sent upon my request: Tabarrok and Cowen 20110217 follow up 20110221.xls

2. Downloaded from BEA, the current (30 Jan 2014) version of Table 3.2 (for Federal spending and revenue): BEA download.xls

3. File 2 as a page in file 1, allowing me to revise Mike Kimel's graph by referencing the more recent values: Current BEA data.xls

4. For yesterday's graph of Real Private Spending in the 1960s, the Google Drive spreadsheet named A Look at Mike Kimel's Data. That's the original Excel file Kimel sent me, converted to Google Docs format. So I could look at his data for the 1960s.