This article is also available as a TechRepublic download.
The article was originally published on August 10, 2004.
Comparing current results to results recorded in the past is a mainstay of business decision making. Often the best way to communicate such comparisons is through a graphical representation. The charting capability of Microsoft Excel is an excellent tool for this type of scenario. But getting that charting tool to behave just as we want it to behave can get a little involved. This was the gist of tion-thee's question in a TechRepublic Technical Q&A:
"I have 9 columns on Excel, say columns A1, A2, A3, B1, B2, B3, C1, C2, C—here's the scenario, I want to create a bar chart that displays the amount of A1, A2, A3 but the amount of A2 and A3 should be sum up. It's like I have a separate bar for A1 and a separate bar for A2+A3. Same goes with B1, B2+B3 and C1, C2+C3, this should be showed in 1 chart only. Right now I'm having a hard time doing it so what I opted to do is to separate the chart for A1 and A2+A3..."
TechRepublic member gill_potter suggested the specific informational nugget that would make this possible in his answer:
"The quick way is to create a new row A4 that is a sum of A2 and A3. Then select just the 1 row and ctrl select the 4 row and click the chart wizard. This will create a bar chart that has the A1 value next to the A4 value and then the next bucket will be the B1 value compared to the B4 value. If you want to compare all of the 1 values together and all of the 4 values together, just change the chart option to data in columns rather then data in rows."
The key to the solution is adding the two columns and creating a fourth column to hold the results. Often, the best method for solving any problem in Excel is by breaking it up piecemeal and solving the bits until you get to the desired end results. Creating the additional data will give tion-thee the data he/she would like to compare in a bar graph (see Figure A).
Figure A |
Example One |
The bar chart in Figure A was derived from this simple data table:
10 |
14 |
17 |
11 |
15 |
18 |
13 |
16 |
19 |
24 |
31 |
37 |
ColA |
ColB |
ColC |
However, after gill-potter made his suggestion, the question further evolved into a query about how to insert some automation into the process, so that the comparison could be updated as new current results were reported. A little more data manipulation will be required to make this work.
Set up the scenario
We'll create a simple scenario for our example. Let's say we want to report company revenue to a group of shareholders. We know these shareholders want to compare revenue in 2004 with revenue in 2003 and 2002. We also know they want to see a comparison for each quarter as well as the annual totals.
The key factor in this case is the number of data points we have recorded so far in 2004. If it is only February, then we are only going to be able to compare two months; but if it is August, we have to compare the accumulated revenue for eight months and the accumulated revenue for the first two months of the third quarter. To get the number of revenue data points (months) in 2004 available so far, we use the COUNT() function (the orange G1 cell in Figure B).
Figure B |
Example Two |
With the number of data points counted, we can determine what quarter we are comparing using the IF() statement in combination with the AND() function:
=IF(G1<=3,1,IF(AND((G1)>3,(G1)<=6),2,IF(AND((G1)>6,(G1)<=9),3,IF(AND((G1)>9,(G1)<=12),4,""))))This logical formula will return "2" if the COUNT() in G1 is greater than 3 and less than or equal to 6, which means we are examining the second quarter. It will accordingly return, "1", "3", or "4" depending on the quarter designated by the value of G1. (See the green J5 cell in Figure B.)
The COUNT() function, in conjunction with the OFFSET() function, is also used to accumulate the total revenues for each year up to the month being examined. In other words, if we are looking at results through August 2004, we use this formula:
=SUM(B5:OFFSET(B5,G1-1,0))
The result is displayed in blue cells F6, G6, and H6 (Figure B). These values are the SUM totals of the rows ranging from January through August. The total for the year is important for determining how much each quarter total has contributed to the year, which is the main idea of our graphical presentation.
The purple cells in Figure B are the running totals for each quarter for each year. The calculations also take advantage of the OFFSET() function in Excel. Using an IF() statement, we can calculate the value for each quarter, like this formula for the third quarter of 2004:
=IF(J5=3,F6-F8-F9,OFFSET(B1,10,0)+OFFSET(B1,11,0)+OFFSET(B1,12,0))
The purple set of cells was created to make our calculations for the remaining blue row of F5, G5, and H5 easier to calculate. The following formula takes the information calculated for the year-to-date totals and for the quarters so far and determines the value to be displayed for the month being examined:
=IF(J5=1,F6,IF(J5=2,F9,IF(J5=3,F10,IF(J5=4,F11,F6))))
If we had not calculated the values in the purple cells first, we would have had to put their corresponding formulas into this formula, which would have been unwieldy and prone to error. These calculations are shown in Figure B for the purposes of this article, but in practice you should hide these cells on another worksheet.
Chart it
Once we have calculated the year-to-date totals for each year and the corresponding quarterly totals, charting the results can be accomplished with the Excel Chart Wizard. As you can see in Figure B, using the standard bar chart found in the wizard, we can present a graphical representation that compares each year's revenue on an annual basis and on a quarterly basis. Once again, in most normal circumstances, you would want the chart to appear on a page by itself. The only data the user has to input will be located in the green section of Figure B, where the 2004 data will be entered.
Improvement
While Figures A and B can give you a general feel for how the Excel worksheet works, it cannot be as helpful as a real live Excel worksheet. That's why we are making the two examples discussed in this article available for download. Neither file is a work of art, but they do work, and they should help tion-thee with his Tech Q&A question, or at least give him something to think about.
However, there is plenty of room for improvement in these example worksheets, and I encourage you to download them and poke around. If you do take some time to enhance or improve them, or if you have a better technique than what is outlined here, we'd like to see it.
Full Bio
Mark Kaelin is a CBS Interactive Senior Editor for TechRepublic. He is the host for the Microsoft Windows and Office blog, the Google in the Enterprise blog, the Five Apps blog and the Big Data Analytics blog.