Thanks for your feedback on our earlier blogs. We have already covered how to create a summary report quickly via Excel VBA. In this blog, we will cover the charts in excel.
If you have not gone through the earlier blog, then please go through this link: Create Summary Reports Quickly via Excel VBA.
In this blog, we will try to prepare how to create sales summary report from your raw data using charts in excel. You don’t need to prepare the report again and again. But the interesting thing is that there is no column name by “Quarter”. At first, you need to create a column by this name “Quarter” and then you can create the report. Your report should look like below.
If you chose any quarter from the above, then at one time you will get four reports at a time, which is Region wise, Market wise, product wise and Business Segment-wise sales summary from my “Raw Data” sheet. We will go step by step to create this report.
I have a huge record set almost 68,000 records in my file which look like below.
So I have the 5 columns which are important to create the report. Now if you look very closely you will notice that there is no such column by “Quarter”. So you need to create this column. So what we will do is we will create a new sheet of this sheet so that our original data remains intact. So let’s create the same using VBA. I have written the below code which will help us to create a new sheet and give the name of the sheet as “Copy of Raw Data” and then it will insert a new column after “Invoice_Date” (Column M”) and also extract the Quarter of the respective date. Here is the code
Sub CopyofNewSheet() Dim wsToCopy As Worksheet, wsNew As Worksheet Set wsToCopy = ThisWorkbook.Sheets("Raw Data") Set wsNew = ThisWorkbook.Sheets.Add wsNew.Name = "Copy of New " & wsToCopy.Name wsToCopy.Cells.Copy wsNew.Cells Sheets("Copy of New Raw data").Select Range("M2").Select ActiveCell.EntireColumn.Offset(0, 1).Insert Range("N1").Value = "Quater" lr = Cells(Rows.Count, 13).End(xlUp).Row For x = 2 To lr Cells(x, 14).Value = "Quarter " & Application.WorksheetFunction.RoundUp(Month(Cells(x, 13)) / 3, 0) Next x End Sub
Your code will look like below and if you run the below code you will get a new sheet and the quarter of the date also.
Now in a new sheet (quarter wise summary) I have drawn a new shape and assign the same code with that shape. So that when it clicks it will create a new sheet with the name of “Copy of New Raw Data”. But wait…will it work all the time? It is good for the first time. But when you click next time the sheet is already there so the code will give an error. So you need to delete the sheet first. In that case, we need to modify our code a little bit.
Read related article: Merge Multiple Excel Workbooks into a Spreadsheet using Excel Macro
Here is the below code where it will then check the sheet name exists or not and if it exists, then the code will delete the sheet and then the rest of the code will again create the sheet with all other functionality.
Now, this time, it is fine. We will now concentrate on the next part. Now we need to create all the segments for which we need to create all the reports. All we need to do is we need to apply an advanced filter for all the segments and create the list. I have created the list in this way and arranged on the sheet and my sheet looks like below:
Now we need to create a validation list for the quarters. You can simply create validation drop down list, but this will not be dynamic. As because then you need to click on the “Create Summary” Tab which will help us to create the new sheet with fetching “Quarter”. So, in that case, we will draw a combo box from ActiveX Controls from the developer box.
Now we need to set the value for this box. And the value should be Quarter 1, Quarter 2, Quarter 3 and Quarter 4. So I have created a named range and put the same name in “ListFillRange” properties. After setting this property your value should come in the box.
Now we will go one by one to create the report:
Region wise Summary:
As our range is fixed the code will be very simple. Please use the below code and run the code manually to check whether it is populating the result or not. If we are getting the result for the same then we can apply the same for the others.
I have used simple Sumifs functions. And it is working fine when I check the total sales of the regions.
I have used the same logic for all the other segments. Now all you need to do is whenever I change the Quarter from the drop down it will automatically change the total sales as per the value selected in the box.
Now we will create four charts in excel from the values and have given the chart name also. Finally, it looks like below:
Now if you change the quarter from the drop down the value will be changed and at the same time, the chart will also be changed as its corresponding value is changing.
Preparing such kind of reports is not difficult, but you need to know the technique which we are trying to cover each and every day.
Let us know your feedback and also, let us know what kind of blog will help you in your day to work life. We will try to cover that part also. Thanks for reading the blog.