Create Sunrise Chart in Excel

You must have seen sunrise charts on different weather sites for depicting daylight time in different months or weeks of years. Being a creative individual, you’ve just thought if you can create this Sunrise Sunset chart in excel. I did. In this article, we will learn how to create a sunrise chart in excel that shows the daylight hours.
Let’s see an example to make things clear.

Example: Creating Excel Sunrise Sunset Graph
To create this chart we need sunrise time, the daylight time and the evening time. Here I have gathered the average sunrise and sunset time so that we can use them to create a sunrise chart.

Preparation of Data
So here we have sunrise time in B column and sunset time in C column. Obviously, before sunrise, it is night time, and the daylight time is between sunrise time and sunset time.

To get daylight time, we will subtract sunrise time from sunset time. This will be daylight hours.

Daylight hours = Sunset time - Sunrise Time

Using the above formula, we write this in D4 and copy it down the cells.

Now to calculate evening hours, we will subtract sunset time from 1. In excel, in terms of time, 1 means a day (24 hours). So if we subtract the sunset time from 1 the remaining time is evening hours.

Evening hours = 1- Sunset time

Using the above formula in write this formula in E4 and drag it down.

 Now we have our data ready. Now we have our time table of sunrise, daylight and evening hours is ready, let’s head to creating the damn graph.

Creating a sunrise-sunset graph

1. 1. Select the Months and Sunrise time column
   2. In excel 2016, Go to insert=> charts => Recommended charts. Click on it.
   3. Find the area chart and then select the stacked area chart.

1. 1. Right-click on the chart, and click on select data. Select data source dialog will open.
   2. In legend and entries, click on Add. In the series name, select D3, and in the data source, select D4:D15. Hit OK. Now the chart will look like this.

1. Now again, right-click on the chart and click on the select data. Click on add and select E3 for the series name and for Data select E4:E15. Now the chart will look like this.

• The orange area depicts the daylight time and the rest is night time.

The Sunrise chart is ready but it doesn’t look that great. Let’s make it a little bit more readable and eye-catching.

Formatting the Sunrise Chart

1. 1. Select the chart and go to the design tab. Choose the chart design with verticle lines.

Now the chart looks like this.

1. 1. Change the colors of different parts of the chart as you like. I have chosen this color combination. You can have it as you want.

1. 1. Format the axis. The Y-axis is not that readable. By default, it has a gap of 4.48 hours. Right-click on Y-axis and click on the format axis option. In the axis option, in the major box, write this number. 0.166667 (4/24 hours). This equivalent to 4 hours in excel. Inbound, set maximum value to 1 (24 hours). And it's done.

1. Now the chart is more readable. Select the daylight part and click on the plus sign. Click on the plus sign. Check data labels. Make data labels black. Add a chart title and we are done. Finally, the chart will look like this.

The sunrise and sunset chart is ready. The time shown in the orange area is the average hours of daylight in that month.

So yeah guys, this how you can make a sunrise sunset chart in excel. This is quite easy if you know the basics of charts in excel. I have used excel 2016 in this example but this can be made in excel 2007 to 2019. Let me know if you have any doubts regarding this article or any other excel topic of advanced excel or VBA.

Download file:

Related Articles:

Pareto Chart and Analysis

Waterfall Chart

Excel Sparklines : The Tiny Charts in Cell

Speedometer (Gauge) Chart in Excel 2016

Creative Column Chart that Includes Totals

4 Creative Target Vs Achievement Charts in Excel

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel