Learn to Create a Cumulative Sum Chart in Excel: A Step-by-Step Guide

This comprehensive tutorial guides you through the process of creating a visually powerful cumulative sum chart in Excel. Cumulative sum charts are highly effective tools for tracking progress over time, often used in performance analysis, financial reporting, or quality control. By blending a standard bar chart with a cumulative line, we can easily visualize both individual period contributions and the overall running total.

The final result of this process will be a dynamic visualization that allows stakeholders to quickly understand how performance accumulates throughout the reporting period. We will use a typical sales tracking example to demonstrate the necessary calculations and charting techniques.

Below is the finished chart we aim to replicate, illustrating monthly sales (bars) against the year-to-date total (line):

Let’s begin the step-by-step implementation.

Step 1: Structuring and Entering the Source Data

The foundation of any effective visualization is well-organized data. Before we can calculate the cumulative totals, we must first establish a dataset that clearly separates the time periods (e.g., months) from the discrete values (e.g., sales figures) associated with those periods. Using clean column headers is essential for clarity when generating the chart later.

For this example, we will track the total sales for a specific product across twelve consecutive months. This requires three columns: the Month, the corresponding Sales, and a dedicated column for the Cumulative Sales calculation, which we will populate in the next step.

Set up your spreadsheet with the following structure, ensuring that your data points align correctly with the corresponding months:

It is important that your data range starts in row 1 with appropriate headers (Month, Sales, Cumulative Sales) to ensure that Excel correctly interprets the series when generating the initial chart. Having the monthly sales figures in column B is crucial for the formula application in Step 2.

Step 2: Mastering the Cumulative Sum Calculation

The core component of this chart is the accurate calculation of the running total. A cumulative sum is created by adding the current period’s value to the total sum of all preceding periods. In Excel, this is achieved efficiently using the SUM function combined with a strategic use of absolute and relative cell referencing.

The formula must reference a fixed starting point (the first sales figure) and a dynamic endpoint (the current month’s sales figure). The following formula, entered into cell C2, achieves this balance:

=SUM($B$2:B2)

Let’s analyze this formula: the reference $B$2 uses absolute referencing (indicated by the dollar signs), meaning that when the formula is copied down, the start of the range will always remain locked to the value in B2. Conversely, B2 uses relative referencing. As you copy the formula down to C3, C4, and so on, this relative reference will automatically adjust to B3, B4, and thus expand the summing range (e.g., $B$2:B3, $B$2:B4, etc.).

Type this formula into cell C2 and then use the fill handle (the small square at the bottom-right corner of the cell) to copy and paste it down to the remaining cells in column C. This will instantaneously populate the cumulative sales figures for the entire year, preparing the data for visualization.

Step 3: Generating the Initial Clustered Column Chart

With the necessary data prepared, the next step involves generating a standard chart that incorporates both the monthly sales figures and the cumulative totals. Although our final output requires a combination chart (bars and a line), we must start with a basic column chart format to initiate the visualization process in Excel.

To begin, highlight the entire cell range containing your data, including the headers: A1:C13. It is vital to include the headers (Row 1) so Excel can automatically assign appropriate labels to the data series.

Navigate to the Insert tab located in the top ribbon menu. Within the Charts group, select the option for Clustered Column. This action will immediately render a standard column chart displaying both the sales and cumulative sales data series as vertical bars.

The following initial chart will be created. Notice that the cumulative sales bars (orange) are disproportionately taller than the monthly sales bars (blue), making the monthly sales difficult to interpret. This is because the cumulative totals are much larger than the individual monthly values. We will correct this in the subsequent step by introducing a secondary axis.

Step 4: Transforming the Chart Type for Visualization

To create the characteristic cumulative sum visualization, we need to convert the cumulative sales series from a bar format into a line format and move it to a secondary vertical axis. This dual-axis approach is critical for effective data visualization when dealing with two series that have vastly different scales.

To perform this transformation, right-click anywhere within the chart area and select Change Chart Type from the context menu:

In the dialog box that appears, you need to select the Combo option (Combination Chart) from the list of chart types. Within the Combo settings, locate the table that lists your data series (Sales and Cumulative Sales). For the Monthly Sales series, ensure the Chart Type is set to Clustered Column. For the Cumulative Sales series, change the Chart Type to Line.

Crucially, check the box under the Secondary Axis column next to the Cumulative Sales series. This action assigns the cumulative line to its own vertical axis on the right side of the chart, allowing it to be scaled independently of the monthly sales bars.

Click OK to apply these changes. The chart will immediately update, providing the desired visual representation. The blue bars now clearly represent the monthly sales, scaled according to the primary Y-axis (left), and the orange line represents the running cumulative total, scaled according to the secondary Y-axis (right).

Step 5: Refining and Customizing Your Cumulative Sum Chart (Optional)

While the chart is now technically correct, taking a few additional steps for customization can significantly enhance its aesthetic appeal and professional quality. Customization ensures the chart is easily digestible and fits the overall reporting context.

Start by adding a descriptive chart title (e.g., “Monthly Sales and Cumulative Performance”) that immediately informs the viewer of the data being displayed. You should also consider labeling both the primary (Monthly Sales) and secondary (Cumulative Total) axes clearly to eliminate ambiguity regarding the scales.

Further aesthetic refinements include adjusting the color palette to align with corporate branding, modifying the line style (e.g., adding markers to the line for clarity at each data point), and adjusting the gap width of the bar columns. Reducing the gap width can make the bars appear more substantial and easier to read. These optional customizations transform a functional graph into a polished analytical tool.

The final customized plot should look similar to this enhanced version:

Step 6: Additional Resources for Excel Mastery

Understanding the creation of cumulative sum charts is a powerful addition to your Excel skillset, particularly in analytical and reporting roles. This technique relies heavily on core Excel features such as referencing and chart manipulation, which are fundamental to advanced data analysis.

To continue building expertise in Excel and tackle other common analytical challenges, explore tutorials on related topics. Common tasks often involve calculating moving averages, performing statistical regression analysis, or formatting conditional data.

The following list provides areas where further tutorial exploration would be beneficial:

• Creating dynamic dashboards using pivot tables and slicers.
• Implementing advanced conditional formatting rules based on cumulative totals.
• Utilizing lookup functions (VLOOKUP, XLOOKUP) for integrating data from multiple sheets.