Creating Graphs Dashboard

Creating Graphs for Word Count Tracking

Creating graphs is one way to visualize your writing progress. It can provide quick insights, particularly over time as you record more information about your word counts in your spreadsheet. If you're unfamiliar with using Excel, you may want to start with the previous posts in this series, starting with the benefits of writing streaks, demystifying spreadsheets, improving the tracker, pivoting for more information, adding project details, and writing a formula to track streaks.

Creating Graphs 101

If you've already worked through the previous posts in this series, you've already set everything up for creating graphs of your writing progress. Excel does make it easy to create really bad graphs, fortunately it is also easy to create good graphs. First rule in creating graphs, keep it simple

Forget 3D styles, colorful backgrounds, and all of that sort of thing. Most of the time those sorts of features obscures the data and makes it more difficult to gain useful insight from the visualization. Try to use less as often as you can when you create graphs. Though the field of data visualization is deep and can be complex, for our purposes we'll look at a few visualizations of the data in the word count tracker.


The dashboard is nothing more than a tab we'll use to show visualizations and information from our data. To get started, I'll add a new blank worksheet and double-click on it to name it Dashboard.

Think of this as a sandbox. We can add features, change things, and make this look however we like. I'm going to start adding features. I might move them around later, but for now I want to see a few basic details.

Quick Formulas

First off, I want to know how long my longest streak was. I'm going to type “Longest Streak” in the first column. Next to it, I want to add a formula that gets the highest number from my streak count column.

=MAX(Words[Streak])Excel Max Formula<img class=”tve_image wp-image-1124″ alt=”Excel Max Formula” width=”409″ height=”349″ title=”Excel Max Formula” data-id=”1124″ src=”//” style=”width: 100%;”>

That's an easy formula. It uses the MAX function to get the highest number from the Words table and the Streak column. We can use the same formula to get the last day I wrote simply by changing the column referenced. As you type the formula, press TAB to accept the autocomplete suggestions (if you want). After entering the formula for the last day, it returns a number rather than the date. That's simply that the cell isn't formatted for the date.

With the number selected, choose the format you like from the dropdown list in the number section on the Home tab of the ribbon.

Creating Graphs With PivotCharts

Let's add a graph to the dashboard. On the ribbon's Insert tab, click the PivotChart button in the Charts section. It will open a dialog very much like what we saw previously when creating a PivotTable. The defaults should be fine, so click OK.

The PivotChart Fields panel works just like the PivotTable Fields panel. Since it's already using the data models, we have our tables available, and there's a box on the dashboard where it will draw our graphs.

I'm interested in seeing words written over time, so I'm going to drag the Date (Month) to the Axis, Words to the Values quadrant. I also want to add the Date (Year) to the Filters so that I can just set it to show this year.

Creating Graphs Dialog<img class=”tve_image wp-image-1126″ alt=”Creating Graphs Dialog” width=”387″ height=”346″ title=”Creating Graphs Dialog” data-id=”1126″ src=”//” style=”width: 100%;”>Creating Graphs PivotChart<img class=”tve_image wp-image-1128″ alt=”Creating Graphs PivotChart” width=”1448″ height=”886″ title=”Creating Graphs PivotChart” data-id=”1128″ src=”//” style=”width: 100%;”>

Modifying the PivotChart

Now that I've created the PivotChart, I'm going to modify it, cleaning up some things so that it shows what I want. First, there are some things I'll turn off.

  • Field Buttons. Those are the ugly gray buttons on the chart. On the plus side, they can be used to change what is being displayed. I just don't like them (and there are other ways to change things). I'll right-click one and choose to turn them all off from the menu.
  • Legend. I'm only showing one thing—words. I don't need a legend. I'll turn it off. If I want to change it later, I can. Up on the ribbon, under PivotChart Tools, I'll click on Design. And then I'll click the down arrow under the Add Chart Element button (on the far left) to turn off the legend. This is also one place where I can turn it back on later if I want.
  • Chart Gridlines. Under the Add Chart Element button, I'll go to the gridlines section and deselect the horizontal gridlines.
  • Vertical Axis. The vertical axis displays the word counts. In the Axes section under Add Chart Element, I'm going to turn that off as well. Instead, I'll turn on Data Labels and choose outside end.
  • Gridlines. This isn't the chart, but the dashboard worksheet. By default, Excel shows gridlines around each cell. They don't print, but they make it easier to see each cell. I don't need them for my dashboard so I'll turn them off by clicking on the View tab in the ribbon and uncheck the gridlines box. While here, I'm going to uncheck the formula bar and the headings as well so that I have a very clean dashboard.
  • Border. The PivotChart has a border around it. I'll turn that off by right-clicking the chart and click the Format Chart Area command. That opens a panel full of formatting options. Right now, with Chart Options selected in the dropdown, I want to expand the Border option and change it to No line. Then I'll click the x for this panel to close it.

That's looking much better. I want to move the chart up on my dashboard, I have a few other tweaks to make.

I want to change the title from “Total” to “Words.” Click, click again and then replace the title. In the PivotChart Fields panel, next to Date (Year) I want to click the down arrow on the right (doesn't appear until the item is selected). That will open the filter panel. I'll select 2018 and click ok. The chart changes to show the months from this year only.

I only have data for this month right now, so that's the only month the chart shows. As I track more writing sessions I can refresh the chart to see the updated information. I have one more change to make. I want the value to use a comma. In the values quadrant, where it says Sum of Words, I'm going to click the down arrow and choose Value Field Settings.

In the dialog that opens, I'll click the Number Format button, I'll choose Number as the format, check the Use 1000 separator box and set the decimal count to zero. Then ok and ok again. Click off the chart to deselect it and hide the PivotChart Fields panel.

Dark Matters Cover<img class=”tve_image wp-image-699″ alt=”Writer T-Shirt” width=”478″ height=”457″ title=”Have Fun Write Now” data-id=”699″ src=”//” style=”width: 100%;”>

Creating Graphs by Duplicating the PivotChart

I spent time with that PivotChart to illustrate a few of the possibilities available. I also wanted to emphasize simplicity when creating graphs. This monthly graph of word count won't have very many columns. It's easier to put the exact count above each bar than to look at the vertical axis.

Creating Graphs Simplicity<img class=”tve_image wp-image-1132″ alt=”Creating Graphs Simplicity” width=”836″ height=”359″ title=”Creating Graphs Simplicity” data-id=”1132″ src=”//” style=”width: 100%;”>

An easy way to create additional graphs is simply to select one that you already have and copy and paste a duplicate that you can modify. Use the PivotChart Tools section of the ribbon and modify the copy how you like. You can change the type of chart, the fields used in the chart, whatever you want. I created a new graph for the dashboard that show word count by the type of project.

On my dashboard, I put the pie chart right beneath the monthly word count. It gives me a quick view of the relative amount I've written on each type of project. If I wanted, I could have added a column to the monthly word count for each type. That would complicate that graph. Or, I could use a stacked column instead. That would show the same total words for the month, but the column would show the relative proportions for that time period.

If I don't stack them, I do want to change the color so that the blue of the “Novel” group isn't the same color as the total group.

Creating Graphs Pie<img class=”tve_image wp-image-1134″ alt=”Creating Graphs Pie” width=”442″ height=”302″ title=”Creating Graphs Pie” data-id=”1134″ src=”//” style=”width: 100%;”>

Avoiding Unnecessary Graphs

The dashboard should provide useful information. It should make sense at a glance. Ultimately, it's for your use. Track what you want on the dashboard, but avoid unnecessary graphs. Pick those key pieces that you want and show those. Change it as your needs change. Experiment and have fun.

I think I've reached the end of this series. I could keep tweaking and adding additional functionality to the word count tracker but that's more time that I'm not spending writing! I plan to use the tracking spreadsheet to get back on track with my own writing streak.

Share questions and thoughts in the comments. If you'd like to get emails from me with new content, please sign up for my newsletter.

Posted in Instruction.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.