Chart Tutorial

Here is a brief tutorial explaining how I made the chart in my last post.

First, open CelNav.xlsm and click the “Data Series” tab to get to the Data Series calculator. Then, select the date and time you would like to start the calculation, the body, the number of iterations and the interval. Press the “Calculate” button.

Above, I have calculated the data for the Sun, starting from May 12th, 1993 at 0900 UT and for each hour thereafter for 24 hours. We are going to need two columns of this data for our chart: the Julian date and the calculated altitude. I have labeled these columns “1.” and “2.” For now, we only need the JD in column “1.”

Select all of the cells in column “1.” and press [Ctrl] + [C] to copy them. Open a new Excel workbook, select the cell where you would like to paste the data and press [Ctrl] + [Shift] + [V] to open the “Paste Special” dialogue.

Press [V] or click the radio button to select “Values” and press “OK”. (This will prevent Excel from pasting any formulas, formatting or anything else we don’t need.) The JD values will be pasted into the new sheet.

Next, select all of the cells immediately to the right of the data you just pasted.

Type the following formula:

=A1:A25-2415018.5

… and press [Ctrl] + [Shift] + [Enter]. This will fill all of the selected cells with the formula automatically. (If necessary, change the cell references [“A1:A25”] to match the range your data is actually in.) The purpose of this formula is to convert the JD into the date format that Excel uses. This is similar to the JD, but the starting date is January 1st, 1900. Excel cannot make sense of dates before then, so you will need to use a workaround if your data is from before that date. Take note of the first and last numbers (34101.38 & 34102.38). These will be important later.

With the new column still selected, right-click anywhere in the selection and click “Format Cells”.

On the “Number” tab, select the date and/or time format you would like. I have chosen to use a custom format which shows the two digit day, a dash and the hours and minutes. You can create whatever format you like by typing the format code into the “Type:” field. Typing “ddd” will give you the day of the week, “yy” or “yyyy” will give you the 2 or 4 digit year, respectively, and so on. Experiment and see which format you prefer. The options are practically limitless. A real-time preview of the format you are creating will be shown in the “Sample” field.

Once you have the date/time formatted, go back to the Data Series calculator and select and copy all of the cells in the Hc column (labeled “2.” in the first image). Paste them as before, using the “Paste Special” option. Repeat the calculation for each body you would like to include in the chart. Paste the calculated altitudes in the columns next to the data you already pasted in the new sheet. (For simplicity, I recommend calculating all data for the same period and interval.) For this tutorial, I will only be using the Sun and Jupiter.

Now we can start making the chart!

Select all of the cells you would like to include in the chart. In this case, we want the date and time, but not the JD - so we leave the first column out. We also make sure to select all of the calculated altitude columns. If your data is in non-adjacent columns, don’t worry. You can add or remove data from the chart at any time.

Click the “Insert” tab and select a “scatter” plot. I like the smoothed lines with no markers on the upper right, but you can choose whatever kind of chart you like. You should get something like this:

In the next screenshot, I have already resized the chart so that it is easier to see.

Right-click the x-axis and set the minimum and maximum to the desired values. Remember those two numbers that I said would be important? Those are the numbers you need to make the chart start and end exactly with the data. Here you can also set the units to something that makes sense to humans. I chose 1/24 of a day … or 1 hour.

Click the y-axis and configure it, as well. Since this is going to be a visibility chart, I chose a min of 0 and a max of 90.

In order to approximate the visibility of Jupiter, we will delete all of the altitudes which correspond to a time when the Sun is above the horizon. To do this, we simply select the cells containing the data and press [Delete].

Now we will very roughly approximate the brightness of the sky. Click the “Insert” tab, select “Shapes” and click the rectangle. Draw the shape anywhere on the chart by clicking and draging. You can resize it at any time.

Right-click the shape and select “Format Shape”. Select “Gradient” for the fill (1., below) and 0° for the angle (2.). You can add or remove steps as desired (3.) and set their transparency (4.) In this chart, I have set all of the steps to black. The outer two have a transparency of 90%, and the inner two are set to 50%. I have also selected no border (5.)

Align the edges of the shape with the top and bottom of the chart, and with the times that the Sun sets and rises. Adjust the positions of the steps so they make sense. In the final chart, the Moon rose about halfway through the night, so I chose to brighten the sky at that time. Again, this is just a very rough approximation.

All that’s left to do now is some minor formatting. Right-click each chart element you want to format and change it to your liking. Below, I have simply filled the plot area with a light blue and the chart area with a light grey.

Once you have finished, you can select the chart and copy it to the clipboard using [Ctrl] + [C]. From there, you can paste it as an image wherever you like.

I hope this tutorial will help you make really neat and interesting charts of your own. There’s almost no limit to the types of charts you can make. If you have any questions or if there is some additional feature you would like to see added, please let me know in the comments. Thank you!

-Shawn