none
Excel pivot table column value fill down

    General discussion

  • I recently had a user request a method for filling down values in an Excel pivot table. 

    I couldn't find an option for changing the layout of a pivot table within Excel that would accomplish this. 

    I did find a procedure in another forum:

    http://www.mrexcel.com/tip004.shtml

    ...that outlined a method to accomplish this.  It was a pretty good solution, but my users would prefer to not have to follow those procedures repeatedly as they format their tables.  So I composed a macro that will ask for input regarding the pivot table and then copy it over to a new tab with a filled down version. 

     

    The following is a novice Excel user level rewrite of the procedures I found in the above forum post followed by the code from the macro I composed.  I know the macro might not follow VBA best practices, but it works. 

    Hopefully one or the other or both will be helpful to someone else. 

    Cheers

     

    ******************************************************************************

    The following is a procedure to "fill down" values in pivot table columns.  You can't do it while the data is in the pivot table.  You have to move it out and then fill down.  So you need to get your pivot table exactly as you want it and then copy the data into a new sheet. 


    Your first time through this procedure it would be best to just read the names of the steps just to get an idea of what we're trying to accomplish at each stage.  I have the steps broken down into pretty good detail, but once you're familiar with the procedure it really is just 5 easy steps.  It's not a push button solution, but if you want to accomplish this in Excel it is the best available method. 

     

    Step #1. Copy your pivot table into another sheet.
    Highlight the entire row of your pivot table header (not the filter fields).
    Hold <shift> and hit <Page Down> until you have selected all the rows in your pivot table. 
    Hit ctrl + V
    Hit alt+E   (puts you in the Edit menu)
    Hit S  (opens the Paste Special dialog box)
    Hit W   (selects "Column Widths")
    Hit <enter>  (or you could just click OK)
    This pastes the values from these formulas along with the column widths, which should make it look entirely the same as your pivot table source.

     

    Step #2. Select the data you want to fill down.
    Let's say you have 2 columns on the left side of your newly pasted table (formerly a pivot table) that need to be filled in.  You need to select from cell A1 (or wherever your pivot table column headings start) all the way down to cell B999 (or whatever is the last row of your pivot table).  This selects the block of column data you want to have filled in with values. 

     

    Step #3. Selecting only the blank cells in your pivot table.
    Hit ctrl+G  (Ctrl G brings up the GoTo dialog)
    Hit alt+S  (alt+S will pick the "Special" button from the dialog box)
    Hit K   (Hit "K" to pick blank cells)
    Hit <enter>  (or you could just click OK)
    You should now have selected all of the blank cells in the pivot table columns. These are all of the cells which you want to fill in with values.

     

    Step #4.
    Hit the equals key.   (starts entering a formula)
    Hit the Up arrow.   (basically says, "I want this cell to be just like the cell above me.")
    Hold down Ctrl and hit enter.   (basically says, "Enter this same formula in every selected cell)
    Now you have formulas in the place of blanks, but that's not the same as values so we move on to step 5. 

     

    Step #5 Change all of those formulas to values.
    Select all of the cells in A1 through B999 again, not just the blanks.
    Hit ctrl+c to copy this range.
    Hit alt+e   (puts you in the Edit menu)
    Hit S  (opens the Paste Special dialog box)
    Hit V   (selects "Values")
    Hit <enter>  (or you could just click OK)
    This pastes the values from these formulas.

     

    Your new table is now complete with no blanks. 

     

    ******************************************************************************

     

    Sub PivotFillDown()
    '
    ' PivotFillDown Macro
    '
    '   initialize variables with user input and calculations
    Dim sourceSheet As String
    Dim targetSheet As String
    Dim leftColumn As String
    Dim topRow As Integer
    Dim headerCount As Integer
    Dim rightColumn As String
    Dim rightColumnFill As String
    Dim bottomRow As Integer
    Dim footerCount As Integer
    Dim rangeObj As Range
    Set rangeObj = Application.InputBox( _
        prompt:="Please select the top, leftmost cell in your table.", Type:=8)
    headerCount = Application.InputBox("How many header rows do you have when you start there?", Type:=1)
    leftColumn = Mid(rangeObj.Address, 2, InStr(2, rangeObj.Address, "$") - 2)
    topRow = Mid(rangeObj.Address, InStr(2, rangeObj.Address, "$"))
    Set rangeObj = Application.InputBox( _
        prompt:="Please select the bottom, rightmost cell in your table.", Type:=8)
    sourceSheet = ActiveSheet.Name
    targetSheet = sourceSheet & "-Fill"
    footerCount = Application.InputBox("How many footer rows do you have when you start there?", Type:=1)
    rightColumn = Mid(rangeObj.Address, 2, InStr(2, rangeObj.Address, "$") - 2)
    rightColumnFill = UCase$(Application.InputBox("What is the rightmost column you want to fill down?", Type:=2))
    bottomRow = Mid(rangeObj.Address, InStr(2, rangeObj.Address, "$"))
       
    '   copy table first row to new sheet
        Range(leftColumn & topRow & ":" & rightColumn & topRow).Select
        Selection.Copy
        Sheets.Add.Name = targetSheet
        Range(leftColumn & topRow).Select
        ActiveSheet.Paste
        Sheets(targetSheet).Select
        Application.CutCopyMode = False
    '   copy table minus first row to new sheet
        Sheets(sourceSheet).Select
        Range(leftColumn & topRow + 1 & ":" & rightColumn & bottomRow).Select
        Selection.Copy
        Sheets(targetSheet).Select
        Range(leftColumn & topRow + 1).Select
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveSheet.Paste
    '   select cells in columns in new sheet for fill down
        Range(leftColumn & topRow + headerCount + 1 & ":" & rightColumnFill & bottomRow - footerCount).Select
        Range(leftColumn & topRow + headerCount + 1).Activate
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Application.CutCopyMode = False
        Selection.FormulaR1C1 = "=R[-1]C"
    '   select the new sheet table and copy/paste again to convert formulas to values
        Range(leftColumn & topRow & ":" & rightColumn & bottomRow).Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        ActiveWindow.ScrollRow = topRow
    End Sub

     

     

    Wednesday, August 13, 2008 5:25 PM

All replies

  •  

    Hi ,

    Thanks for this informative post .

    While looking this post we can not found any questions or Issues involved .

    Therefore we are marking it as Answered .

    Thanks for contacting Microsoft Innovate on office .

     

    Thanks ,

    Uday

    Wednesday, August 13, 2008 8:02 PM
  • Hi Pole,

     

    Much appreciated your help to fill down the values. i have tried the shortcut method rather than using macro. but excllent one. i don't have words to thank you.

     

    thanks a million.

     

    cheers

     

    Friday, November 21, 2008 5:20 AM
  • Many Many thanks I have been looking for a way to do this for ages to allow further use of pivot table data for sorting based on grand totals whilst maintaining the information in Left hand columns!
    Thanks!

    Friday, September 25, 2009 9:44 AM