How to Combine Daily and Monthly Data in Excel Chart

There are a few reasons why you might want to combine daily and monthly data in an Excel graph. For example, if you’re tracking a stock price, you might want to see both the overall trend (represented by the monthly data) and the more detailed fluctuations (represented by the daily data). Another reason to combine daily and monthly data is if you’re tracking data that fluctuates a lot from day to day but has a general trend over time. Whatever the reason is, here you will learn to combine the daily and monthly data in an Excel Chart with ease.

How to Combine Daily and Monthly Data in Excel Graph: 3 Easy Steps

In the following screenshot, you can see two separate datasets. The first one represents daily data and the second one represents monthly data. Using these two datasets, I will show you to combine both the daily and monthly data in an Excel graph with some easy steps.

Step-1: Extract Month from Date

At first, you need to create two blank columns named Monthly Data and Month as shown in the following screenshot. Later, you will use formulas in these two blank columns to extract monthly and month.

Now in the column Month, we will use a formula using the MONTH function to extract the months from the Date column. Now follow the steps below:

❶ Type the following formula in cell G5.

❷ Then press the ENTER button.

Here, the MONTH function will extract only the months from the dates mentioned in the range F5:F10. As it’s an array formula, the output will be automatically spilled over the range G5:G10.

Read More: How to Create Graph from List of Dates in Excel

Step-2: VLOOKUP Monthly Data into Daily Data

Now, you need to extract the monthly data from column H into column C. For this purpose, we will look up the data in column H using the VLOOKUP and the MONTH functions.

Now follow the steps below:

❶ Insert the following formula in cell C5.

❷ Then press the ENTER button.

❸ Now copy down the formula to the rest of the cells just double-click on the Fill Handle icon.

Now the monthly data from column H has been populated over the range C5 to C186.

Read More: How to Show Only Dates with Data in Excel Chart

Step-3: Generate the Chart Combining Daily and Monthly Data

So, you have successfully populated the monthly data over the daily data range. Now you can plot both the daily and monthly data on the same Excel chart.

To generate a graph,

❶ Select all the column headers of the first dataset as shown in the following screenshot.

❷ Then press and hold the CTRL & SHIFT keys. After that press the down arrow key.

This will select the entire first dataset.

❸ After that, go to the Insert tab.

❹ From the Charts group, click on the Recommended Charts command.

Next, the Insert Chart dialog box will appear.

❺ Go to the All Charts tab.

❻ Then click on Combo.

❼ Now select Clustered Column for the Monthly Data and Line for the Daily Data.

❽ Finally, hit the OK button.

Now you will get a chart both combining the Monthly Data as well as Daily Data in the same Excel Graph.

Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Conclusion

To sum up, I have discussed steps to combine daily and monthly data in Excel Graph. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.

Related Articles

• How to Plot Time over Multiple Days in Excel
• Excel Chart by Month and Year
• How to Change Date Range in Excel Chart
• How to Ignore Blank Cells with Formulas in Excel Chart

<< Go Back to Data for Excel Charts | Excel Charts | Learn Excel