Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


Steps to Combine Daily and Monthly Data in Excel Graph

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.

=MONTH(F5:F10)

❷ 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.

Combine Daily and Monthly Data in Excel Chart

Read More: How to Create Graph from List of Dates in Excel (with Easy Steps)


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.

=VLOOKUP(MONTH(B5),$G$5:$H$10,2,FALSE)

❷ Then press the ENTER button.

Formula Breakdown

  • MONTH(B5) extracts months from the data in cell B5.
  • $G$5:$H$10 is the lookup array here.
  • 2 refers to the second column in the range $G$5:$H$10.
  • FALSE refers to the exact match between the output of MONTH(B5) and $G$5:$G$10.
  • VLOOKUP(MONTH(B5),$G$5:$H$10,2,FALSE) extracts monthly data from column H.

Combine Daily and Monthly Data in Excel Chart

❸ 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.


Similar Readings


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.

Combine Daily and Monthly Data in Excel Chart

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.

Combine Daily and Monthly Data in Excel Chart

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

Combine Daily and Monthly Data in Excel Chart

Read More: How to Expand Chart Data Range in Excel (5 Suitable Methods)


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.


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. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo