7 Steps to Create a Spreadsheet for Solar Power Generation | Practical for Real-World Use

Table of Contents

• Concepts to organize before creating a spreadsheet

• Step 1 Separate input items and create the sheet's foundation

• Step 2 Create a field to calculate equipment capacity

• Step 3 Set a baseline value for annual power generation

• Step 4 Create adjustment fields for orientation, tilt, shading, and losses

• Step 5 Expand into monthly power generation

• Step 6 Calculate self-consumption and surplus electricity

• Step 7 Link through to fields for confirming electricity cost savings and payback

• Tips for making the spreadsheet easy to use in practice

• Summary

Concepts to Clarify Before Creating a Spreadsheet

When calculating solar power generation, in practical work you can first make sufficiently effective estimates using a spreadsheet even without a dedicated analysis environment. In fact, spreadsheets are a very powerful tool because they let you view in a single workflow the comparison of system capacities, the organization of conditions for each roof surface, the checking of monthly generation, the grasp of self-consumption and surplus energy, and the amount of electricity cost reduction and the direction of payback. Especially for practitioners who search for "solar power generation calculation," it is more important to see at a glance which items should be entered, the order in which adjustments should be made, and what to look at in the end than to memorize the formulas themselves.

Therefore, the first thing you should sort out when creating a spreadsheet is to keep inputs, calculations, and outputs separate. If you separate the area where you simply list the figures collected on site, the area where you convert those figures into annual kWh and monthly kWh, and the area where you finally display self-consumption and reduction amounts, the sheet becomes much easier to work with. Conversely, if you cram input values, formulas, and results into a single column, when you review the sheet later you won't be able to tell what is an assumption and what is a result, and even making small changes to the conditions becomes difficult.

Also, before creating the spreadsheet, it is important to be clear in your own mind about the difference between kW and kWh. kW indicates the size of the system. For example, figures like 5 kW or 10 kW represent system capacity. On the other hand, kWh is the actual amount of electricity generated. Numbers such as 5,000 kWh per year, 400 kWh per month, or 14 kWh per day are the result of how much the system has worked. In a spreadsheet, if you don’t treat these two separately, discussions about system size and generated energy can easily become mixed. In practice, you need to switch which unit you look at: compare system capacities in kW, and compare self-consumption, electricity sales, or savings in kWh.

Also, with spreadsheet calculations for solar power generation, it's important not to try to create a perfect single sheet from the start. First, make a simple version that produces an initial annual generation value, and then gradually improve accuracy by adding orientation correction, shading correction, a monthly breakdown, self‑consumption calculations, and checks of the economic impact; this approach is far more practical in actual work. If you try to pack everything in from the beginning, the number of items becomes excessive and the overview actually gets worse. Thinking of the spreadsheet as something to be developed over time makes planning its structure considerably easier.

This article explains, following that approach, the procedure for assembling a spreadsheet suitable for practical use, divided into seven steps. Rather than merely introducing formulas, it summarizes the sheet’s rationale, the roles of columns and fields, and how to build it so it can be easily modified later. To make it easy to understand even without diagrams, it organizes in words the meanings of input items and the connections between those items and the formulas.

Step 1 Separate input items and create the sheet's foundation

The first step is to separate the input items and build the foundation of the spreadsheet. If you carefully create this part, the subsequent calculations become surprisingly easy. A common practice in the workplace is to start entering numbers in the first empty cell and then keep adding formulas as you go. With this method you can perform the calculations once, but when you want to change conditions or reuse the sheet for another project, you can no longer tell which cells are inputs and which are calculation results.

First, the idea of creating an area that summarizes only the input items is effective. Here you gather the values brought from the site, such as project name, building type, region, per-surface area for equipment installation, output per panel, assumed number of panels, orientation, tilt, presence or absence of shading, provisional loss rate, provisional self-consumption rate, and so on. The important point is that, in principle, you do not include formulas in this area and complete it with manual entry or selection inputs. That way, you can see at a glance which input conditions are being used.

Next, separate the area for calculations. Here, place in order the results derived from the input values—system capacity, annual generation, monthly generation, self-consumption, surplus electricity, reduction amount, confirmation of payback, and so on. By separating the input fields and the calculation fields, it becomes clear which numbers are assumptions and which are results. In practice, simply having this division makes it much easier to explain things to stakeholders.

Also, when handling projects with multiple roof planes, it's convenient to incorporate the idea of per-plane input from the outset. For example, if you separate them into units such as south-, east-, west-, and north-facing planes, later orientation corrections and shading corrections will be easier. Whether for detached houses, factories, or warehouses, designing the sheet to accommodate per-plane differences greatly expands its range of application.

At this stage, there is no need to include complex formulas yet. What matters is keeping input and calculations separate, making the assumptions clear to anyone who looks at it later, and making it easy to reuse for other projects. For spreadsheets, the foundation—the structure—is more important than appearance. If the initial setup is well organized, about 70% of the later work will already go well.

Step 2: Create a field to calculate equipment capacity

The second step is to create a field that can calculate the installed capacity. In spreadsheet calculations of solar power generation, the starting point for everything is the installed capacity in kW. If this is unclear, the annual and monthly generation figures that follow will all be off. Therefore, it is important to first create a layout that can determine the installed capacity from the number of panels and the output per panel.

The idea is very simple. The system capacity (kW) is calculated as the number of panels × output per panel (kW). For example, if each panel is 0.4 kW, 12 panels equal 4.8 kW, and 15 panels equal 6.0 kW. In a spreadsheet, it's useful to set up this calculation so it can handle both per-orientation and total amounts. Calculate separately how many panels are on the south-facing, east-facing, and west-facing sides, then sum them to get the system’s total kW. Doing this makes it easier to apply orientation and shading corrections later on a per-face basis.

Also, before calculating equipment capacity, it is practical to incorporate the concept of usable area. Even if the roof surface looks large, in reality there are portions where you cannot place panels because of edge clearances, skylights, mechanical equipment, inspection routes, and so on. Therefore, even when deriving the number of panels from the area, it is more realistic to first determine the usable area and then consider how many panels can be arranged within that usable area. In spreadsheets, separating total area, usable area, and estimated number of panels makes it easier to prevent the kW at the service entrance from becoming an excessively large figure.

Also, it’s useful to make the equipment capacity column usable for comparative evaluation. For example, if you list multiple options such as 5 kW, 7 kW, and 10 kW proposals, you can immediately see how annual kWh and the savings change when the equipment capacity is altered. The strength of spreadsheets is their robustness to this kind of condition change. For that reason, the equipment capacity column should not be a single fixed value but built with a structure that is resilient to changes.

In practice, it's common to share only the installed capacity figures verbally, but if you make it possible to trace how many panels those kW come from and how many kW are on each surface, the overall reliability of the estimate increases. Installed capacity is the entry point to calculating solar power generation. Building this entry point properly greatly affects the quality of the entire spreadsheet.

Step 3 Set a reference value for annual power generation

The third step is to set a baseline value for annual power generation. Once the equipment capacity in kW has been determined, make it possible to derive an initial estimate of how much that system will generate annually. A useful concept here is the guideline for annual generation per 1 kW. Place the basic equation Annual generation (kWh) = Equipment capacity (kW) × Guideline of annual generation per 1 kW (kWh/kW·year) at the center of the sheet.

For example, if you use a baseline of about 1,050 kWh per kW per year in a given region, a 5 kW system would be about 5,250 kWh, a 10 kW system about 10,500 kWh, and a 20 kW system about 21,000 kWh as initial estimates. These values are still somewhat theoretical, but they are very useful for comparing system sizes and matching them against required generation. In practice, having this initial estimate first makes it easier to grasp the outline of the system.

The important point here is to make clear that this annual generation figure is not the final value but the pre-adjustment reference value. In the spreadsheet, keeping separate columns for the annual reference value and the post-adjustment actual generation makes the meaning of the numbers much easier to understand. If you cram everything into a single number from the start, it becomes hard to see why the annual kWh turned out as it did. It is important to separate the reference value from the actual (operational) value.

Also, to accommodate regional differences, it is convenient to make the cell for the estimated annual generation per 1 kW adjustable by input. Some regions have good solar irradiation conditions, while others are more affected by cloudy weather or snowfall. Because the baseline value changes by region even for the same installed capacity, it is better not to make this cell too fixed. In practice, a structure that can be reviewed on a per-project basis is easier to use.

Furthermore, this benchmark value can be used when you want to work backwards from the required generation. If you want 8,000 kWh per year, in a region with about 1,000 kWh per year per kW it appears that around 8 kW will be required. In other words, the annual benchmark is useful not only for reading generation from equipment capacity but also for determining the required equipment capacity from the required generation. In spreadsheets, it is very useful to set things up so that this bidirectional use is possible.

The reference value for annual power generation is the entry point for converting a facility’s sense of scale into the contours of its generation output. Simply including this field makes the facility’s performance—which could not be seen from capacity alone—much easier to understand. It is an indispensable core element for making spreadsheets practical for business use.

Step 4 Create a correction field for azimuth, angle, shadow, and losses

The fourth step is to create adjustment fields for orientation, tilt, shading, and losses. The baseline value for annual generation alone does not yet sufficiently reflect the site-specific conditions of the project. To produce figures that are usable in practice, these adjustments should be managed in separate fields and structured so they can be applied to the input values in sequence. Making this visible within the spreadsheet makes it easier to explain why the final value in kWh ended up as it did.

First, azimuth and tilt corrections. On surfaces that are more south-facing and on east–west surfaces, light reception conditions differ even with the same area and the same installed capacity. If the tilt is different, the way light enters in summer and winter also changes. Therefore, having azimuth and tilt correction factors for each surface and applying them to each surface's installed capacity or power generation makes the model much more site-specific. If you apply a single correction to the whole system at once, the differences between surfaces tend to be masked.

Next is shading correction. Shadows are not just a matter of presence or absence; their significance changes depending on how much they affect each face at different times of day. There can be large differences by site — east-facing surfaces with strong morning shade, west-facing surfaces with strong afternoon shade, or south-facing surfaces that receive long shadows only in winter, for example. In a spreadsheet, keeping shading corrections in a separate column lets you quickly reassess their impact when shading conditions change. A major advantage is that it becomes especially easy to update the numbers after on-site verification.

Also include a correction for system losses. You can treat losses in conversion equipment, wiring losses, output reductions due to high temperature, soiling, and so on as a single loss factor, or separate them if necessary. What matters is that it is clear what is included in the reference value and what is being subtracted as corrections. If this is ambiguous, the same loss can easily be counted twice or important losses can be overlooked.

The purpose of this procedure is to convert the input annual generation into an operational value by applying site conditions. For example, even if the annual baseline is 10,500 kWh, after applying orientation correction, shading correction, and loss correction, the operational value can easily become around 8,700 kWh. An important point to make spreadsheets usable in practice is not to treat this difference as a single black box, but to make it clear how much each correction changed the value.

Method 4 Calculate self-consumption and surplus electricity separately

The fifth method is to calculate self-consumption and surplus electricity separately. Even if you can see the annual generation and the corrected actual generation, that alone is not enough to fully assess the value of the installation. This is because the significance of the installation changes greatly depending on how much of the generated electricity can be used on-site. In spreadsheets, structuring the data so that not only the total generation but also the self-consumption and surplus amounts are separated makes it highly practical for operational use.

The basic idea is that surplus electricity (kWh) = generation (kWh) − self-consumption (kWh). For example, if annual generation is 10,000 kWh and 4,000 kWh of that can be used on-site to meet daytime demand, the surplus is 6,000 kWh. In a spreadsheet, keep generation, self-consumption, and surplus electricity in separate columns so that the meaning of each is clear.

The reason the concept of self-consumption is important is that even with the same total power generation, the value of the installation can differ. Facilities with high daytime loads tend to have higher self-consumption, so the system’s benefits are felt more strongly. Homes that are often unoccupied during the day tend to have more surplus, and a large portion of the generated power may be sold back to the grid or counted as surplus. In other words, this distinction is necessary to avoid treating generation output itself as the system’s benefit.

Also, if linked to the monthly sheet, this breakdown becomes even more useful. For example, in summer generation is high and self-consumption may also increase due to air conditioning demand. In spring and autumn, even if generation is high, demand may be relatively low and surpluses may increase. In winter generation is low, surpluses are small, but the self-consumption rate may be high. These differences are hard to see in an annual aggregate, and having them together with monthly data makes them much easier to understand.

Having this setup in a spreadsheet makes it easier to see what it means when you change the installed capacity. For example, when you increase the installed capacity, you can immediately see whether the increase is in self-consumption or in surplus. This is very useful for judging the appropriateness of the facility size. In other words, separating self-consumption and surplus electricity is an important step for connecting generation calculations to practical value judgments.

Method 5: Calculate the electricity cost savings and payback period

The final method is to calculate by linking power generation to electricity bill savings and the payback period. The greatest strength of a spreadsheet is that it lets you carry generation in kWh straight through to an estimate of economic impact. If you can organize installed capacity, annual generation, self-consumption, and surplus electricity, you can handle the resulting electricity bill savings and payback period with much greater clarity. In practice, a spreadsheet that allows you to view everything continuously like this is overwhelmingly easier to use.

As an approach, first multiply the amount of self-consumed energy by the unit price of purchased electricity to reveal the outline of the electricity cost savings. If there is surplus generation, treat that as a separate value. The important thing is not to mix the self-consumed portion and the surplus portion as if they were the same figure. Self-consumption represents the effect of reducing purchased electricity, while surplus is evaluated on a different axis. Keeping this distinction in your spreadsheet makes it much easier to explain the value of the equipment.

Furthermore, once you can see the annual economic benefit, you can also get an estimate of the payback period by comparing it with the total installation burden of the equipment. Here, too, what matters is not the initial generation figure but using the adjusted actual generation, self-consumption, and surplus. Otherwise, the payback period tends to become an overly optimistic number. In other words, the payback calculation is determined not just by the final division at the end but by how many practically achievable kWh you can produce beforehand.

The advantage of this method is that when you change the system capacity you can immediately see not only the power generation but also the differences in savings and payback. For example, when comparing a 5 kW option and a 7 kW option, if you line up not only the annual kWh but also self-consumption, surplus, amount of savings, and the payback period, it becomes much easier to explain which is reasonable for that project. This is the major reason for using spreadsheets in practical work.

Also, by linking it to monthly sheets, you can see differences in reduction amounts caused by seasonal variation. You can understand the structure—summer has higher generation and demand so self-consumption tends to increase; spring tends to have larger surpluses; and winter often suffers from insufficient generation—allowing you to organize the annual economic effects in a more convincing way. In other words, a spreadsheet is not merely a calculator but can be used as an organizing table to explain the value of the equipment.

Common mistakes in Excel calculations

If you implement the five methods covered so far in a spreadsheet, you'll end up with a very powerful sheet that can be used in real-world work. However, there are mistakes that tend to occur precisely because it's a spreadsheet. The most common is mixing input values and formulas. If it's unclear which cells are intended for manual input and which are calculation results, the meaning of the numbers can easily be broken when you modify them later. Therefore, you should always separate input cells from calculation cells.

The next most common mistake is using the standard value for annual generation as if it were the final value. The value obtained by multiplying system capacity by an annual generation guideline per 1 kW of capacity is a convenient starting point, but it often does not sufficiently account for orientation, shading, and losses. If you use that starting value as-is for self-consumption and payback period calculations, the figures tend to be overly optimistic. In spreadsheets, it's important to keep the baseline values and the adjusted practical values separate.

Also, it's risky to discuss self-consumption or reduction amounts using only annual values without creating monthly sheets. Even if the annual total is the same, if the distribution across spring, summer, autumn, and winter differs, the self-consumption rate and surplus will change. One of the advantages of spreadsheets is that they are easy to expand month by month, so it's a waste to stop at annual figures alone. At a minimum, it's more practical in real-world use to have a structure that allows you to check seasonal differences at least once.

Furthermore, it is common to leave the meaning of correction coefficients ambiguous. If orientation corrections, shading corrections, and loss coefficients are mixed together, it becomes easy to double-count them or, conversely, to overlook important losses. More important than simply applying a coefficient is making clear what that coefficient represents.

Spreadsheets are convenient, but precisely because they're convenient, stuffing too much into a single sheet can actually make things harder to see. What you need is not more numbers, but visibility into the relationships between numbers. In that sense, a tip for preventing mistakes is to separate the structure and make your assumptions explicit.

Summary

To build a spreadsheet for photovoltaic power generation that can be used in practice, it's easiest to follow seven steps: first separate the input items to create the foundation; create a field to calculate the system capacity; set a baseline value for annual generation; prepare adjustment fields for orientation, tilt, shading, and losses; calculate self-consumption and surplus electricity separately; and finally link everything through to electricity cost savings and the payback period. Simply keeping this order in mind turns a spreadsheet from a mere collection of formulas into a practical decision-making tool.

What is especially important is to keep the annual generation entered as input separate from the adjusted actual generation. It is also important to distinguish self-consumption from surplus electricity and not to assess the value of the system solely by the total kWh. Spreadsheets have the major advantage of being robust to changes in assumptions, strong for comparing multiple scenarios, and effective for explanations; to leverage these strengths, it is essential to clearly separate the roles of input, calculation, and output.

Furthermore, if you truly want to improve the accuracy of such spreadsheet calculations, you need to accurately understand the on-site conditions. If the roof surface orientation, positions of obstacles, elevation differences, and the way shadows fall are ambiguous, then no matter how neatly you build the spreadsheets, the final kWh figure will tend to fluctuate. In particular, orientation, shading, and layout conditions are aspects where the local positional relationships readily translate into differences in energy output.

In that regard, as a means of accurately capturing the positional relationships on site, LRTK — an iPhone-mounted GNSS high-precision positioning device — is extremely effective. Because it makes it easier to accurately record the positions of roof edges and obstacles on site, it becomes easier to improve the accuracy of power generation estimates that account for azimuth, shading, and layout conditions. If you want spreadsheet calculations of solar power generation to be truly usable in practice, properly capturing site conditions with measures like LRTK is a major advantage.