Excel Periodical Report

by tuenhidiy in Circuits > Microsoft

4216 Views, 2 Favorites, 0 Comments

Excel Periodical Report

Compare.jpg

Here are my tips for periodic consumption reports in Excel 2010. In the tutorial video below, this report tell us about specific consumption of electricity, water, oxygen, nitrogen per ton of finished products, according to weekly, monthly, quarterly, yearly. Actually all production data will be recorded and entered daily by operators. For understanding easily, in my tutorial, it is simulated in 2 years (2019 & 2020).

Creating an Excel Table

Creating an Excel table named "CONSUMPTION", with its columns as following:

  • Input Date from 1/1/2019 to 12/31/2020.
  • Week = WEEKNUM([@Date])
  • Month = TEXT([@Date],"mmm")
  • Quarter = "Q." & ROUNDUP(MONTH([@Date])/3,0)
  • Year = YEAR([@Date])

Entering consumption data at columns:

  • Electricity (kW)
  • Water (m3)
  • Oxygen (m3)
  • Nitrogen (m3)
  • Product (ton)

For simulation, I used a RANDBETWEEN(min, max) function to generate a random integer between given numbers (min, max). In reality, we will record & input these data daily during production.

Creating Pivot Table With Pivot Chart

  • Select any of the cells in your new data sheet and go to Insert Tab → Charts → Pivot Chart.
  • Enter table name "CONSUMPTION" at Table/Range Tab.

​Create a Calculated Field

To calculating specific consumption, go to PivotTable Tools → Options → Fields, Items, & Sets → Calculated Field: Enter at Name & Formula Tabs as follows:

  • Electricity (kW/ton)='Electricity (kW)'/'Product (ton)'
  • Water (m3/ton) ='Water (m3)'/'Product (ton)'
  • Oxygen (m3/ton) ='Oxygen (m3)'/'Product (ton)'
  • Nitrogen (m3/ton) ='Nitrogen (m3)'/'Product (ton)'

Choose Fields to Add to Report

In PivotTable Field List, we will choose fields to add to reports and put them correct areas:

1. Axis Fields:

  • Year
  • Quarter
  • Month
  • Week

2. Values:

  • Average of Electricity (kW/ton)
  • Average of Water (m3/ton)
  • Average of Oxygen (m3/ton)
  • Average of Nitrogen (m3/ton)

Insert Slicer

This useful feature is excellent for viewing and comparing varied combinations of your data with a simple click.

To add Slicers in Excel 2010:

  • Go to PivotTable Tools → Options → Insert Slicer.
  • In the Insert Slicers dialog box, click the check boxes: Year, Quarter, Month, Week.
  • We had totally 4 slicers and in every slicer window, we can click on any item which you want to filter.

Final Report

Finally you can submit weekly / monthly / quarterly / yearly reports to your managers, and you can also compare consumption between weeks of the month, month or quarter of the year or copy these charts to Microsoft PowerPoint for presentation.

  • Jan/ 2019 - Report

  • Comparing Quarter 1 & 2/ 2019 vs Quarter 1 & 2/ 2020 Report (hold CTRL key and click multiple items)

  • Sample Excel file for this instructable: Download