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