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.

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

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

Similar Readings

• How to Select Data for a Chart in Excel (2 Ways)
• Selecting Data in Different Columns for an Excel Chart
• How to Change Chart Data Range in Excel (5 Quick Methods)
• Excel VBA: Get Source Data Range from a Chart (with Quick Steps)
• How to Format Data Series in Excel (with Easy Steps)

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.

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

• How to Change Data Source in Excel Chart (3 Useful Examples)
• Add Data to an Existing Chart in Excel (5 Easy Ways)
• How to Plot Time over Multiple Days in Excel (With Easy Steps)
• Change Chart Data Range Automatically in Excel (2 Easy Ways)
• How to Create Excel Chart Using Data Range Based on Cell Value
• Excel Chart by Month and Year (2 Suitable Examples)
• How to Edit Chart Data in Excel (5 Suitable Examples)