Multigraph charts in Excel
Adding an average line to Excel charts
In-cell formatting in 1-2-3 for Windows
Incremental dates in 1-2-3
Q My company uses Harvard Graphics to present summaries of our research budget and spending at monthly staff meetings. The presentations often include one page with two charts: a pie chart showing the portion of total research dollars spent and unspent, and a column chart with a monthly breakdown of spending. I want to reproduce these on one Excel chart sheet, but I can't figure out how to get more than one chart on a sheet.
- Susan Lindquist
A One approach is to create a column chart on the worksheet along with the data, create a pie chart on the chart sheet, then use the Camera tool to place an image of the column chart on the same sheet as the pie chart.
1. In a blank worksheet, enter the data in range A1:B13, the labels in C1:C3, and the Approved value in cell D1. Enter the formula =sum(B2:B13) in cell D2 and =D1-D2 in cell D3.
2. Select A1:B13, and click the ChartWizard tool to create a column chart on the worksheet. In step 3 of the ChartWizard, choose chart 1 in the dialogue box. In step 5, select No at the "Add a Legend?" prompt, and enter Spending by Month in the Chart Title box.
3. To exclude August through December from the chart, hide rows 9 through 13: select A9:A13, then select Format-Row-Hide. As the year progresses, unhide the rows one at a time to enter data; the chart will automatically incorporate the new values.
4. Decrease the width between the graph columns by double-clicking the grey area inside the graph, then selecting Format-Column Group, clicking the Options tab, and decreasing the width to around 50 per cent. To turn off grid lines, select Tools-Options, and on the View page, deselect Gridlines.
5. Select C2:D3, select Insert-Chart-As New Sheet, and follow the ChartWizard's prompts to create a 3-D pie chart on a new chart sheet. In step 3, choose chart 7. In step 5, enter 1996 Funds in the Chart Title box. Click just outside the chart to select its plot area, then drag a corner selection handle to shrink the pie into the top left corner of the sheet.
6. Next, find the Camera tool: select View-Toolbars and click the Customize button. Choose Utility in the list of categories, find the Camera, and drag it onto the worksheet. Click Close.
7. Select the cells underneath the column chart in the worksheet. (The Camera tool only takes snapshots of ranges, not objects like charts.) Click the Camera tool, go to the chart sheet, and click where you want the column chart to appear. To resize the picture after you've dropped it onto the chart sheet, click it and then drag its selection handles. To add an outline or drop shadow, use the Format-Selected Object command. Use the Filled Freeform tool on the Drawing toolbar to connect the two charts.
Q I created a set of column graphs in Excel using the Insert/Chart/As New Sheet command. Now I need to add a line to each chart showing the average of all the values. Can I do this without re-creating every chart?
- Ann Pickel
A Yes, you just add the averages to your worksheet data, paste them into the chart, where they appear as additional columns, then change those new columns to a line. Let's say your original data is in range A2:C13. Calculate the average, and copy it into range D2:D13
Now, select range D2:D13, and choose Edit-Copy. Click inside the chart, or go to your chart sheet and select Edit-Paste. You'll get a new set of columns for the new data. To change the columns into a line for the average, click on one of the new columns to select the series, then select Format-Chart Type, select the Line chart, and click OK.
Q I hate it when a program upgrade won't let me do what I could do in an older version. Back when I used 1-2-3 release 3.1, I could italicise or underline a single word inside a label. Now, in 1-2-3 for Windows, I can apply formatting only to the entire cell. This really fries my socks!
- Susan Fuhr
A Although you can't perform all the tricks you could with the Wysiwyg add-in (which is available only for releases 2.3, 2.4, 3.1, 3.1+, and 3.4), the following undocumented trick lets you do the same kind of formatting in the Windows versions of 1-2-3.
Wysiwyg uses special keystrokes and code letters to apply in-cell formatting. While the keystrokes don't work in Windows, some control codes do.
Suppose you want the word bold to be bold in a label. Double-click the cell that contains the label, click the insertion point in front of the b, press <Alt>+<F1>, type ba (for begin attribute), and type b. Just after the d, press <Alt>+<F1>, and type ea (for end attribute). When you press <Enter>, only the word bold will show up in boldface. You can also enter these codes as you type the text.
Q I track client billing in 1-2-3 release 5.0. Client A might receive a bill on the 13th of every month; client B on the 17th, and so on. I have one sheet per client, and I want to create a list of the billing dates for each client. Can I create formulas to calculate the dates?
- Genifer Caldecott
A You could use 1-2-3's Fill feature to do this. For example, enter 01/13/96 into cell A1 and 02/13/96 into cell A2, select A1..A2, move the cursor to the bottom right corner of the selection until you see double arrows, as in the figure, and drag down the column.
But if you want to be able to change the initial date and have the series recalculate, you need formulas. In that case, enter your first date into cell A1, then enter +A1+31+@day(A1)-@day(A1+31) in cell A2 and copy it down column A.
By Richard Scoville