Going insane!

Hi,

I'll admit I'm not exactly what you'd call an excel guru, but I want to learn. Right now however I just want to solve my problem.

I am creating a personal account spreadsheet to watch where my money goes, and to keep track of my budget. The problem is I have a bar chart with that incidate my expenditure over 12 months. Then I have a line graph which cuts off at where I want my budget to be.

My issue is to get this line graph I have to create yet another series of data on my spreadsheet with a static number so I can get the series data. Is there a way I can just enter the series data on the chart so I don't have to have this extra line?

I realise I'm probably sounding like an idiot, so I've attached a small example* (at least I thought I did!)

(ps - I realise it seems awfully poor of me to just come on here and rattle off advice but I tend to learn better when someone shows me rather than sifting through books and web sites for the answer. That's not to say I haven't looked, but I just haven't found the answer - maybe I am looking wrong?)

Originally Posted by kik

Hi,

I'll admit I'm not exactly what you'd call an excel guru, but I want to learn. Right now however I just want to solve my problem.

I am creating a personal account spreadsheet to watch where my money goes, and to keep track of my budget. The problem is I have a bar chart with that incidate my expenditure over 12 months. Then I have a line graph which cuts off at where I want my budget to be.

My issue is to get this line graph I have to create yet another series of data on my spreadsheet with a static number so I can get the series data. Is there a way I can just enter the series data on the chart so I don't have to have this extra line?

I realise I'm probably sounding like an idiot, so I've attached a small example

most people will not be able to open an .xlsx file, is this from Excel 2007 ?

can you output the file as a .xls ?

---

Hi,

Here's one I did a few months back, I adjusted it to your requirements.

Originally Posted by kik

Thank you for your response.

I took a look at your chart, and it certainly is a lot better than what I was doing. I only have one other question:

I took a look at your series data and noted you had three fields:

Series name, Series X values and Series Y values.

I spent the best part of the afternoon churning through looking how I can get three fields when I create a chart on Excel 2007. As is though I have not succeeded. I went to the help system but couldn't find anything and plowed through the book I bought (Using Excel 2007 by Bill Jelen) but it seems to be one of those things that's so bloody simple that no one tells you how to do it!

So I have to ask: how do you get three fields, because every chart I create, regardless how much data I use, I only get Series name and Series value?

Thanks again in advance.

Hi kik,

I've looked by in my emails and found the source, I got it from http://www.officeletter.com/ it was an article back in October 9th 2006 by James E. Powell, I've extracted the instructions that also included for Excel 2007, so I hope this helps. Don't forget to subscribe to the newsletters at http://www.officeletter.com/ you will pick up some good tips, not only on Excel - enjoy the read!

2) EXCEL: ADD A HORIZONTAL LINE TO A COLUMN CHART
-------------------------------------------------------------------

When I build a column chart, I find it helpful to provide my audience with a sense of the average of all values so they can see which column is higher or lower than the average. I can also use a horizontal line when I need to show a minimum value -- for example, to highlight which salespeople have exceeded a monthly goal.

Excel offers a way to do this, though it takes several steps. When I originally went searching for a suitable technique, I found several Web sites that promised to provide the answer, but explanations were either incomplete (a crucial step was omitted) or the tutorial assumed too much knowledge on the part of the reader. In one case the author ignored the scale of an axis, for example, which would lead to erroneous results.

After careful experimentation, I developed detailed, step-by-step instructions that anyone can follow to successfully add a horizontal line to a column chart

To begin this discussion, we’ll use the following sample data:

Column A:
Row 1: Agent
Row 2: Adams
Row 3: Baker
Row 4: Carson
Row 5: Davis

Column B:
Row 1: Sales
Row 2: 50
Row 3: 100
Row 4: 150
Row 5: 70
Row 6: =AVERAGE(B2:B5)

Column C:
Row 1: Average
Rows 2 through 5 all contain this formula: =$B$6

You can download this sample worksheet here for Excel 2002 and 2003
users

http://www.officeletter.com/samples/...horizontal.xls

or

http://www.officeletter.com/samples/...orizontal.xlsx

for Office 2007 users.


EXCEL 2002/2003:


1. Select cells A1 through B5.

2. From the Chart wizard, choose the Standard Types tab. Choose the Column type, and for subtype choose "Clustered Column. Compares values across categories."

3. Click on Finish. Excel adds a chart to the current worksheet.

4. Select cells C1 through C5 and copy them to the clipboard (use the Edit/Copy command or Ctrl + C).

5. Click on the chart. From the main menu, choose Edit/Paste Special. In the "Add cells as" section of the Paste Special dialog box (see Figure 1 in our online edition), choose "New series." In the "Values (Y) in" section, choose Columns. Check the "Series names in first row" option and click on OK. Excel adds a second set of bars, all of the same height.

6. Right click on this new series of equal-height bars and right-click your mouse. Choose Chart Type. In the Chart Type dialog box, select the Standard Types tab. Choose "XY (Scatter)" from the "Chart type" column, and from the sub-types at the right choose "Scatter with data points connected by lines without markers" -- it’s the last icon of the last row in the sub-types section. Click on OK.

