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:
- Select the cells containing the numbers you want to format.
- Press the keyboard shortcut "Ctrl +1" to bring up the "Format cells" dialog box.
- Choose the "Custom format" option.
- 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:
- Build a chart with just a single chart type. Select both kWh and CDD data and insert a chart column.
- Under chart tools, go to the Format tab, and choose the CDD series in the plot area.
- Go to the design tab and change the chart type to line chart.
- 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.
- 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 [email protected].
Rama Ayer of consulting firm Energy Efficiency and Demand Management, Inc. (EE+DM) contributed most of this content. She can be reached at [email protected].
