- HOW TO PLOT A GRAPH IN EXCEL WITH FORMULA HOW TO
- HOW TO PLOT A GRAPH IN EXCEL WITH FORMULA SERIES
- HOW TO PLOT A GRAPH IN EXCEL WITH FORMULA DOWNLOAD
But these formulas use INDEX-MATCH instead. Normally, we’d use the SUMIFS function to return values from the Table with monthly values. The formulas that return numbers in the green cells use the pattern shown here:ī15: =INDEX(MonthlyActual,MATCH(DATE($A15,B$13,1),MonthlyDates,0))*ScaleReport Here are the top few rows of the staging table for chart 1, the chart with one line:Īnd here’s the top of the staging table for Chart 2:
HOW TO PLOT A GRAPH IN EXCEL WITH FORMULA DOWNLOAD
You’ll find these Tables in CyclePlotA.xlsx workbook, that you can download here. And in all of them, I applied a green fill to show you the areas of the staging tables that each chart references. And the key to each chart is the structure of the staging table.Īll of them use formulas to return data from the same dataset. Step 2: Set Up the Staging TablesĮach of the previous charts gets its data from one or more staging tables. Typically your data will be in an Excel Table. You just need to have your data in Excel so that you can access it easily-usually with the SUMIFS function. Here’s the first step: Step 1: Set Up Your Data. You can download the actual workbooks here.įour steps are needed to create a Cycle Plot like Figure 3. To keep this article from turning into a short book, I’m going to explain only the most important elements of the original workbook that contained these charts.
HOW TO PLOT A GRAPH IN EXCEL WITH FORMULA HOW TO
How to Create a Cycle-Plot Chart in Excel But because the total variation is only about 2% of sales, the effect isn’t significant in this instance.Īlso, because the two sets of columns are virtually identical, we know that this pattern hasn’t changed much between the first and second half of the total reporting period. Here, we see that sales tend to fall in the middle of the week, but rise on the weekends. But then I wondered whether using column charts provides more useful information for daily sales. I first used the cycle plot for the weekly data. Still another way to show seasonal data is by the day of the week: Because our high-sales months are growing more quickly, this plot shows that sales are becoming more seasonal. This data shows that June’s sales are growing more quickly than those of the other months, and that sales in November and December are growing more slowly. And the fat blue lines show the trend in each month’s sales during the years. The gray horizontal lines show the average sales for each month during those years. The black lines show the trend in performance for each January, each February, and so on for all eleven years.
In this Excel chart, we easily can compare the performance of each month of the year. Naomi Robbins, writing in her book, Creating More Effective Graphs, introduces another way to display the same data, using Cycle Plots: But with many years displayed, as above, the swarm of lines becomes meaningless. With only a few years displayed, we could see the typical trends more easily during the year.
HOW TO PLOT A GRAPH IN EXCEL WITH FORMULA SERIES
Rather than showing a continuous trend as in the chart above, we use Excel to plot one data series per year.īut this approach offers little improvement. ( You can download the two workbooks discussed in this article here.)įor example, which months have the most sales? Are sales becoming more seasonal or less so? On average, how much do we sell each June? Which month is growing most quickly? Most slowly?įigure 2 shows another common way to present data like this.
This is because the finer details are obscured by the significant seasonality of the sales. With the exception of a dip during the years of the Great Recession, sales seem to be on the rise.Īlthough this chart makes the general pattern obvious, it provides little help in understanding the details of the sales performance.
This Excel chart shows the continuous trend in sales over an eleven-year period. If your company’s sales are seasonable, you’ve probably seen a chart that looks something like the first one below.