Pivot Tables and Dashboards - End user needs to start with a empty Dataset possibly.... RRS feed

  • Question

  • I want to develop a spreadsheet for an end user(s) where they will have to enter their data into the data source on a sheet that I will create with predefined fields.

    I also want to have a dashboard set up for them that tracks certain aspects of their business.

    However my understanding of pivot tables/dashboards is that in order for the pivot table to work data must always exist in the table before the pivot table can display the data.

    Since the end user will not be proficient with Excel and know how to create pivot tables and dashboards, is there anyway I could set all of this up and deploy an empty datasource so when the user opens the workbook all they have to do is enter data into the datasource sheet and then the pivot table(s) are created for them and the dashboard is created and displayed on a separate sheet with the appropriate graphs/slicers, etc?

    If this can only be done through VBA, are there any examples you can point me to so I can then modify it to meet my needs?

    Or, would it just make it easier to deploy the workbook with just one row of data in the table with example data and then just instruct the end user to start with that row and modify it with their data?


    Keith Aul

    Friday, June 17, 2016 12:38 PM

All replies

  • With VBA you can do it either of the two ways you suggested. Let the user enter the data and then click on a button, "Display result" or the like. When clicking the button it starts VBA code that builds the pivot tables and (possibly) create a new sheet where the result is displayed.

    How this VBA code should be composed is impossible to tell from your description but here is some bare-bone VBA code to create a pivot table:

     Dim PivotSource As Range
     Dim PivotTablePos As Range
     Dim pc As PivotCache
     Dim pt As PivotTable
     Dim pi As PivotItem

      Set PivotSource = Range("<your range>")
      Set PivotTablePos = Range("<your position for the Pivot table>")
      Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, PivotSource)
      Set pt = pc.CreatePivotTable(PivotTablePos, "<your header>"))
      With pt
       .PivotCache.MissingItemsLimit = xlMissingItemsMax 'Allow fields to be missing
        '* Add the columns you want
        .AddDataField pt.PivotFields("<your column>")), "<your column>", xlCount  'Same name as the data field
        .AddDataField pt.PivotFields("<your column>")), "Minimum", xlMin
        .AddDataField pt.PivotFields("<your column>"), "Maximum", xlMax
        .CompactLayoutRowHeader = "<your label>"
    End With
      '... etc ...

    Detailed info here:

    Best regards, George

    Friday, June 17, 2016 1:07 PM
  • keith

    When you close the worksheet you could have a worksheet event (beforeclose) to clear the underlying data- clearing rather than deleting won't shrink the pivot table range.

    Then you could click the refresh on open button in the pivot table options-

    hey presto totally blank when user opens up file

    Friday, June 17, 2016 2:41 PM
  • Let me clarify what you just said.

    So I create the pivot tables and dashboard against a datasource table that just has 1 row of data in it.  I create the pivot table(s) and dashboard/slicers/charts.

    Then I create a worksheet event called beforeclose to clear the data when the worksheet closes. Are you saying there is a 'clear' function that will clear the data in each cell in the row, but it won't actually delete the row?

    If I do this, then the pivot table(s) will just show no data but will keep intact the pivot table fields and pivot table areas intact. Correct?

    So when I reopen(pivot tables get refreshed) the file, there will be no data in the table but the pivot tables and dashboard are intact.Correct?

    My one question with the above is, if I leave the beforeclose event intact and give it to the user, then this would mean that everytime they insert new data into the table and then close the file it will clear all that data? Correct? If so this is not what I would want. I want to preserve all their data that the end user enters.

    Keith Aul

    Friday, June 17, 2016 5:33 PM
  • My advice is to NOT make it automatic by any means but let the user decide! Add to that: Education and you're home!

    Best regards, George

    Friday, June 17, 2016 5:36 PM
  • Keith

    I thought you wanted it to be blank every time they open it-

    So you want to give them a template that they populate over a period of time- you want to pivot tables graphs etc to grow as the data source grows.

    Really easy

    Set every thing up with sample data for you- Source table, pivots and graphs linking to it etc.

    Make the table source a table- by highlighting the range- including headings- then insert & table.

    You can now delete all rows on the table- except header- could leave top one with sample datas well for user to over write if you like-

    note highlight the blue area (Table) not the row on the spreadsheet (White) behind.

    Then as before get the pivot to refresh on open- you might need to work on you graphs and possible use a pivot graph- depending on type of info put in and what you wnat to show.


    • Proposed as answer by David_1234 Monday, June 20, 2016 12:45 PM
    Monday, June 20, 2016 8:54 AM
  • Lots of variables that could drive the exact solution. I'll give you what I think is the least complex based on the least complex design requirement:

    • Create a one row table, header and one row of data.
    • Format as a table so the range grows as data is entered
    • Build your pivot charts/tables/slicers whatever.
    • Record a macro that refreshes the pivot table.
    • Add a button that calls the macro.
    • Save the file and distribute as appropriate.

    Users enter data and hit the button when they want the outputs refreshed.

    If the users need a blank sheet each time and don't need to save the previously entered data, you could do the 'clear on save' thing.

    If they want/need to save the entered data each time, then they'd either append to the file they're using or pull up the 'blank' file each time.

    Monday, June 20, 2016 3:55 PM
  • ehhh sounds familiar


    Monday, June 20, 2016 3:59 PM
  • This is what is happening if i try to leave one row in the table and have no data in it.

    The pivot tables/dashboard will show 'Blank' as one of the items. If I clear the data in the source table on the sheet then the pivot tables will show 'blank' as you can see from the attached image. Then when I add values to the row and do the refresh it still shows 'blank' as one of the values plus it shows the values I entered.

    How can I prevent the blank showing up so the user doesn't have to deal with 'blank' as one of the values or for the option on the slicers?

    Keith Aul

    Thursday, August 4, 2016 4:03 PM
  • Hi Keith,

    >> How can I prevent the blank showing up so the user doesn't have to deal with 'blank' as one of the values or for the option on the slicers?

    To prevent blank showing, you could try Right Click Pivot Table-> PivotTable Options->Layout&Format->clear the check box of For empty cells show. You could refer the link below for more information.

    # Change how blank cells, blank lines, and errors are displayed

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 5, 2016 5:40 AM