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

Updated on January 14, 2020
Joshua Crowder profile image

Joshua has work experience in manufacturing, distribution, and aerospace. He received his BBA in accounting from Kent State University.

A PivotTable Graph

This is a graph of my quarterly HubPages earnings since started earning in 2019. Having a graph to look at give me chance to reflect on how far I have come to get to this point.
This is a graph of my quarterly HubPages earnings since started earning in 2019. Having a graph to look at give me chance to reflect on how far I have come to get to this point.

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.

Graphing HubPages Earnings Data in a PivotTable

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.

Getting the Earnings Report

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.

Access the Earnings Report

It's not immediately clear where your earnings report is located. Remember to click on the filter option to get access to a downloadable .csv file that holds all of your earnings data.
It's not immediately clear where your earnings report is located. Remember to click on the filter option to get access to a downloadable .csv file that holds all of your earnings data.

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.

Export an Earnings 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.

Save .CSV as an Excel Workbook

Saving your file as an Excel workbook will help you gain functionally functionality with your spreadsheet when working with your data in Excel.
Saving your file as an Excel workbook will help you gain functionally functionality with your spreadsheet when working with your data in Excel.

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.

Clean the Data

Notice that my data is blurred. This data represents my daily earning for 2018 and 2019. The image is blurred to keep me from disclosing earnings data to the public.
Notice that my data is blurred. This data represents my daily earning for 2018 and 2019. The image is blurred to keep me from disclosing earnings data to the public.

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.

Select the Data

It's not mandatory to select the range of data to be used in the PivotTable in this step. This can wait until the create PivotTable window appears where the select a Table/Range option can be utilized.
It's not mandatory to select the range of data to be used in the PivotTable in this step. This can wait until the create PivotTable window appears where the select a Table/Range option can be utilized.

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.

Generating a PivotTable

Note that if you have many columns of data, you may want to choose the option that allows you to create the PivotTable and chart in a new worksheet. This could help to keep your workbook organized.
Note that if you have many columns of data, you may want to choose the option that allows you to create the PivotTable and chart in a new worksheet. This could help to keep your workbook organized.

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.

PivotTable & Field List Displayed

Here a PivotTable is displayed in it rawest form. It won't be until attributes are added to each axis until the table or graph form.
Here a PivotTable is displayed in it rawest form. It won't be until attributes are added to each axis until the table or graph form.

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.

Adding Data to the PivotTable

PivotTables need to be linked to data in the pivot chart fields section. Data here can be manipulated in various ways to design a chart right for your analysis.
PivotTables need to be linked to data in the pivot chart fields section. Data here can be manipulated in various ways to design a chart right for your analysis.

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.

Drilling Down on the Dates

Since what I have earned per year is irrelevant, it makes since for me to drill down on the data by looking at each quarter. I could also look at the data in other views such as monthly.
Since what I have earned per year is irrelevant, it makes since for me to drill down on the data by looking at each quarter. I could also look at the data in other views such as monthly.

Analysis

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.

Final Results

By the way things are going, my HubPages earnings will continue to rise as long I can keep adding content on consistent basis and make updates to older articles to keep them relevant.
By the way things are going, my HubPages earnings will continue to rise as long I can keep adding content on consistent basis and make updates to older articles to keep them relevant.

Other Scenarios

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.

More to Come

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.

The Excel 2019 Bible

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2019 Joshua Crowder

Comments

    0 of 8192 characters used
    Post Comment
    • Joshua Crowder profile imageAUTHOR

      Joshua Crowder 

      7 months ago from SE United States

      Thanks! I think everyone should see how they have improved or set a benchmark for improvement.

    • SANJAY LAKHANPAL profile image

      Sanjay Sharma 

      7 months ago from Mandi (HP) India

      Nice idea. Thanks for sharing the information.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://maven.io/company/pages/privacy

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)