7. Excel adds a horizontal, thin line (see Figure 2). Click that line until you have selected it, then right-click on the line and choose "Format Data Series..." from the pop-up menu.

8. Using the Patterns tab, in the line section, find the Custom options and set the Color to red (or some other strong color) and the Weight to the next heavier value. Click on OK.

9. Move your mouse to the center of the chart -- to the large gray area -- until the Tooltip next to your mouse cursor reads "Plot Area." Right-click your mouse and choose Chart Options. Select the Axes tab, and check the box "Value (X) Axis" in the "Secondary Axis" section. Click OK. Excel adds an X axis to the top of the chart.

10. Double-click on any tick mark on this top X axis. Excel opens the Format Axis dialog box. Select the Scale tab (see Figure 3). Enter 1 for the Minimum value and 4 for the Maximum value. Excel will uncheck the boxes associated with the Minimum and Maximum settings. The horizontal line now extends from the chart’s Y axis on the left to the right edge of the chart (see Figure 4).

11. Double-click on any tick mark on the top X axis. From the Format Axis dialog box choose the Patterns tab. In the right-half of the dialog box, choose "None" for three settings: "Major tick mark type," "Minor tick mark type," and "Tick mark labels." (See Figure 5.) Click on OK.

12. Your chart is complete, as shown in Figure 6.

Note: if you change any sales information, Excel will recalculate the average, and replot all values, including the new average.

OFFICE 2007:

1. Select cells A1 through B5. Click on the Insert tab to open the Insert Ribbon.

2. In the Charts group, click on the down-pointing arrow on the Column button. Choose the first icon in the 2-D Column row (if you hover over this icon it will read "Clustered Column").

3. Excel adds a chart to the current worksheet.

4. Select cells C1 through C5 and copy them to the clipboard.

5. Click on the chart to select it. Paste the copied cells into the chart using the keyboard shortcut Alt+E, S. (Alternatively, move to the Home Ribbon, choose the down-pointing arrow on the Paste button, and choose Paste Special.) Excel adds a second set of columns, all of the same height.

6. Right-click on this new series of equal-height bars and choose Change Series Chart Type. In the left column of the Change Chart Type dialog box choose "X Y (Scatter)" and pick the last icon in the "X Y (Scatter)" row on the right. (If you hover over the icon, the popup box reads "Scatter with straight lines.") Click on OK.

7. Excel adds a horizontal, thin line (see Figure 7). Click that line once, then right-click and choose "Format Data Series..." from the pop-up menu.

8. From the panel on the left choose Series Options. Be sure that in the right panel the "Secondary Axis" option has been selected.

9. In the left panel, choose Line Color. In the right panel, choose Solid line, then pick the color (choose red from the Standard Colors" section) as shown in Figure 8. Click on Close.

9. Notice that Excel had added a new X axis to the top of the chart. Right-click on any tick mark on this new axis and choose Format Axis. From the Format Axis, choose Axis Options from the left panel (it should be selected by default). In the right panel, set the Minimum and Maximum values to Fixed. Enter 1 for the Minimum value and 4 for the Maximum value (see Figure 9). The horizontal line now extends from the chart’s Y axis on the left to the right edge of the chart (see Figure 10).

10. Right-click on any tick mark on the top X axis. From the Format Axis dialog box choose Axis Options in the left panel. In the right panel, choose "None" for three settings: "Major tick mark type," "Minor tick mark type," and "Axis labels" (see Figure 11). Click on Close.

11. Notice that the red line isn’t properly positioned against the left scale -- it looks as though it represents 150. In this step we’ll adjust the right axis and hide it. Right-click on any tick mark on the right-hand Y axis (the scale goes from 0 to 100 using our sample data) and choose Format Axis. In the Maximum setting, click on Fixed and enter the largest value from the left Y-axis (160 in our example). This is because you want to keep the Y-axis on the left and the Y-axis on the right in synch. While still in the Format Axis dialog box, and with Axis Options still selected in the left panel, choose "None" for three settings: "Major tick mark type," "Minor tick mark type," and "Axis labels." Click on Close.

12. Your chart is complete, as shown in Figure 12.

Special Note: if you change any sales information, Excel will recalculate the average, but the chart may no longer be correct because the scale for the average line is not adjusted. To fix this:

1. Click inside the chart. Note that a Contextual Ribbon labeled Chart Tools appears with three tabs. Choose the Layout tab.

2. Click on the down-pointing arrow on the Axes button, choose "Secondary Vertical Axis" and choose "None" to reset it.

3. Choose the down-pointing arrow on the Axes button again, choose "Secondary Vertical Axis" again, and this time choose "Show Default Axis."

4. Repeat step 11 above to change the maximum value and hide the axis.

-- James E. Powell

If you got this far you should be able to sort it now?

Yes and thanks to James E. Powell - thanks for the feedback