Supposed we have a named range “ Data ”, but the reference is “ =1 ”. Ensure that the reference for the named range is valid. Output: Pivot table created with the range “Data” Creating a pivot table with a named rangeįigure 12. We can now successfully create a pivot table using this range.įigure 11. Click the Refers to bar and select cells B2:E6 to define the range “Data”. In the Name Manager dialog box, click New. Creating a named range using Name Manager Click the Formulas tab, then select Name Manager. We first define a named range before we use it to create a pivot table. The error naturally exists because we tried to create a pivot table basing the values on the range “ Data ”, when in fact, the range does not exist and is not defined. Error in creating a pivot table with a named range The “ Data source reference is not valid. In the Create PivotTable dialog box, enter “ Data ” into the Table/Range bar. Select cell G2, then click the Insert tab. Let’s use below data and create a pivot table. When inserting a pivot table with a named range, make sure the range exists and is defined.Save the Excel file to the computer or local drive.Renaming the Excel file to remove square brackets Remove the brackets by renaming the file to “ Correcting the Source Reference not Valid Error in a Pivot Table.xlsx”.įigure 3. Note the square brackets in the word Correcting. Error in creating a pivot tableĪs shown, the file name is “ the Source Reference not Valid Error in a Pivot Table.xlsx”. When we try to insert a PivotTable, an error is displayed and the pivot table is not created.įigure 2. If it contains any square brackets, rename the file and remove the brackets.īelow is an example of a simple table with range B2:E6. The data source refers to a named range with invalid referenceĬhecklist to Correct the Source Reference Not Valid Error.The pivot table data source refers to a non-existent range.File comes from the email or on the web and is not saved in the local drive.Excel file name contains the characters “ ” or square brackets.Most probably, the reason for the error is one or more of the following: “Data source reference is not valid” error message in Excel When working with pivot tables, we sometimes encounter the error “Data source reference is not valid”.įigure 1. Correcting the Source Reference not Valid Error in a Pivot Table
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |