Graph with menu – part 2

Select cell C16, place the cursor after the equal sign and type
IF ($ B $ 12 = 2005, C5
 let it be C5
We continue typing NB ()) after C5
the formula is = IF ($ B $ 12 = 2005, C5, NB ())
IF B12 = 2005, give the contents of cell C5, otherwise NOT AVAILABLE
Copy the formula with the fill handle to cell N16
With the cells C16 up to and including N16 still selected, copy the formula with the fill handle down to cell N21.

Select cell C17 and change 2005 to 2006 in the formula bar, and copy to the right to N17.
Select cell C18 and change 2005 to 2007 in the formula bar, and copy to the right to N18.
Select cell C19 and change 2005 to 2008 in the formula bar, and copy to the right to N19.
Select cell C20 and change 2005 to 2009 in the formula bar, and copy to the right to N20.
Select cell C21 and change 2005 to 2010 in the formula bar, and copy to the right to N21.

We will now seek the highest value in the data area C16 to N21. Click in an empty cell (no matter where) and type the formula = MAX (C16: N21) and press Enter. Record this result and delete the contents of the cell with the formula (in our example, the highest value is 33865).

Click in the area of ??the vertical shaft (1), right click and select “Format Axis” (2).

In the “Axis Options” (1) window, choose the “Format Axis” tab and select the “Fixed” (2) option, we put “0” in the ‘Minimum’ and a value more than the result we obtained with the = MAX (C16: N21) formula in the ‘Maximum’, so take “50000”, put “5000” in primary unit and “1000” in Secondary Unit (3).

Go to the “Format” tab (1) and choose “Currency” (2) and click “Close”.

Click in the chart area, so that the chart is selected, click on the “Insert” (1) ? “Forms” (2) and select “Text Box” (3) from the Ribbon.

Draw the text box (1) in the “Chart Area” (2) but above the “drawing Area” (3).

With the text still selected, you type the formula,  = $ B $ 12 and press ENTER. (Not in the text box!)
Adjust the size of the text box. (You can also change the font, alignment and color etc.)

Move the chart upwards so that the upper right of the graph is located at cell O14.

Drag the handle to the left until the cell area B15 to N21 is hidden.

Choose a date in cell B12 and the chart adjusts accordingly.

With a little creativity, you get a nice result (1).

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats