How to Make a Monthly Report in Excel – Quick Steps

Step 1: Import Your Dataset

This is the sample dataset.

Step 2: Create Pivot Tables from the Dataset

• Select a cell in the dataset and press Ctrl+A.

• Go to the Insert tab on the ribbon.
• In Tables, select PivotTable and choose From Table/Range.

• Select New Worksheet.
• Click OK.

• Go to the new sheet and select Day and Total Sales from the PivotTable Fields.

• The pivot table with the column headers will be displayed.

Step 3: Insert a Daily Report Chart

• Select a cell in the pivot table.
• Go to the Insert tab on the ribbon.
• In Charts, select Recommended Charts.

• In Insert Chart, select Column and the type of column chart you want. Here, Stacked Column.
• Click OK.

• A column chart will be created.

• You can modify the chart style by selecting it and using the plus and the brush icons on the right.

• This is the output (Style 8 was selected and the legend was removed).

In Insert Chart, choose a line plot.

• Select a cell in the pivot table.
• Go to the Insert tab and select Recommended Charts from the Charts group.

• In the Insert Chart dialog box, select Line.
• Choose a type of line chart.

• Click OK. The line chart will be displayed.

• You can change chart elements and styles, by selecting the chart and using the plus and the brush icons.

• This is the output (chart styles were changed and the legend was removed).

Read More: How to Make Daily Sales Report in Excel

Step 4: Insert a Weekly Report Chart

• Use the pivot chart in step 2 or create a new pivot table.
• Right-click one of the cells in the days’ column.
• Select Group.

• In Grouping, select Days and unselect Months.
• In Number of Days, select 7.

• Click OK.
• This is the output.

• Select one of the cells in the pivot table and go to the Insert tab.
• In Charts, choose Recommended Charts.

• In the Insert Chart dialog box, select Pie and a type of pie chart.

• Click  OK.
• A pie chart will be displayed.

• You can change chart elements and styles, by selecting the chart and using the plus and the brush icons.

• This is the output (the color palette was changed).

To create a line plot:

• Select a cell in the pivot table.
• Go to the Insert tab and in Charts, select Recommended Charts.

• In the Insert Chart dialog box, select Line and choose a style.

• Click OK.

This is the line plot.

• This is the output (style was changed and the legend removed).

Step 5: Generate a Final Report

Daily or weekly reports can be copied to a new sheet to create a monthly report.

Read More: How to Make Sales Report in Excel

How to Make a Report for Consecutive Months in a Year in Excel

Step 1: Import Your Dataset

In the following dataset data is tracked and recorded by months.

Step 2: Create a Pivot Table

Convert your dataset into an Excel pivot table:

• Select the dataset.

• Go to the Insert tab.
• In Tables, select PivotTable.
• Choose From Table/Range.

• Select New Worksheet and click OK.

• A new worksheet will be created.
• Go to the sheet and in PivotTable Fields select Month and Profit.

• A pivot table will be displayed.

Step 3: Insert a Chart from a Dataset

• Select a cell in the pivot table.
• Go to the Insert tab.
• In Charts, select Recommended Charts.

• In the Insert Chart dialog box, select Column on the left.
• Choose a type of column chart.

• Click OK.

A column chart will be created.

Step 4: Generate a Final Report

• You can modify the chart style by selecting it and using the plus and the brush icons on the right.
• This is the output.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Create an Expense Report in Excel
• How to Create an Income and Expense Report in Excel
• How to Make Production Report in Excel
• How to Make Daily Production Report in Excel
• How to Make a Monthly Expense Report in Excel

<< Go Back to Report in Excel | Learn Excel