12/01/2013

Microsoft Excel: Energy Management at Your Fingertips

A valuable tool may be sitting unused on your desktop

 

Microsoft Excel, a common computer program, can help you perform basic energy management and analysis. Enlist it now to capture your building’s baseline and implement future projects.

A valuable energy tool may be sitting unused on your computer desktop.

Microsoft Excel is well suited for energy management and advanced analysis, but it is underutilized by most businesses. Many are not aware of several shortcuts that could provide insight and save time.

Excel is not a replacement for sophisticated programs such as eQUEST, TRACE, or HAP, which have established their places in energy engineering. However, think of Excel as a Swiss Army knife that can perform many functions sufficient for day-to-day energy engineering and financial analysis.

Most organizations don't provide formal training on Excel, so you must pursue tips, tricks, and techniques on your own. The 2013 version of Excel has over 450 functions, but don't let that overwhelm you.

To effectively employ Excel, you should be using units, charts, keyboard shortcuts, and functions for date and time. Below are some must-know features.

Use Units
To display a unit of measure (kWh, Therms, etc.) in a cell without changing the number to text, create a custom number format following the steps below:

  1. Select the cells containing the numbers you want to format.
  2. Press the keyboard shortcut "Ctrl +1" to bring up the "Format cells" dialog box.
  3. Choose the "Custom format" option.
  4. In the type field, select a number format for your cells and type the unit of measure enclosed in quotes to the right of the number format.

You can use this formatted cell in any calculations, and once you have created this format, you can use it anywhere in the workbook.

Chart Correlations
A combination chart is used to show one chart on top of another. This allows you to better study data sets to determine whether any relationships exist. For example, plotting the electricity consumption and cooling degree days (CDD) on the same chart helps users compare and see if there is a relationship between weather and electricity consumption.

Make a combination chart using the following steps:

  1. Build a chart with just a single chart type. Select both kWh and CDD data and insert a chart column.
  2. Under chart tools, go to the Format tab, and choose the CDD series in the plot area.
  3. Go to the design tab and change the chart type to line chart.
  4. Since the scale of CDD is very small compared to the kWh, all the CDD values are crowded at the bottom and no insight is gained.
  5. Go to the Format tab, select the series CDD, click on "Format selection" just beneath it and choose the secondary axis radio button.

Tips and Tricks
Below are miscellaneous pointers that energy engineers and facilities managers can use to speed up analysis.

  • Use keyboard shortcuts to quickly navigate and select cells or ranges if you work with hour-by-hour data.
  • Is the 15-minute kW interval data for one year from the utility occupying over 35,000 rows? Control the data automatically with information functions for any missing values, text values, or outliers, then use the offset function to transform the data into 365 rows. Apply the date and day functions, and analyze the data by month or day of the week. Generate load duration curves.
  • Need to sort values based on a rule, find duplicate values, or highlight the top 20 peak hours in your kW data? Use conditional formatting to quickly unearth that information.
  • Want an equation that depicts the chiller input kW with varying chiller load? Use Excel's regression capabilities.
  • Use the date and time functions to chart peak loads by month or weekday for the entire year.

 

Eric Woodroof, Ph.D., is a board member of the Certified Energy Manager (CEM) Program. He can be reached at eric@profitablegreensolutions.com.

Rama Ayer of consulting firm Energy Efficiency and Demand Management, Inc. (EE+DM) contributed most of this content. She can be reached at rama@eedminc.com.

 


Visit our website today to learn about the design flexibility of a Morton building and the endless possibilities of partnering with our designBUILD team.


Wood construction is both cost and energy efficient. Check out Morton Buildings and our designBUILD team online today to discover all the benefits of post-frame construction.


When choosing a metal-clad building for your next construction project, consider Morton Buildings, Inc., and their designBUILD team, we’ll make your dream a reality.

We Can Help You Reduce Energy by 30%

Our mission is to help our customers manage their buildings' energy costs, improve reliability, and enhance performance while having a positive impact on the environment.
CLICK HERE to find out how.

Add highly responsive multi-zone comfort to any building project, in any climate. Our CITY MULTI H2i R2- and Y-Series VRF systems give you flexibility to fit the needs of any building. Enjoy 100% heating capacity at 0°F outdoor ambient, and 85% heating capacity at -13°F outdoor ambient.  For more information, log on to www.mitsubishipro.com


Visit our website today to learn about the design flexibility of a Morton building and the endless possibilities of partnering with our designBUILD team.


Wood construction is both cost and energy efficient. Check out Morton Buildings and our designBUILD team online today to discover all the benefits of post-frame construction.


When choosing a metal-clad building for your next construction project, consider Morton Buildings, Inc., and their designBUILD team, we’ll make your dream a reality.

We Can Help You Reduce Energy by 30%

Our mission is to help our customers manage their buildings' energy costs, improve reliability, and enhance performance while having a positive impact on the environment.
CLICK HERE to find out how.

Add highly responsive multi-zone comfort to any building project, in any climate. Our CITY MULTI H2i R2- and Y-Series VRF systems give you flexibility to fit the needs of any building. Enjoy 100% heating capacity at 0°F outdoor ambient, and 85% heating capacity at -13°F outdoor ambient.  For more information, log on to www.mitsubishipro.com

 
comments powered by Disqus

Related Products