Create a Graph in Microsoft Excel to Analyze Your Earnings Progress on HubPages

I wanted to take the time to show my fellow HubPages writers other ways to look at earnings data. I'm starting with this simple explanation and if I can get some feedback, I'll add some more content related to graphing earnings data directly to this article.

Generally speaking, a PivotTable is great for looking at data in different dimensions. PivotTables are usually more worth wild when there are lots of attributes involved, but it's a nice quick way turn your HubPages earning data into a table with a corresponding bar graph.

The first step to creating this PivotTable for HubPages data is to gather the needed data. The earnings data can be found by clicking earnings in the side bar on the HubPages site or by clicking the earnings reports tab under the earning section. Either method will take you to the correct page.

In this next screen you will need to find the word filter on the page. Click this filter option so you can receive access to a downloadable earnings report.

Next, click the first drop down shown below and choose all programs. Click in the next drop down below and select all time. These two settings will allow us to receive all data relevant to daily sales amounts. Click the export as .csv button to download the report.

Immediately after the report is downloaded it needs to be saved as an Excel workbook because the .csv file format has some reporting limitations. To save the file go to save as, choose place on your computer to save it, save it as an Excel workbook and click the OK button. Each step can be found in the illustration below.

When you open the file click on one of the cells and press Ctrl + End. This will take you to the last row. To clean the data delete the last totals row. Next, select the last row of data and press Ctrl + Shift + ↑. This will select all of your data.

At this point, all the data should be highlighted. Select the insert tab and click on the PivotTable button that is located in the chart section. You will have two options. Choose the option PivotChart & PivotTable.

A create PivotTable window will appear. Click on the bubble for existing worksheet and the click in a cell where you want the PivotTable to appear. The location of the cell chosen will show up in the box below that is labeled with the number 2. Finally, click OK to generate a PivotTable and graph.

A blank PivotTable chart and field list will appear. If the field list does not appear, go ahead and click on the button labeled field list.

With the left button on the mouse held down, drag the date word from top of the PivotTables fields area to the categories box. Additionally, drag ad program down to the values box.

I'd like break these years into quarters to have a better understand of how my earning have increase incrementally. By clicking on the boxes with the the plus signs on the left side of the PivotTable, the PivotTable and graph will drill down into quarters.

It hard to properly analyse the graph without the totals for each bar. Unfortunately, the numeric data needs to be blurred. It is obvious that I made earnings in each quarter since I started writing articles. This can be seen by the incline in bar heights for each quarter. Each quarter earnings grew except for the current quarter (4th quarter 2019) because I still have a months worth of earnings to collect before the new year. The low 2019 4th quarter may also be attributed to the fact that my article views are normally the lowest at this time of year.

In the example used above, ad program revenue was used by dropping that text into the values box in the pivot chart fields section. The revenue shown in the graph can be easily changed to show Amazon revenue or total revenue. This can be completed by right clicking on the arrow to the right of sum of ad program and selecting remove field. Next, one of the other revenue variables can be dropped into the values section.

Be sure to let me know what you think about this article. Soon I will add some more topics here to show what other graphs are relevant for this kind of data if anyone is interested.

To learn more about building graphs in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.