Page 1 of 1

Using Excel: Tips and Tricks, from Beginner to Advanced

PostPosted: Tue Dec 10, 2013 9:25 pm
by James McMahon
A few years ago I never would have guessed that I would be using Excel so much. My journey to political economy was circuitous, and even then I did not anticipate that I would need to reacquaint myself with a program I thought I said, "Goodbye!" to in high school.

This thread is for all advice and questions about using excel. To some of us, excel is a simple and straightforward program; but we quickly forget that people come to political economic empirical research from different intellectual backgrounds, which means that, sometimes, excel or a similar program is an obstacle to doing more thorough investigations.

So let's share ideas, from the simplest to the more advanced!

How to re-base two series to 100.

So you have two series that, overall, are of different size (picture 1). When you plot these two series onto one graph, the scale of the left axis creates a sort of visual warp. The movement of the series with the larger numbers (Series B) is shown more clearly, but the series with smaller numbers (Series A) appears to be a flat line (picture 2).

Re-basing the two series to 100 (or 1, or 10, or any other number) is not your only option to represent the two series, but it is a useful strategy to compare the slopes of the two series. Remember: you can always tell the reader what you are doing: the series are different in size, and a re-base allows for a visual comparison.

STEP 1. Decide which year will be the base of BOTH series.

Re-basing two series essentially means you will put the two series on the same scale because they will have the same value for one of the years.

In this case we will use 1991. So what we are doing is changing the scale of the two series so that, for each of them, 1991 = 100.

STEP 2. Re-scale the first series.

For Series A we will divide B3 by itself and then multiply by 100. E3 = (B3/$B$3)*100
$B$3 allows us to fill the rest of the series and use B3 as the constant denominator. The $ sign anchors the cell for the rest of the formula. Thus, E4 = (B4/$B$3)*100, E5 = (B5/$B$3)*100, and so on.

STEP 3. Repeat for the second series.

Start over again for series B, which also needs to be scaled. F3 = (C3/$C$3)*100
Remember to use the 1991 value of Series B for the re-scaled Series B. A remember the $ sign before you drag the formula down. Thus, F4 = (C4/$C$3)*100, F5 = (C5/$C$3)*100, and so on.

Here is the final result:

STEP 4. Graph the two re-based series.


Notice the difference in presentation. Unlike in the first graph, the significant movements of Series A are now comparable to the movements of Series B. Moreover we can see how much Series A grew. What was not visible in the first graph was that Series A, from 1991 to 2007, grew 352%.

FINAL Thoughts:
The decision to re-base two series is a logical one. This means that it should only be done if it makes sense. There are other options (presenting the two series as rates of changes, or using a logarithmic scale). Moreover, there are times when there is a good reason to NOT use the re-base method. For instance, the two series should NOT be re-based if they are in two different units of measurement. If, hypothetically, "Series A" was a percentage and "Series B" was a price level, there may be no logical reason to rebase to 100, particularly for the percent series.